SQL Native Client and ODBC

We still use OLE DB throughout our Microsoft products – Data connections from website applications, SQL SSIS etc. And it seems Microsoft still encourages its use – default choices in many cases. There are other data connection choices – ODBC, ADO.NET. But we have always found OLE DB to be the recommended technology by developers for compatibility with all functionality, speed and ease of use.

The OLE DB drivers were always included in the SQL Native Client package which also contained ODBC drivers and you needed to install it on the SQL server and match up your connection strings with the correct version you were using.

I was starting to evaluate SQL 2016 and was looking for the latest SQL Native client update for that version. It became a big surprise when I learnt that the native client driver has not been updated since the release of SQL 2012 and that Microsoft announced in 2011 (just before the release of SQL 2012) that they were deprecating OLE DB in 2018, that no new versions would be released and were recommending the use of ODBC instead:

https://blogs.msdn.microsoft.com/sqlnativeclient/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e696d0ac-f8e2-4b19-8a08-7a357d3d780f/microsoft-is-aligning-with-odbc-for-native-relational-data-access-faq?forum=sqldataaccess

So, you may find that in future OS and IIS versions the OLE DB connection is broken. I decided a comprehensive review of all the drivers available needed to be performed, the results of which you will find listed below. You will notice that there has been 3 new releases of the ODBC drivers now named ‘Microsoft ODBC Driver for SQL Server’.

The main problem that I have found is with nText and Nvarchar(max) fields – you should be using Nvarchar(max) as Ntext was deprecated in SQL 2005. But even then none of the ODBC drivers (except the vintage SQL 2000 driver using Driver={SQL Server}) will read large blob type data in nText/Nvarchar(max) type fields – you just get blank. So the only way around this is to use OLEDB driver (SQLNCLI) or limit the field to nvarchar(4000) which is the max length you can set (nvarchar(max) is blob type storage which can store up to 2GB). This was one incompatibility that we found – there may well be more!

So, let me re-iterate, Microsoft have dropped OLE DB in their latest driver releases, they recommend you use ODBC instead but ODBC does not work with all SQL functionality! I did pose a stack overflow question which didn’t come up with any viable solutions:

http://stackoverflow.com/questions/38608879/oledb-odbc-ntext-and-nvarcharmax

But we have also noticed that the default in SQL 2016 SSIS is to use OLE DB! In fact it took us a while to work out that ODBC has been included – you have to choose other options. Also, if you go to any conference where SSIS is being discussed they will all be using OLE DB, or ADO.NET when using multiple parameters as it is more manageable, certainly not
ODBC.

We also asked Microsoft execs about the situation at the same conferences and their typical response was, and i’m paraphrasing, ‘well we announced it is deprecated but there is such a huge base of customers using it that we don’t see it dissapearing anytime soon’. Indeed the main thrust of their deprecation announcement seems to be to do with SQL Azure – I have no experience with Azure but it sounds like you can only use ODBC with Azure and therefore, they are getting on premises people to change over to ODBC because that will make migrating to Azure much easier – of course in Microsoft’s eyes everyone will migrate to Azure so the issue over OLE DB becomes academic to them.

Anyway, we decided to stick with OLE DB in Native client rather than having to test everything with the latest ODBC driver as it looks like there will be no problem when we upgrade to SQL 2016 with continuing to use OLE DB.

SQL Native Client:

The SQL Native Client installs both 32bit and 64bit OLE DB and ODBC drivers.

The versions that are currently available are:

SQL Native Client V9 (SQL 2005):

OLEDB Provider code: SQLNCLI
OLEDB User/Password connection string:

objname.Open "Provider=SQLNCLI;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;User ID=Username;Password=Password;"

OLEDB Trusted Connection connection string:

objname.Open "Provider=SQLNCLI;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;Trusted_Connection=yes;"

ODBC Driver code: {SQL Native Client}

ODBC User/Password connection string:

objname.Open "Driver={SQL Native Client};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Passwor"

ODBC Trusted Connection connection string:

objname.Open "Driver={SQL Native Client};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

SQL Native Client V10 (SQL 2008):

OLEDB Provider code: SQLNCLI10
OLEDB User/Password connection string:

