Linked Server connection issue

We have a SQL Server 2005 Enterprise Edition set up on Server 2003 Standard Edtion OS and one on Server 2003 OS Enterprise Edition. All of them have their Service Pack ones installed. On the 2005 on Standard 2003 OS I can get to linked ODBC connection servers with no problem. On the 2005 SQL Server on the Enterprise Edition I get

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "EAWARMBKUP".

We are not sure whether or not this is caused by something on the OS or on the SQL SERVER software. Any help would be appreciated.

Allow remote connections is on and so is the SQL Browser Service.

I presume it is 32 bit server. There is no 64-bit MSDASQL.

Can you try using the same parameters which you have for the linked server and try connecting to the server from outside of SQL Server. You can use a new UDL file, or a simple VB script for that. Would it work?

|||

if your linked server EAWARMBKUP is a sql2000 (sp3/sp3a or sp4) you should run instcat.sql on that sql2000 instance.

check out http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

also check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=381384&SiteID=1 where is stated that in a 64-bit env, "

You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

"

|||It is a 32 bit system trying to get to a Sybase database. I can get to it from another SQL Server 2005 database with no problem but not from the one we just setup. The one I can get to it from is on a Server 2003 standard edition OS and the new one is on a Server 2003 Enterprise Edition. We have to use Enterprise Edition because we are going to cluster it in Production.|||did you enable oledbconnections in your sql2005 surface area config ?|||I'm not sure how to do that so probably not.|||

Start / programs / Microsoft SQL Server 2005 / configuration tools / SQL Server Surface Area Configuration

Change computer (to your sql2005 where you have the problems)

Surface Area Configuration for Features

Select oledb automation and enable it

|||It is enabled but I get the same message do I need to restart anything?|||

Can you successfully ping the SQL Server machine from the machine where you are executing the linked server query?

Also you may want to review some of the ideas suggested at http://www.aspfaq.com/show.asp?id=2544 to trouble shoot linked server errors.

Thanks

Suroor