SQL with Manoj

This was 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