CODEWITHSUNDEEP
sql-serverconnectionodbc
alancc
alancc

Reputation: 811

A Common ODBC Connection Strings works for SQL Server 2005+

I am using CDatabase in VC2008 to connect to SQL Server via ODBC.

I want to use a common connection string that can work for SQL Sever 2005 and all higher versions.

I check https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/ and see there are many different kinds of connection strings.

Whether I should use:

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;

or

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword; Pwd=myPassword;

Update:

Based on my test, using Driver={SQL Server} or Driver={SQL Server Native Client 10.0} can both connect to SQL Server 2008. I guess the former will works for all SQL Server versions, but the latter will only works for SQL Server 2008. Please confirm my guess.

Upvotes: 4

Views: 1983

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

Both driver will connect to Sql Server.But purpose of both drivers are different.

Driver={SQL Server} :

It is called Windows Data Access Components (WDAC).

It do not support new feature of Sql server 2005+ like xml data type,Multiple active result sets (MARS), user-defined data types (UDT), query notifications etc.

It support only Read Committed transaction isolation.

Driver={SQL Native Client}

This driver is suitable for application that need to take advantage of new feature of Sql server 2005+.

It support both Read Committed transaction and snapshot transaction isolation level.

System.Data.SQLClient

SQLClient is optimize for .NET and database Sql server 2000+. It give maximum performance.

So if your application is new or existing then use SQL Native Client.It can access all feature of connected version of Sql server 2005,2008 etc.

ODBC :Use ODBC when application is connected to multiple database or it is expected that database may change.DAL code is written in such a manner it will work for any database.

Suggested Reading

Common Connection : I don't think there can be any common connection.Suppose in my system SQL server native client 10 is install then I know that I cannot connect to Sql server 2016 + .SQL server native client 10 will work for Sql Server 2016 and below.

Of course by programming we can make dynamic connection string which will accept driver as parameter etc.

To connect to higher version ,s I will hv to upgrade my driver and do minor change in connection string.Or if you don't want to touch code then Use DSN.

Yes I forgot, Driver={SQL Server Native Client} it can connect to Sql Srver 2005 only.if you try to connect to higher version then it will throw error.

Upvotes: 4

Related Questions