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

Andy Watts gravatar 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

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

Thanks but when copy the last section of my message into the command prompt it attempts to execute each section seperately and returns the messsage "is not recognized as an internal or external command". Should I be trying to execute this via a batch file or something??
Nov 17, 2009 at 07:55 AM Andy Watts
I reckon you don't have the PATH set to where BCP.EXE is located. Change to that directory first, and then try to execute BCP. If that works then you can either add that to the PATH on that machine, or just use the long, explicit, "x:\path\bcp.exe" in your command. I haven't got a SQL2005 box handy, but it may be something like "X:\Program Files\Microsoft SQL Server\90\Tools\Binn"
Nov 17, 2009 at 08:03 AM Kristen ♦
Just type BCP /? at the command prompt. Change folder until that gives you the help screen! then at least you will know you are in the right folder :)
Nov 17, 2009 at 08:04 AM Kristen ♦
You got the path spot on. Is there any chance you can write the actual command that I would need to run because when I attempt to process 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'
+' -SLOCALHOST"'
EXEC master..xp_cmdshell @cmd, NO_OUTPUT"
From within this directory it doesnt work!
Nov 17, 2009 at 08:21 AM Andy Watts

Run this. You will be able to find the error if any

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
Nov 17, 2009 at 09:35 AM Madhivanan
(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

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

Thanks but I executed this script directly on the server and subsequently checked the server HD but no joy (I also checked just now to insure I wasnt being an idiot and its definately not there!)
Nov 17, 2009 at 07:49 AM Andy Watts
(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

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

I have tried running this both at a command prompt from 'C:\Program Files\Microsoft SQL Server\90\Tools\Binn\ directory, it still says DECLARE is not a recognised interal/external command, equally if I run this within the SQL Server 2005 query window it also fails with these errors: SQLState = 42S02, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'dbo.quoteproduct'.
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.NULL
I must be missing something simple!?
Nov 17, 2009 at 11:33 AM Andy Watts
Madhivanan's example is intended to be run from SQL. My comment (which starts "Try this from CMD prompt: ...") on my answer is suitable to run directly from Command Prompt
Nov 17, 2009 at 02:09 PM Kristen ♦
(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

Jason Cumberland gravatar image

Jason Cumberland
507 2

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1951
x374
x42

asked: Nov 17, 2009 at 07:21 AM

Seen: 6537 times

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