Data not loaded in email?

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  September 27, 2013 at 3:00 am

  #278392

  Hi

  Result set is not loaded in Email body, what could be worng in this script?

  declare @results varchar(max)

  select MACHINE, COUNT(*) as Instance

  into #tableA

  from LIMSPROD.dbo.CM_INSTANCE

  group by MACHINE

  Select COUNT(*)/4 as InstanceA

  into #tableB

  from LIMSPROD.dbo.CM_INSTANCE

  Select A.MACHINE, A.Instance, B.InstanceA,

  (B.InstanceA-A.Instance) as Diff

  into #results

  from #tableA A,

  #tableB B

  where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3

  -- number of records to not be zero

  If ((select count(*) from #results) <> 0)

  Begin

  SET @Results = 'select * from #results'

  EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',

  @recipients='ananda.murugesan@xyz.com',

  @subject='Alert!-Verify Instance',

  @query=@Results

  End

  drop table #tableA

  drop table #tableB

  drop table #results

  Error msg in job history

  NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

  Thanks

  ananda

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  September 27, 2013 at 3:14 am

  #1653923

  I don't believe you can use a local temp table to populate a results table for sp_send_dbmail as it runs in a different SPID asynchronously.

  You need to either use a global temp table (##) or a permanent table.

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  September 27, 2013 at 3:44 am

  #1653935

  I have changed Global Temp Table as per your suggestion

  yes.. It is working fine.. and Grate solution given to me

  Thanks a lot..