| Of course we first look at the flagship, SQL Server provider. A typical Connection string SQL Server looks like this: provider=SQLOLEDB;data source=EM_SERVER\VSdotNET;initial catalog=eLandBill;password=svcslave;persist security info=true;user id=sa;workstation id=EM_SERVER;packet size=4096 Note that if we were using the SqlConnection class the Provider=SQLOLEDB element would not be used. Also if an element (or property) is missing it will use the default setting. We've already gone over most of these settings (when we looked at the Data Link Properties page). Just for completeness, I'll summarize them again here: -
Provider This is the name of the OLEDB data provider, in this case SQLOLEDB is the name of SQL Server provider. -
Data source or server The name of the database server. You can use the Windows machine name, an IP address, a DNS name, an SQL Server alias, or a WINS name. -
Initial catalog or database The name of the database on the server you specified. This is optional but if omitted all references to SQL Server objects must be fully qualified with the database name. -
User ID SQL Server login name of the user if you are not using Windows NT integrated security. -
Password SQL Server password of the user if you are not using Windows NT integrated security. The rest of the elements are defined next . These are SQL Serverspecific parameters. -
Workstation ID The Windows name of the client machine. The Connection class provides this element for you. It can be used to identify who is logged on when viewing SQL Server process information through the Enterprise Manager. -
Persist security information If true, allows the password to be visible in the Connection string. The ADO .NET Connection classes do not return the password in the Connection string if this value is set to false. The default is false. -
Packet size This is the size of each block of data sent from the server back to the client. The default, 4096, should be fine in 99.99999 percent of the cases. In my view, this setting is a holdover from the days of slow networks when tuning the packet size could have a meaningful effect on performance. With today's fast networks, there is no perceptible effect on performance. -
Integrated security Formerly called Trusted_Connection. Indicates the User Authentication mode. This can be set to yes or no. The default value is no. If this property is set to yes, then SQLOLEDB uses Microsoft Windows NT Authentication mode to authorize user access to the SQL Server database specified by the Data Source and Initial Catalog property values. If this property is set to no, SQLOLEDB uses Mixed mode to authorize user access to SQL Server database. SQL Server login and password are specified in the User ID and Password properties. If using Windows NT Authentication mode you do not provide a user ID and password. -
Current language Indicates a SQL Server language name. Identifies the language used for system message selection and formatting. The language must be installed on SQL Server, otherwise opening the Connection will fail. -
Network address Indicates the network address of SQL Server specified by the Data Source property. This is only available when the connection is open . -
Network library Indicates the name of the network library (.dll) used to communicate with SQL Server. The name should not include the path or the .dll file name extension. The default is provided by SQL Server client configuration. You can use this property to force SQL Server client to use a certain protocol to connect. -
Use procedure for prepare Determines whether SQL Server creates temporary stored procedures when commands are prepared. -
Autotranslate Indicates whether OEM/ANSI characters are converted. This property can be set to true or false. The default value is true. If this property is set to true, SQLOLEDB performs OEM/ANSI character conversion when multibyte character strings are retrieved from, or sent to, SQL Server. If this property is set to false, SQLOLEDB does not perform OEM/ANSI character conversion on multibyte character string data. -
Application name Indicates the client application name. This property is useful for determining who is logged on when viewing the process display in the Enterprise Manager. The Connection class sends this to the server automatically so there is no need to manually set this property. If you do, it will override the setting that the Connection class would have provided. -
Connect timeout The amount of time in seconds that the client waits for a Connection response from the server. The default is 30 seconds. If you are using a slow network or are connecting to a very busy server, you may want to increase this value. A value of zero tells the client to wait indefinitely (not a good idea). -
Use encryption for data Tells SQL Server to encrypt data before sending it back to the client. To use this property, SQL Server must have encryption enabled. If it does not, an exception will be thrown on an open attempt. This is not supported when using MSDE.  |
