VB / ADO picks wrong version of stored proc to execute.
I have two procs with the same name. One is compiled under DBO, and the other is compiled under my user ID.
The code to set up the command object is
Dim cmd As ADODB.Command
Set cmd = CtxCreateObject("ADODB.Com
'Run the procedure
cmd.ActiveConnection = sConnect
cmd.CommandType = adCmdStoredProc
cmd.CommandText = strSQL
By doing this, ADO has always grabbed the version of the stored proc that was compiled under my ID, if there was one, before trying to grab the one compiled under DBO.
However, I notice that when I check the parameters on the Command object after executing that last line, I see that the parameters are from the proc executed under DBO.
This only started happening today. This component has worked correctly for almost a year.
Any ideas why ADO would pick the DBO proc over the proc with the USER ID on the connection?
The code to set up the command object is
Dim cmd As ADODB.Command
Set cmd = CtxCreateObject("ADODB.Com
'Run the procedure
cmd.ActiveConnection = sConnect
cmd.CommandType = adCmdStoredProc
cmd.CommandText = strSQL
By doing this, ADO has always grabbed the version of the stored proc that was compiled under my ID, if there was one, before trying to grab the one compiled under DBO.
However, I notice that when I check the parameters on the Command object after executing that last line, I see that the parameters are from the proc executed under DBO.
This only started happening today. This component has worked correctly for almost a year.
Any ideas why ADO would pick the DBO proc over the proc with the USER ID on the connection?
I would check to see which groups have been granted permissions on the dbo proc. It is possible that the dbo proc was not granted to Execute on public before, and now it is (though I would still expect your user qualification to be used before any other qualification granted through a group you are a member of).
You might also take a look at the connect string to ensure that you are logging in as you, and that it hasn't been changed to log the user in as dbo.
You might also look to see if you've been added to the dbo group.
You might also take a look at the connect string to ensure that you are logging in as you, and that it hasn't been changed to log the user in as dbo.
You might also look to see if you've been added to the dbo group.
ASKER
I found out a few minutes ago that the .NET version of MDAC was installed on our test server.
This is a bug in MDAC.
This is a bug in MDAC.
You can post a zero point question to the community support topic area, giving a link to the URL for this question and ask them to delete it and refund your points.
Congratulations!
Congratulations!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strSQL = "dbo.SprocName"
cmd.CommandText = strSQL
Also, please maintain your open questions:
Small table escalating to table locks Date: 06/10/2002 09:29AM PST
https://www.experts-exchange.com/questions/20309894/Small-table-escalating-to-table-locks.html
Retrieving value from xp_cmdshell Sql Server 2000 Date: 02/15/2002 11:42AM PST
MTS/database performance issue Date: 06/25/2002 12:58PM PST
https://www.experts-exchange.com/questions/20316127/MTS-database-performance-issue.html
Thanks,
Anthony