Urgent - error converting data type varchar to num

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Urgent - error converting data type varchar to num

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2012-11-25 : 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);

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-26 : 15:28:15

I tried this script on a couple of databases and it works without error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources