Check NOT EXISTS against two tables

• donato1026

  SSCrazy

  Points: 2765

  More actions

  May 11, 2010 at 9:40 am

  #1165636

  This is what I have. . .

  declare @body1 varchar(4000)

  set @body1 = 'New Website Product for the ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

  '. Please see attachment. . .'

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name='SQL Server Agent',

  @recipients='email@email.com',

  @subject = 'New Product for Website',

  @body = @body1,

  @body_format = 'HTML',

  @query = 'SELECT OURSERVER.DBO.WEB_CATALOG_IMAGES.* FROM OURSERVER.DBO.WEB_CATALOG_IMAGES LEFT OUTER JOIN OURSERVER.DBO.PRODUCT ON OURSERVER.DBO.WEB_CATALOG_IMAGES.ITEM_CODE = OURSERVER.DBO.PRODUCT.SKU LEFT OUTER JOIN OURSERVER.DBO.ITM ON OURSERVER.DBO.WEB_CATALOG_IMAGES.ITEM_CODE = OURSERVER.DBO.ITM.ITM_CD WHERE OURSERVER.DBO.PRODUCT.PRODUCTID IS NULL AND OURSERVER.DBO.ITM.DROP_CD IS NULL AND OURSERVER.DBO.WEB_CATALOG_IMAGES.WEB_ENABLED = ''Y''',

  @query_result_header = 0,

  @exclude_query_output = 1,

  @append_query_error = 1,

  @attach_query_result_as_file = 1,

  @query_attachment_filename = 'NewProductForWebSite.txt',

  @query_result_no_padding = 1

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 11, 2010 at 9:53 am

  #1165648

  my copy/paste of your query shows it to be syntactically correct; i don't see a problem with it;

  if you change the query to a sample query, does it work and send the email successfully? ie

  @query = 'SELECT top 3 * from OURSERVER.DBO.sysobjects where xtype=''U''',

  the code i pasted works jsut fine with the right profile, i'm not seeing the issue yet....

  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!

• donato1026

  SSCrazy

  Points: 2765

  More actions

  May 11, 2010 at 10:07 am

  #1165655

  I get this error:

  Msg 102, Level 15, State 1, Server 06LEVINDW, Line 1

  Incorrect syntax near '06'.

  Here is my code. . .

  declare @body1 varchar(4000)

  set @body1 = 'New Website Product for the ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

  '. Please see attachment. . .'

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name='SQL Server Agent',

  @recipients='myemail@myaddress.com',

  @subject = 'New Product for Website',

  @body = @body1,

  @body_format = 'HTML',

  @query = 'SELECT top 3 * from 06SERVERDW.DBO.sysobjects where xtype=''U''',

  @query_result_header = 0,

  @exclude_query_output = 1,

  @append_query_error = 1,

  @attach_query_result_as_file = 1,

  @query_attachment_filename = 'NewProductForWebSite.txt',

  @query_result_no_padding = 1

• donato1026

  SSCrazy

  Points: 2765

  More actions

  May 11, 2010 at 10:07 am

  #1165656

  Do I need to use square brackets?

• donato1026

  SSCrazy

  Points: 2765

  More actions

  May 11, 2010 at 10:13 am

  #1165659

  Never mind. . . I got it to work. I was using servername.dbo.table name - rather than databasename.dbo.tablename. It works now!

  Now. . . Is there any way to have it send it as an Excel attachment, rather than text?

  Thanks for ALL of your help!