BCP Syntax error??? 1

[TimBiesiek]

Hi all,

Am running a BCP command from within a stored procedure. However, it ends up having the process blocked, and locks up certain tables in the DB until the process is killed.

I have tried removing the BCP command and running it from the Query Analyzer, but it says there is a syntax error near 'Queryout'.

Below is the command I am using:

bcp "SELECT * FROM mitrecoredata..tmpViewTMSExport" queryout "\\msc\mscdata\rhd\Imports\DRQ20050929 TEST ONLY.TXT" -U sa -P testserver -c

The tmpViewTMSExport is a temporary view I have created, just before running the BCP command...

Any ideas?

 

[mrdenny]

BCP isn't a command that you can run from Query Analyzer. It is a dos command that should be run from the command prompt. If you need to run it from a procedure (which isn't recommended) you need to run it via the xp_cmdshell.

Try putting with (nolock) within your view definetion and on your select statement. That will force dirty ready and may make it work better.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[TimBiesiek]

Hi Denny,

Yip, I just worked it out before you replied. That's exactly what it was. I wasn't running it with the Exec xp_cmdshell....

Cheers!

 

[TimBiesiek]

Hi again mrdenny,

I am now able to run the BCP command in query analyzer successfully, but as soon as I try running the exact same command in a stored procedure it seems to crash the service. Have to stop and start the Service again, before I can open the table that the SP is BCP'ing from...

Any thoughts?

I see you mention that it isn't recommended to run BCP from a procedure, but I've found plenty of evidence to suggest that it SHOULD work without a hitch. I had this running fine yesterday, but this morning it's not... Only a few minor changes to the code, none of which effect this BCP command...

 

[TimBiesiek]

Have worked it out again!

Turns out that BCP doesn't appear to like running within a transaction.

Have moved the Commit or Rollback Transaction code to above the Exec BCP command and now it works fine!