64-bit SQLOLEDB connection failure

[Nick Burkitt]

I'm porting an application from the Win32 platform to the x64 platform
(Win2003x64SP2,VS8SP1,C++). The application is failing to connect to a SQL
database (I've tried both 32-bit MSSQL2000 and 64-bit MSSQL2005 servers with
the same results).
The error being returned is "COM error=800a0ea9 msg=Unknown error 0x800A0EA9
source=ADODB.Connection description=Provider is not specified and there is no
designated default provider."
The connection string being used is "Database=<db name>; Server=localhost;
User ID=sa; Password='sa' ; Provider='sqloledb'".
Any clues? Thanks,

-Nick

[Walter Wang [MSFT]]

Hi Nick,

The sqlserver ole db provider should support both 32-bit and 64-bit
processes. This probably is an environment specific issue that related to
corrupt MDAC component installation.

To narrow down the issue, we can test this with a vbscript file:

set db = CreateObject("ADODB.Connection")
db.open "Database=<db name>; Server=localhost; User ID=sa; Password='sa' ;
Provider='sqloledb'"

Test it with both %windir%\system32\cscript.exe and
%windir%\syswow64\cscript.exe and let me know the result.

Regards,
Walter Wang (waw...@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

[Nick Burkitt]

Hi Walter.

Thanks for your reponse. Here are the results of the test you recommended,
using the script

"set db = CreateObject("ADODB.Connection")

db.open "Database=master; Server=localhost; User ID=sa; Password='sa';
Provider='sqloledb'"
":

C:\>%WINDIR%\system32\cscript.exe dbtest.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

C:\dbtest.vbs(2, 1) ADODB.Connection: Provider is not specified and there is
no designated default provider.

C:\>%WINDIR%\syswow64\cscript.exe dbtest.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

<no output>

What's next?

Thanks again,

-Nick

[Walter Wang [MSFT]]

Hi Nick,

It seems the 32-bit provider is working correctly, only the 64-bit provider
failed.

What's the sqlserver version you're using? Is it 32-bit or 64-bit? Have you
tried to test a sqlserver other than the localhost instance?

Windows Server 2003 should have MDAC 2.8 built-in, this issue might be due
to corrupt MDAC installation. Unfortunately we don't have easy way to
reinstall MDAC components on Windows Server 2003.

Please see if following kb helps:

#Component Checker: Diagnose problems and reconfigure MDAC installations
http://support.microsoft.com/kb/307255

You might also try sfc /scannow to check if any system files are corrupt:

#Component Checker: Diagnose problems and reconfigure MDAC installations
http://support.microsoft.com/kb/307255

[Erland Sommarskog]

A alternative would be to use the SQLNCLI provider that comes with
SQL 2005, and which essentially is an updated version of SQLOLEDB to
support features in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Walter Wang [MSFT]]

Hi Nick,

How's everything going? Please feel free to let me know if there's anything
I can help.

[Nick Burkitt]

Hi Walter.

Sorry for the delay, but I just now got back to this part of the project.
The solution was the one suggested by Erland Sommarskog (thanks, Erland!),
namely changing providers to the SQL Native Client (SQLNCLI).
There's a SQL Books article on the subject as well:
http://msdn2.microsoft.com/en-us/library/ms130978.aspx.
Thanks,

-Nick

[Nick Burkitt]

Thanks, Erland, that was the solution to my problem!

-Nick

[Dinesh]

Hi,

We are trying to move to Clustered web server and clustered sql server 2005
64 bit.

I have tried cscript .vbs i works fine.

Can i have the proper connection string that need to connect classic asp page.

Thanks
Dinesh

[Shocker@discussions.microsoft.com Jimmy Shocker]

Hi Walter,

I have the same issue this person had, only their workaround won't work for
me. Would it be possible to take up where that person left off? I have an
XML file from the MDAC checker.
Thanks!
Jimmy