Search code examples
sql-serverbcp

Why my bcp query out not work in sql server?

I'm new in sql server and want to save select query into the csv file using with bcp query out for that purpose write this query:

declare @cmd as nchar(50)
SET @cmd = 'bcp  select *from [behzad].[dbo].[behzad] queryout "d:\spt_values.dat" -U behbeh -P beh1368421 ' 
EXEC master..XP_CMDSHELL @cmd


but i get this output:
enter image description here
How can i solve this problem?thanks.

Solution

  • As you are using queryout your source must be a query.

    As a query has got blanks, you have to quote it:

    Further more your @cmd nchar(50) is to short and will probably truncate your command.

    Try this:

    declare @cmd as nchar(500)
SET @cmd = 'bcp  "select * from [behzad].[dbo].[behzad]" queryout "d:\spt_values.dat" -U behbeh -P beh1368421 ' 
EXEC master..XP_CMDSHELL @cmd

    With a SELECT * FROM ... query it was easier actually, to use the 3-part-qualified table name together with out instead of a SELECT ... with queryout...