Saving Query Results to .txt using Scripts

• Nishernd

  SSChasing Mays

  Points: 611

  More actions

  September 15, 2009 at 7:07 am

  #135017

  Hi All,

  I need to save the results of any Query to a delimited .txt (Any Delimiter)

  however i need it to be saved from within a SQL Proc, which will be called every night

  in a job.

  Any Help Please...

• Chand00

  SSCertifiable

  Points: 6466

  More actions

  September 15, 2009 at 7:17 am

  #1053745

  Hi,

  You can use bcp with xp_cmdshell or openrowset. Please refer to the following links. I recommend you to use open rowset. Please refer the following links.

  bcp with xp_cmdshell

  http://www.mssqltips.com/tip.asp?tip=1633

  openrowset

  http://www.mssqltips.com/tip.asp?tip=1202

  [font="Verdana"]Thanks
  Chandra Mohan[/font]

• Lowell

  SSC Guru

  Points: 323527

  More actions

  September 15, 2009 at 7:20 am

  #1053748

  both bcp and the more robust sqlcmd have the ability to be fed a query and output the results to a file;

  that is your best choices, unless you write/find a CLR to do it for you instead.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Nishernd

  SSChasing Mays

  Points: 611

  More actions

  September 15, 2009 at 8:08 am

  #1053793

  Thanks Guys,

  Im looking into it now...

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  September 15, 2009 at 8:11 am

  #1053797

  If you are using a job to send the results to a .txt file, the one alternative way would be to create a SSIS package to extract the data and create the file and then schedule this package as run as your nightly job.,

• amns

  SSCrazy

  Points: 2628

  More actions

  September 15, 2009 at 11:08 am

  #1053973

  Hi,

  i faced a similar problem and i created a stored procedure that uses bcp, fmt format file and xp_cmdshell..

  The code follow this:

  select @cmd = 'bcp "SELECT column from table" queryout ' D:\exported.txt -f D:\format.fmt - T

  EXEC @rc = master..xp_cmdshell @cmd

  IF @rc 0

  BEGIN

  PRINT 'cmd faillure!'

  return 1

  END

  ELSE

  BEGIN

  Print 'cmd execution ok!'

  return 0

  END