The SQL Server Connection Director is a feature in Visual Studio 2010 beta 2 and SQL Server 2008 R2 CTP. However, Microsoft has already decided that this feature will not be included in the final version of these products. Please do not use this feature in Visual Studio 2010 beta 2 or SQL Server 2008 R2 CTP.
Dear SQL Server developers and users:
It is this time of the year when we in the SQL Connectivity product team take a step back, review the priorities and goals for the long term and identify areas that will benefit from investments.
We view YOU and your organization as a key stakeholder in this process and would like to gather your inputs in this survey, which should take no more than 5 - 10 minutes and a few other surveys that we will conduct in the next few months. The feedback you provide is very valuable and be rest assured that each and every response will be read and will provide the background for some of the key decisions that we will make that will benefit our user community - developers, DBAs and all those who use SQL Server or are looking for ways to expand the scope of the power of SQL Server within your organizations.
This survey will be open for your submissions until October 21, 2009 and can be found here.
So, you've heard about some new ODBC features that were introduced in Windows 7 beta and you wanna know more about it. There's a great blog posting on our sister blog site describing the features. Please go to http://blogs.msdn.com/data/archive/2009/07/06/odbc-dm-3-80-in-windows-7-and-windows-server-2008-r2.aspx for all the goodies.
Jimmy Wu
SQL Server Native Client Team
(Anton Klimov, a developer on the SQL Server Native Client team, wrote the following article.)
Connection strings for an OLE DB provider is a concept that causes a lot of confusion. One problem is that OLE DB itself does not define connection string as a provider concept.
The documentation at Connection String Syntax (http://msdn.microsoft.com/en-us/library/ms722656(VS.85).aspx) says:
"A connection string is a string version of the initialization properties needed to connect to a data store and enables you to easily store connection information within your application or to pass it between applications. Without a connection string, you would be required to store or pass a complex array of structures to access data. When accessing OLE DB directly, using IDataInitialize (http://msdn.microsoft.com/en-us/library/ms714296%28VS.85%29.aspx), the connection string is passed as a parameter to create an OLE DB data source object."
“when accessing OLE DB directly” actually means that the application doesn’t directly instantiate a corresponding provider object, but rather goes through the “service component”, (otherwise known as “core services”, layer (oledb32.dll) by doing something like:
hr = CoCreateInstance(
CLSID_MSDASC, // CLSID of “service component”
NULL, // pUnkOuter
CLSCTX_INPROC_SERVER,
IID_IDataInitialize,
(void**)&m_pIDataInitialize)
;
This way, you get additional services from oledb32 like session pooling, automatic transaction enlistment, cursor engine, and support for additional interfaces.
This also applies to ADO; so for an ADO programmer, a connection string is a similar concept but the way the provider looks at it is frequently misunderstood.
From the provider point of view there is no such thing as a connection string, instead the provider understands the initialization properties, which are hinted to in the above passage as “complex array of structures to access data”. See also OLE DB Initialization Properties: Quick Reference (http://msdn.microsoft.com/en-us/library/ms723996(VS.85).aspx).
What makes the situation especially confusing is that one of the properties is similar to a connection string. These are the so called “Extended Properties” (DBPROP_INIT_PROVIDERSTRING).
http://msdn.microsoft.com/en-us/library/ms723996(VS.85).aspx for it says:
"A string containing provider-specific, extended connection information. Use of this property implies that the consumer knows how this string will be interpreted and used by the provider. Consumers should use this property only for provider-specific connection information that cannot be explicitly described through the property mechanism."
However, in practice, lack of understanding leads consumers to use a combination of keywords both corresponding to the generic OLE DB properties and to provider specific keywords making sense only in the context of the provider string.
For example the site listing lots of examples of connection strings has the following for SQL Server Native Client 10.0 OLE DB Provider (http://www.connectionstrings.com/sql-server-2008):
|
Provider=SQLNCLI10; Server=myServerAddress; Database=myDataBase; Trusted_Connection=yes; MarsConn=yes; |
|
Equivalent key-value pair: "MultipleActiveResultSets=true" equals "MARS_Connection=yes" |
Here “Provider” keyword is used by oledb32 to find a CLSID of the provider to instantiate. “Server”, “Database”, “Trusted_Connection” and “MarsConn” are not the names of the OLE DB initialization properties (which would be “Data Source”, “Integrated Security”, and “Mars Connection”). Moreover, MarsConn=yes will have no effect, see explanation below.
MultipleActiveResultSets is a keyword for ADO.NET , and MARS_Connection is for ODBC.
oledb32 process a connection string as follows: it tries to find the keywords corresponding to the generic OLE DB initialization keywords (corresponding to DBPROPSET_DBINIT), and it also queries a provider by using IDBProperties::GetPropertyInfo (http://msdn.microsoft.com/en-us/library/ms718175(VS.85).aspx) to see which initialization properties provider supports (for SQL Server Native Client it is DBPROPSET_SQLSERVERDBINIT). For matched keywords, the corresponding properties will be set, everything else goes into “Extended Properties”.
Consider the following example in VBScript:
set connection = CreateObject("ADODB.Connection")
connection.ConnectionString = "provider=sqlncli10;data source=akl6; integrated security=sspi;initial catalog=tempdb"
connection.Open
WScript.Echo connection.ConnectionString
Here is the result of the execution:
Provider=SQLNCLI10.1;Integrated Security=sspi;Initial Catalog=tempdb;Data Source=akl6;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AKL5;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False
If we try the following connection string
"provider=sqlncli10;server=akl6;database=tempdb;Trusted_Connection=yes;MarsConn=yes"
we will get:
Provider=SQLNCLI10.1;Extended Properties="server=akl6;database=tempdb;Trusted_Connection=yes;MarsConn=yes";Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=AKL5;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connect
ion=False;DataTypeCompatibility=0;Trust Server Certificate=False
As you can see all the keywords that we specified are bundled into “Extended Properties” since they are not recognized as property names. What is worse is that now we apparently have two keywords corresponding to MARS Connectivity and one is “yes”, whether the other is “False”.
Which one wins? It turns out that the generic value wins. So the setting “MarsConn=yes” as a part of OLE DB connection string has no effect. It would work only if the provider was initialized directly and provider specific property was not used, but “Extended Properties” was used.
If you have the following:
“Server=server1; Data Source=server2;”
The data Source will win.
Generic Initialization properties (DBPROPSET_DBINIT)
The following list shows property IDs and their associated description/keywords:
|
Property ID |
Description / keyword |
|
DBPROP_AUTH_INTEGRATED |
Integrated Security |
|
DBPROP_AUTH_PASSWORD |
Password |
|
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO |
Persist Security Info |
|
DBPROP_AUTH_USERID |
User ID |
|
DBPROP_INIT_ASYNCH |
Asynchronous Processing |
|
DBPROP_INIT_CATALOG |
Initial Catalog |
|
DBPROP_INIT_DATASOURCE |
Data Source |
|
DBPROP_INIT_HWND |
Window Handle |
|
DBPROP_INIT_LCID |
Locale Identifier |
|
DBPROP_INIT_PROPMT |
Prompt |
|
DBPROP_INIT_PROVIDERSTRING |
Extended Properties |
|
DBPROP_INIT_TIMEOUT |
Connect Timeout |
|
DBPROP_INIT_GENERALTIMEOUT |
General Timeout |
If default value is specified the property cannot be overridden with an “extended property” when used in an OLE DB connection string.
Provider Specific Properties (DBPROPSET_SQLSERVERDBINIT)
The following list shows the associated property IDs, default values (if any) and description/keywords for provider specific properties.
SSPROP_INIT_CURRENTLANGUAGE
(no default value)
Current Language
SSPROP_INIT_NETWORKADDRESS
(no default value)
Network Address
SSPROP_INIT_NETWORKLIBRARY
(no default value)
Network Library
SSPROP_INIT_USEPROCFORPREP
1
Use Procedure for Prepare
SSPROP_INIT_AUTOTRANSLATE
VARIANT_TRUE
Auto Translate
SSPROP_INIT_PACKETSIZE
4096
Packet Size
SSPROP_INIT_APPNAME
(no default value)
Application Name
SSPROP_INIT_WSID
(no default value)
Workstation ID
SSPROP_INIT_FILENAME
(no default value)
Initial File Name
SSPROP_INIT_ENCRYPT
VARIANT_FALSE
Use Encryption for Data
SSPROP_AUTH_REPL_SERVER_NAME
(no default value)
Replication server name connect option
SSPROP_INIT_TAGCOLUMNCOLLATION
VARIANT_FALSE
Tag with column collation when possible
SSPROP_INIT_MARSCONNECTION
VARIANT_FALSE
MARS Connection
SSPROP_INIT_FAILOVERPARTNER
(no default value)
Failover Partner
SSPROP_AUTH_OLD_PASSWORD
(no default value)
Old Password
SSPROP_INIT_DATATYPECOMPATIBILITY
0
DataTypeCompatibility
SSPROP_INIT_TRUST_SERVER_CERTIFICATE
VARIANT_FALSE
Trust Server Certificate
SSPROP_INIT_SERVERSPN
(no default value)
Server SPN
SSPROP_INIT_FAILOVERPARTNERSPN
(no default value)
Failover Partner SPN
Note that the corresponding properties are not going to be set, and as mentioned above, some of the settings might be ignored.
The following list shows keywords recognized in the provider string and its (approximate) corresponding property.
“Server"
DBPROP_INIT_DATASOURCE
“UID"
DBPROP_AUTH_USERID
“PWD"
DBPROP_AUTH_PASSWORD
“APP"
SSPROP_INIT_APPNAME
“WSID"
DBPROP_INIT_CATALOG
“Database"
DBPROP_INIT_CATALOG
“Language"
SSPROP_INIT_CURRENTLANGUAGE
“Network" or “Net” or “Network Library”
SSPROP_INIT_NETWORKLIBRARY
“Address" or “Addr”
SSPROP_INIT_NETWORKADDRESS
“Trusted_Connection"
DBPROP_AUTH_INTEGRATED
“UseProcForPrepare"
SSPROP_INIT_USEPROCFORPREP
“LCID"
Not used
“Prompt"
DBPROP_INIT_PROPMT
“PersistSensitive"
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO
“AutoTranslate" or “Auto Translate”
SSPROP_INIT_AUTOTRANSLATE
“Timeout"
DBPROP_INIT_TIMEOUT
“PacketSize"
SSPROP_INIT_PACKETSIZE
“HWND"
DBPROP_INIT_HWND
“AttachDBFileName"
SSPROP_INIT_FILENAME
“Encrypt"
SSPROP_INIT_ENCRYPT
“MarsConn"
SSPROP_INIT_MARSCONNECTION
“FailoverPartner"
SSPROP_INIT_FAILOVERPARTNER
“DataTypeCompatibility"
SSPROP_INIT_DATATYPECOMPATIBILITY
“TrustServerCertificate"
SSPROP_INIT_TRUST_SERVER_CERTIFICATE
“ServerSPN"
SSPROP_INIT_SERVERSPN
“FailoverPartnerSPN"
SSPROP_INIT_FAILOVERPARTNERSPN
Linked server configuration note:
In the "Linked Server Properties -- New Linked Server" dialog box“ (SQL Server Management Studio go to Server Objects -> Linked Servers -> New Linked Server), the Provider string” really corresponds to “Extended Properties”.
Some users try putting their generic OLE DB Connection string, which leads to the server discarding all or the part of the settings and it could end up connecting to the local machine instead of the machine that the user thinks is specified. For instance if “Failover Partner” is specified in that dialog and it contains a space, the setting will have no effect since in the provider string no space is expected.
Hi, My name is David Schwartz and I own the documentation for SQL Server Native Client and Windows (formerly Microsoft) Data Access Components.
Are there sections in the documentation that don't meet your needs? I'm interested to know your thoughts on the documentation.
My email address is dschwart@microsoft.com and I'll read and reply to your documentation comments. I look forward to hearing from you.
ODBC 3.8 is in beta 1 of the Windows 7 SDK...
ODBC 3.8 includes new features such as
· Executing connection operations asynchronously.
· Streamed output parameters.
· ODBC C data type extensibility.
The SQL Server Native Client team at Microsoft is accepting applications for the position of program manager.
For more information about this position, see:
http://members.microsoft.com/careers/search/details.aspx?JobID=F9C9E835-AB77-449C-9E6B-732AFD1A7EAB
If you are passionate about ODBC and native data providers, please contact Raghu Ram (raghu.ram@microsoft.com) or Zlatko Michailov (zlatko.michailov@microsoft.com).
Earlier this week Microsoft release a PHP driver for SQL Server. You can download it from http://www.microsoft.com/downloads/details.aspx?FamilyId=61BF87E0-D031-466B-B09A-6597C21A2E2A&displaylang=en Documentation is available online at http://msdn.microsoft.com/en-us/library/cc296221.aspx. There is a team blog at http://blogs.msdn.com/sqlphp/ and a community forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=2108&SiteID=1
The SQL Server Driver for PHP represents a new way for Microsoft to help developers access SQL Server from non-Microsoft development environments. The driver is a thin C++ wrapper on top of the Microsoft SQL Server Native Client ODBC driver, translating the PHP calls into ODBC API calls. This approach simplifies development and reduces the surface area from a security standpoint. In the future, we could build drivers for other languages (Ruby, Python, etc.) using this same approach without having to duplicate the low-level client interfaces. By making the source code for the PHP driver available, we are providing a blueprint to communities that do not yet have Microsoft-built SQL Server connectivity to show how they can access SQL Server via our ODBC driver.
The source code for the PHP driver is available from the codeplex site http://www.codeplex.com/SQL2K5PHP
In general we recommend against configuring a deployed application to use a new version of SQL Native Client (SNAC) unless the application developer has certified that the application has been tested with the version of SNAC in question. This is because we don't know how well applications will behave when they encounter differences between a new version of SNAC and earlier versions of SNAC or MDAC/WDAC. In this post we'll look at the types of change we make between releases and how to design an application to anticipate then. Briefly, the types of change that could cause problems can be categorized as:
-
New functionality
-
New data types
-
Error handling
Let's look at each of these in turn.
New functionality
This is an area where there should be no impact unless an application opts-in to use specific new features, so the application developer has complete control and the scope for existing code mis-behaving on upgrade is very small.
New data types
This is the area where most problems occur. With new releases of SQL Server we often add new data types. New types should have no impact on deployed applications when the server is upgraded but the client application isn't. Firstly, for application to be exposed to the new type at all schema changes would need to be made. Secondly, if a schema change is made, applications already deployed will 'see' new types as an existing type, often nvarchar. This is because SQL Server knows what types the client understands from the TDS protocol version the client API uses and will convert new types to pre-existing types when it sends data to clients.
We generally make it possible for an application using an older version of SNAC to detect the actual type (via a 'local type name' property) in case it needs to be aware of the new type but is not able to upgrade to the newer version of SNAC that exposes the type. This means that applications should base their logic on API type codes (eg SQL_VARCHAR in ODBC) rather than local type names (eg 'xml' or 'datetimeoffset') wherever possible. In practice this is the simplest way to code applications, so is no burden.
Problems can arise when a deployed application is configured to use a later version of SNAC than the one it was developed and tested with and when it encounters types introduced since its deployment. The way to avoid this type of problem is to treat types that an application doesn't recognise as nvarchar. ODBC and OLE DB provide conversions to and from nvarchar for all data types. Applications should avoid code such as
switch (dataType) {
case SQL_INTEGER://deal with integer
break;
case SQL_WVARCHAR://deal with nvachar
break;
}
where a new type can introduce undefined behavior. Instead, add code to deal with unrecognised types:
switch (dataType) {
case SQL_INTEGER://deal with integer
break;
case SQL_WVARCHAR://deal with nvachar
break;
default://either (a) add clean error handling code or
// (b) process the type as SQL_WVARCHAR. SNAC
// convert parameter and result values to SQL_C_WVARCHAR
}
One other pitfall is handling types with unspecified size, such as xml and varchar(max). SNAC returns the size of these types as SQL_SS_LENGTH_UNLIMITED in ODBC. This has the value 0 and can cause unpredictable behavior if an application uses it in arithmetic expressions for buffer allocation, for example. ODBC applications should treat a column or parameter size of 0 as 'huge' rather than 'small'. OLE DB returns the size of these types as ~0 (ie all bits set). Both ODBC and OLE DB in SNAC conform to the API specifications in using these values, so this isn't behavior we invented for SNAC alone, though their use isn't common in other products.
Finally, sometimes we strengthen our conformance to API specifications, and this can catch non-conforming applications. For example, in SNAC10 validation of the scale of SQL_TYPE_TIMESTAMP/DBTYPE_DBTIMESTAMP is more strict than in earlier versions. This is because in SQL Server 2008 datetime2 can have a scale (fractional seconds digits) of 0 to 7, where in earlier versions of SQL Server it could only be 0 or 3. It turns out that OLE DB didn't validate the bScale parameter of ICommandWithParameters::SetParameterInfo in MDAC and SNAC9, though it should have. Applications should always adhere to API specifications, even when a particular driver or provider's validation falls short.
Error handling
Newer releases of SNAC will often include changes in error messages. This is one way in which try to make it easier for you to diagnose and resolve issues without having to make a support call. Sometimes we improve the clarity of a message, sometimes we will add hints in the message to suggest the most common causes or cures for particular errors. Therefore, applications should avoid dependencies on error message text and should use error codes (such as SQLSTATE in ODBC) or numbers instead. If you do this you're also making it easier for your application to be used with localized versions of SNAC.
Occasionally when we optimize SNAC in a new release we change the order in which we validate parameters or split or merge code paths. This can mean that when an application tries to do something invalid the error reported changes between releases or an error is reported on a different API call. Generally we aim for earlier error detection so an error that is caught at execute time in one version might be caught at bind time in a leter version. It's also possible that if an API call has multiple errors the error we detect first may change. In almost all cases changes in this area should have no impact on code already debugged and tested with an earlier version of SNAC since the changes invariably mean there is an error in the application logic. Note that errors associated with runtime conditions such as connection failures or duplicate key errors are much less likely to change.
Microsoft SQL Server 2008 Feature Pack RC0, June 2008 is now available for download from http://go.microsoft.com/fwlink/?LinkId=110393
This includes the standalone installer for SQL Server 2008 Native Client (SNAC10).
The Feature Pack also includes a Command Line Utilities installer which enables installation of sqlcmd and bcp on client systems.
In SQL Server 2008, the x64 and Itanium versions of sqlncli.msi will install the 64-bit and 32-bit versions of SQL Server Native Client. The x86 version of sqlncli.msi will only work on 32-bit Windows operating systems and will install the 32-bit version of SQL Server Native Client. You will not be able to run the 32-bit version of sqlncli.msi on a 64-bit Windows operating system.
In prerelease versions of SQL Server 2008, you may find that the 32-bit version of sqlncli.msi does run on a 64-bit operating system. However, this may cause unexpected results. Use the appropriate 64-bit version of sqlncli.msi to install both the 64-bit and 32-bit version of SQL Server Native Client on your 64-bit Windows operating system.
(The content of this entry was developed by Anton Klimov, a software engineer on the SQL Server Native Client team.)
Table-valued, user-defined functions (TVF) can be used as an alternative to views or stored procedures. (
http://technet.microsoft.com/en-us/library/ms191165.aspx)
There is a problem, however, with the existing support for obtaining parameter information from classic (VB script) ADO code -- if you call a TVF as if it were a stored procedure, it is possible to derive parameter information, but execution will fail.
If one tries to prepare TVF as a select statement it is not possible to obtain the parameter information from a SQL OLE DB provider because providers do not support obtaining parameter information for parameters in the ‘FROM’ clause.
The example below obtains the parameter information and then uses it for the parameterized select statement.
Sub ADO_example()
Dim cn As New ADODB.connection
Dim param1 As ADODB.Parameter
cn.ConnectionString = "provider=sqloledb;integrated security=sspi;Data Source=akl3vm1; initial catalog=tempdb"
cn.Open
cn.Execute "if OBJECT_ID('f_distance', 'IF') is not null drop function f_distance"
cn.Execute "create function f_distance (@x int, @y int) returns table " _
& "as return select @x as x, @y as y, sqrt(@x*@x+@y*@y) as distance"
Set cmd1 = CreateObject("ADODB.Command")
cmd1.CommandText = "f_distance"
cmd1.CommandType = adCmdStoredProc
cmd1.ActiveConnection = cn
cmd1.Parameters.Refresh
Rem At this point you will get the parameters collection starting with the retun value
Rem which we will not use.
Rem The command text will be like: "{?=call f_distance(?,?)}"
Rem The 3 lines below would produce "The parameter is incorrect"
'cmd1.Parameters("@x") = 1
'cmd1.Parameters("@y") = 2
'Set rs = cmd1.Execute
Rem We will use a second command
Set cmd2 = CreateObject("ADODB.Command")
cmd2.CommandText = "select * from f_distance(?,?)"
cmd2.CommandType = adCmdText
cmd2.ActiveConnection = cn
Rem The line below would produce "Syntax error or access violation"
'cmd2.Parameters.Refresh
For i = 1 To cmd1.Parameters.Count - 1
Set param1 = cmd1.Parameters(i)
cmd2.Parameters.Append param1
Debug.Print param1.Name, param1.Type
Next i
cmd2.Parameters("@x") = 1
cmd2.Parameters("@y") = 2
Set rs = cmd2.Execute
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Debug.Print rs(i).Name, rs(i).Value
Next i
rs.MoveNext
Loop
End Sub
The SQL Server Customer Service and Support (CSS) group developed a new a more efficient way for you to get SQL Server 2005 hot fixes. For more information on hot fixes and cumulative updates, see http://blogs.msdn.com/psssql/archive/2008/04/25/sql-server-2005-sp3-and-self-service-hotfixes.aspx.
The setup for CTP6 of SQL Server 2008 does not install sqlncli.h in the SDK\Include directory. This is a bug in the CTP6 setup. To get sqlncli.h in the SDK\Include directory, you need to invoke Setup\sqlncli.msi from the directory where you ran setup.exe and when prompted, select Modify. Then, specify that you want the sqlncli.h and .lib files to be installed.
