SQL Server/ADO/XP problem "query cannot be executed"

[George Copeland]

This is a request for help fixing a SQL Server 2000/ADO problem on Windows
XP. I would appreciate any useful assistance.

PROBLEM: SQL Server access on my machine fails as follows:

1. All of my VB6 apps reference the following ADO typelib:
Microsoft ActiveX Data Objects 2.7 Library

Located at:
c:\Program Files\Common Files\System\AD O\msado27.tlb

When I open one of these projects in VB6, the reference to msado27.tlb is
shown as MISSING. If I remove the reference then open the references dialog
again, msado27.tlb is listed normally. When I check it and try to close the
references dialog, I take an "Error in loading DLL".

I can remove the reference to msado27.tlb and add a reference to msado15.dll
(Microsoft ActiveX Data Objects 2.8 Library).

When I run the app, I take a trappable error on the first ADO line my app
executes:

Dim lo_Connection As ADODB.Connectio n

Set lo_Connection = New ADODB.Connectio n

The error is:
Automation error
The specified procedure could not be found.

2. In Enterprise Manager, when I do "Open Table/Return All Rows" on any
table, I take the following error:
"The query cannot be executed because some files are either missing or not
registered. Run setup again to make sure the required files are
registered."


CURRENT CONFIGURATION
I develop software in VB6 that accesses SQL Server via a dsn-less
connections and disconnected recordsets. I am running SQL Server 2000
Standard locally, version installed from the MSDN install CD and updated by
service pack download.

SQL Server version: version 8.00.760 (SP3)

VB version: VB6 SP5

Component Checker 2.0 shows no problems, and reports:
The following product releases were matched: MDAC 2.8 RTM.

My OS is Windows XP Home. As far as I can tell, it is completely updated
with all hotfixes, including the following:
KB810218
KB820291
KB821253
KB822603
KB823182
KB824105
KB824141
KB826939
KB828035
KB829558
Q322011
Q327979
Q814995
Q815485


ATTEMPTS TO FIX
A. I have uninstalled and reinstalled SQL Server and client tools *numerous*
times with no change.

B. Queries in Query Analyzer execute just fine.

C. The last four lines out of sqlstp.log (located in c:\Windows) are as
follows:

12:40:04 StatsGenerate returned: 2
12:40:04 StatsGenerate (0x0,0x1,0xf000 000,0x100,1033, 303,0x0,0x1,0,0 ,0
12:40:04 StatsGenerate -1,George Copeland)
12:40:04 Installation Succeeded

D. The only services running during setup are those specified by Microsoft
KB article 192710. There are no relevant errors in the event log.

E. I tried reinstalling MDAC 2.7 SP1 Refresh and MDAC 2.8. No change.

