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