Send Query result into a Text file in SqlServer

I want to send the result from a scalar variable into a text file,

My Code looks like this

  • Datetime format doesn't do what is supposed to do
  • Select multiple SQL rows into one row
  • Convert SQL Server database to MySQL
  • Difference of two date time in sql server
  • Return Select Statement as formatted HTML (SQL 2005)
  • How can I optimize this SQL query? (Solarwinds Orion)
  •  DECLARE @test varchar(10)
     SET @test='This is sample text'
     EXEC master..xp_cmdshell'bcp ' + @test + ' queryout "D:\sample.txt" -S LocalHost -U 
     sa -P 123 -c  -T -t' 

    But this is showing the following errors

     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"]         [-x generate xml format file]

    Dont know how to give the format assigning a scalar variable value in bcp command
    Please any one help.

    And tried in this way also

      Create table #sample
       productid int
      Insert into #sample(productid) values(1001098)
      EXEC master..xp_cmdshell'bcp "select * from #sample" queryout "D:\sample.txt" -S 
      LocalHost -U sa -P 123 -c  -T -t' 

    It gives as Error that

       #sample does not exist (in bcp command line) 

    Can any one please solve this.
    Thanks in advance.

    2 Solutions collect form web for “Send Query result into a Text file in SqlServer”

    First thing, a temp table is bound to a scope, and thus cannot be used in a different process ID.

    But, you can declare a global temp table (be sure to delete it afterward and make sure to use a very specific name to make sure you don’t interfere with some other code).

    To do so, you just have to double the ‘#’ in the table name.

    if object_ID('tempdb..##sample') is not null
        drop table ##sample
    Create table ##sample
     productid int
    Insert into ##sample(productid) values(1001098)

    Then all you have to do is your output.

    EXEC master..xp_cmdshell'bcp "select * from ##sample" queryout "d:\sample.txt" -w -U sa -P 123 -S server_name\instance_name' 

    As you can see I changed a couple of switches too.
    I found it’s preferable to use -w to generate ansi characters, so I removed -c.
    -T is for trusted connection, but since you provide a username and password, you don’t need it.

    Then you should be fine.

    Test this:

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.