Microsoft SQLNCli team blog

Introducing the new Microsoft ODBC Drivers for SQL Server

2013-01-23T23:08:00Z

Today, we are happy to announce availability of new Microsoft ODBC Drivers for SQL Server on Windows and for SQL Server on Linux.

Introducing the new Microsoft ODBC Drivers for SQL Server

As outlined in a previouspost, Microsoft is adopting ODBC as the de-facto standard for native access to SQL Server and Windows Azure SQL Database. We have provided longstanding support for ODBC on Windows and, in the SQL Server 2012 timeframe, released support for ODBC on Linux (Red Hat Enterprise Linux 5 and 6, and SUSE Enterprise Linux). Microsoft is aligning and renaming the previous ODBC drivers as follows:

SQL Server Native Client (SNAC) along with its ODBC functionality is now called Microsoft ODBC Driver 11 for SQL Server on Windows
Microsoft SQL Server ODBC Driver 1.0 for Linux along with its functionality is now called Microsoft ODBC Driver 11 for SQL Server on Linux

This change will result in our ODBC drivers for Windows and Linux being better aligned going forward. We hope that this change makes it easier for you to find the right driver for your needs and makes it easier to stay informed of new releases. Today’s availability marks the first combined release for your use.

These new ODBC drivers also include additional functionality not available in the previous versions; learn more about the new drivers.

New key features for the Windows version.

Driver-aware connection pooling: Driver-aware connection pooling allows driver writers to customize the connection pooling behavior in their ODBC driver.
Connection resiliency in the Windows ODBC Driver: To help improve resiliency for cloud scenarios, the ODBC driver on Windows can restore broken connections along with a session state to a Windows Azure SQL Database.
Asynchronous Execution (Polling Method): An application thread can call an ODBC function in asynchronous mode and the function can return before the
operation is complete, allowing the application thread to perform other tasks. Beginning in the Windows 8 SDK, you can determine that an asynchronous
operation is complete using the notification method. Learn more about Asynchronous Execution (Polling Method).

Download the Microsoft ODBC Driver 11 for SQL Server on Windows

Download Command line Utilities for Microsoft ODBC Driver 11 for SQL Server

New key features for the Linux version

Connect Linux to Windows Azure SQL Database: You can now connect from both Linux on-premises applications and Windows Azure Linux-supported VMs to Windows Azure SQL Database.
High Availability Support: Support for SQL Server 2012 AlwaysOn Availability Groups to enable High Availability and Disaster Recovery for Linux-based applications.
SQL Trace Support: Expanded tracing now supports Linux ODBC to help you know what your Linux-based application is doing all from the same SQL Trace experience.

Download Microsoft ODBC Driver 11 for SQL Server on Red Hat Linux

Shekhar Joshi, Senior Program Manager, SQL Server Group, Microsoft

Shekhar.joshi@microsoft.com

Preview Release of the SQL Server [C/C++] ODBC Driver for Linux

2011-11-30T18:00:24Z

This has been re-posted from the SQL Server Team Blog.

In our continued commitment to interoperability, we are very excited to announce the availability of a preview release of the SQL Server ODBC Driver for Linux! This release will allow native developers to access Microsoft SQL Server from Linux operating systems. To assist our customers with native applications on multi-platform we have ported our existing, reliable and enterprise-class ODBC for Windows driver (a.k.a. SQL Server Native Client, or SNAC) to the Linux platform. Download the driver today here!

In this release, the SQL Server ODBC Driver for Linux will be a 64-bit driver for Red Hat Enterprise Linux 5. We will support SQL Server 2008 R2 and SQL Server 2012 with this release of the driver. Notable driver features (in addition to what you would expect in an ODBC driver) include support for the Kerberos authentication protocol, SSL and client-side UTF-8 encoding. This release also brings proven and effective tools and the BCP and SQLCMD utilities to the Linux world.

This SQL Server ODBC Driver for Linux provides a Microsoft-supported solution for C and C++ applications that target SQL Server. It also provides a mechanism for applications and runtimes that leverage ODBC to access SQL Server from Linux platforms. For customers who want to move from Sybase to SQL Server, the SQL Server ODBC Driver for Linux allows C and C++ code to continue running in Linux environments. For additional information on the first release of Microsoft ODBC Driver for Linux please refer to our ODBC Driver on Linux Documentation.

