Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

BCP, OSQL, sp_OACreate and DTS performance

Posted on 2004-11-14
7
Medium Priority
?
710 Views
Last Modified: 2008-02-01
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.

0
Comment
Question by:fearlessfish
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12579818
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
 

Author Comment

by:fearlessfish
ID: 12579857
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
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 750 total points
ID: 12579945
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:fearlessfish
ID: 12580544
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
 

Author Comment

by:fearlessfish
ID: 12584776
Update:  I've created an extended stored procedure in C++ and called this instead. I think it's probably the quickest solution.

0
 

Author Comment

by:fearlessfish
ID: 12584788
Muzzy - I've given you the points anyway for your input. Thanks.
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12587668
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question