Post reply

Query on multiple databases?

  • December 24, 2007 at 8:39 am

    #62724

    Hello all. Happy holiday. Whichever you celebrate. 🙂

    I have two tables, residing in two different databases that I would like to query. I think this should be simple. However... in my usual manner, it's this should be simple part that is tripping me up. Most of the data I need for the report resides on the database CAMaster, in table PIPersonnel. The rest is on COMDB1\SMCSCALC. The query below gives me an error of "Server: Msg 117, Level 15, State 1, Line 6

    The object name 'COMDB1.SMCSCALC..dbo.' contains more than the maximum number of prefixes. The maximum is 3."

    I have also tried COMDB1\SMCSCALC, which gives me a syntax error. And smcscalc. I've also tried changing around the number of dots.

    Both databases have an owner of dbo. The object browser shows the name of the database like this "COMDB1\SMCSCALC(CEM)". I know the name is lowercase, like this "smcscalc".

    I have verified that I have rights to the databases, have not had passwords expired or anything like that. I can query them separately.

    USE CAMaster

    select top 5

    p.SSN, p.Name, p.EmployeeStatus, p.FullorPartTime, p.GradeLevel, p.Title,

    p.JobEntryDate, t.JobCode, p.OriginalHireDate, p.CurrentHireDate,

    p.TermDate1, p.LOAStartDate1, p.LOAReturnDate1, p.DepartmentDesc,

    p.DepartmentEntryDate, p.ManagerSSN, p.ManagersName

    from PIPersonnel p, COMDB1.SMCSCALC..dbo.TypeGrade t

    where p.TypeCode = t.TypeCode

  • December 24, 2007 at 9:56 am

    #762557

    First, the 4 part naming convention is server.database.schema.object. The prefixes are everything before the object.

    Second, I'd recommend that you create a view in one database pointing to the other object. This saves issues later if the databases move, change name, etc. You just change the view.

    create view TypeGrade_v

    as

    select typecode

    , typecodeid

    from SMCSCALC.dbo.TypeGrade

    Use just the database name, the owner (dbo here) and the object. Don't include the server name. Or the instance.

    You are saying data resides on COMDB1\SMCSCALC, which seems like a named instance, not a database. Is this data in two instances? If so, then you need to create a linked server between them to query this data.

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

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