Professional Web Applications Themes

Execution of a Stored_procedure - Microsoft SQL / MS SQL Server

Hello!!! I have a problem with this stored_procedure: I declared some variables in my SP and I made a select that returns some data of a certain activity as for example her name. The select is the following: SELECT strBody = dbo.fn_BodyMail(IDActivity,ISNULL (Activity,''''),ISNULL (Description,''''),StartDate,EndDate,ISNULL (Responsible ,'''')), NoticeData = NoticeData,StartDate =StartDate,EndDate = EndDate, strMailAct = EmailActivity, strMailEve = EmailEvent, intIDRole = IDRole, intNoticeRespAct = NoticeRespAct, intNoticeRespEve = NoticeRespEve, intNoticeAdministrator = NoticeAdministrator, intExpiryRespAct = ExpiryRespAct, intExpiryRespEve = ExpiryRespEve, intExpiryAdministrator = ExpiryAdministrator, intExpiryAreaResponsible = ExpiryAreaResponsible, intAdministratorActivity = AdministratorActivity, intAdministratorEvent = AdministratorEvent FROM view_DataMail_NEW With this code: PRINT strBody EXEC strBody) I get 3 ...

  1. #1

    Default Execution of a Stored_procedure

    Hello!!!
    I have a problem with this stored_procedure:
    I declared some variables in my SP and I made a select
    that returns some data of a certain activity as for
    example her name.
    The select is the following:

    SELECT strBody = dbo.fn_BodyMail(IDActivity,ISNULL
    (Activity,''''),ISNULL
    (Description,''''),StartDate,EndDate,ISNULL
    (Responsible ,'''')),
    NoticeData = NoticeData,StartDate =StartDate,EndDate =
    EndDate,
    strMailAct = EmailActivity, strMailEve = EmailEvent,
    intIDRole = IDRole,
    intNoticeRespAct = NoticeRespAct, intNoticeRespEve =
    NoticeRespEve, intNoticeAdministrator =
    NoticeAdministrator,
    intExpiryRespAct = ExpiryRespAct, intExpiryRespEve =
    ExpiryRespEve, intExpiryAdministrator =
    ExpiryAdministrator, intExpiryAreaResponsible =
    ExpiryAreaResponsible, intAdministratorActivity =
    AdministratorActivity, intAdministratorEvent =
    AdministratorEvent
    FROM view_DataMail_NEW

    With this code:
    PRINT strBody
    EXEC strBody)

    I get 3 error messages:

    Server: message 170, level 15, state 1, line 1
    Line 1: incorrect syntax near 'Start'.

    Server: message 132, level 15, state 1, line 1
    Label 'Activity' is alredy declared. In a query batch or
    in a stored procedure label's names must be unambiguous

    Server: message 132, level 15, state 1, line 1
    Label 'Activity' is alredy declared. In a query batch or
    in a stored procedure label's names must be unambiguous

    What could I do to execute SP correctly?

    Thanks!!!

    Ilenia Guest

  2. #2

    Default Re: Execution of a Stored_procedure

    Since you are executing EXEC(strBody), you need to explain what
    "fn_BodyMail" returns given its parameters.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Ilenia" <it> wrote in message
    news:7d5501c35a6c$24667c90$gbl... 


    SriSamp Guest

  3. #3

    Default Re: Execution of a Stored_procedure

    Hi Ilenia,

    Can you please post the complete code of your stored procedure, not only the
    select statement. Two of the errors you listed refer to a label being used
    more than once and I see no labels in the code you posted.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Ilenia" <it> wrote in message
    news:7d5501c35a6c$24667c90$gbl... 


    Jacco Guest

  4. #4

    Default Re: Execution of a Stored_procedure

    Hi Jacco!

    This code is used to create my SP:

    CREATE PROCEDURE sp_SendMail

    AS

    DECLARE strMailAct VARCHAR(20), strMailEve VARCHAR(20),
    strBody VARCHAR(7000)
    DECLARE NoticeData VARCHAR (20), StartDate VARCHAR (20),
    EndDate VARCHAR (20)
    DECLARE intNoticeRespAct INT, intNoticeRespEve INT,
    intNoticeAdministrator INT
    DECLARE intExpiryRespActINT, intExpiryRespEve INT,
    intExpiryAdministrator INT, intExpiryAreaResponsible INT
    DECLARE intAdministratorActivityINT,
    intAdministratorEvent INT
    DECLARE intIDRole INT
    DECLARE Concatena VARCHAR (7000)


    SELECT strBody = dbo.fn_BodyMail(IDActivity,ISNULL
    (Activity,''''),ISNULL
    (Description,''''),StartDate,EndDate,ISNULL
    (Responsible ,'''')),
    NoticeData = NoticeData,StartDate =StartDate,EndDate =
    EndDate,strMailAct = EmailActivity, strMailEve =
    EmailEvent, intIDRole = IDRole,intNoticeRespAct =
    NoticeRespAct, intNoticeRespEve = NoticeRespEve,
    intNoticeAdministrator = NoticeAdministrator,
    intExpiryRespAct = ExpiryRespAct, intExpiryRespEve =
    ExpiryRespEve, intExpiryAdministrator =
    ExpiryAdministrator, intExpiryAreaResponsible =
    ExpiryAreaResponsible, intAdministratorActivity =
    AdministratorActivity, intAdministratorEvent =
    AdministratorEvent
    FROM view_DataMail_NEW

    PRINT strBody
    EXEC (strBody)

    GO


    This code is used to create fn_BodyMail:

    CREATE FUNCTION fn_BodyMail(IDActivity INT, strActivity
    VARCHAR(100), strDescription VARCHAR(100), strStartDate
    VARCHAR(20), strEndDate VARCHAR(20), strResponsible
    VARCHAR (50))

    RETURNS VARCHAR(1000)
    AS
    BEGIN

    DECLARE strBody VARCHAR(1000)

    SET strBody= strActivity + CHAR (13) + CHAR (13) + CHAR
    (13)
    SET strBody= strBody+ 'Start Date Activity: ' +
    strStartDate + CHAR (13)
    SET strBody= strBody+ 'End Date Activity: ' +
    strEndDate + CHAR (13)
    SET strBody= strBody+ 'Activity Responsible: ' +
    dbo.fn_ListActivityResponsibles(IDActivity) + CHAR (13)
    SET strBody= strBody+ 'Event Responsible: ' +
    strResponsible + CHAR (13)
    RETURN strBody

    END




    Ilenia Guest

  5. #5

    Default Re: Execution of a Stored_procedure

    Hi SriSamp!
    What should I do to explain fn_BodyMail's results?


     
    explain what 

    Ilenia Guest

  6. #6

    Default Re: Execution of a Stored_procedure

    Hi Ilenia,

    You can't just use EXEC(strBody) to send a mail. This will run strBody as
    a SQL statement. To send mail in SQL Server you need to use EXEC
    xp_sendmail. See Books Online for the complete syntax and list of
    parameters.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Ilenia" <it> wrote in message
    news:7dbc01c35a72$bf4b98c0$gbl... 


    Jacco Guest

  7. #7

    Default Re: Execution of a Stored_procedure

    Hi Jacco!
    Now I don't send any email.
    Now I have to create body of mail in my SP, because I have
    to associate with her result recordset in Visual Basic.



    Ilenia Guest

  8. #8

    Default Re: Execution of a Stored_procedure

    If you just want to send the result back to the application, instead of EXEC
    (strBody), you can just write SELECT strBody. This will send the string
    that you formed in the SP as a recordset in the client application that you
    can then retrieve.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Ilenia" <it> wrote in message
    news:066d01c35a7a$49241fc0$gbl... 


    SriSamp Guest

  9. #9

    Default Re: Execution of a Stored_procedure

    Hi Ilenia,

    If you want your procedure to return a resultset you can return the result
    of the SELECT statement instead of assigning all the columns to variables:

    CREATE PROCEDURE sp_SendMail

    AS

    SET NOCOUNT ON


    SELECT dbo.fn_BodyMail(IDActivity,ISNULL
    (Activity,''''),ISNULL
    (Description,''''),StartDate,EndDate,ISNULL
    (Responsible ,'''')) AS Mail_Body,
    NoticeData, StartDate, EndDate, EmailActivity, EmailEvent, IDRole,
    NoticeRespAct, NoticeRespEve, NoticeAdministrator, ExpiryRespAct,
    ExpiryRespEve,
    ExpiryAdministrator, ExpiryAreaResponsible, AdministratorActivity,
    AdministratorEvent
    FROM view_DataMail_NEW

    GO


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Ilenia" <it> wrote in message
    news:066d01c35a7a$49241fc0$gbl... 


    Jacco Guest

  10. #10

    Default Re: Execution of a Stored_procedure

    Hi Jacco!
    I have just tried to use your suggestion, but I have had a
    problem with Visual Basic, when I have executed my SP by
    visual Basic.
    Run-time error 3704: 'operation is not allowed where the
    object is closed'.
    Anyway thank you very much for your help!



    Ilenia Guest

  11. #11

    Default Re: Execution of a Stored_procedure

    Hi SriSamp!
    I have just used your suggestion and my SP returns correct
    result in this way. So I can associate this result with a
    recordset in Visual Basic, but it is in a only string.
    Thank you very much for your help!


    Ilenia Guest

  12. #12

    Default Re: Execution of a Stored_procedure

    Do you have SET NOCOUNT ON in your stored procedure?

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Ilenia" <it> wrote in message
    news:064f01c35a89$e9ee3df0$gbl... 


    Jacco Guest

  13. #13

    Default Re: Execution of a Stored_procedure

    While I was doing some tests, I have just decided to put
    SET NOCOUNT ON in my SP.
    At the end of my SP I have written too:
    SELECT strBody, so now I can get result's SP and I can
    associate it with recordset in Visual Basic.
    Could it be all right?


     [/ref]
    had a 
    >
    >
    >.
    >[/ref]
    Ilenia Guest

  14. #14

    Default Re: Execution of a Stored_procedure

    Ilenia (it) writes: 

    This is getting confused. Please post the procedure code as you have
    it now, and also the VB code.

    Unless, you have it all working by now, of course.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  15. #15

    Default Re: Execution of a Stored_procedure

    Hi Erland!
    This is my SP code:
    CREATE PROCEDURE sp_SendMail
    AS
    SET NOCOUNT ON

    DECLARE strMailAct VARCHAR(20), strMailEve VARCHAR(20),
    strBody VARCHAR(7000)
    DECLARE NoticeData VARCHAR (20), StartDate VARCHAR (20),
    EndDate VARCHAR (20)
    DECLARE intNoticeRespAct INT, intNoticeRespEve INT,
    intNoticeAdministrator INT
    DECLARE intExpiryRespActINT, intExpiryRespEve INT,
    intExpiryAdministrator INT, intExpiryAreaResponsible INT
    DECLARE intAdministratorActivityINT,
    intAdministratorEvent INT
    DECLARE intIDRole INT


    SELECT strBody = dbo.fn_BodyMail(IDActivity,ISNULL
    (Activity,''''),ISNULL
    (Description,''''),StartDate,EndDate,ISNULL
    (Responsible ,'''')),
    NoticeData = NoticeData,StartDate =StartDate,EndDate =
    EndDate,strMailAct = EmailActivity, strMailEve =
    EmailEvent, intIDRole = IDRole,intNoticeRespAct =
    NoticeRespAct, intNoticeRespEve = NoticeRespEve,
    intNoticeAdministrator = NoticeAdministrator,
    intExpiryRespAct = ExpiryRespAct, intExpiryRespEve =
    ExpiryRespEve, intExpiryAdministrator =
    ExpiryAdministrator, intExpiryAreaResponsible =
    ExpiryAreaResponsible, intAdministratorActivity =
    AdministratorActivity, intAdministratorEvent =
    AdministratorEvent
    FROM view_DataMail_NEW

    SELECT strBody
    GO

    This is my VB code:
    Sub LoadEmail()
    Dim myRS As New ADODB.Recordset
    Dim strSQL As String, numRec As Integer

    strSQL = "EXEC sp_SendMail "

    myRS.Open strSQL, myConn, 1, 3
    numRec = myRS.RecordCount

    If myRS.EOF Then Exit Sub

    'txtEmail is textbox
    txtEmail = Replace(myRS(0), Chr(13), vbNewLine)

    Set myRS = Nothing
    End Sub


     
    as you have 
    s.asp 
    Ilenia Guest

  16. #16

    Default Re: Execution of a Stored_procedure

    Ilenia (it) writes: 

    It is not likely to have anything to do with the problem, but don't
    name your procedures sp_something, because sp_ is a prefix that is
    reserved for system procedures, and SQL Server will first look these
    up in master.
     

    Since you only use strBody, why bother about all the other variables?
    And if view_DataMail_New includes more than row, you are only returning
    one back to the client? This may be on purpose, but it looks funny.

    And if all you return is a single value, it would be more logical to
    use an OUTPUT parameter, rather than to return a result set.
     

    Since you have SET NOCOUNT ON, there should not be any closed recordsets
    at this point with only a recordcount - unless that UDF performs a
    SET NOCOUNT OFF. But you can always have a loop:

    while myRS.state = adStateClose
    myRS = myRS.NextRecordset

    (Which you need to make a little more complex, because if there are no
    more record sets myRS will become Nothing.)

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Stopping execution
    By dmitriy1980 in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 17th, 02:30 PM
  2. Out of Process execution and .NET
    By charlie in forum ASP.NET Web Services
    Replies: 15
    Last Post: November 8th, 12:01 AM
  3. parallel execution
    By Franco Fellico' in forum PHP Development
    Replies: 3
    Last Post: October 29th, 02:43 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139