January 15, 2007 at 10:10 am
Hello Everyone,
We just switch to SQL Server for our dev server.
We are facing a frustrating problem.
The way SQL Server 2005 sp1 resolve object name in stored procedures has changed:
If you issue "select * from sys.tables" in a stored procedure that is create in the master database, at execution time, when this stored proc will be executed, the table that will be queried will be "master.sys.tables", not the <curent database>.sys.tables as we would expect.
I am pretty sure that without sp1, this was not the behavior. And, of course, it is not the behavior on SQL Server 2000.
Is there a simple way to work around that (other than doing dynamic SQL)?
Regards.
Carl
January 15, 2007 at 11:30 pm
If you use old system table now they are views in 2005 you get the same behaviour where as if you use new catolog views or tables you don't get the same and you have to use dynamic SQL to handle it...
create
proc
dbo
.sp_tbl
as
select
* from sysobjects where type = 'u'
MohammedU
Microsoft SQL Server MVP
January 16, 2007 at 6:37 am
Thanks Mohammed,
You are rigth : Using old system table (now views on SQL Server 2005) work the same way as SQL Server 2000 for naming resolution.
Maybe we won't have the choice to use them but we would like to use knew sys schema views instead.
At my sense, it is a lack that new SQL Server 2005 views (owned by sys schema as sys.tables) don't let us manage them the way SQL Server 2000 does with "old system views" and INFORMATION_SCHEMA views. It would be so simple to have a keyword in the creation of the stored procedure to let the engine knows we want object naming resolution to be done at run time... Why Microsoft SQL Server dev. team choose not to do this?
Regards,
Carl
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply