Post reply

query over two databases

  • July 11, 2005 at 2:41 pm

    #93511

    Hi,

    I'm trying to run a query (eventually used in stored-procedures) that uses a multiple tables from two databases.

    I tried the query below and I got 'Invalid Object name' error.

    select cp.vcProjectNumber, cp.vcProjectName, pa.vcPMLastName

    from [CapitalProejct].[tblCapitalProject] cp

    inner join [ProjectAccounting].[tblProjectManager] pa

    on cp.intPMID = pa.intPMID

    CapitalProject and ProjectAccounting are the databases on the same server.

    I tried books on line but I could not find anything remotely closed to what I'm trying to do. I'll appreciate your help. Thanks.

    Dong

  • July 11, 2005 at 2:43 pm

    #573130

    Select col1 from DBName.owner.TableName inner join...

  • July 11, 2005 at 2:48 pm

    #573135

    Thank you, Remi. It worked. What do you call this kind of query? I'd like to read more about this from BOL. Thank you.

  • July 11, 2005 at 2:54 pm

    #573139

    I haven't found anything usefull... This is a relatively basic select statement. The only difference is that you use 3 parts naming instead of 2 (owner + object).

  • July 11, 2005 at 2:56 pm

    #573142

    Ok. Thank you.

  • July 12, 2005 at 3:07 am

    #573277

    Also worth remembering to use 4 part names if your databases are on 2 different servers (i.e. [server name].[db name].[owner].

    Just don't forget to register the remote server as a linked server in EM.

  • July 12, 2005 at 9:01 am

    #573410

    There's no special name for that kind of query, but using multi-part object names is called a qualified reference.  For example, see "Qualifying Names Inside Stored Procedures" in BOL.

Viewing 7 posts - 1 through 6 (of 6 total)

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