XP_sendmail send large (query results) files

• WoodyB

  Grasshopper

  Points: 13

  More actions

  August 5, 2008 at 9:36 am

  #123657

  I am writing a script based on the xp_senmail function

  The xp_sendmail part is not an issue but I need to create an export to a file where the result of the query Is bigger than the max 7990 characters.

  is there an example of a script that succefully uses a temp table to store the result and then writes it to the file to be attached?

  this example from MS does not do the trick

  CREATE TABLE ##texttab (c1 text)

  INSERT ##texttab values ('Put your long message here.')

  DECLARE @cmd varchar(56)

  SET @cmd = 'SELECT c1 FROM ##texttab'

  EXEC master.dbo.xp_sendmail 'robertk',

  @query = @cmd, @no_header= 'TRUE'

  DROP TABLE ##texttab

  the part where it says Put your long message here needs to be a query to an specific database and table like Query: Select * from setstock

  so more like:

  CREATE TABLE ##texttab (c1 text)

  INSERT ##texttab values (select * FROM factdim.dbo.setstock')

  DECLARE @cmd varchar(56)

  SET @cmd = 'SELECT c1 FROM ##texttab'

  EXEC master.dbo.xp_sendmail 'robertk',

  @query = @cmd, @no_header= 'TRUE'

  DROP TABLE ##texttab

  anyone?

• nary

  SSCrazy

  Points: 2869

  More actions

  August 5, 2008 at 9:44 am

  #853275

  How about if you attach the Query Result instead of sending direcly to Email message..

  Or if you have any SQL Server 2005 Server from wher u can send the email then big result won't be the problem ,as i use to send 1 Mb or some time big query result through Database mail