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

Question

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

Answer 1

0

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

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 '09 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:pathbcp.exe" in your command. I haven't got a SQL2005 box handy, but it may be something like "X:Program FilesMicrosoft SQL Server90ToolsBinn"

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

(comments are locked)

Answer 2

0

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

Madhivanan
1.1k ● 1 ● 2 ● 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 '09 at 07:49 AM Andy Watts

(comments are locked)

Answer 3

0

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

answered Nov 17 '09 at 09:38 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

I have tried running this both at a command prompt from 'C:Program FilesMicrosoft SQL Server90ToolsBinn 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 '09 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 '09 at 02:09 PM Kristen ♦

(comments are locked)

Answer 4

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"

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

Follow this question

Related Questions