This release marks a big milestone in Microsoft’s continued commitment to interoperability and our alignment with ODBC as the preferred way to access SQL Server. Download the driver today; please provide your feedback and ask us questions via our forums; or post a comment via Microsoft Connect. We look forward to hearing from you.

Thanks,

Shekhar Joshi
Senior Program Manager
Microsoft SQL Server ODBC Driver For Linux

Announcing Microsoft SQL Server ODBC Driver for Linux

2011-10-13T00:13:00Z

Greetings Developer community:

We heard yesterday and today at the PASS conference about the exciting new areas that we are investing in bringing the power of SQL Server to our customers. Many of our developers who rely on native connectivity to SQL Server primarily use ODBC for their connectivity needs. We have been supporting ODBC as a part of the SQL Native Access Client (SNAC) libraries. In our continued commitment to interoperability, today we also announced that we will be releasing the Microsoft SQL Server ODBC Driver for Linux. We will be releasing first community technology preview (CTP) around mid-November and will be available along with SQL Server 2012 when it is released. Please look for announcement on our SQL Connectivity home page and SQL Server blog page.

We will be showcasing Microsoft SQL Server ODBC Driver for Linux along with our Java and PHP solutions for SQL Server and Azure at PASS conference session “[AD-211-M] Developing Multi-Platform Applications for Microsoft SQL Server and Azure” on Thursday October 13th at 5:00PM at Washington State Convention Center Room #4C4. Also, if you have any questions or feedback on our multi-platform strategy as well as the entire gamut of support we provide to the application developers, I would encourage you to attend the PASS Panel Discussion with SQL Connectivity Leadership “[AD-101-M] SQL Connectivity Leadership Unplugged” on Friday, October 14, 2011, 2:30 PM - 3:45 PM at Washington State Convention Centre Room# 612 where I will be hosting a panel along with the rest of the leadership team that drives the strategy for our application platform.

Thanks,

Raghu Ram

Principal Group Program Manager

SQL Server

Annual SQL Connectivity Customer Survey [Date extended]

2011-09-07T22:48:02Z

Greetings to the Developer community:

Thank you very much for an overwhelming response on our survey. Feedback is continuing to pour. We are extending the deadline to complete the survey to September 15^th, 2011 to allow everyone to provide input. Your feedback helps us on the broad SQL Connectivity components that address major development platforms, including ODBC, ADO.NET, JDBC and PHP.

We view your organization as a key stakeholder in the process that we have to identify areas for future investments. The feedback you provide is valuable and each response will be read and will be treated with utmost confidence. The survey can be found in the link below and will be available until 16^th September, 2011 5:00 PM PST.

http://www.zoomerang.com/Survey/WEB22CS45XT9FE/

Microsoft is Aligning with ODBC for Native Relational Data Access

2011-08-29T22:59:00Z

ODBC is the de-facto industry standard for native relational data access, which is supported on all platforms including SQL Azure. Cloud is universal and in order to support all client applications connecting from any platform to the cloud, Microsoft has been fully aligned with ODBC on SQL Azure, as ODBC is the only set of APIs that are available on all platforms including non-Windows platforms. From our surveys, cross-platform support is one of the main reasons indicated by our partners for aligning their applications with ODBC. The other reason often mentioned in the surveys is the ease of programming with ODBC. The interfaces are simple and straight forward. With this alignment, C/C++ developers can now focus on one set of APIs for all their native client applications. This will also make porting applications to cloud more seamless.

The commercial release of Microsoft SQL Server, codename “Denali,” will be the last release to support OLE DB. Support details for the release version of SQL Server “Denali” will be made available within 90 days of release to manufacturing. For more information on Microsoft Support Lifecycle Policies for Microsoft Business and Developer products, please see Microsoft Support Lifecycle Policy FAQ. This deprecation applies to the Microsoft SQL Server OLE DB provider only. Other OLE DB providers as well as the OLE DB standard will continue to be supported until explicitly announced.

