Personally, I wouldn't use driver. I would use provider instead. If I am not mistaken, using DRIVER will cause the application to use ODBC to connect to the database. ODBC is just another layer of software that you don't actually need. So I would do something like this:
Code:
Provider=SQLNCLI10;Server=SERVER-Name\SQLExpress;Database=DataBaseName;Uid=Username; Pwd=Password;
The Provider part can be a little tricky because it depends on what is installed on the client computer.
SQLNCLI10 = SQL Server 2008 native client driver.
SQLNCLI = SQL Server 2005 native client driver
SQLOLEDB = SQL Server 2000 OLEDB provider
SQLOLEDB has been included with operating systems for a very long time, so you can be reasonably certain that it will exist.
The native client drivers are free to download from Microsoft.
Scroll down to "Microsoft SQL Server 2008 Native Client"
Using the native client drivers will be slightly faster. If you are packaging your application for distribution, you'll probably want to include the native client drivers along with your install.
UID = User Identification ?
This is the SQL Server login name. When you use UID, you are essentially saying, "I want to use SQL Authentication". Be default, SQL Authentication is not enabled.
PWD = Password ?
Yes.
APP = ?
This would represent the name of the application that is connecting to SQL Server. You do not need to use APP (I never do). If you do use APP, the name of your application will show up when you run certain queries (like sp_who2).
APP, DATABASE will they always be the same ? Do I need both of them ?
Technically, you don't need either of them. Each login has a default database. By default, the default database is Master, and not your user database. If you do NOT specify database, you will be connected to the login's default database (probably master) instead of the user database that you actually want. I never use APP, but I always use DATABASE.
There's nothing wrong with using APP in your connection string, in fact, I should probably start doing this myself. The APP doesn't have to be the same as the database name. It can be the same, but doesn't have to be. Just like the street you live on could be the same as your last name, but doesn't have to be.
In my app, it is common for my customers to do a yearly process that effectively creates a new database. This allows them to connect to "last year's" database to run reports. So, I could use APP=Widget; DATABASE=Widget2011 and then next year... APP=Widget; DATABASE=Widget2012;
Make sense?
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
