Whats wrong with this bcp utility?

I am trying to export data into into a text file using bcp utility? I could n't figure out what i am doing wrong here:-

Declare @Cmd nvarchar(1000)
Declare @Query nvarchar(1000)
Declare @Path nvarchar(500)
Declare @ServerName nvarchar(500)
Declare @UserName nvarchar(50)
Declare @Password nvarchar(50)
Declare @Delimiter char(1)

SET @Path = 'c:\abc.txt'
SET @ServerName = '10.10.10.10\instance1'
Set @UserName = 'sa'
Set @Password = '123456'
Set @Delimiter = '!'

SET @Query = 'SELECT [ID]
          , NULL
          ,[NAME]
          ,[STREET1] + '' '' + [STREET2] as [Address]
          ,[CITY] + '' '' + [PROVINCE] + '' '' + [POSTAL] as City
          ,[PHONE] 
          ,[FAX]
          ,''Title'' as [Title]
          ,[Country]
FROM [temp01]'
SET @Cmd = 'bcp "' + @Query + '" queryout "' +  @Path +
         '" -c -S' + @ServerName + ' -U' + @UserName + ' -P' + 
        @Password + ' -t' + @Delimiter + ''
EXEC master..xp_cmdshell @Cmd

It is not writing开发者_如何学编程 any data into the file. In result box i am getting this output:-

usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize] [-S server name] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"] NULL [-h "load hints"] NULL

Please help.

This question is pretty old, but I've had the same issue. You need the entire command to reside on a single line. Take out any line breaks in @Query and @Cmd.

I think you need a space after your -S switch. Also, in general, when debugging something complex reduce the complexity until you can identify where the problem is. In this case, you can remove each switch one at a time (or remove all of them to start) to determine whether or not that's causing the problem.

SET @Cmd = 'bcp "' + REPLACE(REPLACE(REPLACE(,CHAR(9),CHAR(32)),CHAR(13),CHAR(32)),CHAR(10),'')@Query + '" queryout "' + @Path + '" -c -S' + @ServerName + ' -U' + @UserName + ' -P' + @Password + ' -t' + @Delimiter + ''

You do have to give a database. you only defined the instance an a table but no database. try change your select-statemen like : ...from [yourDB].[temp01]...

I found the Same Issue While trying in my Database, Hence I concluded that You are using

SET @Query = 'SELECT [ID]
           , NULL
           ,[NAME]
           ,[STREET1] + '' '' + [STREET2] as [Address]
           ,[CITY] + '' '' + [PROVINCE] + '' '' + [POSTAL] as City
           ,[PHONE] 
           ,[FAX]
           ,''Title'' as [Title]
           ,[Country] FROM [temp01]'

If U use

SET @Query = 'SELECT [ID], NULL ,[NAME],[STREET1] + '' '' + [STREET2] as [Address],[CITY] + '' '' + [PROVINCE] + '' '' + [POSTAL] as City,[PHONE],[FAX],''Title'' as [Title],[Country] FROM [temp01]'

Will Work Fine Without any new line in the Query Just because it is not accepting the Space in between.

Thanks