Bug with Sybase dynamic SQL resolution

[Former Member]

We have some dynamic SQL in a stored procedure which selects data from a table in another database in server

eg

create proc x

as

...

     select @sql = "select " + @col + " from " + @db_and_view + " where ..."

...

However this fails with the message

The request for view 'DB.dbo.view_name' failed because

'DB.dbo.view_name' is a procedure object.

Upon investigation, it seems the dynamic SQL is failing because a procedure in the current database has the same object id

as the proc in the other database.

DB1

id                     type name

1545053509   P       proc

DB2

id                     type name

1545053509   V      view

We're using Sybase 15.7 SP100.

I assume this is a bug. Anyone know how we can get around this without re-writing tons of stored procedures.

[Former Member]

the actual code is like this.. (I've extracted the main elements)

  select  @TableFullNameSource = @DBName + ".." + @TableName

  select  @sqlStart    =  'select * from (select *, @FileId as __FileId, ',

          @sqlFrom      = ' from ' + @TableFullNameSource + ' ' + coalesce(@sqlWhereClause, '')

...

    exec (@sqlStart + @sqlFrom)

...

Does this help ?

[Mark_A_Parsons]

Your original message mentions objects in databases 'DB1' and 'DB2', but your error description mentions 'DB' (ie, not 'DB1' or 'DB2').  You've either cut-n-pasted the wrong thing or you've really screwed up the creation of your query.

For this particular case we really need to see the nitty-gritty details (eg, how you populate *ALL* of the @variables you use to build your final query).

My guess is that you're not populating your @variables with what you think, eg, you want to query the view in DB2 but your query is referencing the proc in DB1.

You can easily verify your code/logic by simply printing what you're executing, eg, using your latest code snippet:

===============

...

print "%1! %2!", @sqlStart, @sqlFrom

exec(@sqlStart + @sqlFrom)

...

===============

[Former Member]

I was using DB1 and DB2 to try and explain they were different db's.

I've created a test proc which demonstrates the problem

Here's how we do it

create procedure ValidateData

     @DBName varchar(32),

     @TableName varchar(32),

     @sqlWhereClause varchar(200)

as

begin

  declare @TableFullNameSource varchar(1000),

        @sqlStart       varchar(1000),

        @sqlFrom        varchar(1000)

  select  @TableFullNameSource= @DBName + ".." + @TableName

  select  @sqlStart    =  'select * ',

          @sqlFrom      = ' from ' + @TableFullNameSource + ' ' + coalesce(@sqlWhereClause, '')

    exec (@sqlStart + @sqlFrom)

end

go

running this like this

     ValidateData "DB2", 'view_name' , null

fails

but using a different view work. It only happens when the viewname in DB2 has the same object_id as an object in DB1.