my query results are not in my excell attachment can anyone help

• etvph81

  SSC Veteran

  Points: 234

  More actions

  March 2, 2020 at 8:15 pm

  #3730326

  Hello,

  I have a query here that is sending a excell attachment to a email the only problem is my results are not being put into that attachment also how do I distinguish this when I create a Agent Job what parts do I put where in my agent job.

   

  DECLARE @sub VARCHAR(100)

  DECLARE @qry VARCHAR(1000)

  DECLARE @msg VARCHAR(250)

  DECLARE @query NVARCHAR(1000)

  DECLARE @query_attachment_filename NVARCHAR(520)

  SELECT @sub = 'DC Weekly Transfer'

  SELECT @msg = 'This is Just a test.'

  SELECT @query_attachment_filename = 'dcweekly.csv'

  Declare @nOrgID int = 67

  , @startdate date = dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), -1))-- for saturday

  , @enddate date = dateadd(wk, datediff(wk, 0, getdate()), -2) --for sunday

  , @nDonationTypeID int = 4;

  Select ld.nStartLocationID

  , ld.nEndLocationID

  , ld.nDonorCount

  , ld.tEffectiveDate

  , ld.cChangedBy

  From dbo.Data_Org_Location_Donations ld

  Where ld.nOrganizationID = @nORGID

  And ld.nDonationTypeID = @nDonationTypeID

  And ld.nactive = 1

  And ld.tEffectiveDate >= @startdate

  And ld.tEffectiveDate < dateadd(day, 1, @enddate)

  Order By

  ld.tEffectiveDate;

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'WESA DC Weekly',

  @recipients = 'edward.vahovick@gimi.org',

  @subject = 'Multiple file attachments in SQL Server DBMail',

  @body = 'DC Weekly Email Results',

  @file_attachments = 'C:\dcweekly.csv'

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  March 2, 2020 at 8:49 pm

  #3730339

  Why did you start a new thread?  The answer to your issue is in this thread: https://www.sqlservercentral.com/forums/topic/ssis-83#post-3729970

  I provided 2 separate examples...both of which can be executed directly in a query window and validated.  Once validated you can then either:

  1. Create a stored procedure - then execute that stored procedure from the agent job step
  2. Put the code directly into the agent job step

  Please follow up on the other thread...

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs