SQLNCLI11 for linked server “XYZ” returned message “Requested conversion is not supported” | SQL Server 2012 upgrade behavior changes

March 27, 2015 Leave a comment

This is a year long pending post that got lost in my blog posts archives. Today while filtering the Posts on my Dashboard I saw this in Edit mode. So I thought to make it live after doing some edits. So, here it goes :)

While upgrading to SQL Server 2012 from SQL Server 2008 R2 on my DEV box, I observed some behavioral changes with SQL 2012, one ETL job that was working fine on SQL 2008 R2 was not running and throwing following error in SQL 2012:

OLE DB provider “SQLNCLI11″ for linked server “MyLocal” returned message “Requested conversion is not supported.”.
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column “(user generated expression).Expr1003″ from OLE DB provider “SQLNCLI11″ for linked server “MyLocal”.

The above error depicts that there is something wrong with the Linked Server. But the same Linked Server was working for other tables, and it was failing for a specific table only. I checked the table and the ETL script where the Linked Server was being used. That script had a SELECT list fetching records from source table via Linked Server. The column list was having a computed column in the end like: CAST(NULL as UNIQUEIDENTIFIER) AS U_ID. I was not sure why anybody would write that code and what was the need to add this computed column. I removed this column and the error was gone. So it was clear that the CASTing of NULL to UNIQUEIDENTIFIER datatype was throwing this error, but the same code was working fine in SQL Server 2008 R2.

(Please check at the bottom of the post for more such behavioral changes)

–> Here is the issue that I’ve reproduced:

This will run fine in previous versions of SQL Server, but will throw error in 2012:

–> CREATE Linked Server

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver 
	@server = N'MyLocal', 
	@srvproduct=N'MSSQL', 
	@provider=N'SQLNCLI', 
	@datasrc=N'MANOJPANDEY-PC', -- plz change the server name here.
	@provstr=N'PROVIDER=SQLOLEDB;SERVER=MY-PC' -- plz change the server name here.
 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLocal',@useself=N'True',
	@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

–> Create a new table:

USE [AdventureWorks2012]
GO

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
INTO dbo.Person
FROM [AdventureWorks2012].[Person].[Person]

–> Query table via Linked Server:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
FROM [MyLocal].[AdventureWorks2012].dbo.[Person]

