pass stored procedure parameters in asp

Discussion in 'ASP General' started by c676228, May 29, 2009.

  1. c676228

    c676228 Guest

    Hi all,

    I encountered some strange issues when I tried to attach parameters to a
    stored procedure. I don't have the similar issues when there is no need to
    pass parameters from asp program to a stored procedure.
    The adVarChar, adParamInput
    are defined in the include file.

    The stored procedure is like this:
    CREATE proc [dbo].[voidTran]( @productName varchar(25), @tranNumber
    varchar(12))
    and it runs ok in sql analyzer.

    but when I call through my asp program, it seems troublesome.
    ....
    conn.Open "dsn=TXXXX;uid=XXXXX;pwd=mXXXXX"
    .....

    cmdTemp.CommandText = "voidTran" 'stored procedure for exec
    cmdTemp.CommandType = adCmdStoredProc

    cmdTemp.Parameters.Append cmdTemp.CreateParameter("@productName",
    adVarChar, adParamInput, 25, "AC")

    cmdTemp.Parameters.Append cmdTemp.CreateParameter("@tranNumber", adVarChar,
    adParamInput, 12, "VKYF67803456")
    'attach store procedure parameter
    cmdTemp.Execute
    when I didn't add length in the createParameter, the error message is like
    this:

    Provider error '80020005'
    type mismatch

    after add lengths(25, 12) for each parameter.
    The error message is like this:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
    procedure 'voidTran'.

    and voidTran procedure is definitely there. Don't know what I should do.
    I googled, but didn't find something helpful.
    Can you help?
    --
    Betty
    c676228, May 29, 2009
    #1
    1. Advertising

  2. c676228

    c676228 Guest

    Now I chnaged my code to:
    cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    cmdTemp.CommandType = adCmdStoredProc
    cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName", adChar,
    adParamInput)
    cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber", adChar ,
    adParamInput)
    'attach store procedure parameter
    cmdTemp("productName")="AC"
    cmdTemp("tranNumber")="VKYF68483010"
    cmdTemp.Execute

    and the error message is like this:

    Parameter object is improperly defined. Inconsistent or incomplete
    information was provided.


    --
    Betty


    "c676228" wrote:

    > Hi all,
    >
    > I encountered some strange issues when I tried to attach parameters to a
    > stored procedure. I don't have the similar issues when there is no need to
    > pass parameters from asp program to a stored procedure.
    > The adVarChar, adParamInput
    > are defined in the include file.
    >
    > The stored procedure is like this:
    > CREATE proc [dbo].[voidTran]( @productName varchar(25), @tranNumber
    > varchar(12))
    > and it runs ok in sql analyzer.
    >
    > but when I call through my asp program, it seems troublesome.
    > ...
    > conn.Open "dsn=TXXXX;uid=XXXXX;pwd=mXXXXX"
    > ....
    >
    > cmdTemp.CommandText = "voidTran" 'stored procedure for exec
    > cmdTemp.CommandType = adCmdStoredProc
    >
    > cmdTemp.Parameters.Append cmdTemp.CreateParameter("@productName",
    > adVarChar, adParamInput, 25, "AC")
    >
    > cmdTemp.Parameters.Append cmdTemp.CreateParameter("@tranNumber", adVarChar,
    > adParamInput, 12, "VKYF67803456")
    > 'attach store procedure parameter
    > cmdTemp.Execute
    > when I didn't add length in the createParameter, the error message is like
    > this:
    >
    > Provider error '80020005'
    > type mismatch
    >
    > after add lengths(25, 12) for each parameter.
    > The error message is like this:
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    >
    > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
    > procedure 'voidTran'.
    >
    > and voidTran procedure is definitely there. Don't know what I should do.
    > I googled, but didn't find something helpful.
    > Can you help?
    > --
    > Betty
    c676228, May 29, 2009
    #2
    1. Advertising

  3. c676228

    Bob Barrows Guest

    c676228 wrote:
    > Now I chnaged my code to:
    > cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    > cmdTemp.CommandType = adCmdStoredProc
    > cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    > adChar, adParamInput)
    > cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
    > adChar , adParamInput)
    > 'attach store procedure parameter
    > cmdTemp("productName")= "AC"
    > cmdTemp("tranNumber")= "VKYF68483010"
    > cmdTemp.Execute
    >
    > and the error message is like this:


    You have no output parameters. Just call your procedure like this:

    conn.voidTran "AC", "VKYF68483010"

    Make sure the user account being used by the connection has rights to
    that stored procedure.

    --
    HTH,
    Bob Barrows
    Bob Barrows, May 29, 2009
    #3
  4. c676228

    c676228 Guest

    Hi Bob,
    Nice to hear from you again.
    I tried. the error is:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
    procedure 'voidTran'.

    but voidTran stored procedure is there.
    --
    Betty


    "Bob Barrows" wrote:

    > c676228 wrote:
    > > Now I chnaged my code to:
    > > cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    > > cmdTemp.CommandType = adCmdStoredProc
    > > cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    > > adChar, adParamInput)
    > > cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
    > > adChar , adParamInput)
    > > 'attach store procedure parameter
    > > cmdTemp("productName")= "AC"
    > > cmdTemp("tranNumber")= "VKYF68483010"
    > > cmdTemp.Execute
    > >
    > > and the error message is like this:

    >
    > You have no output parameters. Just call your procedure like this:
    >
    > conn.voidTran "AC", "VKYF68483010"
    >
    > Make sure the user account being used by the connection has rights to
    > that stored procedure.
    >
    > --
    > HTH,
    > Bob Barrows
    >
    >
    >
    c676228, May 29, 2009
    #4
  5. c676228

    Bob Barrows Guest

    Then you have a permissions problem. The user account being used in your
    connection string has not been granted permission to execute that
    procedure.

    c676228 wrote:
    > Hi Bob,
    > Nice to hear from you again.
    > I tried. the error is:
    >
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    >
    > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
    > procedure 'voidTran'.
    >
    > but voidTran stored procedure is there.
    > --
    > Betty
    >
    >
    > "Bob Barrows" wrote:
    >
    >> c676228 wrote:
    >>> Now I chnaged my code to:
    >>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    >>> cmdTemp.CommandType = adCmdStoredProc
    >>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    >>> adChar, adParamInput)
    >>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
    >>> adChar , adParamInput)
    >>> 'attach store procedure parameter
    >>> cmdTemp("productName")= "AC"
    >>> cmdTemp("tranNumber")= "VKYF68483010"
    >>> cmdTemp.Execute
    >>>
    >>> and the error message is like this:

    >>
    >> You have no output parameters. Just call your procedure like this:
    >>
    >> conn.voidTran "AC", "VKYF68483010"
    >>
    >> Make sure the user account being used by the connection has rights to
    >> that stored procedure.
    >>
    >> --
    >> HTH,
    >> Bob Barrows


    --
    HTH,
    Bob Barrows
    Bob Barrows, May 29, 2009
    #5
  6. c676228

    c676228 Guest

    Bob,

    But I use the exact same connection string, it doesn't have any problems to
    execute other stored procedures. All those procedures belong to dbo including
    voidTran.
    so I don' have any clues.
    --
    Betty


    "Bob Barrows" wrote:

    > Then you have a permissions problem. The user account being used in your
    > connection string has not been granted permission to execute that
    > procedure.
    >
    > c676228 wrote:
    > > Hi Bob,
    > > Nice to hear from you again.
    > > I tried. the error is:
    > >
    > > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > >
    > > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
    > > procedure 'voidTran'.
    > >
    > > but voidTran stored procedure is there.
    > > --
    > > Betty
    > >
    > >
    > > "Bob Barrows" wrote:
    > >
    > >> c676228 wrote:
    > >>> Now I chnaged my code to:
    > >>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    > >>> cmdTemp.CommandType = adCmdStoredProc
    > >>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    > >>> adChar, adParamInput)
    > >>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
    > >>> adChar , adParamInput)
    > >>> 'attach store procedure parameter
    > >>> cmdTemp("productName")= "AC"
    > >>> cmdTemp("tranNumber")= "VKYF68483010"
    > >>> cmdTemp.Execute
    > >>>
    > >>> and the error message is like this:
    > >>
    > >> You have no output parameters. Just call your procedure like this:
    > >>
    > >> conn.voidTran "AC", "VKYF68483010"
    > >>
    > >> Make sure the user account being used by the connection has rights to
    > >> that stored procedure.
    > >>
    > >> --
    > >> HTH,
    > >> Bob Barrows

    >
    > --
    > HTH,
    > Bob Barrows
    >
    >
    >
    c676228, May 29, 2009
    #6
  7. c676228

    Bob Barrows Guest

    It's permissions...
    Or, you did not create the procedure where you thought you did ...

    c676228 wrote:
    > Bob,
    >
    > But I use the exact same connection string, it doesn't have any
    > problems to execute other stored procedures. All those procedures
    > belong to dbo including voidTran.
    > so I don' have any clues.
    >
    >> Then you have a permissions problem. The user account being used in
    >> your connection string has not been granted permission to execute
    >> that procedure.
    >>
    >> c676228 wrote:
    >>> Hi Bob,
    >>> Nice to hear from you again.
    >>> I tried. the error is:
    >>>
    >>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    >>>
    >>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
    >>> procedure 'voidTran'.
    >>>
    >>> but voidTran stored procedure is there.
    >>> --
    >>> Betty
    >>>
    >>>
    >>> "Bob Barrows" wrote:
    >>>
    >>>> c676228 wrote:
    >>>>> Now I chnaged my code to:
    >>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    >>>>> cmdTemp.CommandType = adCmdStoredProc
    >>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    >>>>> adChar, adParamInput)
    >>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
    >>>>> adChar , adParamInput)
    >>>>> 'attach store procedure parameter
    >>>>> cmdTemp("productName")= "AC"
    >>>>> cmdTemp("tranNumber")= "VKYF68483010"
    >>>>> cmdTemp.Execute
    >>>>>
    >>>>> and the error message is like this:
    >>>>
    >>>> You have no output parameters. Just call your procedure like this:
    >>>>
    >>>> conn.voidTran "AC", "VKYF68483010"
    >>>>
    >>>> Make sure the user account being used by the connection has rights
    >>>> to that stored procedure.
    >>>>
    >>>> --
    >>>> HTH,
    >>>> Bob Barrows

    >>
    >> --
    >> HTH,
    >> Bob Barrows


    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, May 29, 2009
    #7
  8. Hi Betty,

    >But I use the exact same connection string, it doesn't have any problems

    to
    >execute other stored procedures. All those procedures belong to dbo

    including
    >voidTran.
    >so I don' have any clues.


    Could you run SQL Server Profiler at server side to see the query passed
    from client? What's the query?

    http://msdn.microsoft.com/en-us/library/ms187929.aspx

    Can it work if you run the query directly in the management studio?

    Regards,
    Allen Chen
    Microsoft Online Support

    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

    Note: MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 2 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions. Issues of this
    nature are best handled working with a dedicated Microsoft Support Engineer
    by contacting Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
    ==================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Allen Chen [MSFT], Jun 1, 2009
    #8
  9. c676228

    c676228 Guest

    Hi Bob,

    I believe it is the permission issue.
    For now the only thing I can think of is
    voidTran is a stored procedure for deleting records from the database.
    While any other stored procedures I don't have any problems are just
    insert or update records in the database.

    Maybe there is a special permission needed for deleting records?

    --
    Betty


    "Bob Barrows" wrote:

    > It's permissions...
    > Or, you did not create the procedure where you thought you did ...
    >
    > c676228 wrote:
    > > Bob,
    > >
    > > But I use the exact same connection string, it doesn't have any
    > > problems to execute other stored procedures. All those procedures
    > > belong to dbo including voidTran.
    > > so I don' have any clues.
    > >
    > >> Then you have a permissions problem. The user account being used in
    > >> your connection string has not been granted permission to execute
    > >> that procedure.
    > >>
    > >> c676228 wrote:
    > >>> Hi Bob,
    > >>> Nice to hear from you again.
    > >>> I tried. the error is:
    > >>>
    > >>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > >>>
    > >>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
    > >>> procedure 'voidTran'.
    > >>>
    > >>> but voidTran stored procedure is there.
    > >>> --
    > >>> Betty
    > >>>
    > >>>
    > >>> "Bob Barrows" wrote:
    > >>>
    > >>>> c676228 wrote:
    > >>>>> Now I chnaged my code to:
    > >>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    > >>>>> cmdTemp.CommandType = adCmdStoredProc
    > >>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    > >>>>> adChar, adParamInput)
    > >>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("tranNumber",
    > >>>>> adChar , adParamInput)
    > >>>>> 'attach store procedure parameter
    > >>>>> cmdTemp("productName")= "AC"
    > >>>>> cmdTemp("tranNumber")= "VKYF68483010"
    > >>>>> cmdTemp.Execute
    > >>>>>
    > >>>>> and the error message is like this:
    > >>>>
    > >>>> You have no output parameters. Just call your procedure like this:
    > >>>>
    > >>>> conn.voidTran "AC", "VKYF68483010"
    > >>>>
    > >>>> Make sure the user account being used by the connection has rights
    > >>>> to that stored procedure.
    > >>>>
    > >>>> --
    > >>>> HTH,
    > >>>> Bob Barrows
    > >>
    > >> --
    > >> HTH,
    > >> Bob Barrows

    >
    > --
    > Microsoft MVP - ASP/ASP.NET - 2004-2007
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
    c676228, Jun 1, 2009
    #9
  10. c676228

    Bob Barrows Guest

    No. you grant Execute permission to the user for this procedure the same way
    you grant permissions for the other procedures.

    Here is the T-SQL statement to do so:

    Use NameOfYourDatabase
    GO
    GRANT Execute For dbo.voidTran NameOfUser

    This just occurred to me: what is the default schema for NameOfUser? If it
    is not "dbo", change it to "dbo":

    ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;


    c676228 wrote:
    > Hi Bob,
    >
    > I believe it is the permission issue.
    > For now the only thing I can think of is
    > voidTran is a stored procedure for deleting records from the database.
    > While any other stored procedures I don't have any problems are just
    > insert or update records in the database.
    >
    > Maybe there is a special permission needed for deleting records?
    >
    >
    >> It's permissions...
    >> Or, you did not create the procedure where you thought you did ...
    >>
    >> c676228 wrote:
    >>> Bob,
    >>>
    >>> But I use the exact same connection string, it doesn't have any
    >>> problems to execute other stored procedures. All those procedures
    >>> belong to dbo including voidTran.
    >>> so I don' have any clues.
    >>>
    >>>> Then you have a permissions problem. The user account being used in
    >>>> your connection string has not been granted permission to execute
    >>>> that procedure.
    >>>>
    >>>> c676228 wrote:
    >>>>> Hi Bob,
    >>>>> Nice to hear from you again.
    >>>>> I tried. the error is:
    >>>>>
    >>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    >>>>>
    >>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
    >>>>> stored procedure 'voidTran'.
    >>>>>
    >>>>> but voidTran stored procedure is there.
    >>>>> --
    >>>>> Betty
    >>>>>
    >>>>>
    >>>>> "Bob Barrows" wrote:
    >>>>>
    >>>>>> c676228 wrote:
    >>>>>>> Now I chnaged my code to:
    >>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    >>>>>>> cmdTemp.CommandType = adCmdStoredProc
    >>>>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    >>>>>>> adChar, adParamInput)
    >>>>>>> cmdTemp.Parameters.Append
    >>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
    >>>>>>> 'attach store procedure parameter
    >>>>>>> cmdTemp("productName")= "AC"
    >>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
    >>>>>>> cmdTemp.Execute
    >>>>>>>
    >>>>>>> and the error message is like this:
    >>>>>>
    >>>>>> You have no output parameters. Just call your procedure like
    >>>>>> this:
    >>>>>>
    >>>>>> conn.voidTran "AC", "VKYF68483010"
    >>>>>>
    >>>>>> Make sure the user account being used by the connection has
    >>>>>> rights to that stored procedure.
    >>>>>>
    >>>>>> --
    >>>>>> HTH,
    >>>>>> Bob Barrows
    >>>>
    >>>> --
    >>>> HTH,
    >>>> Bob Barrows

    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET - 2004-2007
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"


    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Jun 1, 2009
    #10
  11. c676228

    c676228 Guest

    Bob,
    I got the script from the mgment studio and bettys already under dbo schema.
    CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH DEFAULT_SCHEMA=[dbo]

    And I looked at the properties(mgment studio, right click on stored
    procedure and properties) of voidTran stored procedure and other procedures,
    there isn't anything different. That's bizzare.

    And I did the following:
    use mydatabase
    go
    grant execute on dbo.voidTran to bettys

    still the same error message.


    --
    Betty


    "Bob Barrows" wrote:

    > No. you grant Execute permission to the user for this procedure the same way
    > you grant permissions for the other procedures.
    >
    > Here is the T-SQL statement to do so:
    >
    > Use NameOfYourDatabase
    > GO
    > GRANT Execute For dbo.voidTran NameOfUser
    >
    > This just occurred to me: what is the default schema for NameOfUser? If it
    > is not "dbo", change it to "dbo":
    >
    > ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;
    >
    >
    > c676228 wrote:
    > > Hi Bob,
    > >
    > > I believe it is the permission issue.
    > > For now the only thing I can think of is
    > > voidTran is a stored procedure for deleting records from the database.
    > > While any other stored procedures I don't have any problems are just
    > > insert or update records in the database.
    > >
    > > Maybe there is a special permission needed for deleting records?
    > >
    > >
    > >> It's permissions...
    > >> Or, you did not create the procedure where you thought you did ...
    > >>
    > >> c676228 wrote:
    > >>> Bob,
    > >>>
    > >>> But I use the exact same connection string, it doesn't have any
    > >>> problems to execute other stored procedures. All those procedures
    > >>> belong to dbo including voidTran.
    > >>> so I don' have any clues.
    > >>>
    > >>>> Then you have a permissions problem. The user account being used in
    > >>>> your connection string has not been granted permission to execute
    > >>>> that procedure.
    > >>>>
    > >>>> c676228 wrote:
    > >>>>> Hi Bob,
    > >>>>> Nice to hear from you again.
    > >>>>> I tried. the error is:
    > >>>>>
    > >>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > >>>>>
    > >>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
    > >>>>> stored procedure 'voidTran'.
    > >>>>>
    > >>>>> but voidTran stored procedure is there.
    > >>>>> --
    > >>>>> Betty
    > >>>>>
    > >>>>>
    > >>>>> "Bob Barrows" wrote:
    > >>>>>
    > >>>>>> c676228 wrote:
    > >>>>>>> Now I chnaged my code to:
    > >>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec void
    > >>>>>>> cmdTemp.CommandType = adCmdStoredProc
    > >>>>>>> cmdTemp.Parameters.Append cmdTemp.CreateParameter("productName",
    > >>>>>>> adChar, adParamInput)
    > >>>>>>> cmdTemp.Parameters.Append
    > >>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
    > >>>>>>> 'attach store procedure parameter
    > >>>>>>> cmdTemp("productName")= "AC"
    > >>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
    > >>>>>>> cmdTemp.Execute
    > >>>>>>>
    > >>>>>>> and the error message is like this:
    > >>>>>>
    > >>>>>> You have no output parameters. Just call your procedure like
    > >>>>>> this:
    > >>>>>>
    > >>>>>> conn.voidTran "AC", "VKYF68483010"
    > >>>>>>
    > >>>>>> Make sure the user account being used by the connection has
    > >>>>>> rights to that stored procedure.
    > >>>>>>
    > >>>>>> --
    > >>>>>> HTH,
    > >>>>>> Bob Barrows
    > >>>>
    > >>>> --
    > >>>> HTH,
    > >>>> Bob Barrows
    > >>
    > >> --
    > >> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >> Please reply to the newsgroup. This email account is my spam trap so
    > >> I don't check it very often. If you must reply off-line, then remove
    > >> the "NO SPAM"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET - 2004-2007
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
    c676228, Jun 1, 2009
    #11
  12. c676228

    c676228 Guest

    Hi Allen,
    Here is my stored procedure. Basically what it does is find an order_id for
    a specific transaction number and delete the records from table(i) and table
    vs based on the order_id, that's it.

    It works completely fine from management studio. I am not sure if
    Execute sp_executesql will cause any issue or not.

    Thanks,
    Betty

    CREATE proc [dbo].[voidTran] @productName varchar(25), @tranNumber varchar(12)
    As

    Declare @tableName varchar(15)
    Declare @sqlStr nvarchar(500)
    Declare @orderID nvarchar(26)
    Declare @orderIDOUT nvarchar(26)
    Declare @ParmDefinition nvarchar(500)

    If @productName='TUSA'
    Select @tableName='table0
    If @productName='GUSA'
    Select @tableName='table1'
    If @productName='Select'
    Select @tableName='table2'
    Set NoCount on
    Set @sqlStr=N'Select @orderIDOUT=order_id from ' + @tableName +' where
    pnref=@trxnNumber'

    Set @ParmDefinition=N'@trxnNumber varchar(15), @orderIDOUT varchar(26)
    OUTPUT'
    Execute sp_executesql @sqlStr, @ParmDefinition, @trxnNumber=@tranNumber,
    @orderIDOUT=@orderID OUTPUT

    Select @orderID
    If Len(@orderID) > 0
    Set @sqlStr=N'Delete from ' + @tableName + ' where order_id=@orderID'
    Set @ParmDefinition=N'@orderID varchar(26)'
    Execute sp_executesql @sqlStr, @ParmDefinition, @orderID=@orderID

    Set @sqlStr=N'Delete from vs where order_id=@orderID'
    Execute sp_executesql @sqlStr, @ParmDefinition, @orderID=@orderID
    Set @ParmDefinition=N'@orderID varchar(26)'
    Execute sp_executesql @sqlStr, @ParmDefinition, @orderID=@orderID
    Set NoCount OFF
    Return




    "Allen Chen [MSFT]" wrote:

    > Hi Betty,
    >
    > >But I use the exact same connection string, it doesn't have any problems

    > to
    > >execute other stored procedures. All those procedures belong to dbo

    > including
    > >voidTran.
    > >so I don' have any clues.

    >
    > Could you run SQL Server Profiler at server side to see the query passed
    > from client? What's the query?
    >
    > http://msdn.microsoft.com/en-us/library/ms187929.aspx
    >
    > Can it work if you run the query directly in the management studio?
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Support
    >
    > Delighting our customers is our #1 priority. We welcome your comments and
    > suggestions about how we can improve the support we provide to you. Please
    > feel free to let my manager know what you think of the level of service
    > provided. You can send feedback directly to my manager at:
    > .
    >
    > ==================================================
    > Get notification to my posts through email? Please refer to
    > http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
    >
    > Note: MSDN Managed Newsgroup support offering is for non-urgent issues
    > where an initial response from the community or a Microsoft Support
    > Engineer within 2 business day is acceptable. Please note that each follow
    > up response may take approximately 2 business days as the support
    > professional working with you may need further investigation to reach the
    > most efficient resolution. The offering is not appropriate for situations
    > that require urgent, real-time or phone-based interactions. Issues of this
    > nature are best handled working with a dedicated Microsoft Support Engineer
    > by contacting Microsoft Customer Support Services (CSS) at
    > http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
    > ==================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    >
    c676228, Jun 1, 2009
    #12
  13. c676228

    Bob Barrows Guest

    In Management Studio, open a new query window, logging in with the bettys
    account and try to execute the procedure.

    Alternatively, you can use the EXECUTE AS statement to test the ability of
    the bettys account to run the procedure, like this:

    EXECUTE AS User='bettys'
    exec voidTran ...
    REVERT --revert to your own context


    c676228 wrote:
    > Bob,
    > I got the script from the mgment studio and bettys already under dbo
    > schema. CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH
    > DEFAULT_SCHEMA=[dbo]
    >
    > And I looked at the properties(mgment studio, right click on stored
    > procedure and properties) of voidTran stored procedure and other
    > procedures, there isn't anything different. That's bizzare.
    >
    > And I did the following:
    > use mydatabase
    > go
    > grant execute on dbo.voidTran to bettys
    >
    > still the same error message.
    >
    >
    >
    >> No. you grant Execute permission to the user for this procedure the
    >> same way you grant permissions for the other procedures.
    >>
    >> Here is the T-SQL statement to do so:
    >>
    >> Use NameOfYourDatabase
    >> GO
    >> GRANT Execute For dbo.voidTran NameOfUser
    >>
    >> This just occurred to me: what is the default schema for NameOfUser?
    >> If it is not "dbo", change it to "dbo":
    >>
    >> ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;
    >>
    >>
    >> c676228 wrote:
    >>> Hi Bob,
    >>>
    >>> I believe it is the permission issue.
    >>> For now the only thing I can think of is
    >>> voidTran is a stored procedure for deleting records from the
    >>> database. While any other stored procedures I don't have any
    >>> problems are just insert or update records in the database.
    >>>
    >>> Maybe there is a special permission needed for deleting records?
    >>>
    >>>
    >>>> It's permissions...
    >>>> Or, you did not create the procedure where you thought you did ...
    >>>>
    >>>> c676228 wrote:
    >>>>> Bob,
    >>>>>
    >>>>> But I use the exact same connection string, it doesn't have any
    >>>>> problems to execute other stored procedures. All those procedures
    >>>>> belong to dbo including voidTran.
    >>>>> so I don' have any clues.
    >>>>>
    >>>>>> Then you have a permissions problem. The user account being used
    >>>>>> in your connection string has not been granted permission to
    >>>>>> execute
    >>>>>> that procedure.
    >>>>>>
    >>>>>> c676228 wrote:
    >>>>>>> Hi Bob,
    >>>>>>> Nice to hear from you again.
    >>>>>>> I tried. the error is:
    >>>>>>>
    >>>>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    >>>>>>>
    >>>>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
    >>>>>>> stored procedure 'voidTran'.
    >>>>>>>
    >>>>>>> but voidTran stored procedure is there.
    >>>>>>> --
    >>>>>>> Betty
    >>>>>>>
    >>>>>>>
    >>>>>>> "Bob Barrows" wrote:
    >>>>>>>
    >>>>>>>> c676228 wrote:
    >>>>>>>>> Now I chnaged my code to:
    >>>>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec
    >>>>>>>>> void cmdTemp.CommandType = adCmdStoredProc
    >>>>>>>>> cmdTemp.Parameters.Append
    >>>>>>>>> cmdTemp.CreateParameter("productName", adChar, adParamInput)
    >>>>>>>>> cmdTemp.Parameters.Append
    >>>>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
    >>>>>>>>> 'attach store procedure parameter
    >>>>>>>>> cmdTemp("productName")= "AC"
    >>>>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
    >>>>>>>>> cmdTemp.Execute
    >>>>>>>>>
    >>>>>>>>> and the error message is like this:
    >>>>>>>>
    >>>>>>>> You have no output parameters. Just call your procedure like
    >>>>>>>> this:
    >>>>>>>>
    >>>>>>>> conn.voidTran "AC", "VKYF68483010"
    >>>>>>>>
    >>>>>>>> Make sure the user account being used by the connection has
    >>>>>>>> rights to that stored procedure.
    >>>>>>>>
    >>>>>>>> --
    >>>>>>>> HTH,
    >>>>>>>> Bob Barrows
    >>>>>>
    >>>>>> --
    >>>>>> HTH,
    >>>>>> Bob Barrows
    >>>>
    >>>> --
    >>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    >>>> Please reply to the newsgroup. This email account is my spam trap
    >>>> so
    >>>> I don't check it very often. If you must reply off-line, then
    >>>> remove
    >>>> the "NO SPAM"

    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET - 2004-2007
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"


    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Jun 1, 2009
    #13
  14. c676228

    c676228 Guest

    Yes, I did log in as bettys and execute voidTran, no problem at all.
    --
    Betty


    "Bob Barrows" wrote:

    > In Management Studio, open a new query window, logging in with the bettys
    > account and try to execute the procedure.
    >
    > Alternatively, you can use the EXECUTE AS statement to test the ability of
    > the bettys account to run the procedure, like this:
    >
    > EXECUTE AS User='bettys'
    > exec voidTran ...
    > REVERT --revert to your own context
    >
    >
    > c676228 wrote:
    > > Bob,
    > > I got the script from the mgment studio and bettys already under dbo
    > > schema. CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH
    > > DEFAULT_SCHEMA=[dbo]
    > >
    > > And I looked at the properties(mgment studio, right click on stored
    > > procedure and properties) of voidTran stored procedure and other
    > > procedures, there isn't anything different. That's bizzare.
    > >
    > > And I did the following:
    > > use mydatabase
    > > go
    > > grant execute on dbo.voidTran to bettys
    > >
    > > still the same error message.
    > >
    > >
    > >
    > >> No. you grant Execute permission to the user for this procedure the
    > >> same way you grant permissions for the other procedures.
    > >>
    > >> Here is the T-SQL statement to do so:
    > >>
    > >> Use NameOfYourDatabase
    > >> GO
    > >> GRANT Execute For dbo.voidTran NameOfUser
    > >>
    > >> This just occurred to me: what is the default schema for NameOfUser?
    > >> If it is not "dbo", change it to "dbo":
    > >>
    > >> ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;
    > >>
    > >>
    > >> c676228 wrote:
    > >>> Hi Bob,
    > >>>
    > >>> I believe it is the permission issue.
    > >>> For now the only thing I can think of is
    > >>> voidTran is a stored procedure for deleting records from the
    > >>> database. While any other stored procedures I don't have any
    > >>> problems are just insert or update records in the database.
    > >>>
    > >>> Maybe there is a special permission needed for deleting records?
    > >>>
    > >>>
    > >>>> It's permissions...
    > >>>> Or, you did not create the procedure where you thought you did ...
    > >>>>
    > >>>> c676228 wrote:
    > >>>>> Bob,
    > >>>>>
    > >>>>> But I use the exact same connection string, it doesn't have any
    > >>>>> problems to execute other stored procedures. All those procedures
    > >>>>> belong to dbo including voidTran.
    > >>>>> so I don' have any clues.
    > >>>>>
    > >>>>>> Then you have a permissions problem. The user account being used
    > >>>>>> in your connection string has not been granted permission to
    > >>>>>> execute
    > >>>>>> that procedure.
    > >>>>>>
    > >>>>>> c676228 wrote:
    > >>>>>>> Hi Bob,
    > >>>>>>> Nice to hear from you again.
    > >>>>>>> I tried. the error is:
    > >>>>>>>
    > >>>>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > >>>>>>>
    > >>>>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
    > >>>>>>> stored procedure 'voidTran'.
    > >>>>>>>
    > >>>>>>> but voidTran stored procedure is there.
    > >>>>>>> --
    > >>>>>>> Betty
    > >>>>>>>
    > >>>>>>>
    > >>>>>>> "Bob Barrows" wrote:
    > >>>>>>>
    > >>>>>>>> c676228 wrote:
    > >>>>>>>>> Now I chnaged my code to:
    > >>>>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec
    > >>>>>>>>> void cmdTemp.CommandType = adCmdStoredProc
    > >>>>>>>>> cmdTemp.Parameters.Append
    > >>>>>>>>> cmdTemp.CreateParameter("productName", adChar, adParamInput)
    > >>>>>>>>> cmdTemp.Parameters.Append
    > >>>>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
    > >>>>>>>>> 'attach store procedure parameter
    > >>>>>>>>> cmdTemp("productName")= "AC"
    > >>>>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
    > >>>>>>>>> cmdTemp.Execute
    > >>>>>>>>>
    > >>>>>>>>> and the error message is like this:
    > >>>>>>>>
    > >>>>>>>> You have no output parameters. Just call your procedure like
    > >>>>>>>> this:
    > >>>>>>>>
    > >>>>>>>> conn.voidTran "AC", "VKYF68483010"
    > >>>>>>>>
    > >>>>>>>> Make sure the user account being used by the connection has
    > >>>>>>>> rights to that stored procedure.
    > >>>>>>>>
    > >>>>>>>> --
    > >>>>>>>> HTH,
    > >>>>>>>> Bob Barrows
    > >>>>>>
    > >>>>>> --
    > >>>>>> HTH,
    > >>>>>> Bob Barrows
    > >>>>
    > >>>> --
    > >>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >>>> Please reply to the newsgroup. This email account is my spam trap
    > >>>> so
    > >>>> I don't check it very often. If you must reply off-line, then
    > >>>> remove
    > >>>> the "NO SPAM"
    > >>
    > >> --
    > >> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >> Please reply to the newsgroup. This email account is my spam trap so
    > >> I don't check it very often. If you must reply off-line, then remove
    > >> the "NO SPAM"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET - 2004-2007
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
    c676228, Jun 2, 2009
    #14
  15. c676228

    Bob Barrows Guest

    Well then, all I can suggest is using SQL Profiler to run a trace and make
    sure the expected user accountis being used to connect to the database, and
    that the user is successfully logged in, and that the correct procedure name
    is being called.


    c676228 wrote:
    > Yes, I did log in as bettys and execute voidTran, no problem at all.
    >
    >> In Management Studio, open a new query window, logging in with the
    >> bettys account and try to execute the procedure.
    >>
    >> Alternatively, you can use the EXECUTE AS statement to test the
    >> ability of the bettys account to run the procedure, like this:
    >>
    >> EXECUTE AS User='bettys'
    >> exec voidTran ...
    >> REVERT --revert to your own context
    >>
    >>
    >> c676228 wrote:
    >>> Bob,
    >>> I got the script from the mgment studio and bettys already under dbo
    >>> schema. CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH
    >>> DEFAULT_SCHEMA=[dbo]
    >>>
    >>> And I looked at the properties(mgment studio, right click on stored
    >>> procedure and properties) of voidTran stored procedure and other
    >>> procedures, there isn't anything different. That's bizzare.
    >>>
    >>> And I did the following:
    >>> use mydatabase
    >>> go
    >>> grant execute on dbo.voidTran to bettys
    >>>
    >>> still the same error message.
    >>>
    >>>
    >>>
    >>>> No. you grant Execute permission to the user for this procedure the
    >>>> same way you grant permissions for the other procedures.
    >>>>
    >>>> Here is the T-SQL statement to do so:
    >>>>
    >>>> Use NameOfYourDatabase
    >>>> GO
    >>>> GRANT Execute For dbo.voidTran NameOfUser
    >>>>
    >>>> This just occurred to me: what is the default schema for
    >>>> NameOfUser? If it is not "dbo", change it to "dbo":
    >>>>
    >>>> ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;
    >>>>
    >>>>
    >>>> c676228 wrote:
    >>>>> Hi Bob,
    >>>>>
    >>>>> I believe it is the permission issue.
    >>>>> For now the only thing I can think of is
    >>>>> voidTran is a stored procedure for deleting records from the
    >>>>> database. While any other stored procedures I don't have any
    >>>>> problems are just insert or update records in the database.
    >>>>>
    >>>>> Maybe there is a special permission needed for deleting records?
    >>>>>
    >>>>>
    >>>>>> It's permissions...
    >>>>>> Or, you did not create the procedure where you thought you did
    >>>>>> ...
    >>>>>>
    >>>>>> c676228 wrote:
    >>>>>>> Bob,
    >>>>>>>
    >>>>>>> But I use the exact same connection string, it doesn't have any
    >>>>>>> problems to execute other stored procedures. All those
    >>>>>>> procedures belong to dbo including voidTran.
    >>>>>>> so I don' have any clues.
    >>>>>>>
    >>>>>>>> Then you have a permissions problem. The user account being
    >>>>>>>> used in your connection string has not been granted permission
    >>>>>>>> to execute
    >>>>>>>> that procedure.
    >>>>>>>>
    >>>>>>>> c676228 wrote:
    >>>>>>>>> Hi Bob,
    >>>>>>>>> Nice to hear from you again.
    >>>>>>>>> I tried. the error is:
    >>>>>>>>>
    >>>>>>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    >>>>>>>>>
    >>>>>>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
    >>>>>>>>> stored procedure 'voidTran'.
    >>>>>>>>>
    >>>>>>>>> but voidTran stored procedure is there.
    >>>>>>>>> --
    >>>>>>>>> Betty
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>> "Bob Barrows" wrote:
    >>>>>>>>>
    >>>>>>>>>> c676228 wrote:
    >>>>>>>>>>> Now I chnaged my code to:
    >>>>>>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec
    >>>>>>>>>>> void cmdTemp.CommandType = adCmdStoredProc
    >>>>>>>>>>> cmdTemp.Parameters.Append
    >>>>>>>>>>> cmdTemp.CreateParameter("productName", adChar, adParamInput)
    >>>>>>>>>>> cmdTemp.Parameters.Append
    >>>>>>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
    >>>>>>>>>>> 'attach store procedure parameter
    >>>>>>>>>>> cmdTemp("productName")= "AC"
    >>>>>>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
    >>>>>>>>>>> cmdTemp.Execute
    >>>>>>>>>>>
    >>>>>>>>>>> and the error message is like this:
    >>>>>>>>>>
    >>>>>>>>>> You have no output parameters. Just call your procedure like
    >>>>>>>>>> this:
    >>>>>>>>>>
    >>>>>>>>>> conn.voidTran "AC", "VKYF68483010"
    >>>>>>>>>>
    >>>>>>>>>> Make sure the user account being used by the connection has
    >>>>>>>>>> rights to that stored procedure.
    >>>>>>>>>>
    >>>>>>>>>> --
    >>>>>>>>>> HTH,
    >>>>>>>>>> Bob Barrows
    >>>>>>>>
    >>>>>>>> --
    >>>>>>>> HTH,
    >>>>>>>> Bob Barrows
    >>>>>>
    >>>>>> --
    >>>>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    >>>>>> Please reply to the newsgroup. This email account is my spam trap
    >>>>>> so
    >>>>>> I don't check it very often. If you must reply off-line, then
    >>>>>> remove
    >>>>>> the "NO SPAM"
    >>>>
    >>>> --
    >>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    >>>> Please reply to the newsgroup. This email account is my spam trap
    >>>> so
    >>>> I don't check it very often. If you must reply off-line, then
    >>>> remove the "NO SPAM"

    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET - 2004-2007
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"


    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Jun 2, 2009
    #15
  16. c676228

    c676228 Guest

    Bob,
    Thank you so much for your patience and consistent help.
    I guess I do have to use SQL Profiler. I never used before. I have to try.

    Sincerely,
    --
    Betty


    "Bob Barrows" wrote:

    > Well then, all I can suggest is using SQL Profiler to run a trace and make
    > sure the expected user accountis being used to connect to the database, and
    > that the user is successfully logged in, and that the correct procedure name
    > is being called.
    >
    >
    > c676228 wrote:
    > > Yes, I did log in as bettys and execute voidTran, no problem at all.
    > >
    > >> In Management Studio, open a new query window, logging in with the
    > >> bettys account and try to execute the procedure.
    > >>
    > >> Alternatively, you can use the EXECUTE AS statement to test the
    > >> ability of the bettys account to run the procedure, like this:
    > >>
    > >> EXECUTE AS User='bettys'
    > >> exec voidTran ...
    > >> REVERT --revert to your own context
    > >>
    > >>
    > >> c676228 wrote:
    > >>> Bob,
    > >>> I got the script from the mgment studio and bettys already under dbo
    > >>> schema. CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH
    > >>> DEFAULT_SCHEMA=[dbo]
    > >>>
    > >>> And I looked at the properties(mgment studio, right click on stored
    > >>> procedure and properties) of voidTran stored procedure and other
    > >>> procedures, there isn't anything different. That's bizzare.
    > >>>
    > >>> And I did the following:
    > >>> use mydatabase
    > >>> go
    > >>> grant execute on dbo.voidTran to bettys
    > >>>
    > >>> still the same error message.
    > >>>
    > >>>
    > >>>
    > >>>> No. you grant Execute permission to the user for this procedure the
    > >>>> same way you grant permissions for the other procedures.
    > >>>>
    > >>>> Here is the T-SQL statement to do so:
    > >>>>
    > >>>> Use NameOfYourDatabase
    > >>>> GO
    > >>>> GRANT Execute For dbo.voidTran NameOfUser
    > >>>>
    > >>>> This just occurred to me: what is the default schema for
    > >>>> NameOfUser? If it is not "dbo", change it to "dbo":
    > >>>>
    > >>>> ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;
    > >>>>
    > >>>>
    > >>>> c676228 wrote:
    > >>>>> Hi Bob,
    > >>>>>
    > >>>>> I believe it is the permission issue.
    > >>>>> For now the only thing I can think of is
    > >>>>> voidTran is a stored procedure for deleting records from the
    > >>>>> database. While any other stored procedures I don't have any
    > >>>>> problems are just insert or update records in the database.
    > >>>>>
    > >>>>> Maybe there is a special permission needed for deleting records?
    > >>>>>
    > >>>>>
    > >>>>>> It's permissions...
    > >>>>>> Or, you did not create the procedure where you thought you did
    > >>>>>> ...
    > >>>>>>
    > >>>>>> c676228 wrote:
    > >>>>>>> Bob,
    > >>>>>>>
    > >>>>>>> But I use the exact same connection string, it doesn't have any
    > >>>>>>> problems to execute other stored procedures. All those
    > >>>>>>> procedures belong to dbo including voidTran.
    > >>>>>>> so I don' have any clues.
    > >>>>>>>
    > >>>>>>>> Then you have a permissions problem. The user account being
    > >>>>>>>> used in your connection string has not been granted permission
    > >>>>>>>> to execute
    > >>>>>>>> that procedure.
    > >>>>>>>>
    > >>>>>>>> c676228 wrote:
    > >>>>>>>>> Hi Bob,
    > >>>>>>>>> Nice to hear from you again.
    > >>>>>>>>> I tried. the error is:
    > >>>>>>>>>
    > >>>>>>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > >>>>>>>>>
    > >>>>>>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
    > >>>>>>>>> stored procedure 'voidTran'.
    > >>>>>>>>>
    > >>>>>>>>> but voidTran stored procedure is there.
    > >>>>>>>>> --
    > >>>>>>>>> Betty
    > >>>>>>>>>
    > >>>>>>>>>
    > >>>>>>>>> "Bob Barrows" wrote:
    > >>>>>>>>>
    > >>>>>>>>>> c676228 wrote:
    > >>>>>>>>>>> Now I chnaged my code to:
    > >>>>>>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec
    > >>>>>>>>>>> void cmdTemp.CommandType = adCmdStoredProc
    > >>>>>>>>>>> cmdTemp.Parameters.Append
    > >>>>>>>>>>> cmdTemp.CreateParameter("productName", adChar, adParamInput)
    > >>>>>>>>>>> cmdTemp.Parameters.Append
    > >>>>>>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
    > >>>>>>>>>>> 'attach store procedure parameter
    > >>>>>>>>>>> cmdTemp("productName")= "AC"
    > >>>>>>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
    > >>>>>>>>>>> cmdTemp.Execute
    > >>>>>>>>>>>
    > >>>>>>>>>>> and the error message is like this:
    > >>>>>>>>>>
    > >>>>>>>>>> You have no output parameters. Just call your procedure like
    > >>>>>>>>>> this:
    > >>>>>>>>>>
    > >>>>>>>>>> conn.voidTran "AC", "VKYF68483010"
    > >>>>>>>>>>
    > >>>>>>>>>> Make sure the user account being used by the connection has
    > >>>>>>>>>> rights to that stored procedure.
    > >>>>>>>>>>
    > >>>>>>>>>> --
    > >>>>>>>>>> HTH,
    > >>>>>>>>>> Bob Barrows
    > >>>>>>>>
    > >>>>>>>> --
    > >>>>>>>> HTH,
    > >>>>>>>> Bob Barrows
    > >>>>>>
    > >>>>>> --
    > >>>>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >>>>>> Please reply to the newsgroup. This email account is my spam trap
    > >>>>>> so
    > >>>>>> I don't check it very often. If you must reply off-line, then
    > >>>>>> remove
    > >>>>>> the "NO SPAM"
    > >>>>
    > >>>> --
    > >>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >>>> Please reply to the newsgroup. This email account is my spam trap
    > >>>> so
    > >>>> I don't check it very often. If you must reply off-line, then
    > >>>> remove the "NO SPAM"
    > >>
    > >> --
    > >> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >> Please reply to the newsgroup. This email account is my spam trap so
    > >> I don't check it very often. If you must reply off-line, then remove
    > >> the "NO SPAM"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET - 2004-2007
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
    c676228, Jun 2, 2009
    #16
  17. c676228

    c676228 Guest

    Bob,

    It is definitely a permission issue. I just cannot delete a record through
    asp program.
    I used another way of programming to void a transaction. The program tells
    me that the void is successful. But when I looked at the data in the
    database, the data are still there.
    I used reponse.write to verify that my program got the right command to
    execute the delete statement. Here is the code:

    If Request("ProductName") ="" Then
    Response.Write "You need to select a product." & "<br>"
    Response.End
    Else
    product=Request("ProductName")
    Select Case product
    Case "AC"
    productTable="table1"
    Case "IMED"
    productTable="Table2"
    End Select
    End If
    If Request("TrxnNumber")="" Then
    Response.Write "You need to enter a transaction number." & "<br>"
    Response.End
    Else
    trxnNumber=Trim(Request("TrxnNumber"))
    End If
    cmdTemp.CommandText="Select order_id from " & productTable & " where
    pnref='" & trxnNumber & "'"

    set rs=cmdTemp.Execute

    If rs.EOF Then
    Response.Write "Record was not found."
    Else
    Response.Write rs(0)

    cmdStr="Delete from " & productTable & " where order_id='" & rs(0) & "'"
    conn.Execute(cmdStr)
    cmdStr1="Delete from tis_vs where order_id='" & rs(0) & "'"
    conn.Execute(cmdStr1)

    Response.Write "The transaction " & trxnNumber & " was voided."
    End If

    --
    Betty


    "Bob Barrows" wrote:

    > Well then, all I can suggest is using SQL Profiler to run a trace and make
    > sure the expected user accountis being used to connect to the database, and
    > that the user is successfully logged in, and that the correct procedure name
    > is being called.
    >
    >
    > c676228 wrote:
    > > Yes, I did log in as bettys and execute voidTran, no problem at all.
    > >
    > >> In Management Studio, open a new query window, logging in with the
    > >> bettys account and try to execute the procedure.
    > >>
    > >> Alternatively, you can use the EXECUTE AS statement to test the
    > >> ability of the bettys account to run the procedure, like this:
    > >>
    > >> EXECUTE AS User='bettys'
    > >> exec voidTran ...
    > >> REVERT --revert to your own context
    > >>
    > >>
    > >> c676228 wrote:
    > >>> Bob,
    > >>> I got the script from the mgment studio and bettys already under dbo
    > >>> schema. CREATE USER [bangaltiger] FOR LOGIN [bettys] WITH
    > >>> DEFAULT_SCHEMA=[dbo]
    > >>>
    > >>> And I looked at the properties(mgment studio, right click on stored
    > >>> procedure and properties) of voidTran stored procedure and other
    > >>> procedures, there isn't anything different. That's bizzare.
    > >>>
    > >>> And I did the following:
    > >>> use mydatabase
    > >>> go
    > >>> grant execute on dbo.voidTran to bettys
    > >>>
    > >>> still the same error message.
    > >>>
    > >>>
    > >>>
    > >>>> No. you grant Execute permission to the user for this procedure the
    > >>>> same way you grant permissions for the other procedures.
    > >>>>
    > >>>> Here is the T-SQL statement to do so:
    > >>>>
    > >>>> Use NameOfYourDatabase
    > >>>> GO
    > >>>> GRANT Execute For dbo.voidTran NameOfUser
    > >>>>
    > >>>> This just occurred to me: what is the default schema for
    > >>>> NameOfUser? If it is not "dbo", change it to "dbo":
    > >>>>
    > >>>> ALTER USER NameOfUser WITH DEFAULT_SCHEMA = dbo;
    > >>>>
    > >>>>
    > >>>> c676228 wrote:
    > >>>>> Hi Bob,
    > >>>>>
    > >>>>> I believe it is the permission issue.
    > >>>>> For now the only thing I can think of is
    > >>>>> voidTran is a stored procedure for deleting records from the
    > >>>>> database. While any other stored procedures I don't have any
    > >>>>> problems are just insert or update records in the database.
    > >>>>>
    > >>>>> Maybe there is a special permission needed for deleting records?
    > >>>>>
    > >>>>>
    > >>>>>> It's permissions...
    > >>>>>> Or, you did not create the procedure where you thought you did
    > >>>>>> ...
    > >>>>>>
    > >>>>>> c676228 wrote:
    > >>>>>>> Bob,
    > >>>>>>>
    > >>>>>>> But I use the exact same connection string, it doesn't have any
    > >>>>>>> problems to execute other stored procedures. All those
    > >>>>>>> procedures belong to dbo including voidTran.
    > >>>>>>> so I don' have any clues.
    > >>>>>>>
    > >>>>>>>> Then you have a permissions problem. The user account being
    > >>>>>>>> used in your connection string has not been granted permission
    > >>>>>>>> to execute
    > >>>>>>>> that procedure.
    > >>>>>>>>
    > >>>>>>>> c676228 wrote:
    > >>>>>>>>> Hi Bob,
    > >>>>>>>>> Nice to hear from you again.
    > >>>>>>>>> I tried. the error is:
    > >>>>>>>>>
    > >>>>>>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > >>>>>>>>>
    > >>>>>>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
    > >>>>>>>>> stored procedure 'voidTran'.
    > >>>>>>>>>
    > >>>>>>>>> but voidTran stored procedure is there.
    > >>>>>>>>> --
    > >>>>>>>>> Betty
    > >>>>>>>>>
    > >>>>>>>>>
    > >>>>>>>>> "Bob Barrows" wrote:
    > >>>>>>>>>
    > >>>>>>>>>> c676228 wrote:
    > >>>>>>>>>>> Now I chnaged my code to:
    > >>>>>>>>>>> cmdTemp.CommandText = "voidTran" 'stored procedure for exec
    > >>>>>>>>>>> void cmdTemp.CommandType = adCmdStoredProc
    > >>>>>>>>>>> cmdTemp.Parameters.Append
    > >>>>>>>>>>> cmdTemp.CreateParameter("productName", adChar, adParamInput)
    > >>>>>>>>>>> cmdTemp.Parameters.Append
    > >>>>>>>>>>> cmdTemp.CreateParameter("tranNumber", adChar , adParamInput)
    > >>>>>>>>>>> 'attach store procedure parameter
    > >>>>>>>>>>> cmdTemp("productName")= "AC"
    > >>>>>>>>>>> cmdTemp("tranNumber")= "VKYF68483010"
    > >>>>>>>>>>> cmdTemp.Execute
    > >>>>>>>>>>>
    > >>>>>>>>>>> and the error message is like this:
    > >>>>>>>>>>
    > >>>>>>>>>> You have no output parameters. Just call your procedure like
    > >>>>>>>>>> this:
    > >>>>>>>>>>
    > >>>>>>>>>> conn.voidTran "AC", "VKYF68483010"
    > >>>>>>>>>>
    > >>>>>>>>>> Make sure the user account being used by the connection has
    > >>>>>>>>>> rights to that stored procedure.
    > >>>>>>>>>>
    > >>>>>>>>>> --
    > >>>>>>>>>> HTH,
    > >>>>>>>>>> Bob Barrows
    > >>>>>>>>
    > >>>>>>>> --
    > >>>>>>>> HTH,
    > >>>>>>>> Bob Barrows
    > >>>>>>
    > >>>>>> --
    > >>>>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >>>>>> Please reply to the newsgroup. This email account is my spam trap
    > >>>>>> so
    > >>>>>> I don't check it very often. If you must reply off-line, then
    > >>>>>> remove
    > >>>>>> the "NO SPAM"
    > >>>>
    > >>>> --
    > >>>> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >>>> Please reply to the newsgroup. This email account is my spam trap
    > >>>> so
    > >>>> I don't check it very often. If you must reply off-line, then
    > >>>> remove the "NO SPAM"
    > >>
    > >> --
    > >> Microsoft MVP - ASP/ASP.NET - 2004-2007
    > >> Please reply to the newsgroup. This email account is my spam trap so
    > >> I don't check it very often. If you must reply off-line, then remove
    > >> the "NO SPAM"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET - 2004-2007
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >
    c676228, Jun 2, 2009
    #17
  18. Hi Betty,

    >It is definitely a permission issue. I just cannot delete a record through
    >asp program.


    Have you tried SQL Server Profiler? I believe it's the most useful tool for
    you to troubleshoot this issue. What's the query you see from the SQL
    Server Profiler? Can it work if you execute that query from Management
    studio directly? You can refer to the link I provided in my previous post
    to learn how to use it. Please feel free to ask if you need any assistance
    regarding how to use SQL Server Profiler.

    Regards,
    Allen Chen
    Microsoft Online Support
    Allen Chen [MSFT], Jun 4, 2009
    #18
  19. Hi Betty,

    >It is definitely a permission issue. I just cannot delete a record through
    >asp program.


    Do you have any progress on this issue?

    Regards,
    Allen Chen
    Microsoft Online Support
    Allen Chen [MSFT], Jun 9, 2009
    #19
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike P
    Replies:
    0
    Views:
    3,283
    Mike P
    Jun 19, 2006
  2. Replies:
    2
    Views:
    610
    =?Utf-8?B?TXVoYW1tYWQgTW9zYQ==?=
    Oct 7, 2006
  3. Replies:
    2
    Views:
    4,353
  4. Machelle Chandler

    Using query string to pass a value to a stored procedure parameter

    Machelle Chandler, Oct 21, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    159
    Machelle Chandler
    Oct 21, 2003
  5. Mike Lopez

    Pass a Date to stored procedure

    Mike Lopez, Jan 25, 2005, in forum: ASP .Net Web Services
    Replies:
    1
    Views:
    109
    Mike Lopez
    Jan 25, 2005
Loading...

Share This Page