Execute a Stored procedure from different databases

  • tarish.velivela

    SSC-Addicted

    Points: 484

    Hi,

    I am trying to write a stored procedure which can be executed from any database and with context of the calling database.

    For example, I have a table ‘table1’ in multiple databases. I would like to create a stored procedure as

    ——————————-

    CREATE PROCEDURE dbo.test

    AS

    select count(*) from table1

    GO

    ——————————-

    in one of the databases (say ‘db1’) and would like execute the same from different databases.

    When I execute this stored procedure using “Exec db1.dbo.test” from different database ‘db2’ it looks for the table ‘table1’ in ‘db1’ database instead of ‘db2’.

    I tried creating system stored procedure and execute the same from different databases. This time it could be executed without specifying database name and owner but the procedure looks for table ‘table1’ in ‘master’ database as it is created in master database.

    I need a way to execute this stored procedure dynamically from any database and execute as if it is executed locally. As this need to be executed across different databases, I can not specify database name along with table name.

    So, I tried to send table name as a parameter as below,

    ——————————-

    CREATE PROCEDURE dbo.test

    @t as sysname (or varchar (100))

    AS

    select count(*) from @t

    GO

    ——————————-

    When I tried to execute this stored procedure with

    Exec db1.dbo.test @t = ‘db2.dbo.table1’

    it gives an error message (Must declare the variable ‘@t’).

    Could anybody please let me know how to execute a stored procedure from a different database on local tables or use database name/ table name as parameter.

    My actual stored procedure is much more complex than what I’ve specified in example.

    Thanks in advance,

    Tarish.

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21921

    You can create a stored procedure in the master database, and name it sp_whatever

    the sp_ will make it available in all of your databases, and the context will be the calling database.

    Example:

    use master

    GO

    create proc sp_alma1 as

    select db_id()

    GO

    use tempdb

    GO

    exec sp_alma1

    GO

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21921

    Another thing, you cannot use @t in the from clause if @t is not a table variable.

    If you are passing only the name, you need to build up your query using dynamic SQL

    declare @query varchar(1000)

    set @query = ‘select * from ‘ + @a

    exec(@query)

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • tarish.velivela

    SSC-Addicted

    Points: 484

    Andras Belokosztolszki (11/1/2007)


    You can create a stored procedure in the master database, and name it sp_whatever

    the sp_ will make it available in all of your databases, and the context will be the calling database.

    Example:

    use master

    GO

    create proc sp_alma1 as

    select db_id()

    GO

    use tempdb

    GO

    exec sp_alma1

    GO

    Regards,

    Andras

    Hi Andras,

    Thanks for you reply.

    I was able to get dbid() or db_name() by executing stored procedure which is created in different database.

    But my requirement is to access tables in current database by executing stored procedure which is created in different database.

    Here is an example/sample of my requirement.

    ————————————————–

    use tempdb

    GO

    create table temptable1(col1 varchar(100), col2 int)

    GO

    use master

    GO

    create proc sp_alma1

    as

    select * from temptable1

    GO

    use tempdb

    GO

    exec sp_alma1

    GO

    ————————————————–

    Executing this stored procedure from different database gives me:

    Server: Msg 208, Level 16, State 1, Procedure sp_alma1, Line 3

    Invalid object name ‘temptable1’.

    Please suggest me a solution to get through this.

    Thanks,

    Tarish.

  • Roy Latham

    SSC Enthusiast

    Points: 182

    I think this answers your question:

    http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

    Roy

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21921

    Roy’s link is the solution that you could use for the above. Just to include the solution here too:

    On 2000 when you create the stored procedure in the master database, you can create it like:

    use master

    GO

    EXEC master.dbo.sp_MS_upd_sysobj_category 1

    GO

    create proc sp_alma1

    as

    select * from dbo.temptable1

    GO

    EXEC master.dbo.sp_MS_upd_sysobj_category 2

    GO

    use tempdb

    On 2005 you can just change an existing stored procedure (in my example sp_alma1) to a system proc with:

    use master

    GO

    EXEC sys.sp_MS_marksystemobject sp_alma1

    GO

    use tempdb

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • tarish.velivela

    SSC-Addicted

    Points: 484

    Roy Latham (11/2/2007)


    I think this answers your question:

    http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

    Roy

    Hello Roy,

    Thanks a lot. Your solution is what I was looking for. This makes my stored procedures work according to my requirement.

    I have another concern here. An article in SQLServerCentral says there are some pitfalls with System Stored procedures.

    The 2nd point in the article “http://www.sqlservercentral.com/articles/Administering/creatingasystemstoredprocedure/1358/” says

    ——————————————————————————————–

    2. You must keep a copy of the SP somewhere else because when the master database gets rebuilt your system stored procedures will be gone. This could also happen when applying a service pack and/or during an upgrade to a different version of SQL Server.

    ——————————————————————————————–

    Is this true? if so, how can I overcome this issue? I will be having more than 100 or nearly 200 SPs this way. Even if I keep a copy of the queries used in these SPs, it will be a tedious job to create so many SPs again in future .

    Please suggest me a solution/ work around for this issue.

    Thanks a lot in advance,

    Tarish.

  • tarish.velivela

    SSC-Addicted

    Points: 484

    Hi Andras,

    Thanks for your post. This worked

    Thanks,

    Tarish.

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    tarish.velivela (11/12/2007)


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

    2. You must keep a copy of the SP somewhere else because when the master database gets rebuilt your system stored procedures will be gone. This could also happen when applying a service pack and/or during an upgrade to a different version of SQL Server.

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

    Is this true? if so, how can I overcome this issue? I will be having more than 100 or nearly 200 SPs this way. Even if I keep a copy of the queries used in these SPs, it will be a tedious job to create so many SPs again in future .

    If you ever have to rebuild your master database (say, due to corruption), this is true. However, you should have a backup to apply after you get SQL Server up and running again. With respect to service packs, your own stored procedures aren’t very likely to be affected. If you modify a system stored procedure belonging to Microsoft, that’s a different story.

    With that said, it’s still better to stay clear of the master database as much as possible.

    K. Brian Kelley
    @kbriankelley

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply