Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    13

    Unanswered: How to get data from Different Database

    HI,
    i am facing a problem.. i want to get data from different database with in Stored Procedure.

    for eg.

    tbl_Product_Original (This table is Exist in db_Original)
    tbl_Product_temp (This table is Exist in db_TEMP)


    Create Proc SP_TEST
    @id as numeric(9)
    AS
    Declare @productid as numeric(9)

    set @productid = (Select productid from tbl_Product_Original where Productid = @id) -- its not working rite now just wrote for understanding

    select * from tbl_Product_temp where Product_ID = @productid

    Go


    so i hope u got the problem... how can i get productid from different db.

    NOTE: DATABASES ARE ON DIFFERENT MACHINES ON SAME NETWORK.

    Regards
    Adi
    Last edited by adeelnasim; 09-21-04 at 13:11.

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    Did you try

    set @productid = (Select productid from db_Original..tbl_Product_Original where Productid = @id)

  3. #3
    Join Date
    Sep 2004
    Posts
    13
    Quote Originally Posted by RogerWilco
    Did you try

    set @productid = (Select productid from db_Original..tbl_Product_Original where Productid = @id)

    Yes try this.. but its not working.

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by adeelnasim
    Yes try this.. but its not working.
    That should work. What error do you get?

    I can do the following when my database connection isn't set to Northwind:

    Code:
    SELECT TOP 5 * FROM Northwind..Customers

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You nned to create a linked server, then reference the table is the 4 part name

    server.dbname.owner.table

    look up sp_addlinkedserver
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Brett Kaiser
    You nned to create a linked server, then reference the table is the 4 part name
    That's if the database is on a separate server. I guess I assumed both databases were on the same server.

  7. #7
    Join Date
    Sep 2004
    Posts
    13
    Quote Originally Posted by Brett Kaiser
    You nned to create a linked server, then reference the table is the 4 part name

    server.dbname.owner.table

    look up sp_addlinkedserver
    This is the rite solution... Thanks Brett and Roger for ur cooperation..

Posting Permissions

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