BCP Error

Hi there,

Does anyone know why this won't work:

bcp "SELECT DISTINCT Label From GMS_48hrAccess..tbl_SurgerySlot" queryout "c:\Labels.txt" -c -S@.@.SERVERNAME

It gives me the following error message:

Server: Msg 170, Level 15, State 1, Procedure sproc_ExportLabels, Line 9
Line 9: Incorrect syntax near 'bcp'.I'd expect a "SQL Server does not exists or access denied" errormessage but yours even suggests an error in a procedure. I really wonder how the sp comes into play, I'd suggest you remove the @.@.Servername and change it into the name of the server instead. @.@.Servername is not (directly) available from a command-prompt.|||Thanks , but I've already tried inserting the server name directly with/without quotation marks and it still doesn't work...I can get it to work if I do the following:

DECLARE @.cmdshell varChar(255)

SET @.cmdShell = 'bcp "SELECT DISTINCT Label From GMS_48hrAccess..tbl_SurgerySlot" queryout c:\Labels.txt -c -S' + @.@.SERVERNAME

exec master..xp_cmdshell @.cmdShell

However, this only seems to work on SQL Server 2000 and I need it to work on SQL Server 6.5 as well....hence why I was trying to get the previous to work.|||That could explain why the errormessage indicates a fault in the sp: you're running it from the sp. I have a case where @.@.servername is NULL btw, don't know how bad that is, but concatting a null to var/char usually results in a null var/char. The parameter -T might also help authenticating. Other than that, I don't see what's wrong but I don't know much about 6.5.|||You need to indicate if it's a trusted connection -T, or supply the user name and the password...|||That's an SQL error message. BCP is intended to be executed from the Windoze command prompt, not from within Query Analyzer. Where are you trying to execute that command?

-PatP|||Thanks Pat, that explains it! So my xp..cmdShell example is the correct way to do it, any ideas why this wouldn't work on a 6.5 server?|||Thanks Pat, that explains it! So my xp_cmdShell example is the correct way to do it, any ideas why this wouldn't work on a 6.5 server?|||A lot would depend on what error the 6.5 machine coughed up when you tried to run it... For one thing, the queryout parameter wasn't supported on 6.5, and it was a lot touchier about authentication so you'd have to provide /U and /P values.

-PatP

bcp

Any ideas why this wont work?
bcp {Pic_Count_Import} in \\DATASRVR\InetPub\wwwroot\images\Pic_Count.txt -q -t "," -SDATA -U"aa" -P"blah"
Thanksdrop the squirrelly brackets around the table name and the quotation marks around the user name and password.

Generally, when I use bcp I explicitly state the table in the form <database name>.<owner name>.<table name>.

What error message or undesired results are you getting?|||I changed it to this
bcp dbo.Pic_Count_Import in \\DATASVR\InetPub\wwwroot\images\Pic_Count.txt -q -t "," -SDATA -Uaa -Pblah

Still I get syntax error near '.'

Originally posted by Paul Young
drop the squirrelly brackets around the table name and the quotation marks around the user name and password.

Generally, when I use bcp I explicitly state the table in the form <database name>.<owner name>.<table name>.

What error message or undesired results are you getting?|||from books online, bcp utility, overview:

-q

Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL Server. Use this option to specify a database, owner, table, or view name that contains a space or a quotation mark. Enclose the entire three-part table or view name in double quotation marks (" ").

I would suggest dropping the -q argument. Try it and let us know.|||Same

Originally posted by Paul Young
from books online, bcp utility, overview:

-q

Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL Server. Use this option to specify a database, owner, table, or view name that contains a space or a quotation mark. Enclose the entire three-part table or view name in double quotation marks (" ").

I would suggest dropping the -q argument. Try it and let us know.|||what happens if you add the database name?|||Same..

Originally posted by Paul Young
what happens if you add the database name?|||Ya know what, this may be a stupid question.. can you use bcp ina stored procedure?

That is where I am doing this..

preceded with EXEC bcp....

or should I be using BULK INSERT ?

not sure what the differences are?|||bcp is a command line utility. You can run bcp within a stored procedure but you need to use xp_cmdshell. You should use the bulk insert tsql statement instead - also have you looked at using the bulk insert task in dts ?|||Also know that bcp can import/export while bulk insert can only copy a data file in sql server (one-way only)