Linked Server to Windows Azure SQL Database Inconsistent Metadata

Question

Some tables return data OK. Some not.

SELECT * FROM OPENQUERY([GAMMA_AZURE], 'SELECT * FROM Sales.SalesOrderHeader');
-- (31466 row(s) affected)
SELECT * FROM [GAMMA_AZURE].AdventureWorks2012.Sales.SalesOrderHeader;
/*
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "GAMMA_AZURE" supplied inconsistent metadata 
for a column. The column "SubTotal" (compile-time ordinal 20) of object 
""AdventureWorks2012"."Sales"."SalesOrderHeader"" was reported 
to have a "DBTYPE" of 131 at compile time and 6 at run time.
*/
SELECT * FROM OPENQUERY([GAMMA_AZURE], 'SELECT * FROM HumanResources.Shift');
/*
Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' for linked server 'GAMMA_AZURE' returned data that does not match expected 
data length for column '[MSDASQL].StartTime'. The (maximum) expected data length 
is 16, while the returned data length is 12.
*/

SELECT * FROM [GAMMA_AZURE].AdventureWorks2012.HumanResources.Shift;
/*
Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' for linked server 'GAMMA_AZURE' returned data that does not match 
expected data length for column '[GAMMA_AZURE].[AdventureWorks2012].[HumanResources].[Shift].StartTime'. 
The (maximum) expected data length is 16, while the returned data length is 12.
*/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 1

Could be a defect in the metadata discovery.

Specifically in one or more of these:

sys.sp_describe_first_result_set
sys.dm_exec_describe_first_result_set
sys.dm_exec_describe_first_result_set_for_object
sys.sp_describe_undeclared_parameters

What is you poll each using

EXECUTE sp_describe_first_result_set
@sql = N'SELECT * FROM [GAMMA_AZURE].AdventureWorks2012.Sales.SalesOrderHeader'

Etc?

---

Arthur My Blog

Answer 2

The problem with Shift is that time is binary(16).  Nothing works.

SalesOrderHeader/SalesOrderDetail: 4-part naming SELECT works(erratic), OPENQUERY works (erratic).

-- SELECT works
EXECUTE sp_describe_first_result_set
@sql = N'SELECT * FROM [GAMMA_AZURE].AdventureWorks2012.Sales.SalesOrderHeader'
/*
SalesOrderID	0	56	int
RevisionNumber	0	48	tinyint
OrderDate	0	42	datetime2(7)
DueDate	0	42	datetime2(7)
ShipDate	1	42	datetime2(7)
Status	0	48	tinyint
OnlineOrderFlag	0	104	bit
SalesOrderNumber	0	231	nvarchar(25)
PurchaseOrderNumber	1	231	nvarchar(25)
AccountNumber	1	231	nvarchar(15)
CustomerID	0	56	int
SalesPersonID	1	56	int
TerritoryID	1	56	int
BillToAddressID	0	56	int
ShipToAddressID	0	56	int
ShipMethodID	0	56	int
CreditCardID	1	56	int
CreditCardApprovalCode	1	167	varchar(15)
CurrencyRateID	1	56	int
SubTotal	0	108	numeric(19,4)
TaxAmt	0	108	numeric(19,4)
Freight	0	108	numeric(19,4)
TotalDue	0	108	numeric(19,4)
Comment	1	231	nvarchar(128)
rowguid	0	36	uniqueidentifier
ModifiedDate	0	42	datetime2(7)
*/

-- SELECT fails
EXECUTE sp_describe_first_result_set
@sql = N'SELECT * FROM [GAMMA_AZURE].AdventureWorks2012.HumanResources.Shift'
/*
ShiftID	0	48	tinyint
Name	0	231	nvarchar(50)
StartTime	0	173	binary(16)
EndTime	0	173	binary(16)
ModifiedDate	0	42	datetime2(7)
*/

-- SELECT works
EXECUTE sp_describe_first_result_set
@sql = N'SELECT * FROM [GAMMA_AZURE].AdventureWorks2012.Sales.SalesOrderDetail'
/*
SalesOrderID	0	56	int
SalesOrderDetailID	0	56	int
CarrierTrackingNumber	1	231	nvarchar(25)
OrderQty	0	52	smallint
ProductID	0	56	int
SpecialOfferID	0	56	int
UnitPrice	0	108	numeric(19,4)
UnitPriceDiscount	0	108	numeric(19,4)
LineTotal	0	108	numeric(38,6)
rowguid	0	36	uniqueidentifier
ModifiedDate	0	42	datetime2(7)
*/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

Still thinking of a bug. It somehow finds about the length being 12. What is actually in the table definition?

---

Arthur My Blog

Answer 4

Here it is, time(7):

CREATE TABLE [HumanResources].[Shift](
	[ShiftID] [tinyint] IDENTITY(1,1) NOT NULL,
	[Name] [dbo].[Name] NOT NULL,
	[StartTime] [time](7) NOT NULL,
	[EndTime] [time](7) NOT NULL,
	[ModifiedDate] [datetime] NOT NULL)

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 5

This must be the bug, right there in the sys.sp_describe_first_result_set;

It mistakenly taken time(7) as binary(16).

And the figure 12 is coming from the value in the table field like

12:34:56.123

I guess.

---

Arthur My Blog

Answer 6

Thanks Arthur.

Connect bug report:

https://connect.microsoft.com/SQLServer/feedback/details/780161/azure-sql-time-data-type-throws-error-on-linked-server-query

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 7

Thank you Kalman,

I scouted for a possible fix in the SP 1 for SQL Server 2012 and its CU1, but did not find this stored procedure mentioned as fixed.

I have also added a comment to the Connect item mentioning this thread.

---

Arthur My Blog

Answer 8

Hi Arthur,

I tried a different connection recommended by MS, the time data type issue went away, however, other issues popped up. Thread:

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/52f3d1b9-579c-4594-b39d-f5931b2d0e9a

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012