BCP, OSQL, sp_OACreate and DTS performance

Hi,
I'm looking at ways of creating a text file from a SQL Server 2000 table.
In terms of performance does anyone know how  BCP,  OSQL, sp_OACreate and a DTS package with some VBScript creating FileSystemObjects   compare when it comes to performance.  i.e.  what's quick and what's slow.

fearlessfishAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
If BCP will do the job, it's about the quickest option. It might not always be the easiest in terms of creating the exact output format you want, but in terms of actually extracting the data, I don't think any of the others will beat it. In terms of simplicity of programming, a DTS package is pretty easy to create. Personally, I wouldn't bother with sp_OACreate (presumably using the FileSystemObject is what you meant) if one of the other approaches will do the job, nor would I worry about OSQL - if you're going for a command line tool anway, use BCP.
0
fearlessfishAuthor Commented:
Thanks muzzy. I assume you've used BCP before. I've had a go today but couldn't get it working. Any idea what permissions I need to set up to get it to work ??

0
muzzy2003Commented:
You need to run it logged on as a user who has permissions to create the output file, and then this login either needs to be one who has permissions to access the database in question using Windows authentication (bcp parameter -T), or you need a valid SQL Server login that you can specify on the command line (bcp parameters -U and -P). What problems are you getting? If you post what you are typing on your command line I could have a look at it for you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fearlessfishAuthor Commented:
Muzzy,
My command looks something like:
bcp "exec myDB..sp_getTextFileInfo 42" queryout "c:\myfolder\File42.txt" -c -U TestUser -P test

I'm running it from a trigger (which I know may not be a good idea).    
myDB   is my database      
sp_getTextFileInfo   is a stored proc. which outputs an XML string to put in the file
TestUser   is a sql login given db_owner permissions on myDB

The command works okay from the OS command line.  
0
fearlessfishAuthor Commented:
Update:  I've created an extended stored procedure in C++ and called this instead. I think it's probably the quickest solution.

0
fearlessfishAuthor Commented:
Muzzy - I've given you the points anyway for your input. Thanks.
0
muzzy2003Commented:
No problem. Sorry not to reply quicker on your BCP syntax - I'm in the UK, answering mostly outside of working hours. Glad you got it all working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.