PRSQL9103050: Column Datatype Not Listed on SP_HELP ID Number: Q71065
1.10 OS/2 buglist1.10 fixlist1.11
Summary:
PROBLEM ID: PRSQL9103050
SYMPTOM When the SP_HELP command is used to create a column listing, the datatype of the columns of a TABLE created with the SELECT…INTO command is not displayed.
CAUSE The SELECT…INTO command, when used to create a table, does not correctly create the datatype in the syscolumns table.
RESOLUTION Microsoft has confirmed this to be a problem in SQL Server version 1.1. This problem has been corrected in SQL Server version 1.11, which is available from Microsoft Product Support Services. For more information, contact your primary support provider.
More Information:
The following is a script that creates a stored procedure that will correct the problems that occur with the SELECT…INTO command. After creating the procedure in the desired database, run the script using the following command:
fixinto originaltab, newtab
Please note that if the new table is created by a join, this procedure will have to be run against all tables concerned. Also note that if the tables in question are not in the same database, this procedure will not correct this problem.
The script is as follows:
/* Stored procedure to fix the problems created with select into */
sp_configure “allow”, 1 go reconfigure with override go
create procedure fixinto @oldtab char(30), @newtab char(30) as declare @test int
declare @usertype int declare @username char(30) select @test = count(*) from syscolumns where id=object_id(@newtab)
while @test > 0 begin select @username = name from syscolumns where id = object_id(@newtab) and colid = @test select @usertype = NULL select @usertype = usertype from syscolumns where id=object_id(@oldtab) and name = @username if @usertype = 0 begin print “cannot update, check order of tables: oldtab, newtab” return end if @usertype != NULL begin update syscolumns set usertype = @usertype where id = object_id(@newtab) and colid = @test end select @test = @test - 1 end go
sp_configure “allow”, 0 go reconfigure with override go
