March 7, 2010 at 6:32 am
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...
March 7, 2010 at 8:54 am
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
March 7, 2010 at 8:56 am
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
March 7, 2010 at 10:05 pm
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
March 8, 2010 at 6:19 am
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
March 8, 2010 at 6:22 am
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')
March 8, 2010 at 5:27 pm
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
March 8, 2010 at 9:48 pm
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
March 9, 2010 at 6:20 am
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
March 9, 2010 at 6:30 am
I didnot think of that, Now I understand clearly what the problem is ...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply