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