F. Microsoft KB Article 315868
(http://support.microsoft.com/default...en-us%3B315868) says
the following:

If the reinstallation of the SQL Server 2000 Client Tools does not correct
the problem, then re-register the following DLLs by using Regsvr32.exe, and
then restart the computer:
C:\Program files\Common Files\System\AD O\Msado15.dll
C:\Program files\Common Files\System\Ol e Db\Oledb32.dll
C:\Program files\Common Files\System\Ol e Db\Sqloledb.dll
C:\WINNT\System 32\Atl.dll

Attempting to register the first three dlls fails with
"LoadLibrary("C :\Program files\Common Files\System\AD O\Msado15.dll")
failed - The specified procedure could not be found.". In any case, this
does nothing to fix the problem. And since these link libraries are not
registerable, this KB article is just a little, shall I say, unhelpful.


Please note that the MS newsgroups are FULL of requests for help on this
EXACT problem. All attempts to help have failed--NOBODY has figured out the
fix. Most people trying to help are treating it as an installation issue,
because that is what the error message says. However, I suspect instead
that a Windows XP hotfix has CLOBBERED ADO.

BTW, this is a BIG issue for me. My development machine is hosed, and until
I get this fixed, I AM COMPLETELY DOWN.

[George Copeland]

Re: SQL Server/ADO/XP problem "query cannot be executed"

"Rand Boyd [MSFT]" <rboyd@onlinemi crosoft.com> wrote in message
news:xpJ0lxVIEH A.3064@cpmsftng xa10.phx.gbl...
[color=blue]
> I suspect something is wrong with the MDAC installation. Unfortunately,
> once a version of MDAC is installed you cannot reinstall it without taking
> other steps.
>
> Look at the following article and use the resolution from it to see if you
> can get MDAC 2.8 to reinstall.
>
> The article references MDAC 2.6 and 2.7 but I believe it will apply to[/color]
MDAC[color=blue]
> 2.8 as well.
>
> http://support.microsoft.com/?kbid=286442
> PRB: MDAC 2.6/2.7 Roll Back Installation or Get Error on Install[/color]

Thanks a million, that fixed the problem!

Some notes about the KB article:

1. My dasetup.log did not show an install failure as the article said it
should.
2. Renaming Odbcconf.dll did not fix the problem.
3. Even though the article warned against it, I downloaded and ran the
Unmgrt.exe tool then reinstalled MDAC 2.8 according to the instructions.
4. After the install, Windows required a reboot. On shutdown, there were
several programs that crashed. Nothing in the event logs.
5. After the reboot, queries in Enterprise Manager and native ADO worked
just fine.

Great job!

[George Copeland]

Re: SQL Server/ADO/XP problem &quot;query cannot be executed&quot;

"Rand Boyd [MSFT]" <rboyd@onlinemi crosoft.com> wrote in message
news:xpJ0lxVIEH A.3064@cpmsftng xa10.phx.gbl...
[color=blue]
> I suspect something is wrong with the MDAC installation. Unfortunately,
> once a version of MDAC is installed you cannot reinstall it without taking
> other steps.
>
> Look at the following article and use the resolution from it to see if you
> can get MDAC 2.8 to reinstall.
>
> The article references MDAC 2.6 and 2.7 but I believe it will apply to[/color]
MDAC[color=blue]
> 2.8 as well.
>
> http://support.microsoft.com/?kbid=286442
> PRB: MDAC 2.6/2.7 Roll Back Installation or Get Error on Install[/color]

Thanks a million, that fixed the problem!

Some notes about the KB article:

1. My dasetup.log did not show an install failure as the article said it
should.
2. Renaming Odbcconf.dll did not fix the problem.
3. Even though the article warned against it, I downloaded and ran the
Unmgrt.exe tool then reinstalled MDAC 2.8 according to the instructions.
4. After the install, Windows required a reboot. On shutdown, there were
several programs that crashed. Nothing in the event logs.
5. After the reboot, queries in Enterprise Manager and native ADO worked
just fine.

Great job!

[Efrat]

Re: SQL Server/ADO/XP problem &quot;query cannot be executed&quot;

"George Copeland" <tma102@DELETET HISmail.com> wrote in message news:<NkTec.740 1$eO7.6817@news svr24.news.prod igy.com>...[color=blue]
> "Rand Boyd [MSFT]" <rboyd@onlinemi crosoft.com> wrote in message
> news:xpJ0lxVIEH A.3064@cpmsftng xa10.phx.gbl...
>[color=green]
> > I suspect something is wrong with the MDAC installation. Unfortunately,
> > once a version of MDAC is installed you cannot reinstall it without taking
> > other steps.
> >
> > Look at the following article and use the resolution from it to see if you
> > can get MDAC 2.8 to reinstall.
> >
> > The article references MDAC 2.6 and 2.7 but I believe it will apply to[/color]
> MDAC[color=green]
> > 2.8 as well.
> >
> > http://support.microsoft.com/?kbid=286442
> > PRB: MDAC 2.6/2.7 Roll Back Installation or Get Error on Install[/color]
>
> Thanks a million, that fixed the problem!
>
> Some notes about the KB article:
>
> 1. My dasetup.log did not show an install failure as the article said it
> should.
> 2. Renaming Odbcconf.dll did not fix the problem.
> 3. Even though the article warned against it, I downloaded and ran the
> Unmgrt.exe tool then reinstalled MDAC 2.8 according to the instructions.
> 4. After the install, Windows required a reboot. On shutdown, there were
> several programs that crashed. Nothing in the event logs.
> 5. After the reboot, queries in Enterprise Manager and native ADO worked
> just fine.
>
> Great job![/color]


Hi,

In the article is written :
Put the Unmigrt.cmd and Unmigrt.rsp files in a folder on the computer
that has the problem.
What does it mean??
what is the folder on the computer that has the problem??
doe it the Microsoft SQL Server folder??
When I try to run the Unmgrt.exe I get the message can find
mdacxpak.inf file
Do you get this error?

Thanks
Efrat

[Erland Sommarskog]

Re: SQL Server/ADO/XP problem &quot;query cannot be executed&quot;

Efrat (shachare@bguma il.bgu.ac.il) writes:[color=blue]
> In the article is written :
> Put the Unmigrt.cmd and Unmigrt.rsp files in a folder on the computer
> that has the problem.
> What does it mean??
> what is the folder on the computer that has the problem??[/color]

It means any folder. For instance C:\TEMP\MDAC.
[color=blue]
> When I try to run the Unmgrt.exe I get the message can find
> mdacxpak.inf file
> Do you get this error?[/color]

I have not tried running these programs myself, but as I read the KB
article, you should also put mdac_typ.exe into the same folder.
(mdac_typ.exe is the installation file for MDAC.) Did you do this?


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[George Copeland]

Re: SQL Server/ADO/XP problem &quot;query cannot be executed&quot;

"Efrat" <shachare@bguma il.bgu.ac.il> wrote in message
news:bcb64ccd.0 405110407.1a8a5 a6b@posting.goo gle.com...
[color=blue]
> Put the Unmigrt.cmd and Unmigrt.rsp files in a folder on the computer
> that has the problem.
> What does it mean??[/color]

I don't know any other way to say it than what it says in the instructions.
You unzip the download on the computer that is throwing the ADO errors.
[color=blue]
> what is the folder on the computer that has the problem??[/color]

Wherever you unzip the download.
[color=blue]
> doe it the Microsoft SQL Server folder??[/color]

It is not a SQL Server problem, it is an MDAC problem, an ADO problem, a
data access over the network problem.
[color=blue]
> When I try to run the Unmgrt.exe I get the message can find
> mdacxpak.inf file
> Do you get this error?[/color]

Unbgrt.exe is a self-executing zip. Run it and take all the defaults.