We encourage you to adopt ODBC in the development of your new and future versions of your application. You don’t need to change your existing applications using OLE DB, as they will continue to be supported on Denali throughout its lifecycle. While this gives you a large window of opportunity for changing your applications before the deprecation goes into effect, you may want to consider migrating those applications to ODBC as a part of your future roadmap. Microsoft is fully committed to making this transition as smooth and easy as possible. In order to prepare and help our developer community, we will be providing assistance throughout this process. This will include providing guidance via technical documentation as well as tools to jump start the migration process, and being available right away to answer questions on our forum.

Update (October 19, 2011): In an effort to assist you with your planning, we have developed a tool that can scan your source code and provide a report showing the impacted code. This tool identifies and reports the lines of code that reference OLE DB APIs and provide a summary of the overall code impact. You can obtain this tool by sending an email by clicking 'Email Blog Author' link under the 'Options'. It will be available until April 18, 2012.

Please bookmark and revisit this site to see more tools and documentation updates.

For information on how to migrate your applications, please refer : http://msdn.microsoft.com/en-us/library/hh967418.aspx

To see Frequently Asked Questions (FAQ) or to submit technical questions, please log onto: http://social.technet.microsoft.com/Forums/en/sqldataaccess/threads

For more on SQL Server and Microsoft’s commitment to interoperability, see: http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/29/microsoft-s-commitment-to-interoperability.aspx

Shekhar Joshi,

Sr. Program Manager - SQL Server Data Platform,

Microsoft Corporation

Note: This blog post was updated on September 13^th, 2011 to reflect that the time frame for OLE DB support in SQL Server Code Name “Denali” will be made available within 90 days of release to manufacturing. We are committed to supporting features in SQL Server Code Name “Denali” through the product’s lifecycle as per Microsoft Support Lifecycle Policy.

Annual SQL Connectivity Customer Survey

2011-08-19T20:52:11Z

Your Voice is Important

2011-05-24T21:09:23Z

Greetings to the SQL Server community:

Back around October 2009, we introduced the process of interacting on a regular basis with the developers and users in the form of surveys. Today, we are happy to introduce the ability for you to suggest and vote on feature scenarios through UserVoice. Our UserVoice site http://mssqlconn.uservoice.com enables you to vote for the feature most important to you and if you do not see the feature you need most, you can enter your own feature idea. This provide us with the ability to validate some of the requests we have got from developers, users and partners such as you as well as ideas that we have gathered internally as a part of our development process.

Regards,
Microsoft SQL Server

Troubleshooting SQL Azure applications with SQL Server Native Client

2010-11-30T19:52:00Z

In the Feb. 2010 blog post, “Using SQL Server Client APIs with SQL Azure Version 1.0”, Steve Hale provided details regarding similarities and differences to watch out for when developing an application with SQL Azure. In this post, the focus is on tips of resolving issues once the application encounters an error condition. Please note that the exact error messages may differ slightly, but this should provide a good starting point.

Now, let’s jump right into it. Starting with the initial setup of the database table:

Msg 40054, Level 16, State 1, Server server19, Line 1

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

As the error message stated, you’ll need to create a cluster index on the table. The following sample demonstrates how you can create the cluster index:

Create table employees(EmployeeID int, EmployeeName varchar(30))

Create clustered index empIndex on employees(EmployeeID)

Please note that in order to insert data into a SQL Azure database table, you will need to have a cluster index anyways, so it is definitely in your interest to create the cluster index from the start.

Now that your database table is created, time to connect to the database. During connection, if you encounter:

Msg 40531, Level 11, State 1, Server servername.mscds.com, Line 1

Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.mscds.com). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match.

Verify that the “servername” is specified as part of the user name in the format the error message stated (uid = username@servername).

Another error you may encounter is:

HResult 0x35, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [53].

Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Microsoft SQL Native Client : Login timeout expired.

In this scenario, there can be a couple of different configuration issues. Double check the following:

Verify the local firewall is not blocking outbound network traffic on TCP 1433.
Verify the Forefront Threat Management Gateway (TMG) or Internet Security Acceleration (ISA) Firewall client, or equivalent is installed, if the company firewall blocks non-authenticated traffic.
Verify using the SQL Azure portal that the client IP address is in the set of SQL Azure allowed IP ranges.

