April 17, 2009 at 9:52 am
Upgrade to 2005. 😛
You are probably going to have to do this row-by-row. :sick:
I can probably find some time later today to come up with a rough idea if you want.
Sean
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2009 at 10:06 am
How many rows are we talking about? And how often do you need to run it?
Can you link the 2000 servers to the 2005 server?
Also the five columns you clear in the output ('Company Name', 'Database Company ID' , 'Office Number', 'Office Name' , 'Office ID' ) is this always all or nothing scenario, or some of them are cleared while the others are not?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 17, 2009 at 10:10 am
slange (4/17/2009)
Upgrade to 2005. 😛
LOL that is the same thing I told the guy who I wrote the script for when he told me about the error. 😛 Where I work we have clients on 2000,2005, and now 2008 servers, what a pain in the a$$! :hehe:
If you have to spend a bunch of time on it then dont worry about it. I was just hoping it would be an easy fix, but it's not sounding like it.
Thanks again for all the help! 😀
April 17, 2009 at 10:14 am
JacekO (4/17/2009)
How many rows are we talking about? And how often do you need to run it?Can you link the 2000 servers to the 2005 server?
Also the five columns you clear in the output ('Company Name', 'Database Company ID' , 'Office Number', 'Office Name' , 'Office ID' ) is this always all or nothing scenario, or some of them are cleared while the others are not?
Jacek, unfortunately no, we cant link the servers. Row count in the results it typically less that 500, and I'm hitting static tables with only a few thousand rows in them, so efficiency is not super critical for this one. As for the columns you listed, yes they are all or nothing.
Thanks for the reply.
Josh
April 17, 2009 at 10:18 am
Basically, I would write a cursor to loop through all the rows. Do a control break type logic such that you stick the AccountName in a variable. Start by initializing a @PrevAcctName variable to ''. then each record if the current AccountName = @PrevAccountName output it. I can throw together a quick and dirty one pretty quickly. It sounds like this is something you will have to run frequently? the performance of this type of solution is not fast. But if it just for reports or something it may be acceptable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2009 at 10:24 am
OK,
I think you might be able to pull it off by using temp table. Create a temp table with an identity field, a bit field and all your other fields. Load the data into temp table sorted the way you want it exported. Do a self join on the table to update the bit field to 1 for each max identity per Company. Then export your fields where the bit is 1 otherwise have them as empty strings.
I hope I explained the steps clearly.
If I have more time later today I can try to code it using the sample data with one column.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 17, 2009 at 10:33 am
Thanks, Jacek. I'll ive the temp table a shot.
April 17, 2009 at 10:34 am
CREATE TABLE test
(
Accountname VARCHAR(10),
Booknumber INT,
Bookname VARCHAR(20)
)
;
GO
;
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 1 ,'A1')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 2 ,'A2')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 3 ,'A3')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 4 ,'A4')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreOne', 5 ,'A5')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 1 ,'A1')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 2 ,'A2')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 3 ,'A3')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 4 ,'A4')
INSERT INTO test (Accountname , Booknumber, Bookname) VALUES ('StoreFive', 5 ,'A5')
;
GO
;
CREATE TABLE #tmp
(
IDINT IDENTITY,
CanPrint BIT DEFAULT(0),
Accountname VARCHAR(10),
Booknumber INT,
Bookname VARCHAR(20)
)
;
INSERT INTO #tmp
(
Accountname,
Booknumber,
Bookname
)
SELECT
Accountname,
Booknumber,
Bookname
FROM test
;
UPDATE #tmp
SET
CanPrint = 1
FROM #tmp T1
WHERE ID = ( SELECT MIN(ID)
FROM #tmp T2
WHERE T2.AccountName = T1.AccountName)
;
SELECT
CASE
WHEN CanPrint = 1 THEN Accountname
ELSE ''
END AS Accountname,
Booknumber,
Bookname
FROM #tmp
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 17, 2009 at 10:39 am
You're the man, thanks! I'll create the new script using your suggestion above and let you know how it goes. THANK YOU!
April 17, 2009 at 10:43 am
Lucky you my lunch buddy is home sick today so I am having it at my desk.
Gave me the extra time to play with the code. 😀
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply