November 12, 2007 at 2:29 pm
This is what is currently happening (ridiculous): Export data from database, T, using a gui that packages data into an Excel file. This often errors out if ALL data is extracted, so several Excel files are usually created.
Take Excel files and link them into Access.
Import from Access into SQL Server, C, tables where myself and other analysts are allow to create tables, etc, but not allowed to bcp. (Also, data contains commas so .csv files have been a problem in the past.) Combine these tables into one table.
Process takes about 4 1/2 hours.
I am thinking a dba or developer could easily create a dts package from an already existing stored procedure that the gui calls, or just schedule a stored procedure to move this data from T to C in the wee hours of the morning.
Problem I would like to solve is manager wanting these reports asap, but not being able to deliver them until after lunch.
Ideas? Input? My logic is flawed?
tia
November 12, 2007 at 2:39 pm
November 12, 2007 at 2:41 pm
mrpolecat (11/12/2007)
is T also SQL Server or another flavor of database?
Both are SQL Server 8.0.
November 12, 2007 at 2:47 pm
Well then a DTS package would work just fine to transfer the data directly between the 2 databases. Exporting to Excel is bad because Excel has a limitation of 65K rows ( except I think for the newest version). If you can't set up a DTS package then you should export to a delimted file such as comma, pipe, or tab. If your data contains all of these characters you can use quoted identifiers and if your data also includes quotes you can pick some other type of identifier.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply