November 18, 2009 at 11:47 am
Hi,
I have a stored procedure that takes about 10 seconds to run directly on the server in QA. The proc is a series of select statements and only returns about 10 - 20 rows.
If I use this same proc in a BCP queryout command, it takes 2.5 minutes to complete.
sproc command in QA (takes 10 seconds):
exec [MyDB].[dbo].[MySproc]
BCP command (take 2.5 minutes):
declare @sql varchar(200)
select @sql = 'bcp "exec [MyDB].[dbo].[MySproc]" queryout \\server\share\filename.txt -c -T'
exec master..xp_cmdshell @sql
I can see the filename.txt is created immedialtely with 0 bytes. 2.5 minutes later the resutls are written to the file and the file is closed.
Thanks for any help.
When running the BCP command in QA, the output shows:
NULL
Starting copy...
NULL
13 rows copied.
Network packet size (bytes): 4098
Clock Time (ms.): total 10625
NULL
November 19, 2009 at 10:38 am
More strange findings when using BCP:
The proc has a cursor. I added an audit log step to the proc so it inserts audit info to another table.
When I run the proc from Query Analyzer it runs as expected and logs data for each step of the cursor as expected.
When I run this using the above BCP command, it writes double records to the audit table:
audit record 1
audit record 2
audit record 1
audit record 2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply