XP_CMDSHELL

Question

XP_CMDSHELL

sheik

SSC Eights!

Points: 800
More actions
March 7, 2010 at 6:32 am

#139271

Hi,
I am using XP_CMDSHELL in SQL 2005. If Run the below query then it creates the file in the specified folder
EXEC MASTER..XP_CMDSHELL 'BCP "select Employee_Code from db1.Table1_tmp" QUERYOUT "C:\Tst\tst.txt" -c -T -Sserver1'
but if I pass a variable instead of query string I am not getting the file. Below is the qry which I am not getting the file output,
DECLARE @QRYVARCHAR(8000)
DECLARE@QRY_INVARCHAR(8000)
SELECT@QRY_IN= 'select Employee_code fromdb1..table1_tmp'
SELECT@QRY= 'BCP "' + @QRY_IN + '" QUERYOUT "' + LTRIM(RTRIM('C:\Tst\tst.txt')) + '" -c -T -S' + @@SERVERNAME
EXEC MASTER..XP_CMDSHELL @QRY
Can anyone suggest what could be the problem...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

sheik (3/7/2010)
Hi,
I am using XP_CMDSHELL in SQL 2005. If Run the below query then it creates the file in the specified folder
EXEC MASTER..XP_CMDSHELL 'BCP "select Employee_Code from db1.Table1_tmp" QUERYOUT "C:\Tst\tst.txt" -c -T -Sserver1'
but if I pass a variable instead of query string I am not getting the file. Below is the qry which I am not getting the file output,
DECLARE @QRYVARCHAR(8000)
DECLARE@QRY_INVARCHAR(8000)
SELECT@QRY_IN= 'select Employee_code fromdb1..table1_tmp'
SELECT@QRY= 'BCP "' + @QRY_IN + '" QUERYOUT "' + LTRIM(RTRIM('C:\Tst\tst.txt')) + '" -c -T -S' + @@SERVERNAME
EXEC MASTER..XP_CMDSHELL @QRY
Can anyone suggest what could be the problem...

Add the following in just before the EXEC and see if you're creating the exact same query that you're running manually...

PRINT @QRY

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

As a side bar, I would encapsulate the server name in double quotes in case you ever need to run the code against a named instance which will have a backslash in it or one of those improperly named servers that have a dash or spaces in the server name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sheik SSC Eights! Points: 800 More actions · Answer 3

Thans for your input...

The problem was, I worte the query like this

SELECT@QRY_IN= 'select

Employee_code from db1..table1_tmp'

If I change the query in the below format then it is working,

SELECT@QRY_IN= 'select Employee_code from db1..table1_tmp'

I dont know the exact reason for that may be some junk value are sitting inbetween the select and employee_code

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

sheik (3/7/2010)
Thans for your input...
The problem was, I worte the query like this
SELECT@QRY_IN= 'select
Employee_code from db1..table1_tmp'
If I change the query in the below format then it is working,
SELECT@QRY_IN= 'select Employee_code from db1..table1_tmp'
I dont know the exact reason for that may be some junk value are sitting inbetween the select and employee_code

Heh... don't feel bad... even I missed that one. Yeah... it's all got to be on one line. The first way you did it puts a literal carriage return into the line.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

Check what @@servername returns. If the server got renamed that may not be the actual name of the server. The following will get the current name that Windows is using:

select serverproperty('servername')

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

cfradenburg (3/8/2010)
Check what @@servername returns. If the server got renamed that may not be the actual name of the server. The following will get the current name that Windows is using:
select serverproperty('servername')

The problem turned out to be a multi-line entry according to the OP's post a bit further up on this thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sheik SSC Eights! Points: 800 More actions · Answer 7

Yes, It is because of no of lines in the dynamic query.

Either write in single line or use concatenation operation in the dynamic qry, then it is working

Charles Kincaid SSChampion Points: 13593 More actions · Answer 8

Yes you all have it right nut have not stated why.

In SQL a carriage return or two adds readability. We get into the habit of it and it's not a bad thing.

However you are passing out to the command shell where you are speaking DOS and not SQL. There the carriage return ends the statement giving you a syntax statement.

It's like sticking a semicolon into the middle of a SQL statement.

So the statement that BCP got ended with the word "select".

ATBCharles Kincaid

sheik SSC Eights! Points: 800 More actions · Answer 9

I didnot think of that, Now I understand clearly what the problem is ...