–> Query it after adding a column via Linked Server:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(NULL as UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

OLE DB provider "SQLNCLI11" for linked server "MyLocal" returned message "Requested 
conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "(user generated expression).Expr1003" 
from OLE DB provider "SQLNCLI11" for linked server "MyLocal".

–> Creating a View in Remote Server:

CREATE VIEW [dbo].[vwPerson]
AS
SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(NULL as UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

–> View also fails, as it is also expanded (same error as above):

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate, U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[vwPerson]

–> Workaround 1:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

–> Workaround 2:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(0x AS UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

–> Final Cleanup:

DROP TABLE dbo.Person
DROP VIEW dbo.vwPerson
GO

USE [master]
GO
EXEC master.dbo.sp_dropserver @server=N'MyLocal', @droplogins='droplogins'
GO

–> I’ve documented more behavioral changes after SQL Server 2012 upgrade, and here is the list:
1. IDENTITY column value hop
2. Temp #Tables created with negative IDs
3. No native Linked Server support to SQL Server 2000

GROUPING SETS equivalent | for SQL Server 2005 and below

March 23, 2015 2 comments

Sometime back I discussed about [GROUPING SETS] in my previous post and today one reader of this blog asked me how we can create the same result-set without using GROUPING SETS if we are on previous versions like SQL Server 2005 or 2000.

–> Let’s take the following SQL Query that uses GROUPING SETS operator and let’s see what Output we get:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)
Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

You can refer to the DDL and population of this #tempTable in my previous post, [link].

–> And now let’s see how can we recreate the same result set without using GROUPING SETS if we are on lower versions of SQL Server (<= 2005):

-- ROLLUP and Grouping Sets Equivalent (pre SQL 2008)
SELECT class, section, rollno, marks as [sum]
FROM #tempTable

UNION ALL

SELECT class, section, null as rollnu, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section

UNION ALL

SELECT class, null, null, sum(marks) [sum]
FROM #tempTable
GROUP BY class

UNION ALL

SELECT null, null, null, sum(marks) [sum]
FROM #tempTable

ORDER BY class DESC, section DESC, rollno DESC

Thanks, please let me know your thoughts and comments!!!

Capture multiple errors in TRY CATCH by using THROW statement

February 4, 2015 2 comments

This post relates to my earlier post [link] where I mentioned on benefit of using THROW clause with same SQL examples.

The THROW clause was introduced in SQL Server 2012 and may be replacing the RAISERROR function in near future.

Normally the SQL statements returns single error, but some SQL statements returns more than one error message when they go wrong due to some reason or exception.

–> On executing the below BACKUP statement in SSMS we can see we get two errors:

BACKUP DATABASE [AdventureWorks2012] 
TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'

The above code throws 2 errors with Error-Message IDs 3201 & 3013, as shown below:

error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


-> But when we want to track these errors by using RAISERROR function it just returns the last (single) error message and its details, and the previous error message details are not returned by this function.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000) = ERROR_MESSAGE()
	RAISERROR(@msg,16,0)
END CATCH

Here, only 1 error message will be returned:

error messages:
Msg 50000, Level 16, State 0, Line 7
BACKUP DATABASE is terminating abnormally.


–> With the new THROW clause you won’t see any issue of omitting the previous errors, as it returns all error details thrown by the SQL Statement itself.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	THROW;
END CATCH

The above statement throws both the error details as we saw in the first example:
error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


Thus, if you are on SQL Server 2012 and above you must consider using THROW clause instead of the RAISERROR function.

Microsoft’s upcoming USCMO Webcasts | Digital Events

January 16, 2015 Leave a comment

Microsoft announced their new and improved USCMO webcast programs!

The USCMO (U.S. Central Marketing Organization) team manages and optimizes programs through the customer lifecycle in order to drive business results of conversion, pipeline, and managed costs.

Each webcast will stream live with interactive Q&A and will be made available on demand.

–> Please check the webcasts below you might be interested to register:

Webcast Title Webcast Date Registration URL
Protect Your Business Against Online Fraud 1/20/2015 http://aka.ms/protectblog
Social in the Enterprise 1/21/2015 http://aka.ms/enterpriseblog
Windows Server 2003 Migration: Hardware Modernization 1/22/2015 http://aka.ms/WS03blog
It’s a New Year, Be Ready to Adapt 1/22/2015 http://aka.ms/adaptblog
HIPAA Compliant Cloud Solutions with Microsoft BAA 1/23/2015 http://aka.ms/BAAblog
Announcing the Enterprise Cloud Suite 1/26/2015 http://aka.ms/suiteblog
Get a fresh start in 2015 with new Windows devices 1/28/2015 http://aka.ms/windeviceblog
Need fast AND affordable? Why not try SQL Server? 1/29/2015 http://aka.ms/SQLserverblog
Mobile Productivity in the Modern Workplace 2/4/2015 http://aka.ms/mobileblog
Windows Server 2003: Most Common Application Migration Concerns 2/5/2015 http://aka.ms/commonblog
Enabling Customer Insights Using Business Analytics 2/12/2015 http://aka.ms/customerblog
Windows Server 2003: Security Risk and Remediation 2/18/2015 http://aka.ms/remeblog
The Connected Workforce 2/18/2015 http://aka.ms/connectedblog
Fine Tune Your Supply Chain with Better Insight 2/19/2015 http://aka.ms/fineblog

Thanks !!!

2014 in review

December 30, 2014 Leave a comment

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here's an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 330,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 14 days for that many people to see it.

Click here to see the complete report.

Follow

Get every new post delivered to your Inbox.

Join 429 other followers