?
Solved

VB / ADO picks wrong version of stored proc to execute.

Posted on 2002-07-15
5
Medium Priority
?
185 Views
Last Modified: 2010-05-02
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.Command")
       
    '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?
0
Comment
Question by:KLRDER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7155270
Try calling it explicitly as follows:
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  
http://www.experts-exchange.com/mssql/Q_20309894.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
http://www.experts-exchange.com/visualbasic/Q_20316127.html

Thanks,
Anthony
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7155340
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.
0
 

Author Comment

by:KLRDER
ID: 7155345
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.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7155790
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!
0
 

Accepted Solution

by:
ComTech earned 0 total points
ID: 7233524
As per request, this question will be placed in PAQ.

Regards,

ComTech
Community Support
Administrator @ EE
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question