3

I am working on moving stored procedures from an on-prem SQL Server database to an Azure SQL Data Warehouse (ASDW). Throughout the process I have had to work around a few missing features - time consuming but not impossible. One thing I have had to do is replace CTE's followed by MERGE statements with temp tables followed by UPDATE/INSERT/DELETE statements (since CTE's cannot be followed by these statements). At the beginning of each SP I check for the temp tables and delete them if they exist.

Today, I created another stored procedure in the ASDW without any temp tables (no updates/inserts/deletes so I left the CTE's in there), it "compiled", and I was able to run it without issue (returned an empty result set, as there is no data yet). I created another SP after this, and when I went to execute it, I got the following error:

...No catalog entry found for partition ID (id) in database 26. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption...

I then went back to the first SP that I mentioned, and it gave me the same error, even though it had previously run without flaw.

I tried running DBCC CHECKDB as instructed but alas, it is not supported/doesn't work.

I dug around a lot, and what I ended up doing was scaling my database from 100DWU's to 500DWU's. I am at 0.16% of my database storage size limit, and there is barely any data anywhere (total DB size is <300MB).

Is there an explanation for this? If not, I can't in good conscience use this platform in a production environment.

Full error: 

Msg 110802, Level 16, State 1, Line 1
110802;An internal DMS error occurred that caused this operation to fail.
Details: Exception: Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.DmsSqlNativeException,
 Message: SqlNativeBufferReader.Run, error in OdbcExecuteQuery: SqlState:
 42000, NativeError: 608, 'Error calling: SQLExecDirect(this->GetHstmt(), (SQLWCHAR *)statementText, SQL_NTS), SQL return code: -1 | SQL Error Info: 
SrvrMsgState: 1, SrvrSeverity: 16,  Error <1>: ErrorMsg: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]No catalog entry found for partition ID
72057594047758336 in database 36. The metadata is inconsistent. Run DBCC
CHECKDB to check for a metadata corruption. | Error calling: pReadConn-
>ExecuteQuery(statementText, bufferFormat) | state: FFFF, number: 134148, 
active connections: 100', Connection String: Driver={pdwodbc};APP=TypeC01-
DmsNativeReader:DB196\mpdwsvc (2504)-    ODBC;Trusted_Connection=yes;AutoTranslate=no;Server=\\.\pipe\DB.196-
bb5f9dd884cf\sql\query
How 'bout a Fresca
  • 2,267
  • 1
  • 15
  • 26
  • How are you testing for your temporary table? – Rob Farley Feb 29 '16 at 20:50
  • if object_id('tempdb..#tablename') is not null drop table. The stored procedures that are revealing this error are not using temp tables though - they're still using CTE's. I'll add that detail to the post. – How 'bout a Fresca Feb 29 '16 at 21:07
  • But CTEs with INSERT/DELETE/UPDATE aren't supported, so that could be your problem, right? – Rob Farley Feb 29 '16 at 21:09
  • Sorry for the confusion - there are some stored procedures that don't have INSERT/DELETE/UPDATE statements in them. For those SPs, I kept the CTE's. When I run the SP's that have the CTE's at 100DWU, I get the error mentioned above – How 'bout a Fresca Feb 29 '16 at 21:14
  • And if you use sub-queries instead of CTEs? – Rob Farley Feb 29 '16 at 21:37

1 Answers1

1

I'm sorry to hear about your experience with Azure SQL Data Warehouse. I believe this is a defect related to BIT data type handling for NOT NULL columns. Can you confirm that you have a BIT NOT NULL column (e.g., CREATE TABLE t1 (IsTrue BIT NOT NULL);)?

If so, a fix has been coded and is in testing for release. To mitigate this now, you can either switch to a TINY INT or remove the NOT NULL setting for the column.

Matt Usher
  • 1,325
  • 6
  • 10
  • I do not have a column of this data type. If this were causing the issue, how would scaling the DW from 100 DWU to 500 DWU resolve it? – How 'bout a Fresca Mar 01 '16 at 18:37
  • If you haven't already done so please raise a support ticket for this issue so it can be investigated properly. – JRJ Mar 07 '16 at 20:37
  • Can you create a support ticket by following the instructions in the link below? https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-get-started-create-support-ticket/ If you don't have a support plan (required to create a technical support case), please email the SQL Data Warehouse team directly at sqldwfeedback@microsoft.com and include your server name, database name, error message and the approximate timestamp of this error – JRJ Mar 08 '16 at 17:53