Post reply

Send Email using OPEN QUERY

  • June 16, 2010 at 12:49 pm

    #143816

    I have code to check for new product and then send an email to me of the product that is in the database, but not in the Product table on our website.

    Here is my code, but it does not work. . .

    declare @body1 varchar(4000)

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

    '. Please see attachment. . .'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='SQL Server Agent',

    @recipients='email@mail.com', -- seperated by semicolons

    @subject = 'New Product for Website',

    @body = @body1,

    @body_format = 'HTML',

    @query = 'SELECT * FROM OPENQUERY([06websql\websql], 'SELECT Commerce.dbo.WEB_CATALOG_IMAGES.item_code as [Item Code], Commerce.dbo.WEB_CATALOG_IMAGES.vendor_code as [Vendor Code], Commerce.dbo.WEB_CATALOG_IMAGES.product_description as [Description] FROM Commerce.dbo.WEB_CATALOG_IMAGES LEFT OUTER JOIN Commerce.dbo.PRODUCT ON Commerce.dbo.WEB_CATALOG_IMAGES.ITEM_CODE = Commerce.dbo.PRODUCT.SKU LEFT OUTER JOIN Commerce.dbo.ITM ON Commerce.dbo.WEB_CATALOG_IMAGES.ITEM_CODE = Commerce.dbo.ITM.ITM_CD WHERE Commerce.dbo.PRODUCT.PRODUCTID IS NULL AND Commerce.dbo.ITM.DROP_CD IS NULL AND Commerce.dbo.ITM.VE_CD IS NOT NULL AND Commerce.dbo.WEB_CATALOG_IMAGES.WEB_ENABLED = 'Y' ORDER BY Commerce.dbo.ITM.VE_CD''),

    @query_result_header = 1,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'NewProductForWebsite.txt',

    @query_result_no_padding = 1

  • June 16, 2010 at 12:56 pm

    #1181236

    well syntactically, i think this is correct, but it's untested:

    SELECT * FROM OPENQUERY( [linked server],'SET FMTONLY OFF; EXEC (''declare @body1 varchar(4000)

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

    ''. Please see attachment. . .''

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name=''SQL Server Agent'',

    @recipients=''email@mail.com'', -- seperated by semicolons

    @subject = ''New Product for Website'',

    @body = @body1,

    @body_format = ''HTML'',

    @query = ''SELECT * FROM OPENQUERY([06websql\websql], ''SELECT Commerce.dbo.WEB_CATALOG_IMAGES.item_code as [Item Code], Commerce.dbo.WEB_CATALOG_IMAGES.vendor_code as [Vendor Code], Commerce.dbo.WEB_CATALOG_IMAGES.product_description as [Description] FROM Commerce.dbo.WEB_CATALOG_IMAGES LEFT OUTER JOIN Commerce.dbo.PRODUCT ON Commerce.dbo.WEB_CATALOG_IMAGES.ITEM_CODE = Commerce.dbo.PRODUCT.SKU LEFT OUTER JOIN Commerce.dbo.ITM ON Commerce.dbo.WEB_CATALOG_IMAGES.ITEM_CODE = Commerce.dbo.ITM.ITM_CD WHERE Commerce.dbo.PRODUCT.PRODUCTID IS NULL AND Commerce.dbo.ITM.DROP_CD IS NULL AND Commerce.dbo.ITM.VE_CD IS NOT NULL AND Commerce.dbo.WEB_CATALOG_IMAGES.WEB_ENABLED = ''Y'' ORDER BY Commerce.dbo.ITM.VE_CD''''),

    @query_result_header = 1,

    @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

    --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!

  • June 16, 2010 at 2:04 pm

    #1181273

    Thanks, Lowell, but it's not working. . .

    I'm getting this:

    OLE DB provider "SQLNCLI" for linked server "06WEBSQL\WEBSQL" returned message "Deferred prepare could not be completed.".

    Msg 8180, Level 16, State 1, Line 1

    Statement(s) could not be prepared.

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near ',

    @query_result_header = 1,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_att'.

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'New'.

  • June 16, 2010 at 2:26 pm

    #1181278

    The probelm is in the Quotes. Take a look at the following Script as a test.

    declare @body1 varchar(4000)

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

    '. Please see attachment. . .'''

    select @body1

    this would return

    'New Website Product as of 16 Jun 2010 15:23:22. Please see attachment. . .'

    Notice how the three sinqle quotes are translated into a single quote in the output.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 16, 2010 at 2:52 pm

    #1181285

    having made a couple of changes to the single quote locations the following should work

    'SET FMTONLY OFF; EXEC (declare @body1 varchar(4000)

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

    ''. Please see attachment. . .''

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name=''SQL Server Agent'',

    @recipients=''email@mail.com'',

    @subject = ''New Product for Website'',

    @body = @body1,

    @body_format = ''HTML'',

    @query = ''SELECT * FROM OPENQUERY([06websql\websql], ''SELECT Commerce.dbo.WEB_CATALOG_IMAGES.item_code as [Item Code], Commerce.dbo.WEB_CATALOG_IMAGES.vendor_code as [Vendor Code], Commerce.dbo.WEB_CATALOG_IMAGES.product_description as [Description] FROM Commerce.dbo.WEB_CATALOG_IMAGES LEFT OUTER JOIN Commerce.dbo.PRODUCT ON Commerce.dbo.WEB_CATALOG_IMAGES.ITEM_CODE = Commerce.dbo.PRODUCT.SKU LEFT OUTER JOIN Commerce.dbo.ITM ON Commerce.dbo.WEB_CATALOG_IMAGES.ITEM_CODE = Commerce.dbo.ITM.ITM_CD WHERE Commerce.dbo.PRODUCT.PRODUCTID IS NULL AND Commerce.dbo.ITM.DROP_CD IS NULL AND Commerce.dbo.ITM.VE_CD IS NOT NULL AND Commerce.dbo.WEB_CATALOG_IMAGES.WEB_ENABLED = ''Y'' ORDER BY Commerce.dbo.ITM.VE_CD''''),

    @query_result_header = 1,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = ''NewProductForWebsite.txt'',

    @query_result_no_padding = 1)'

    I also had to remove the comment. When this is transalated it is read as a single line. As such everything after the comment is treated as part of the comment.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 16, 2010 at 3:14 pm

    #1181294

    Thanks, Dan. However, this is not working either. . . I am receiving the following:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'SET FMTONLY OFF; EXEC (declare @body1 varchar(4000)

    set @body1 = 'New Website Product as of ' + CONVERT( VARCHAR( 20 ), GETDATE'.

  • June 16, 2010 at 3:17 pm

    #1181295

    did you merge this back in with Lowells query? I updated the part you want to pass but you still need the rest of the statement from lowells post.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 17, 2010 at 6:56 am

    #1181524

    Thanks, Dan. . . But it's still not working, even with Lowells code. . .

    OLE DB provider "SQLNCLI" for linked server "06websql\websql" returned message "Deferred prepare could not be completed.".

    Msg 8180, Level 16, State 1, Line 1

    Statement(s) could not be prepared.

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near ''.

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'declare'.

Viewing 8 posts - 1 through 7 (of 7 total)

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