none
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

    Wednesday, February 27, 2013 6:37 PM

Answers

  • 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

    • Marked as answer by Kalman Toth Wednesday, February 27, 2013 10:38 PM
    Wednesday, February 27, 2013 9:16 PM
    Moderator

All replies

  • 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

    Wednesday, February 27, 2013 7:34 PM
    Moderator
  • 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


    • Edited by Kalman Toth Wednesday, February 27, 2013 8:17 PM
    Wednesday, February 27, 2013 8:16 PM
  • Still thinking of a bug. It somehow finds about the length being 12. What is actually in the table definition?

    Arthur My Blog

    Wednesday, February 27, 2013 8:43 PM
    Moderator
  • 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

    Wednesday, February 27, 2013 9:02 PM
  • 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

    • Marked as answer by Kalman Toth Wednesday, February 27, 2013 10:38 PM
    Wednesday, February 27, 2013 9:16 PM
    Moderator
  • 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

    Thursday, February 28, 2013 3:47 PM
    Moderator
  • 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


    • Edited by Kalman Toth Thursday, February 28, 2013 4:01 PM
    Thursday, February 28, 2013 3:59 PM