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

  • How can I give SQL Server permission to read my SSL Key?
  • Update fails after repeating deadlocked query in pymssql
  • Write advanced SQL Select
  • How does one deal with multiple TimeZones in applications that store dates and times?
  • Is there a SQL Server 2008 method to group rows in a table so as to behave as a nested table?
  • TSQL: grouping customer orders by week
  •  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

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

    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:

    EXEC xp_cmdshell 'bcp "SELECT *FROM [YOURDATABASE].[dbo].[YOURTABLE]"  queryout "f:\newOUTPUT.txt" -S DESKTOP-A5CFJSH\MSSQLSERVER1 -UYOURUSERNAME -PYOURPASSWORD -n '
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.