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

 Last reviewed: March 18, 1997
Article ID: Q140079
4.21a 6.00 WINDOWS kbprg kbbug4.21a kbbug6.00

The information in this article applies to:

  • Microsoft SQL Server, versions 4.21a and 6.0

BUG# NT: 11943 (4.21a)

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 reference words: SQL6 6.00 procedure sp sproc
KBCategory: kbprog kbbug4.21a kbbug6.00
KBSubcategory: SSrvStProc SSrvProg
Keywords : SSrvProg SSrvStProc kbbuglist
Version : 4.21a 6.00
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 18, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.