Alternative to bcp 1

[MKVAB]

I've had a lot of issues with bcp in SQL Server 2005 - the data will often not sort correctly even when using the ORDER hint. Anybody know of an alternative to bcp?

thanks!
-MK

 

[SQLDenis]

elaborate please

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[MKVAB]

I'm looking for some other way to write the data set from a stored proc out to a file. I've always used bcp, and have never found or known of another utility.

We have users who pick up the files that our stored proc write out with their automated processes to load the data into their systems. There are often errors on their processing end because the header/trailer information isn't where it's supposed to be because of the sorting issue assocaited with bcp and SQL Server 2005. The header/trailer rows we create within the stored procedure often end up in the middle of the data, instead of the beginning (column headings) or end (data totals).

Before writing the data out using bcp, we can select from the temp table that holds the data, and it's sorted correctly, but when bcp gets ahold of it, it extracts the data in the table in a different order (occasionally). We've used the -h ORDER () hint and this doesn't reduce the occurence of the data being in the wrong order in the output file.

Soooo, I'm looking for a different utility to take the data from a temp table created by a stored procedure to write it out to a file.

Does that help?
Any ideas?
-MK

 

[SQLDenis]

>>Soooo, I'm looking for a different utility to take the data from a temp table created by a stored procedure to write it out to a file

what about SSIS

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[MKVAB]

Thanks SQLDenis.

I've actually been hunting for a download page for SSIS with no luck yet...

SSIS might not be an option for us though, as we may be moving away from SQL Server in the near future. So, implementing something that could be time consuming isn't really practical.

I think we might be stuck with bcp.

Thanks for your help!
-MK

 

[SQLDenis]

have you looked at Jitterbit?

http://www.jitterbit.com/

we are using it for some stuff and it is pretty cool
It is also free :-0

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions