Check NOT EXISTS against two tables

Question

Post reply

Check NOT EXISTS against two tables

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

donato1026 SSCrazy Points: 2765 More actions · Answer 1

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: 323504 More actions · Answer 2

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 · Answer 3

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 · Answer 4

donato1026

SSCrazy

Points: 2765

May 11, 2010 at 10:07 am

#1165656

Do I need to use square brackets?

donato1026 SSCrazy Points: 2765 More actions · Answer 5

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!