Now that you can establish a connection to your SQL Azure database, the following are a few things to watch out for in your application:

Do not execute any “USE <database>” statements. Always connect directly to the database the application requires.
Use at most 2 part names in your query statements. Even though 3 part names are supported, the database portion of the 3 part name must be the name of the current database. Linked servers (4 part names) are not supported in SQL Azure.
Do not call SQLSetConnectAttr() to set SQL_ATTR_CURRENT_CATALOG or SQL_ATTR_ENLIST_IN_DTC
Do not call SQLSetConnectOption() to set SQL_CURRENT_CATALOG

If you do use any of these above syntax in your application, you will most likely encounter one of the following error messages:

SQLState=HY024: Message = "[Microsoft][SQL Server Native Client 10.0]Database is invalid or cannot be accessed“

SQLState=25S12: Message=[Microsoft][SQL Server Native Client 10.0]The partner transaction manager has disabled its support for remote/network transactions.

There are many similarities between programming for SQL Server and for SQL Azure. But, there are also a few things to watch out for. Hopefully these troubleshooting tips help you to recognize the differences quickly and resolve the problems.

Steve Hale and Jimmy Wu
Microsoft SQL Server

SQL Server code-named "Denali" Native Client supporting ODBC 3.8

2010-11-16T23:30:00Z

We’re excited to announce that Denali SNAC CTP1 adds support for the ODBC 3.8 features of the Microsoft ODBC DM (Driver Manager) introduced in Windows 7 and Windows Server 2008 R2. Please refer to http://blogs.msdn.com/b/data/archive/2009/07/06/odbc-dm-3-80-in-windows-7-and-windows-server-2008-r2.aspx for a blog posting detailing the ODBC DM changes.

The ODBC 3.8 features supported in Denali SNAC are:

Streamed Output Parameters
Support retrieving output parameters in parts via SQLGetData when the output parameter was bound using SQLBindParameter. This is extremely valuable when working with large data objects, such as varbinary(max), varchar(max), and nvarchar(max), since allocating an extremely large buffer may not be possible.

ODBC C-Type Extensibility
Leveraging the functionality of C-type extensibility introduced in the ODBC 3.8 driver manager, Denali SNAC introduced SQL_C_SS_TIME2 and SQL_C_SS_TIMESTAMPOFFSET C-type structures. The introduction of these 2 new C-types provides application developers a rich programming experience when using SQL Server Time and DatetimeOffset data types. So, instead of binding these data types as SQL_C_BINARY and having to manually realize the byte sequence can cast to a struct, application developers can now directly bind these data types as the new C-types which automatically maps to the structs.

Asynchronous Connection Operation
Before ODBC 3.80, asynchronous mode was only supported on statement operations, such as SQLExecDirect and SQLGetData. Leveraging the ODBC 3.8 feature, Denali SNAC extends this support to connection operations, such as SQLDriverConnect and SQLEndTran. It also allows applications to cancel connection operations via SQLCancelHandle, just as with SQLCancel on statement operations.

Jimmy Wu
Microsoft SQL Server

SQL Server Customer Survey (Fall 2010) final weekend

2010-10-29T21:16:55Z

Thank-you all for your active participation in our Fall 2010 customer survey. Due to your overwhelming response, we have extended the survey until Nov. 2.

For quick access, the link to the survey is here http://www.zoomerang.com/Survey/WEB22BAW29Z8P2.

Regards,
Jimmy Wu
Microsoft SQL Server

SQL Server Customer Survey (Fall 2010)

2010-10-18T17:07:45Z

Greetings to the SQL Server community:

Last year in October 2009, we introduced the process of interacting on a regular basis with the developers and users in the form of surveys. During the last 12 months, we completed surveys that focused on the broad SQL Connectivity components, including ODBC, ADO.NET, JDBC and PHP. These surveys provide us with an ability to validate some of the requests we have got from developers, users and partners such as you as well as ideas that we have gathered internally as a part of our development process. You have seen our roadmap for SQL Server evolve based on the feedback that we have received.

