SQL Server connection string issue 1

[wbodger]

We have recently upgraded to 2008 r2, but still have a large legacy classic asp application. While adding some new functionality I decided to get rid of some old DSNs with a new DNSless connection string, so I tried

"Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=myuser;password=mypwd;Initial Catalog=db;Data Source=server;"

which gives me the error

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Next I tried

"Provider=SQLOLEDB.1;Server=server;Database=db;UID=myuser;PWD=myuserpwd;"

which worked great, however... Is MS not deprecating OLEDB so we should be moving to ODBC? Is the SQL Native Client ODBC or OLEDB? I have looked around quite a bit at connection strings and still feel fairly unsure on this issue. Going forward what is the safest connection string and what might I be missing with the SQLNCLI10 connection string?

Thx,
wb

 

[gmmastros]

It's hard to say with certainty, but I do have a guess.

First, understand that there are certain "providers" installed on the computer along with the operating system. OLEDB happens to be one of them.

In order to use a certain provider, you may need to install it. You mentioned that this is a classic asp site you are working on, which probably simplifies things a bit because you would only need to install the provider on the web server.

There is a way you can tell which providers are installed on a computer. Right click the desktop, click New, Click Text Document. Change the extension to UDL, and then double click on this file. You should see a window called "Data Link Properties". Click on the "Provider" tab.

At this point, you will see a list of providers that are installed on the computer. I guarantee you will see "Microsoft OLEDB Provider for SQL Server" because you said this one works. I suspect you will NOT see "SQL Server Native Client 10.0" (when you do this on the web server).

To install the provider, go to:

http://www.microsoft.com/en-us/download/details.aspx?id=26728

There are a lot of things to download on this page. If your server has a 64 bit operating system, then you will want this one: 1033\x64\sqlncli.msi

If the server has a 32 bit operating system, get this one:
1033\x86\sqlncli.msi

Once you install the provider on the web server, you should be able to use it in your connection string.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Thanks, I forget that the drivers need to be installed on the web server. Sure enough, there are only oledb drivers on the web server. So, is the SQL Native client an ODBC connection or another proprietary MS connection that they may deprecate?

wb

 

[gmmastros]

http://msdn.microsoft.com/en-us/library/ms131415.aspx

"The SQL Server Native Client ODBC driver complies with the Microsoft Win32 ODBC 3.51 specification. The driver supports applications written using earlier versions of ODBC in the manner defined in the ODBC 3.51 specification."



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

thank you!