Bug with Dynamic SQL and object id (example)

[Former Member]

Hi,

I've managed to re-create an issue with dynamic SQL not resolving to the correct object.

Here's the steps

use master

go

create database db5

go

create database db6

go

use db5

go

create proc P1 as select 1

go

create proc P2 as select 2

go

create proc P3 as select 3

go

create proc P4 as select 4

go

use db6

go

create table T1 (i varchar(99) not NULL)

go

CREATE VIEW dbo.V1 as select i from db6..T1

go

-- check

select name, type, id from db5..sysobjects where id > 760000000

select name, type, id from db6..sysobjects where id > 760000000

-- I get

                type id

-------------- ---- -----------

P1             P      768002736

P2             P      784002793

P3             P      800002850

P4             P      816002907

P0             P     1024003648

(5 rows affected)

                type id

-------------- ---- -----------

T1             U      784002793

V1             V      816002907

use db5

go

create proc P0

as

begin

  declare @sql1 varchar(16384)

select @sql1 = 'insert db6..V1 select * from (select ''0'' as ''c'' from master..sysobjects) t'        -- FAILS

exec (@sql1)

end

go

use db6

go

exec db5..P0

go

Error message I get is

The request for view 'db5.dbo.P4' failed because 'db5.dbo.P4' is a procedure object.

Note: P4 is not mentioned anywhere in the SQL

You can also try it without a proc.

use db5

go

declare @sql1 varchar(16384)

select @sql1 = 'insert db6..V1 select * from (select ''0'' as ''c'' from master..sysobjects) t'        -- FAILS

exec (@sql1)

go

use db6

go

declare @sql1 varchar(16384)

select @sql1 = 'insert db6..V1 select * from (select ''0'' as ''c'' from master..sysobjects) t'        -- FAILS

exec (@sql1)

go

[former_member188958 · Accepted solution]

Hi Mike,

Thanks, that's a great simple reproduction script!

I've simplified it slightly (it doesn't actually require the use of exec() either)

and used it to open defect CR 770497.

Cheers,

-bret

[Former Member · Accepted solution]

Yep - you're right the dynamic SQL is superfluous.

Odd this hasn't been found before - seems a very basic flaw.