The survey can be found in the link below and will be available until 25th October, 2010 5:00 PM PST.

http://www.zoomerang.com/Survey/WEB22BAW29Z8P2

Thank you,

Raghu Ram

Principal Group Program Manager

SQL Connectivity

SQL Server RDBMS Group

Microsoft Corporation

SQL Server Connectivity has a new portal page!

2010-05-27T23:21:00Z

The Microsoft SQL Server team is working on a portal page that brings together common resources and community links for all data access providers to Microsoft SQL Server. We’d like your feedback on how useful you will find it and what else you’d like to see included. Although, at this time we’re concentrating on bringing together the most useful links, in the future we might include more content and your feedback will help us to determine which additional content and links are most useful. Our goal is to give you a single page you can bookmark in order to use as the starting point for your data access needs regardless of the technology you’re using, and to make it easy to explore the different options available to access Microsoft SQL Server in the most efficient way.

Using the Site Feedback link in Additional Resources on the right hand side of the page, please send your feedback, before June 30th please, on the general usefulness of this page, what additional content you’d like to see included in the future and what additional links you’d find useful.

The new portal page can be found at http://msdn.microsoft.com/sqlserver/connectivity.

Thanks.

Jimmy Wu

SNAC PM

SQL Server 2008 R2 Feature Pack is now available

2010-05-12T17:17:00Z

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52

This includes SQL Server Native Client 2008 R2.

Microsoft SQL Server JDBC Survey

2010-03-23T19:19:00Z

Dear SQL Server developers and users

The Microsoft SQL Server JDBC team is beginning to think of future JDBC driver releases. As we review the priorities and goals for the long term, we view you and your organization as key stakeholders. Your input on our survey will help us shape our future product plans. The survey should take no more than 10 minutes and will remain open until April 5^th, 2010.

The Microsoft JDBC team greatly appreciates your time to take part in this survey.

Thanks

Amina Saify|Program Manager| SQL Connectivity - JDBC

Non-Windows ODBC support survey

2010-02-24T00:52:00Z

Dear Colleague,

The Microsoft SQL Server Connectivity team has received feedback indicating interest in ODBC support for non-Windows platforms and is now conducting a more detailed survey of this area. The survey takes less than 10 minutes to complete and you can take part by clicking on the following link https://www.surveymonkey.com/s/SQL_Server_multi-platform_ODBC_survey. The survey is open until March 10^th. Your input will help shape our future product plans and is greatly appreciated. Please feel free to forward this invitation to others you know to be interested in this topic.

Thank you for taking the time to take part in this survey,

Chris Lee
Senior Program Manager
SQL Connectivity
Database Systems Group
Microsoft Corp.
Redmond, WA

Chris.T.Lee@microsoft.com

Using SQL Server Client APIs with SQL Azure Version 1.0

2010-02-12T23:28:00Z

