SQL connection issue

[JPnyc]

I have a painfully simple app I wrote for in-house use here at work, and it functioned perfectly until we swapped out the server housing the SQLserver DB. I changed the servername in the system DSN (and also tried creating a new one) on the station running the app (mine) but the app no longer makes connection. The DSN successfully connects according to the test, but the app crashes.

Since the old server ran 2000 server, and the new one 2003 server, I thought perhaps there's an updating of provider required. Here's what I'm using:

cnSPR.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnSPR.ConnectionString = "Provider=MSDASQL;DSN=SPR;"

That worked fine, up until now. Any help appreciated.

[RobDog888]

Your provider is showing Access. Try this site for a complete listing of ADO / SQL connection strings.

Connection String

[JPnyc]

Your provider is showing Access. Try this site for a complete listing of ADO / SQL connection strings.

Connection String

Unfortunately, that didn't solve the problem either. There is a network/firewall issue afoot here, I'm sure of it. We tried to create a new system DSN using 2 different logins that are known to exist and be correct, yet both logins failed during the DSN creation process. Only using windows authentication, can we create a DSN, but that still doesn't allow us into the DB. Our network admin is working on it. Thanks for your reply!

[szlamany]

A customer of mine just had this problem - upgrade from 2000 to 2003 would not allow links to the DB anymore.

It was because they did not upgrade the server to MDAC 2.8 before the 2003 upgrade.

This left multiple copies of .DLL's for MDAC in place - bad versions.

There is a MSDN knowledge base article about it - it's more about EXCHANGE, but the same issue is related to SQL.

Try searching MSDN for MDAC and 2003 - maybe that will work...

I just searched and found this bit of bad news for you:

No more JET in MDAC 2.6 and above

Like RD asked - why are you not using the proper provider - you should use SQLOLEDB - make that change before you go any further.

[szlamany]

Here's the KB article about upgrading a 2000 to 2003 server that has MDAC 2.7 on it.

You'll find a problem with Oledb32.dll - check it out...

MDAC problem with 2003 upgrade

[JPnyc]

You guys ROCK. I'm home now, but I'll check on that 1st thing tomorrow. Mille grazie, lads.

[randem]

DUNSEL ,

Try this... Auto OS Updater

This will update everything you need.

[VBHasbeen]

cnSPR.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnSPR.ConnectionString = "Provider=MSDASQL;DSN=SPR;"

You set the Provider in two place WHY?

Take the top one out!!!!!!

[JPnyc]

Yeah, the top one was commented out in the actual code. I also tried the correct SQL server provider, that made no difference either. I have to try what else was suggessted today.

[szlamany]

If you have EXCEL on the machine, attempt to connect to DB with EXCEL.

It's done under the IMPORT DATA, NEW DATA SOURCE, NEW SQL OLEDB connection (something like that).

If you have a problem connecting with EXCEL, then forget about your program and fix the DLL problem that I've posted the MSDN links about.

If you can connect with EXCEL, then the problem lies within your connection string.

[DrHippyHomer]

Make sure you have port 1433 (used by MS-SQL Server) open on the new server. And maybe check you have upto date MDAC installed on server & client machines.

[JPnyc]

Well I can tell you that SQL enterprise manager connects with no trouble. I haven't had time yet to check with excel, or try any of the other suggestions yet. I was late today

[szlamany]

The upgrade of 2000 to 2003 fails if MDAC 2.7 is on the machine.

That is a known issue - KB article posted earlier.

ENTERPRISE MANAGER does not experience the problem.

EXCEL will "reflect" the problem - if you choose SQLOLEDB connection method.

Just went through all this with a customer last month - calls to MICROSOFT to get support help...

Hope this helps.

[JPnyc]

Thanks for all the input folks. The old OS wasn't updated, but rather formatted and installed fresh. Further, the old server was retired completely and the new one had XP Pro loaded, prior to the reformat. I don't know if that abrogates any of the known issues described or not.