zero1de
Posting Yak Master
105 Posts |
Posted - 2012-11-11 : 14:32:25
|
Hello,the query generated for big databases the following error message (Error id 8623 Sev. 16)after 15 minutes. In many DB's it runs error free? Does anyone have experience with it? and can help me?Message Windows for the Query.(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(9167 row(s) affected)(11200 row(s) affected)(11200 row(s) affected)(302 row(s) affected)Msg 8623, Level 16, State 1, Line 1The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.Query:DECLARE @SQL NVARCHAR(MAX);DECLARE @datatype TABLE (DATA_TYPE NVARCHAR(128),ALTERNATE_EXPRESSION NVARCHAR(128));INSERT @datatype VALUES('text','CONVERT(VARCHAR(MAX),%n)')INSERT @datatype VALUES('ntext','CONVERT(NVARCHAR(MAX),%n)')INSERT @datatype VALUES('image','CONVERT(VARBINARY(MAX),%n)')INSERT @datatype VALUES('XML','CONVERT(VARCHAR(MAX),%n)')DECLARE @TABLETABLE (Table_schema sysname,Table_name sysname,column_list NVARCHAR(MAX)PRIMARY KEY(Table_schema, Table_name));DECLARE @COLUMNTABLE (ID INT,Table_schema sysname,Table_name sysname,column_name sysname,ORDINAL_POSITION INT,data_type NVARCHAR(128),max_ordinal INT,column_list NVARCHAR(MAX)PRIMARY KEY(ID));-- Capture an overridable column_listl, also enforce an order -- without using ORDER BY in the following SELECTINSERT @TABLESELECT t.Table_schema, t.Table_name, CASE WHEN c.COLUMN_NAME IS NULL THEN '*' ENDFROM INFORMATION_SCHEMA.Tables AS tOUTER APPLY (SELECT TOP 1 c.COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNS AS cWHERE c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameAND c.DATA_TYPE IN (SELECT d.DATA_TYPE FROM @datatype d)) AS c;-- need to assemble an ordered list for following update statement to build column_listINSERT @COLUMNSELECT ROW_NUMBER() OVER (ORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION) AS ID, c.Table_schema, c.Table_name, c.column_name, c.ORDINAL_POSITION, c.data_type, MAX(c.ORDINAL_POSITION) OVER(PARTITION BY c.Table_schema, c.Table_name) AS max_ordinal, NULL AS column_listFROM @TABLE AS tJOIN INFORMATION_SCHEMA.COLUMNS AS cON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameWHERE t.COLUMN_LIST IS NULLORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION;DECLARE @last_table_schema sysname, @last_table_name sysname, @column_list NVARCHAR(MAX);-- String concatenation to get COLUMN_LIST built the hard way -- I wish i had a CONCAT_STRING_AGG() function built inUPDATE cSET @column_list = COALESCE(CASE WHEN COALESCE(@last_table_schema,'') = c.table_schema AND COALESCE(@last_table_name,'') = c.table_name THEN @column_list + ',' END,'') + REPLACE(COALESCE(d.ALTERNATE_EXPRESSION,'%n'),'%n','[' + c.column_name + ']'), @last_table_schema = c.table_schema, @last_table_name = c.table_name, c.column_list = @column_listFROM @COLUMN AS cLEFT JOIN @datatype d ON c.DATA_TYPE = d.DATA_TYPE;-- go back and update our @TABLE variable with the column listUPDATE tSET t.COLUMN_LIST = c.COLUMN_LISTFROM @TABLE AS tJOIN @COLUMN AS cON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameWHERE c.max_ordinal = c.ORDINAL_POSITION -- get only the last column as only it will have the full column list-- Assemble the CHECKSUM for each row and a CHECKSUM_AGG for each TABLE with a UNION ALL to pull them togetherSELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),' UNION ALL '),CONVERT(NVARCHAR(MAX),'')) + 'SELECT CHECKSUM_AGG(CHECKSUM('+ t.column_list + ')) AS CS FROM [' + CONVERT(NVARCHAR(MAX),t.Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),t.Table_name) + ']'FROM @TABLE t;-- Include a CHECKSUM_AGG on the resultset so we can get a DB level CHECKSUMSELECT @SQL = 'SELECT CHECKSUM_AGG(z.CS) AS DB_CHECKSUM FROM (' + @SQL + ') AS z';-- Run the Dynamic SQL statement to get a DB checksumEXEC(@SQL); |
|