This post describes some of the issues that the application developer should be aware of when writing ODBC, ADO.NET SQLClient, BCP, and PHP code that connects to SQL Azure. There is no intention here to replace the SQL Azure documentation available on MSDN (http://msdn.microsoft.com/en-us/library/ee336279.aspx). Rather, the intent here is to provide a quick discussion of the primary issues impacting applications ported to or written for SQL Azure vs. SQL Server. Developers should reference the MSDN documentation for more detailed description of SQL Azure functionality.

Using supported SQL Server client APIs (ODBC, BCP, ADO.NET SQLClient, PHP) with SQL Azure is relatively easy, however, SQL Azure, while very similar, is not SQL Server. Because of this there are some pitfalls that need to be avoided to have the best experience when writing an application that connects to SQL Azure.

Supported components

The first thing to ensure is that you are using a supported component to connect to SQL Azure.

· Microsoft SQL Server Native Client 10.0 ODBC Driver or newer. The SQL Server 2008 R2 version of the SQL Server Native Client ODBC Driver is preferred due to some minor updates to work better with SQL Azure. This is a free download.

· SQL Server 2008 BCP.EXE or newer. The caution here is that the queryout option is not fully supported for SQL Server 2008 RTM. Queryout is fully supported in the SQL Server 2008 R2 release via the addition of the "-d" database parameter.

· ADO.NET 3.5 SqlClient or newer.

Any other components are not currently supported for connecting to SQL Azure. In particular it should be noted that OLEDB connections are not currently supported from any OLEDB provider, even SQL Server 2008 Native Client OLEDB provider. ODBC connections using the on-the-box ODBC driver “SQL Server” are not currently supported.

Also, using the OLEDB Provider for ODBC Drivers (MSDASQL) cannot be supported with SQL Azure because the only ODBC driver supported for SQL Azure is the SQL Server 2008 Native Client ODBC driver or newer, and SQL Server Native Client is not supported with MSDASQL (http://msdn.microsoft.com/en-us/library/ms131035.aspx).

Connecting

Before attempting a connection, make sure you have a valid server name, user ID, and password for SQL Azure. On the SQL Azure portal, set the firewall rules to allow your client IP address to connect to SQL Azure. Additionally, in some corporate environments, you might need to install Microsoft Firewall Client for ISA Server or Forefront TMG client on the client machines or connections will not succeed. And in those environments, using IIS might require the default IIS settings for Application Pool Identity to be changed. Here is how that setting is changed:

Open IIS Manager in Admin Tools, select Application Pools, Pick the pool, Advanced Setting and under Process Model change Identity from BuiltInAccount\ApplicationPoolIdentity to Custom account domain account. Restart IIS.

SQL Azure supports only TCP/IP connections using port 1433, which is the default for most SQL Server APIs and so this generally does not need any special handling by the application developer. But it is good to be aware of this in case you are expecting other protocol choices to work, such as named pipes or using other ports. Additionally, most client APIs do not send the server name when connecting, thus the server name should be included as part of the user ID. Currently the only exception to this is .NET 4.0 which does send the server name. ODBC, BCP, PHP, and .NET 3.5 all require the server name to be sent as part of the user ID. This may change in a future release. Connection strings are all similar for each API stack and require the server name, user ID, password, and optionally the database. Trusted connections (also known as Integrated Security or Windows Authentication) are not supported.

SQLCmd example:

sqlcmd -S o8uyxb64fe.ctp.database.windows.net -U <MyUserID>@o8uyxb64fe -P <MyPassword>

Note the required server name (in yellow) included as part of the user ID.

Creating and Connecting to a Database

In the above example the database was not specified. In that case the default database “master” is used. You must connect to the master database to create databases or drop databases.

Since the “USE database” command is not supported, you must disconnect after creating a database and reconnect directly to the user database. In this example we connect directly to the Northwind database.

sqlcmd -S o8uyxb64fe.ctp.database.windows.net -U <MyUserID>@o8uyxb64fe -P <MyPassword> -d Northwind

It should be noted that the set of databases viewable by the user is different in a user database versus master database. In master, you can see all of your databases for your server, but in a user database only the current user database and master.

Creating tables

Table creation is restricted to user databases; you cannot create tables in the master database. Table creation is substantially the same as on SQL Server, with the exception that certain data types are not supported, for example CLR User Defined Types, Filestream, and row guid types.

Also, it should be mentioned that SQL Azure requires a clustered index or primary key (which uses a clustered index by default) before data can be inserted into a table. For this reason, a clustered index or primary key must always be created for any table intended to contain data.

Example:

Create table employees(EmployeeID int primary key, EmployeeName varchar(30))

Create table employees(EmployeeID int, EmployeeName varchar(30))

Create clustered index empIndex on employees(EmployeeID)

Unsupported features

Unsupported features are features that SQL Server supports and are not deprecated but SQL Azure does not support. Applications that require this functionality cannot work against SQL Azure unless they can be rewritten in such a manner that the unsupported features are not required. The main unsupported features that impact applications are:

· “USE database” is not supported

· 3-part names are not supported unless the database being referenced is the current database.

· Creating or dropping a database when not in the master database

· Creating or dropping a user when not in the master database.

· Table creation in the master database.

· Insertion of data into a table without a clustered index.

· Trusted connections

· CLR UDTs

· XML schema collections

· Distributed transactions (local transactions are supported)

· AttachDB

· Certain stored procedures, for example sp_who and undocumented stored procedures used to support OLEDB schema rowsets.

Deprecated functionality

Some features are deprecated in SQL Server and should not be used in new application development. In most cases SQL Azure does not support deprecated SQL Server features. Some examples of these are:

· System tables such as syslanguages. Use the new system views instead. See http://msdn.microsoft.com/en-us/library/ms187997.aspx for a mapping to the new system views.

· Numbered stored procedures

· “select into” syntax

· “COMPUTE BY” clause

· Two-part drop index statements

See http://msdn.microsoft.com/en-us/library/ee336281.aspx for more detailed Transact-SQL support.

Connection behavior

SQL Azure connections will behave somewhat differently than SQL Server connections. They can be dropped for a number of reasons:

· Excessive resource usage

· Long-running queries

· Long-running single transactions

· Idle connections

· Failover due to server failures

For this reason it’s best to avoid caching connections for later use. A better strategy is to open the connection when needed, use it, and then close it. Additionally, it is recommended that applications provide retry/reconnect logic to recover from dropped connections.

Latency

Latency may be a concern when using SQL Azure unless the SQL Azure server is located near the application (“code near” scenario), such as with Windows Azure and a co-located SQL Azure. Making many round trips to the server can introduce noticeable delay in an application that previously performed adequately when running against a SQL Server on the intranet. Ensure the application connects to the nearest SQL Azure server and that round trips are minimized.

SUMMARY

ü Use only supported client components to connect

ü Connect using TCP/IP and port 1433

ü Use SQL Authentication only, not Trusted Connections/Integrated Authentication/Windows Authentication.

ü Specify the server name in the user ID.

ü Ensure the firewall rules in the SQL Azure portal permit your IP address.

ü Install Microsoft Firewall Client for ISA Server if needed.

ü Change the IIS Application Pool Identity if needed.

ü Connect to the master database to create or drop databases

ü Create a user database for your application since tables cannot be created in master

ü Disconnect and reconnect to change databases

ü Create a clustered index or primary key on a table before inserting data.

ü Remove any unsupported or deprecated features from the application.

ü Avoid caching connections. Open the connection, use it, and then close it.

ü Avoid making numerous round trips to the server.

ü Implement retry logic to recover from dropped connections.

Steve Hale (Microsoft) authored this article.

SQL Server Native Client wants to hear your opinion

2010-01-16T18:35:00Z

Dear SQL Server developers and users:

It is this time of the year when we in the SQL Server Native Client 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. 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 SQL Server database application developer community.

This survey will be open for your submissions until January 31, 2010 and can be found here.

SQL Server Native Client Team

Download locations for SQL Server Native Client

2009-11-03T22:21:00Z

You can download SQL Server 2008 Native Client here: http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

You can download SQL Server 2005 Native Client from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en

SQL Server Connection Director will not be in the RTM version of the release

2009-10-19T20:36:00Z

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 Visual Studio 2010 and SQL Server 2008 R2. Please do not use this feature in Visual Studio 2010 beta 2 or SQL Server 2008 R2 CTP.

Microsoft wants to hear your opinion

2009-10-02T22:57:00Z

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.

ODBC 3.8 Enhancements in Windows 7 and Windows Server 2008 R2

2009-07-09T01:34:00Z

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

SQL Server Native Client: Connection strings and OLE DB

2009-05-07T02:25:00Z

(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

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

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.

SQL Server Native Client, Windows (nee Microsoft) Data Access Components, Documentation, and You

2009-02-24T21:23:00Z

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...

2009-01-17T04:18:00Z

ODBC 3.8 is in beta 1 of the Windows 7 SDK...

And beta 1 of the Windows 7 SDK is now available on http://www.microsoft.com/downloads/details.aspx?FamilyID=a91dc12a-fc94-4027-b67e-46bab7c5226c&DisplayLang=en

ODBC 3.8 includes new features such as

· Executing connection operations asynchronously.

· Streamed output parameters.

· ODBC C data type extensibility.

Help wanted: Program manager for Microsoft SQL Server Native Client team

2008-11-15T20:13:00Z

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).