asked on

Queryout is not working in bcp with xp_cmdshell

Hello Experts,

When I execute the following lines, I am getting this error,

Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.

What's wrong with this statements, Assume that the Table tmp_Load_vou05nov06_txt is already exist.

Declare @BcpCommand varchar(200)
Declare @TempTable varchar(200)
Declare @TempFile varchar(200)
Set @TempTable = 'tmp_Load_vou05nov06_txt'
Set @TempFile = 'C:\tmp_Load_vou05nov06_txt'
Set @BcpCommand = 'bcp "select * from '+@TempTable+'" queryout "'+@TempFile+'" -T -c'
Exec master..xp_cmdshell @bcpCommand

This is urgent.

batchakamal

ASKER

Also note that I want to use SQL Server trusted connection, and the statement will be executed local server.
Do I need to specify the servername, userid and password?
I think -T is for Trusted Connection. right???

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

batchakamal

ASKER

I am getting the same error.

Declare @BcpCommand varchar(200)
Declare @TempTable varchar(200)
Declare @TempFile varchar(200)
Set @TempTable = 'SalesDB.dbo.tmp_Load_vou05nov06_txt' --- I have included the database name too
Set @TempFile = 'C:\tmp_Load_vou05nov06.txt'
SET @BcpCommand = 'BCP "select * from ' +@TempTable+ '" queryout "' +@TempFile+ '" -T -c"'
Exec master..xp_cmdshell @bcpCommand

Aneesh

If you are using the sql authentication try this

SET @BcpCommand = 'BCP "select * from ' +@TempTable+ '" queryout "' +@TempFile+ '" -U login_id -P password /c"'
replace Login_id and password to appropriate username and password ..

If it again fails, pls send the error message, whichwill be useful for further assistance

batchakamal

ASKER

Declare @BcpCommand varchar(200)
Declare @TempTable varchar(200)
Declare @TempFile varchar(200)
Set @TempTable = 'ETL2..tmp_Load_vou05nov06_txt'
Set @TempFile = 'C:\tmp_Load_vou05nov06.txt'
SET @BcpCommand = 'BCP "select * from ' +@TempTable+ '" queryout "' +@TempFile+'" -U sa -P sa /c'
Exec master..xp_cmdshell @bcpCommand

Error Message:

Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.

Aneesh

Declare @BcpCommand varchar(200)
Declare @TempTable varchar(200)
Declare @TempFile varchar(200)
Set @TempTable = 'pubs.dbo.authors' --- I have included the database name too
Set @TempFile = 'C:\tmp_Load_vou05nov06.txt'
Set @BcpCommand = 'bcp "select * from '+@TempTable+'" queryout "'+@TempFile+'" -U sa -P sa /c'

print @bcpCommand
Exec master..xp_cmdshell @bcpCommand

run this without changing anything,..

also put a print statement before the exec statement of the prev query and send me ...

batchakamal

ASKER

Print Statement returns:

bcp "select * from pubs.dbo.authors" queryout "C:\tmp_Load_vou05nov06.txt" -U sa -P sa /c

and the error is,

Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.
usage: bcp [[db_name.]owner.]table_name[:slice_num] [partition pname] {in | out} [filename]
...........
...........
.........

batchakamal

ASKER

Also I have tried the above statement with "out queryout" instead of "queryout"

Then print statement returns,

bcp "select * from pubs.dbo.authors" out queryout "C:\tmp_Load_vou05nov06.txt" -U sa -P sa /c

--------------------------------------------^--------------------I have included the out
and the error message shows,

Syntax Error in 'C:\tmp_Load_vou05nov06.txt'.

batchakamal

ASKER

Hello..

I found the problem and solved it.

The problem is , I have SQL Server 2005 on my pc. So it tries to execute the bcp of that.

Now it is running fine.

thanks for ur support and I am giving this point to you.