SQL Mail

• aruram

  SSCrazy

  Points: 2379

  More actions

  November 18, 2003 at 10:54 am

  #59552

  Is there a way to output the results of a query using SQL mail directly to an Excel Spreadsheet and if so, how ?

  Thanks

• Shas3

  Hall of Fame

  Points: 3671

  More actions

  November 18, 2003 at 1:13 pm

  #482496

  No direct way, but you can setup a DTS pack to generate a .xls file by specifying the same query and let sqlmail pick the file and email.

  EXEC master..xp_sendmail

  @recipients = 's2v'

  ,@message = ' Pls find the Execel sheet'

  ,@subject = 'Execl Attached'

  ,@attachments = 'c:\test.xls'

  The path is from the server.

  Shas3

• rodalesteve

  Grasshopper

  Points: 16

  More actions

  November 19, 2003 at 5:03 am

  #482587

  You could save the file as comma-delimited and a .csv extension. Most pc's have excel assigned as the default application to open comma seperated files.

• Linda Johanning

  SSCertifiable

  Points: 6727

  More actions

  November 19, 2003 at 8:32 am

  #482661

  I have a procedure to BCP from a view and export the data into a spreadsheet. Works like a charm.

  EXECUTE master..xp_cmdshell 'BCP Database..ViewName out "E:\FullPath\Spreadsheet.xls" /c /Sserver /Uuser /Ppassword'

  Linda

• Shas3

  Hall of Fame

  Points: 3671

  More actions

  November 19, 2003 at 10:52 am

  #482714

  As Linda said, BCP is also another option in place of DTS

  Shas3

• manojkumarfbd

  SSC Journeyman

  Points: 89

  More actions

  November 20, 2003 at 2:31 am

  #482802

  if you have same query then you can use OSql utility

  Osql -o option can be used for get the query resulet in .xls file.

  and u can create a .Bat file to execute this command put the schedule thru OS.

  for more detail see BOL.

  Manoj Kumar