MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

SQL Server 2008 migration: "The data types table and varchar are incompatible in the equal to operator.”

leave a comment »


Hello,

this is my first blog post ever so forgive me for being a bit rusty.

We have decided to start writing a blog because we would like to keep track of the solutions we find to our everyday problems (mainly IT related problems but Edmondo could decide to discuss as well his mid-life crisis). Without a blog those solutions would have no other value but for the problem itself and would soon be forgotten (and the effort you made goes with them).

The first subject I would like to discuss is problem I ran into during a migration from SQL Server 2000 to SQL Server 2008. One important fact is that the destination server was a Windows server 2008 R2 x64.

I thought I had prepared well enough for the migration: read all the white papers, ran the Upgrade Advisory, corrected the errors reported etc. Then when I started testing the databases I noticed a stored procedure was failing with a rather peculiar error:

“Msg 402, Level 16, State 1, Line 3 The data types table and varchar are incompatible in the equal to operator.”

We tried to import that database on a SQL Server 2008 (32bit) and that same query ran perfectly well.

Well, the weirdness of this problem does not end here, by changing totally irrelevant code (such as a IF) in the SP the query would start working fine.

This SP was making, among other things, a direct insert in another DB so we have tried to enclose this insert in a SP in the destination database and the SP started executing successfully.

Anyway that would have been a workaround, the actual cause was that “rules” in SQL 2008 have been deprecated, once we’ve removed said rules from the destination database, everything started working fine. Rules are supposed to be substituted with constraints.

If your rules/types apply to many columns I wrote a little script to search through you database for them (you have to define which rules/type to look for), this script will produce another script to create the corresponding CONSTRAINT. Once you have the constraint script, remove the link between rules and type and create the constraints using the outcome of this script.

Declare @tbname varchar(50)
Declare @clname varchar(50)
Declare @dttype varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT T.[name] AS [table_name], AC.[name] AS [column_name],
        TY.[name] AS system_data_type
FROM sys.[tables] AS T
  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0  and (TY.name = 'ACCESS') -- substitute here the name of the type you are looking for
ORDER BY T.[name], AC.[column_id]
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tbname, @clname, @dttype
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'alter table ' + @tbname
if @dttype = 'ACCESS'
begin
  PRINT 'add constraint ck_' + @tbname + '_' + @clname + ' CHECK(' + @clname + ' = ''N'' OR ' + @clname + ' = ''R'' OR ' + @clname + ' = ''W'')' -- modify the constraint according to the rule
end
FETCH NEXT FROM db_cursor INTO @tbname, @clname, @dttype
END
CLOSE db_cursor
DEALLOCATE db_cursor

Remember: constraints are created at database level so as naming policy I was forced to use ck_tablename_columnname.

Advertisements

Written by zantoro

August 12, 2010 at 3:04 pm

Posted in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: