October 25, 2004 at 8:03 am
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.
October 25, 2004 at 9:05 am
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.
October 25, 2004 at 10:32 am
Yes, please ignore this one because I found out I should use OpenRowset and I don't know how to delete this post.
October 26, 2004 at 11:42 am
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