x

How do I output the results of a simple SQL statement to a text file?? PART2

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                     

more ▼

asked Nov 17, 2009 at 07:21 AM in Default

avatar image

Andy Watts
2 2 2 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Nov 17, 2009 at 07:46 AM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left

Note that the file will be created in Server's directory and may not be in your system

more ▼

answered Nov 17, 2009 at 07:39 AM

avatar image

Madhivanan
1.1k 2 5 9

(comments are locked)
10|1200 characters needed characters left

Well. You dont need three dots.

Try this

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'             
EXEC master..xp_cmdshell @cmd            
more ▼

answered Nov 17, 2009 at 09:38 AM

avatar image

Madhivanan
1.1k 2 5 9

(comments are locked)
10|1200 characters needed characters left

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:

set nocount on

select * from $(TABLE_NAME);
go

Then you could run SQLCMD like this and this would output the query to a file c:\temp\yourmom.log:

sqlcmd -S sqlpdmp\pdm_p -E -i c:\temp\myscript.sql -o c:\temp\yourmom.log -v TABLE_NAME="syscolumns"            

Option 2: This does the same as above but does require that the SQL script be in place:

sqlcmd -S yourserver -E -o c:\temp\yourmom.log -Q "set nocount on select top 10 * from sysobjects"            
more ▼

answered Nov 17, 2009 at 03:13 PM

avatar image

Jason Cumberland
507 1 2 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x429
x49

asked: Nov 17, 2009 at 07:21 AM

Seen: 7681 times

Last Updated: Nov 17, 2009 at 07:43 AM

Copyright 2016 Redgate Software. Privacy Policy