asked on

Why won't my ODBC connection work after moving from SQL Server 2000 (8) to 2005 (9)? It's classic asp.

I am moving my classic asp application from SQL Server 2000 to Sql Server 2005 (8 to 9). Please don't tell me to rewrite to .Net because this client is not worth it. However, I use a standard connection (application("DS") = "DRIVER={SQL Server};SERVER=IP Address;UID=UserName;PWD=Password;DATABASE=the name"

Anyway this will not work in SQL Server 2005 even though the connection string looks just fine. Unfortunately this site runs on a shared server so I have little control. Is there something that should be done is SQL Server or even IIS that will make this work that I can tell my hosting provider?

Thank you

David Johnson, CD

there is probably a problem with your connection string. check with your provider for the correct connection string

https://www.connectionstrings.com/sql-server/

Q: why are you not using a supported SQL Server version?

Qlemo

There is no difference between connection strings for 2000 and 2005 (and later) if using the OS driver (as here).
Is it really the connection failing, or certain SQLs? What is the error you get?

Vadim Rapp

Please clarify, what is "this will not work". Any error message?

Craig Chapman

ASKER

Again, because it's a shared server all I can see is a useless 500 error. However, I was sent the IIS error log and it fails when I run the first the first stored procedure with the error "Could_not_find_stored_procedure" so I can only assume it is not connecting. Is there any SQL setting I should ask my provider about? Thanks

Craig Chapman

ASKER

The code to run the stored procedure is this"
set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = application("DS")
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("UserName", adVarChar, adParamInput, 16, UserName)
cmd.CommandText="User_Sts"
set rs = cmd.execute
and the message said it can't find user_sts

Also, if I replace the connection string with the one going to the Server 2000 database, it works like a charm

Craig Chapman

ASKER

Why am I not using a supported SQL Server version? Because I'm only upgrading because I need to move from TLS 1.1 to 1.2 and this seemed to be the easy way without worrying about a serious upgrade that could create additional problems. Seemed like a good idea until now.

Craig Chapman

ASKER

My ISP has gotten back to me and tells me it is reporting it can’t use classic ASP. Anyone know a setting that allows classic asp?

Vadim Rapp

If it says "Could_not_find_stored_procedure", it means that it has connected but did not find stored procedure.

Check if stored procedure User_Sts exists in the database specified in the connection string
Check if the username IIS runs under has permissions to execute it
Modify the code like this and try:

set rs= application("DS").execute("exec User_Sts '" & UserName & "'")

Open in new window

Craig Chapman

ASKER

Thanks, yeah the ISP had not moved the stored procedures.

David Johnson, CD

that will do it ... the stored procedure has to exist in order to use it.

David Johnson, CD

it can’t use classic ASP

so ASP is not installed on the version of IIS you have available to you?

Time to find an ISP that supports your requirements

Vadim Rapp

>Thanks, yeah the ISP had not moved the stored procedures.

I did not quite understand what you said. Did you check the permissions on this stored procedure? Did you verify that the database name and stored procedure name is not misspelled? did you try the short code I suggested?

John_Vidmar

When you migrate SQL-2000 database to SQL-2005 then the stored-procedures should be available because they reside in the database (not in a file-folder). Use SQL Server Management Studio to manually run each stored-procedure to make sure they work, for example, RAISERROR changed slightly, the migration won't show an error, but your first execution will.

If your ASP-classic application is configured for 32-bit then you need a 32-bit ODBC/DSN, otherwise, you need a 64-bit ODBC/DSN.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.