Urgent~How to use OpenDataSource to query from another server???

  • Hi experts,

    In my .net web application, I am creating a datatable which will retrive data from the SQL Server 2000. 

    The main query from SQL1 server, MaintenanceHD database, Job_Tracking_Table:

    SELECT J.Job_ID, J.J_Open_Date, J.J_Vendor_ID FROM Job_Tracking_Table J

    Now the user request to have Vendor name instead of Vendor ID, however, the the Vendor Name has to be retrieved from SQL2 server, HFC database, PM200 table:

    SELECT P.VendorID, P.VendName FROM PM200

    These 2 servers are not linked and can't be linked because SQL2 server stores sensitive data. Therefore, I am thinking if I can use SQL Server's OPENDATASOURCE to combine these 2 queries to act as linked server. 

    I checked the BOL, but I still have no idea of how to use OpenDataSource in this case, can someone show me how to use OpenDataSource to get the VendName as 1 command statement?  If not, can someone tell me how can I do this?

    Thank you.

     

  • I have posted in your other thread re OPENROWSET. Are these two related?

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Yes, please ignore this one because I found out I should use OpenRowset and I don't know how to delete this post. 

  • Hi,

    You can use the script below, I already tested and it certainly worked :

    SELECT J.Job_ID, J.J_Open_Date, P.VendName  FROM Job_Tracking_Table J,

              OPENDATASOURCE('SQLOLEDB', 'Data Source=SQL2_server;User ID=UserName ;Password=UserPassword').HFC.dbo.PM200 P

    WHERE J.J_Vendor_ID=P.VendorID

    You should run this script in SQL1 server, MaintenanceHD database.

    Please change the UserName and UserPassword to the real ones. You should use the ID and password of someone who has access to both databases, servers.

    If there is concern you can reach me at lamd@vmcmail.com

    VMC-WD-MIS-LAMD

Viewing 4 posts - 1 through 3 (of 3 total)

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