|
zero1de
Yak Posting Veteran
Germany
66 Posts |
 Posted - 11/25/2012 : 14:35:11
|
Hello people,
I need it urgent. The script creates on some database conversion errors. Since I am newbie and do not have much experience with MS SQL and there for i need your help. Here's the script.
Msg 8114, Level 16, State 5, Line 1. Error converting data type varchar to numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.
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); |
|
Urgent - error converting data type varchar to num
