SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Urgent - error converting data type varchar to num
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Yak Posting Veteran

Germany
70 Posts

Posted - 11/25/2012 :  14:35:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 11/26/2012 :  15:28:15  Show Profile  Reply with Quote
I tried this script on a couple of databases and it works without error.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000