locked
UDP to query the current DB RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, 

    I'm refactoring my SQL into UDP. The problem is that I want to define the UDP in another DB, because it is a common task that can apply to a bunch of my DBs. 

    The SQL works fine before refactoring into UDP in another DB, but having done that, I got the "Invalid object name " error.

    I think it is because that previously the "select mycol from mytab" works  under current DB. Having moved to mycommondb, and calling it via

    Exec mycommondb..commontask

    would fail, even if still under the current DB. 

    Any way to fix it so that my commontask would work with whatever my current DB is? 

    *UPDATE*: 

    The mycommondb..commontask is called within the context of current DB, and it contains a single "select mycol from mytab..." SQL statement. That single "select mycol from mytab..." SQL statement works fine within the context of current DB, shouldn't it work the same when refactored into UDP? 

    OK, for whatever reason it doesn't work the same, is there any way in UDP to sense what's the calling context is, i.e., the current DB when calling it, and I'll prelude my SQL with a "USE ... GO"? Is it possible?

    Thanks






    • Edited by smetah Thursday, May 30, 2013 7:15 PM
    Thursday, May 30, 2013 2:39 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The below select statement will work in all database if the object "mytab" exists.

    select mycol from mytab

    In case if we are calling an SP from different database then we need to specify DB.Schema.Tablename in the select statement so that the right table object is called. Well in your case you have created a procedure containing the above Select statement in the mycommondb database and that is not working. Are you sure that "mytab" exists in "mycommondb". If yes then the issue is not due to the above select statement. It can be someother.

    Please post the stored procedure.

    Lastly, you are also looking for calling database name so that you can use it in "USE DBname". If you can add another parameter then it is possible using dynamic query as,

    Say @db is a parameter.

    set @strs = 'USE ' + @db

    exec(@strs)

    Regards, RSingh


    Sunday, October 13, 2013 2:22 AM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi,

    Inside your Stored procedure prefix all tables(objects) with 3 part naming convention DatabaseName.SchemaName.TableName , So that even if you reference a Stored procedure in another database ,the objects inside Stored procedure that belongs to another database can also be referenced .

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.


    Thursday, May 30, 2013 4:35 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Inside your Stored procedure prefix all tables(objects) with 3 part naming convention DatabaseName.SchemaName.TableName 

    Hi sathya,

    How can I make my commontask work with whatever my current DB is, just like a plain SQL before refactoring into UDP in another DB?

    Thanks

    Thursday, May 30, 2013 4:39 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi,

    Try these links

    http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

    http://stackoverflow.com/questions/720339/how-to-select-data-of-a-table-from-another-database-in-sql-server

    If you want to reference Stored procedure and its underlying objects in other databases,

    create Stored procedure with objects inside with 3 part naming convention.

    If DB 's are on different servers,create linked server and follow four part naming convention.

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Thursday, May 30, 2013 4:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Both above 2 urls talk about creating linked servers.

    Mine is under single Server, but different DB. Hope there is a simpler solution. 

    Thursday, May 30, 2013 4:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Both above 2 urls talk about creating linked servers.

    Mine is under single Server, but different DB. Hope there is a simpler solution. 


    Did you try with 3 part naming convention ??

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Thursday, May 30, 2013 4:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    see my update in OP.
    Thursday, May 30, 2013 7:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    From smetah's OP...

    *UPDATE*: 

    The mycommondb..commontask is called within the context of current DB, and it contains a single "select mycol from mytab..." SQL statement. That single "select mycol from mytab..." SQL statement works fine within the context of current DB, shouldn't it work the same when refactored into UDP? 

    OK, for whatever reason it doesn't work the same, is there any way in UDP to sense what's the calling context is, i.e., the current DB when calling it, and I'll prelude my SQL with a "USE ... GO"? Is it possible?

    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, June 4, 2013 5:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Smetah,

    Is this still an issue?

    Thanks!

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, September 3, 2013 8:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes still -- there is still no generic solution for my commontask to work with whatever my current DB is.
    Tuesday, September 10, 2013 2:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The below select statement will work in all database if the object "mytab" exists.

    select mycol from mytab

    In case if we are calling an SP from different database then we need to specify DB.Schema.Tablename in the select statement so that the right table object is called. Well in your case you have created a procedure containing the above Select statement in the mycommondb database and that is not working. Are you sure that "mytab" exists in "mycommondb". If yes then the issue is not due to the above select statement. It can be someother.

    Please post the stored procedure.

    Lastly, you are also looking for calling database name so that you can use it in "USE DBname". If you can add another parameter then it is possible using dynamic query as,

    Say @db is a parameter.

    set @strs = 'USE ' + @db

    exec(@strs)

    Regards, RSingh


    Sunday, October 13, 2013 2:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    thanks RSingh. I end up with adding another parameter.
    Monday, October 14, 2013 3:24 PM