Can't seem to make this connection work in SQL 2008

I have a SQL database that I am trying to access from a web server. I created an ODBC connection and it works just fine, but when I try to connect from my script using the DSN=Datasource option, I get invalid cnnection string. I am running on Windows Server 2003 64 bit processor with SQL 2008 R2. I have tried every combination of strings I can find from this website and others to make it work.

My Datasource in the ODBC connector is set to use SQL Native Client 10.0.

This is my original string:
MM_RFI_Datasource_STRING = "DSN=DATASOURCE;"

I then tried:
MM_RFI_Datasource_STRING = "Driver=SQLNCLI10;DSN=DATASOURCE" same error resulted.

I tired :
"Provider=SQLNCLI10;DSN=DATASOURCE;Uid=userid; Pwd="mypwd";"

I tired :
Driver=SQLNCLI10;Server=MyServerName/MyServerInstance; Uid=userid; Pwd="mypwd";"

Any suggestion as to what I am doing incorrectly would be greatly apreciated.

11 answers

Sounds like this might be an 64-32 bit issue.
Do you get any error message?
Is your website running in 32 bit or 64 bit mode?
There are two odbc admins. The one in control panel is 64 bit if youre using a 64 bit windows.
The 32 bit version of odbc admin is located here.
C:\WINDOWS\SysWOW64\odbcad32.exe

You are absolutley ocrrect in that it is a 64V32 bit issue.

I have tried using both the 32 bit and the 64 bit instance of the ODBC admin.

Originally when I moved my Database from the 32 bit processor to the 64 bit, I could get nothing to owrk, so I set up the connection usign the 32 bit and it worked. I thought I was home free, only to discover that I was connecting to the old server because the DNS entry was incorrect. I have since changed all my hostnames to be IPs so I know I am connecting to the correct server.

Do you think it woudl be wise to delete the DSN and recreate it, making sure I use the 64 bit ODBC admin?

As background, I am neither a SQL expert nor a Windows Expert. Nor do I know VBScript at all. I simply got handed thi stask as there was no other IT person available to do it.

I think you must make sure whether your site is running in 32bit or 64bit mode first.
See this disuccion (it's oracle but the same concept goes)
http://stackoverflow.com/questions/1707101/how-to-compile-a-website-to-use-32-bit-oracle-client-on-64-bit-windows
Then check your IIS app-pool thats used.

Ok. I have verified that the AppPool i srunning in 64Bit mode. I still have the same problem with my connection string. I then reset the AppPool to run in 32 BIT mode, since my scripts are from the 32 bit system, howeve, now I can't even get to my website.. I get Service Unavailable. Can anyone recommend some good books to buy that will help me out? Or online classes that I might enroll in.

64 bit mode should work. Use odbc admin in contol panel. Create a system dsn (not a user dsn). Verify that it works in by pressing test. Run website.

Yes. I have done this so many times. This is why I do not know why the .asp will not connect. It works perfectly fine from the old server. and the DSN tests successfully every time I try it.

what is the web.config file? Because the connection string in this file is not correct. I looked up the format and I tried to edit it but I get permission denied. Either through the IIS or just using wordpad.

Sounds like you've found the problem.
Try see the code if it uses something like Configuration.ConnectionStrings["nameInWebConfig"] its loaded from the web.config file.

I found why I could not edit the web.config and entered the correct server/instancename, but all that did was give me a new error.

The new error is "VBscript error '800a1be'
Object doesn't support named operation 'Active.Connection'.

I have read every web page I coul dfind on this error and the format looks correct.

Dim rsRank
Dim rsRank_numRows
Set rsRank =Server.CreateObject("ADODB.Connection")
(below is the line the error is coming from)
rsRank.Active.connection = MM_RFI_Datasource_STRING

My MM_RFI_Datasource_STRING variable looks like this:
"Provider=SQLNCLI10; DSN=RFI_Datasource;"

I have also tried.
"Provider=SQLOLEDB;Integrated Security=SSPI;Persistance SEcurity Inof=False;Initial Catalog=RFI;Data Source=SERVR/Instancename"

Same result no matter what I try.

I think I finally have it. This porblem turned out to be that it should have been

Set rsRank =Server.CreateObject("ADODB.RecordSet") instead of Connection and the Datastring that works is:

"Provider=SQLNCLI10; DSN=RFI_Datasource"

We can mark this closed. Althought I do not see a solved button.

thanks for the help.