[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2145
  • Last Modified:

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.
0
batchakamal
Asked:
batchakamal
  • 6
  • 3
1 Solution
 
batchakamalAuthor Commented:
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???
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Set @TempTable = 'urDb.dbo.A' -----------------------------replace 'urDBB' with your database name
Set @TempFile = 'C:\tmp_Load_vou05nov06_txt'
SET @BcpCommand = 'BCP "select * from ' +@TempTable+ '" queryout "' +@TempFile+ '" -T  -c"'
0
 
batchakamalAuthor Commented:
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

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
batchakamalAuthor Commented:
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'.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 ...
0
 
batchakamalAuthor Commented:
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]
...........
...........
.........
0
 
batchakamalAuthor Commented:
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'.
0
 
batchakamalAuthor Commented:
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.

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now