Results 1 to 8 of 8

Thread: Openrowset?

  1. #1
    Join Date
    Nov 2008
    Posts
    42

    Unanswered: Openrowset?

    Good afternoon!

    I am trying to join 2 tables on server A through an openrowset query on server B. I am having trouble......

    Here is what I have so far:

    Code:
    SELECT a.*, b.*
    FROM OPENROWSET('SQLOLEDB','serverA';'*****';'******',
       'SELECT *
    	FROM data.dbo.agent') AS a
    
    left join 
    
    OPENROWSET('SQLOLEDB','serverA';'*****';'******',
       'SELECT *
    	FROM data.dbo.lstaff') AS b
    
    on a.id0 = b.id0
    and a.netdate between b.startdate and b.enddate
    where netdate = '04/25/09'
    I tried selecting specific columns, but I was having trouble there too, so I just did a select* to try and get the point across.

    Is there a better way to extract data from two joined tables on server A into one table on server B?

    Both servers are using SQL 2000. Any direction would be helpful.

    Thanks
    Drew

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look up sp_addlinkedserver in books online.

    If you create a linked server you can query it using 4 part naming

    i.e. servername.database.owner.object
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    42
    Thanks, I will try linking them up!

    Drew

  4. #4
    Join Date
    Nov 2008
    Posts
    42
    Quote Originally Posted by gvee
    Look up sp_addlinkedserver in books online.

    If you create a linked server you can query it using 4 part naming

    i.e. servername.database.owner.object

    I linked the servers, but when I run a query using 4 part naming serverA.data.dbo.staff, I get an error saying 'the number name contains more than the maximum number of prefixes. The maximum is 3.'.

    I tried looking it in BOL, but I couldn't figure out what I was doing wrong.

    Any help would be appreciated.

    Thanks
    Drew

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, what do the results of
    Code:
    SELECT *
    FROM   sysservers
    look like?
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post the full query you are using?
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2008
    Posts
    42
    Quote Originally Posted by gvee
    Also, what do the results of
    Code:
    SELECT *
    FROM   sysservers
    look like?

    When I run this query, it shows me both servers. Is there a particular field you are interested in?

    Thanks
    Drew

  8. #8
    Join Date
    Nov 2008
    Posts
    42
    Quote Originally Posted by gvee
    Can you post the full query you are using?

    The full query I am using is:

    Code:
    select 
    agentname,
    profname,
    Profname as CallType,
    vudhidlu.site,
    jobno,
    vudallstafflu.cfs,
    tenuregroup,
    --pqtype,
    fulltime,
    --cartier,
    numhours,
    level1,
    level2,
    chronid0,
    pid
    
    from 
    wpccpma001.ccmdata.dbo.vudallstafflu inner join wpccpma001.ccmdata.dbo.vudhidlu
    on wpccpma001.ccmdata.dbo.vudallstafflu.hid = wpccpma001.ccmdata.dbo.vudhidlu.hid 
    and wpccpma001.ccmdata.dbo.vudallstafflu.bid = wpccpma001.ccmdata.dbo.vudhidlu.bid 
    
    where '04/26/09' between startdate and enddate 
    
    group by vudhidlu.site, vudallstafflu.cfs, tenuregroup, profname, jobno, fulltime, numhours, level1,level2, agentname, chronid0, pid
    order by site, vudallstafflu.cfs, tenuregroup

    Thanks for your help.

    The exact error is

    Msg 117, Level 15, State 2, Line 22
    The number name 'wpccpma001.ccmdata.dbo.vudallstafflu' contains more than the maximum number of prefixes. The maximum is 3.

    Thanks
    Drew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •