Yesterday I submitted a question asking how I could output the results of a simple SELECT query to a file in SQL Server 2005 and RICKD answered with the following:
"You want BCP, something like:
DECLARE @cmd VARCHAR(8000) SET @cmd = 'bcp "...Select from ..." queryout' +' "D:\QuoterQuoteDetails.txt" -c -T' +' -SLOCALHOST"' EXEC master..xp_cmdshell @cmd, NO_OUTPUT
Should do the trick."
If I understood the advice correctly I produced the following which executes successfully however it still doesn't produce a file output does anyone have any other ideas?
(I did have to enable the xp_cmdshell option in the 'SQL Server Surface Area Configurator)
DECLARE @cmd VARCHAR(8000) SET @cmd = 'bcp "...SELECT dbo.quoteproduct.quoteid, dbo.quoteproduct.productgroupid, dbo.product.productname, dbo.product.description, dbo.quoteproduct.price FROM dbo.quoteproduct, dbo.product WHERE dbo.quoteproduct.productid = dbo.product.productid..."queryout' +' "D:\QuoterQuoteDetails.txt" -c -T' +' -SLOCALHOST"' EXEC master..xp_cmdshell @cmd, NO_OUTPUT
My recommendation would be that you try the BCP command from the Command Prompt on the Server itself (or use Terminal Services to connect to it).
Any syntax errors in the command will cause it to fail, but you won't get much / any feedback executing it directly from SQL :( so doing it from Command Prompt will enable you to See and Fix those issues first.
Also note that this will run with the SQL Agent user's permissions (I think I've got that right?) so the PATH to BCP.EXE etc. may need to entered explicitly.
answered Nov 17 '09 at 07:46 AM
Note that the file will be created in Server's directory and may not be in your system
answered Nov 17 '09 at 07:39 AM
Well. You dont need three dots.
answered Nov 17 '09 at 09:38 AM
Is it a requirement to be able to run this from T-SQL or can it be done with a scheduled task (SQL Agent job or windows job)? There are some security vulnerabilities to using xp_cmdshell, most notably a SQL injection hole. Below are some example of ways to do this, though you could easily do this from an xp_cmdshell command as well. What is going to consume the text file?
Option 1: If you had a SQL script with this in it:
Then you could run SQLCMD like this and this would output the query to a file c:\temp\yourmom.log:
Option 2: This does the same as above but does require that the SQL script be in place:
answered Nov 17 '09 at 03:13 PM