objname.Open "Provider=SQLNCLI10;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;User ID=Username;Password=Password;"

OLEDB Trusted Connection connection string:

objname.Open "Provider=SQLNCLI10;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;Trusted_Connection=yes;"

ODBC Driver code: {SQL Server Native Client 10.0}

ODBC User/Password connection string:

objname.Open "{SQL Server Native Client 10.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "{SQL Server Native Client 10.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

SQL Native Client V11 (SQL 2012):

Download Location: https://support.microsoft.com/en-us/kb/2726013
OLEDB Provider code: SQLNCLI11
OLEDB User/Password connection string:

objname.Open "Provider=SQLNCLI11;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;User ID=Username;Password=Password;"

OLEDB Trusted Connection connection string:

objname.Open "Provider=SQLNCLI11;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;Trusted_Connection=yes;"

ODBC Driver code: {SQL Server Native Client 11.0}

ODBC User/Password connection string:

objname.Open "{SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "{SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

Microsoft ODBC Drivers:

Microsoft ODBC Driver 11 for SQL Server:

Download path: https://www.microsoft.com/en-gb/download/details.aspx?id=36434
OLEDB Provider code: n/a
With this version Microsoft dropped OLEDB support
This version appears to be a driver wrapper to enable new functionality (like connecting to Azure DBs) you can then supply the underlying driver or use the ODBC driver that comes with it (Unfortunately you can’t use the old OLE DB driver).
So, in order to use Microsoft ODBC Driver 11 with SQL Native Client V11 you would need to install both Microsoft ODBC Driver 11 for SQL Server AND SQL Native Client V11 and use the following connection strings:

Provider code: MSDASQL
ODBC Driver code: {SQL Server Native Client 11.0}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

But you would be better off using the driver that comes with it (I hesitate to use the word native driver – that would confuse things even further!). These are the connection strings to use if you just want to use Microsoft ODBC Driver 11 for SQL Server:

Provider code: MSDASQL
ODBC Driver code: {ODBC Driver 11 for SQL Server}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 11 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 11 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

Microsoft ODBC Driver 13 for SQL Server:

Released 25th July 2016

Download path: https://www.microsoft.com/en-us/download/details.aspx?id=50420

This added support for SQL 2016.

Provider code: MSDASQL
ODBC Driver code: {ODBC Driver 13 for SQL Server}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

Microsoft ODBC Driver 13.1 for SQL Server:

Released 1st August 2016

Download path: https://www.microsoft.com/en-us/download/details.aspx?id=50420

This added functionality for always encrypted connections.
See: https://msdn.microsoft.com/en-us/library/jj730314(v=sql.1).aspx

Provider code: MSDASQL
ODBC Driver code: {ODBC Driver 13 for SQL Server}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

In all cases we are connecting to an SQL 2012 server with database mirroring. Strings may be different for other scenarios. A good website for connection strings is:
https://www.connectionstrings.com/sql-server/
Although they are not up to date with MSDASQL connections at time of writing.

Note: Certain keywords seem interchangeable between drivers e.g. ‘Failover Partner’ and ‘Failover_Partner’; ‘Trusted_Connection’ and ‘Integrated_Security’

Advertisements

Excel and SSIS – the problems and solutions

There are various problems with importing data from Excel files.

Today I came across an article from 2012 by Koen VerBeeck which has been re-blogged on the SQLServerCentral.com website which very succinctly summarises the problems and solutions:

http://www.sqlservercentral.com/blogs/koen-verbeeck/2015/07/10/reblog-whats-the-deal-with-excel-and-ssis/

Please note my comment at the bottom which I will repeat here:

One thing I would add is that if you are in a situation where changing registry settings is difficult or you don’t want to have to bother with changing settings every time you move machines/re-image machines i.e. you have to stick to the default of 8 rows being sampled. Then just add 8 dummy rows to the beginning of your Excel tables with the relevant type of data inserted – bunch of ‘A’s for strings, ‘9’s for numeric etc. – and then delete all the dummy records out with conditional split as mentioned. With this setup you can be certain the SSIS routine will work on any machine with default JET settings.

SQLServerUpdates.com

Brent Ozar has put together a new website that provides a simple run down of the latest service packs and cumulative updates available for each version of SQL server.

There are also links to the Microsoft download pages for the updates.

I used to check for updates using various web sources this brings it altogther in a simple to use table.

Brent is an SQL guru extraordinaire – I caught his talk on wait statistics at the SQL Bits conference in London back in March 2015 and was very impressed by his knowledge and presentation style (hands everywhere!).

http://www.sqlserverupdates.com

http://www.brentozar.com/

http://www.sqlbits.com/Sessions/Event14/Easy_Performance_Tuning_an_Intro_to_Wait_Statistics

 

Idera SQL Diagnostics Manager Mobile Manager

Idera finally came out with a Mobile Manager that works on mobile devices other than a BlackBerry – because they are an American company they concentrated on BlackBerry functionality just when the rest of the world was changing or starting to use iPhones.

I have not used it much yet so cannot give any pros and cons but basically it is a web based tool running as an app on MS IIS which produces web pages enhanced for your particular mobile platform – you would still want to use VPN to access your network first.

Had some problems setting it up though – we were getting an error when trying to load the front screen of the website either via mobile or IE: get a standard HTTP 500 server error.

Turns out we had to tweak the IIS settings slightly. Recommendation from Idera was the following:

Open IIS
Click on server – top level and choose “Modules”
Right click on DynamicCompressionModule and choose “Unlock”
Right click on StaticCompressionModule and choose “Unlock”.
Select the SQLDm website under Sites, Default Website and choose Modules
Right click on DynamicCompressionModules and choose “Remove”
Right click on StaticCompressionModule and choose “Remove”
Restart IIS

We actually changed the default website modules which filtered down automatically to the SQLDm modules – should work for just SQLDm though.

I believe the problem lay with the fact that we also had WSUS installed on the same server and although WSUS is installed as a separate application it makes changes to the IIS global configuration i.e. Dynamic Compression is turned off by default when IIS is installed but WSUS turns it on. This then conflicted with the SQLDm application.

 

Windows Server Strict Naming not allowing server alias names (CNAME DNS records)

By default Microsoft Windows Server only allows clients to access it via file manager (SMB access) using the name of the server. It will not allow you to use an alias e.g. a CNAME alias setup in the DNS server records.

To rectify this follow instructions in this Microsoft article:

http://support.microsoft.com/kb/281308/en-us

This opens up all sorts of possibilities e.g. for SQL mirroring we can give servers names like SQL_Principal, SQL_Mirror, SQL_Witness rather than their actual names which means if we bring a new server online we can simply change the DNS record to the new IP of the server. Or our Exchange server has an alias of Exchange and all our Outlook clients point to Exchange on our domain rather than the actual server name – again a simple DNS change is all that is required to swap servers.

Renaming MS SQL 2005 database filenames

I need to rename the filenames of a database because it did not fit in with our naming policy.

You have to detach and attach the database to rename the files – because they are open files locked by SQL server – this means the database has to be taken down for a short period.

Use the following SQL commands to achieve the rename:

Database: Testdatabase
DB Location: d:mssqldata
DB log location: e:mssqllogs
DB file name: Testdatabase.mdf
DB log file name: Testdatabase_log.ldf
New DB file name: renamedTestdatabase.mdf
New DB log file name: renamedTestdatabase_log.ldf

1. First detach the database:
EXEC sp_detach_db ‘Testdatabase’, ‘true’

2. Then rename the files – using command prompt/file explorer.

3. Lastly reattach the database:
EXEC sp_attach_db @dbname = N’Testdatabase’, @filename1 = N’d:mssqldatarenamedTestdatabase.mdf’, @filename2 = N’e:mssqllogsrenamedTestdatabase_log.ldf’

SQL 2008 Management Studio – Saving changes is not permitted

Upgraded my management desktop recently and made sure I had the latest SQL 2008 Management studio, even though i’m still managing only SQL 2005 databases – works perfectly with older versions.

I edited a table and inserted some columns on trying to save the table I got the following message:

“Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

After much searching I came across the following Microsoft article:

http://support.microsoft.com/kb/956176/en-gb

Problem solved. The old SQL 2005 management studio did not have this function, it was only added in 2008 and the default was set to on – to accomodate change tracking it seems! Another example of Microsoft catering to enterprise customers and not their core small business market.