Microsoft KB Archive/140079

{|
 * width="100%"|

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

 * }

Q140079

-

The information in this article applies to:


 * Microsoft SQL Server versions 4.2x, 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 query words: SQL6 procedure sp sproc

Keywords :

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600 kbSQLServ420OS2