Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    4

    Question Unanswered: xp_sendmail question.

    Hello Everyone,

    I have a question regarding xp_sendmail. Here is what I am trying to do:

    We have multiple 'sites' (locations) & have created email groups for each of these 'sites'.

    Example: For 'site': 3100, the email group is user_3100. All people at this site/location, belong to this email group.

    The temp table: ##DOCInfo, contains the 'site' number (AppField2), among other things. What I would like to do is pick the relevant info. for each 'site' & email it to the people at that site only. So, this is what I have:

    ************************************************** **********
    USE Master
    DECLARE @SITE char(4)

    -- AppField2 contains the 'site' numbers

    DECLARE site_cursor CURSOR
    FOR
    SELECT DISTINCT AppField2
    FROM ##DOCInfo
    WHERE AppField2 != ' '
    AND AppField2 is NOT NULL
    ORDER BY 1

    OPEN site_cursor
    FETCH NEXT FROM site_cursor INTO @SITE

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC xp_sendmail @query = 'SELECT *
    FROM ##DOCInfo
    WHERE AppField2 = @SITE
    ORDER BY 1,2,3,4,5,6',
    @recipients = 'user_@SITE',
    @subject = 'Outbound Document Exceptions for Site: @SITE',
    @width = 132
    FETCH NEXT FROM site_cursor INTO @SITE
    END

    CLOSE site_cursor
    DEALLOCATE site_cursor
    ************************************************** **********

    However, when I run the above query, I get a message saying that the variable @SITE must be declared ! I am not quite sure how to tackle this issue.

    Thanks in advance for your help.
    Suhas

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Where is it failing ? The @SITE needs to be outside the single-quotes.

  3. #3
    Join Date
    Dec 2002
    Posts
    4
    This is the message that I get:

    Server: Msg 137, Level 15, State 2, Line 0
    Must declare the variable '@SITE'.

    If I take out/replace the lines that contain @SITE below, it works fine.

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC xp_sendmail @query = 'SELECT *
    FROM ##DOCInfo
    WHERE AppField2 = @SITE -- take this line out.
    ORDER BY 1,2,3,4,5,6',
    @recipients = 'user_@SITE', -- replace with a valid email address.
    @subject = 'Outbound Document Exceptions for Site: @SITE',
    @width = 132
    FETCH NEXT FROM site_cursor INTO @SITE
    END

    Thanks for your help.
    Suhas

  4. #4
    Join Date
    Dec 2002
    Posts
    4
    I even tried doing it this way:

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC xp_sendmail @query = 'SELECT *
    FROM ##DOCInfo
    WHERE AppField2 = ' @SITE,
    ' ORDER BY 1,2,3,4,5,6',
    @recipients = 'user_'@SITE,
    @subject = 'Outbound Document Exceptions for Site: @SITE',
    @width = 132
    FETCH NEXT FROM site_cursor INTO @SITE
    END

    However, I get the following message:

    Server: Msg 170, Level 15, State 1, Line 95
    Line 95: Incorrect syntax near '@SITE'.

    Thanks,
    Suhas

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    The problem is that you are include the @SITE within the quotes. It needs to be outside the quotes - as a matter of fact I think you have to create a string doing your concatenation and pass that to xp_sendmail.

  6. #6
    Join Date
    Dec 2002
    Posts
    20

    Re: xp_sendmail question.

    Originally posted by stembe


    Did you get a solution to this, as I would be intersted to see it





    Hello Everyone,

    I have a question regarding xp_sendmail. Here is what I am trying to do:

    We have multiple 'sites' (locations) & have created email groups for each of these 'sites'.

    Example: For 'site': 3100, the email group is user_3100. All people at this site/location, belong to this email group.

    The temp table: ##DOCInfo, contains the 'site' number (AppField2), among other things. What I would like to do is pick the relevant info. for each 'site' & email it to the people at that site only. So, this is what I have:

    ************************************************** **********
    USE Master
    DECLARE @SITE char(4)

    -- AppField2 contains the 'site' numbers

    DECLARE site_cursor CURSOR
    FOR
    SELECT DISTINCT AppField2
    FROM ##DOCInfo
    WHERE AppField2 != ' '
    AND AppField2 is NOT NULL
    ORDER BY 1

    OPEN site_cursor
    FETCH NEXT FROM site_cursor INTO @SITE

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC xp_sendmail @query = 'SELECT *
    FROM ##DOCInfo
    WHERE AppField2 = @SITE
    ORDER BY 1,2,3,4,5,6',
    @recipients = 'user_@SITE',
    @subject = 'Outbound Document Exceptions for Site: @SITE',
    @width = 132
    FETCH NEXT FROM site_cursor INTO @SITE
    END

    CLOSE site_cursor
    DEALLOCATE site_cursor
    ************************************************** **********

    However, when I run the above query, I get a message saying that the variable @SITE must be declared ! I am not quite sure how to tackle this issue.

    Thanks in advance for your help.
    Suhas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •