pass stored procedure parameters in asp

[c676228]

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?

 

[c676228]

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.

 

[Bob Barrows]

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.

 

[c676228]

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.

 

[Bob Barrows]

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

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

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.

 

[c676228]

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

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

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

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.

 

[Bob Barrows]

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

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.

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


:

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.

 

[Allen Chen [MSFT]]

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:
(e-mail address removed).

==================================================
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]

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

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

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


:

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.

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

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;

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

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


:

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.

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

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

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;

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


:

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]

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

 

[Bob Barrows]

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

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;

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


:

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]

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

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

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


:

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]

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.

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

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


:

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]

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

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.

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


:

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]

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

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.

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


:

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"

 

[Allen Chen [MSFT]]

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

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