Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Unanswered: Linking tables in a Stored Procedure

    Okay I'll try an easier question.

    I have a stored procedure with an OPENQUERY.
    It is working in a SQL Query.
    I would join it to an existing table.

    Like this (except this doesn't work)
    Code:
    SELECT
    	Co_No, 
    	Div_No, 
    	Job_No, 
    	Descript
    
    FROM OPENQUERY(
    ALPHA, 
    '
    
    	SELECT
    		Job_Master.GCONO as Co_No, 
    		Job_Master.GDVNO as Div_No, 
    		UCase(Job_Master.GJBNO) as Job_No, 
    		Job_Master.GD20A as Descript
    
    
    	FROM 
                             Job_Master
    
    	FOR READ ONLY
    
    '
    )
    
    Job_Descript
    
    Right Join
    tbl_Job_Access
    on
    (Job_No = tbl_Job_Access.Job_No)
    If I don't add the Right Join everything works great.

    I would like to join the data to the table tbl_Job_Access which is a table in the same database.

    I would appreciate any help,

    Thank you,
    Last edited by kenln; 10-10-08 at 12:29.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If Job_Access is in the same database as Job_Master, why don't you join them inside the OPENQUERY statement?
    Or better yet, create a view on the database that joins them, and run your OPENQUERY against that?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2008
    Posts
    5
    Job Master is a linked table from a different server.

    Job_Access is an actual table in the current database.

    If I understand OPENQUERY and linked databases I cannot join them inside the OPENQUERY, of if I can I do not know how.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, now that we have determined that both tables are NOT in the same database, please elucidate on the "it don't work" part of your post.
    Is it throwing an error? Is it returning incorrect data? Is it returning not data?
    We are not psychics here.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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