BUG: Can't Access Table Created w/ SP in Another Database

• Microsoft SQL Server versions 4.2x, 6.0

SYMPTOMS

After you create a table in database "C" from a stored procedure in database "A," you can not access this table from a stored procedure in database C.

WORKAROUND

Create the procedure PROC1 as this one:

Create Procedure PROC1 @Param char(1) as
declare @tablename varchar(30)
select @tablename = 'baseC..TABLE2'
   if @Param = '1'
      Select * into baseC..TABLE2 from TABLE1
        else
       Begin
      EXEC ("select * from " + @tablename )
      EXEC ("Drop Table " + @tablename )
       End 

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

This script demonstrates the problem:

/*************************/ 

use master
go
create database baseA on testdb
go
create database baseB on testdb
go
create database baseC on testdb
go
sp_dboption baseC,'select into/bulkcopy',true
go
use baseC
go
checkpoint
go

/*************************/ 

use baseA
go
create table TABLE1 (ind int,name char(10))
go
insert TABLE1 values (1,'toto11')
insert TABLE1 values (2,'toto12')
go

/*************************/ 

use baseB
go
create table TABLE1 (ind int,name char(10))
go
insert TABLE1 values (1,'toto21')
insert TABLE1 values (2,'toto22')
go

/*************************/ 

use baseA
go
Create Procedure PROC1 @Param char(1) as
   if @Param = '1'
      Select * into baseC..TABLE2 from TABLE1
        else
       Begin
      select * from baseC..TABLE2
      Drop Table baseC..TABLE2
       End
go

/*************************/ 

use baseB
go
Create Procedure PROC1 @Param char(1) as
   if @Param = '1'
      Select * into baseC..TABLE2 from TABLE1
        else
       Begin
      select * from baseC..TABLE2
      Drop Table baseC..TABLE2
       End
go

/*************************/ 

use baseA
go
exec baseA..PROC1 '1'
go

/*********** Here is the problem **************/ 

exec baseB..PROC1 '2'
go 

Additional query words: SQL6 procedure sp sproc

Keywords          : SSrvProg SSrvStProc 
Version           : 4.21a 6.00
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: March 24, 1999