Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Bug with Dynamic SQL and object id (example)

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 Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question