Ole DB Provider on SQL 2000 connecting to SQL 2005 express

[Bren2]

Hi,

I have a problem using DTS (within SQL 2000) connecting to SQL 2005 Express using Ole DB provider. It is an old problem that I never got round to solving. I have had a work around in place for years...

On my development pc, I have a developer edition of SQL server 2000. From here I can create a DTS package, drag & drop a "Microsoft OLE DB Provider for SQL Server" & add to this the alias of my SQL server 2005 express edition (using the SQL login & password required). And it connects to the SQL 2005 express server, no problems...

But, this does not work on my live SQL server. The DTS package does not work with "Microsoft OLE DB Provider for SQL Server". To work around this I have to create an ODBC connection (DSN), & utilise the ODBC connection in the DTS.

The DTS when using ODBC executes slowly. I really want to be able to use the Ole DB provider as can be done on my development pc.

I have tried installing SQL server 2005 express on the server running SQL 2000. I've also installed all the latest packs that contain the latest Ole DB providers. MDAC, SQL 2005 tools, etc...

But I still cannot get the DTS to connect to SQL server 2005 express using an Ole Db Provider.

I get an error when I try to connect & set the login & password to the database using Ole DB provider. The error states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.

Anybody got any idea what I need to do to get this to work?

When I run this from my pc using Ole DB provider is is super fast. Running it using ODBC and the "Other Connection" option is extremely slow.

 

[Bren2]

I eventually figured out why this issue occurred & the solution would seem to me to be very strange.

But, just in case someone ever has a similar problem. Here is how the issue was resolved.

I ran a tracert to the IP address of the SQL 2005 express database server.
- I ran one from my development PC.
- I ran the second from my Live SQL server

... & then compared the results of both.

- They were almost identical. The only difference was the very last entry. The final IP had a name against it on the tracert from my pc, & on the live server it only showed the IP.

This was because many years ago, I had added an entry to my hosts file (in c:\windows\system32\drivers\etc). For a completely different reason.

So: I added an entry to the hosts file on my live SQL 2000 server, basically just giving the IP a name. I didn't change anything else.... & magically the MS OLE DB provider worked... Allowing me to DTS from SQL 2000 to my SQL 2005 express edition on a different server.

So much faster than using the ODBC method.