Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
Thanks and Regards,
Prasanth
HI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:

> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth
|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
[vbcol=seagreen]
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:

Reference another database without hardcoding the name of it...

Hi,
I s there any way to access another database without hardcoding the name in
the stored procedure?
for example:
database1.dbo.sp_Test()
begin
select * from database2.dbo.supertable
end
So instead of writing "database2" (since this name can change in test
environments) is there another way to reference it? One way could be to
dynamicly create some sql, where the name is found in a table - are there
others?
So the database name must set at runtime rather than compile time...
CheersIn SQL Server 2005, you could create a synonym.
In SQL Server 2000, the method I use is what you describe, get the name of
the server and/or database and build a dynamic string. <yuck>
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
> Hi,
> I s there any way to access another database without hardcoding the name
> in
> the stored procedure?
> for example:
> database1.dbo.sp_Test()
> begin
> select * from database2.dbo.supertable
> end
> So instead of writing "database2" (since this name can change in test
> environments) is there another way to reference it? One way could be to
> dynamicly create some sql, where the name is found in a table - are there
> others?
> So the database name must set at runtime rather than compile time...
> Cheers|||In SQL Server 2000, you can't dynamically change the database or table
referenced in a select, update, delete statement without resorting to
building the statement in a varchar and then executing it using Exec. Is it
really necessary to prefix the table name with the database? It's best for
the development / test database to be deployed on a seperate server or
instance.
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
> Hi,
> I s there any way to access another database without hardcoding the name
> in
> the stored procedure?
> for example:
> database1.dbo.sp_Test()
> begin
> select * from database2.dbo.supertable
> end
> So instead of writing "database2" (since this name can change in test
> environments) is there another way to reference it? One way could be to
> dynamicly create some sql, where the name is found in a table - are there
> others?
> So the database name must set at runtime rather than compile time...
> Cheers