I am having problems running bcp with queryout. I believe some of this stems from the fact that I also have the Sybase tools installed on the database servers and it in turn added its binn directory for the Sybase bcp.exe to the environment variables. Seeing this then, I would like to resolve this issue with a solution that does not rely on the order of environment variables.
Here is what is happening:
1) If I just try to run bcp on my server I get...
exec master.dbo.xp_cmdshell 'bcp ?'
/*
output -------------------
'bcp' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/
2) ...so, I add the full path to the location of the bcp program and this command works:
exec master.dbo.xp_cmdshell '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" ?'
/*
output --------------------------usage: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
*/
3) I then attempt a bcp out of a Northwin table... and this works as well
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" Northwind.dbo.Categories out C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output
------
NULL
Starting copy...
NULL
8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 31
NULL
*/
4) I then attempt a queryout and it fails, indicating that it no longer understand the long filename
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" queryout C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output --------------------
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/been doing some more testing on my own local SQL Server. It seems that even if the environment variables are all fine, if I fully qualify the bcp exe and include a query (with double-quotes) it fails. So, what I am wondering is, can one not fully qualify the bcp.exe and do a query at the same time?:
--the below works
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = 'bcp "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
--...while this does NOT work!
DECLARE @.cmdline VARCHAR(4000)
SELECT @.cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT
/*
output ---
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/|||try to
SELECT @.cmdline = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
EXEC master..xp_cmdshell @.cmdline--, NO_OUTPUT|||sorry, really not works
没有评论:
发表评论