none
SQL Query write to text file

    Question

  • Hey guys,

    I know there have been plenty of posts on this topic. I did my research and have my Query outputting to a text file as I wanted. However I have a question with adding something to my command.

    Here is my command that is working:

    EXEC XP_CmdShell 'BCP "Select code, desc From DATABASE.dbo.table" queryout "C:\test.txt" -c -T'

     

    I want to add a "WHERE" command to this line, where I have a value equal to something...Example:

    WHERE mat = '8888'

     

    I know I need to declare "mat" as part of a different table in the database. Here is what I have at the moment...

    EXEC XP_CmdShell 'BCP "Select code, desc From DATABASE.dbo.table, DATABASE.dbo.OtherTable b WHERE b.mat = '8888'" queryout "C:\test.txt" -c -T'

    I am getting a syntax error where '8888' is. I know it is because of the ' character that is commenting out my equal to value. What is the correct syntax to do this? I have tried many things and still haven't gotten it to work. Any help is appreciated! Thanks everyone!

    Tuesday, November 15, 2011 1:34 AM

Answers

  • Use Single Quotation Mark to escape single quotation mark, that is, in a sql string, '' (TWO SINGLE quotation marks) represent 1 single quotation mark

    EXEC XP_CmdShell 'BCP "Select code, desc From DATABASE.dbo.table, DATABASE.dbo.OtherTable b WHERE b.mat = ''8888''" queryout "C:\test.txt" -c -T'

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Iric WenModerator Wednesday, November 16, 2011 5:46 AM
    • Marked as answer by Stephanie Lv Wednesday, November 23, 2011 8:57 AM
    Tuesday, November 15, 2011 2:08 AM

All replies

  • Use Single Quotation Mark to escape single quotation mark, that is, in a sql string, '' (TWO SINGLE quotation marks) represent 1 single quotation mark

    EXEC XP_CmdShell 'BCP "Select code, desc From DATABASE.dbo.table, DATABASE.dbo.OtherTable b WHERE b.mat = ''8888''" queryout "C:\test.txt" -c -T'

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Iric WenModerator Wednesday, November 16, 2011 5:46 AM
    • Marked as answer by Stephanie Lv Wednesday, November 23, 2011 8:57 AM
    Tuesday, November 15, 2011 2:08 AM
  • I tried that idea. I got a "usage:BCP" error message.
    Tuesday, November 15, 2011 2:17 AM
  • Ok I see I was missing a " which was causing the issue. Thanks for the help!
    Tuesday, November 15, 2011 2:35 AM
  • HI Uber !

    Here is the samplesnippet , you may modify it with you requiremets;


    EXEC master..xp_cmdshell 'bcp "YourDatabaseQuery" queryout "YouFilePath" -T, -S "ServeName\InstanceName" -U bcpuser -P bcppassword -w' 
    EXEC master..xp_cmdshell 'bcp "SELECT EmployeeID,NationalIDNumber FROM AdventureWorks.HumanResources.Employee WHERE EmployeeID < 100 " queryout "E:\Test\Employee.txt" -T -S SSSLT105\SQLEXPRESS08 -U sa -P 123456 -w'
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

     

    Tuesday, November 15, 2011 5:39 AM