Query error ! urgently

[zero1de]

Hi,

with this query, I make sure that the content from prod SQL Server 2008 r2 DB's is the same content as in the backup DB which is mirrored. Unfortunately I get on some databases error messages like this.

Does anyone have any idea how to simplify the query, so it does not generate any error message again ?

THX guys

(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 1
The 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.

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 @TABLE
TABLE (
Table_schema sysname,
Table_name sysname,
column_list NVARCHAR(MAX)
PRIMARY KEY(Table_schema, Table_name)
);

DECLARE @COLUMN
TABLE (
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 SELECT

INSERT @TABLE
SELECT t.Table_schema, t.Table_name, CASE WHEN c.COLUMN_NAME IS NULL THEN '*' END
FROM INFORMATION_SCHEMA.Tables AS t
OUTER APPLY (
SELECT TOP 1 c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
AND 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_list
INSERT @COLUMN
SELECT	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_list
FROM @TABLE AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
WHERE t.COLUMN_LIST IS NULL
ORDER 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 in
UPDATE	c
SET		@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_list
FROM @COLUMN AS c
LEFT JOIN @datatype d ON c.DATA_TYPE = d.DATA_TYPE;

-- go back and update our @TABLE variable with the column list
UPDATE t
SET t.COLUMN_LIST = c.COLUMN_LIST
FROM @TABLE AS t
JOIN @COLUMN AS c
ON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_name
WHERE 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 together
SELECT @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 CHECKSUM
SELECT @SQL = 'SELECT CHECKSUM_AGG(z.CS) AS DB_CHECKSUM FROM (
' + @SQL + ') AS z';

-- Run the Dynamic SQL statement to get a DB checksum
EXEC(@SQL);

[Rabbit]

You're trying to union together 9167 checksum queries, of course you're going to run out of resources.

Instead, use a loop to run each one individually and insert them into a temp table. Then run your final checksum against that. That way it can do the 9167 individually instead of all at once.

[zero1de]

o.k thanks but i'm newbee can you show me how to do this with the loop ? i'm not a good coder :(

THX

You're trying to union together 9167 checksum queries, of course you're going to run out of resources.

Instead, use a loop to run each one individually and insert them into a temp table. Then run your final checksum against that. That way it can do the 9167 individually instead of all at once.

[Rabbit]

You'll want to use what is known as a cursor. Here is Microsoft's documentation on cursors with examples:

http://msdn.microsoft.com/en-us/library/ms180169.aspx

[zero1de]

thanks anyway but that was not a big help now.

You'll want to use what is known as a cursor. Here is Microsoft's documentation on cursors with examples:
http://msdn.microsoft.com/en-us/library/ms180169.aspx

[Rabbit]

Which part was confusing? Every thing you need to know to implement the cursor is in that link.

[zero1de]

I have no idea how to do it. I'm not a coder.
Can you perhaps help me there and show me how you can do this ?

THX

Which part was confusing? Every thing you need to know to implement the cursor is in that link.

[Rabbit]

If you're not a coder, who wrote the code you posted?

Unfortunately, this is not a code writing service. We are here to help guide people to solutions so they can learn from it, not give them the answer.