2 SQL server Recordset

[svagelis]

VB 6 SQL SERVER - Can you tell me a way of creating one recordset from 2 SQL SERVERs?

 

[StevenK]

If you're attached to one server and the other one is linked (using the sp_addlinkedserver command) then you should be able to use a query like :
'SELECT A.[Field1], A.[Field2], B.[Field3]
FROM [localserver].[database1].[Table1] A,
[otherserver].[database2].[Table2] B
where A.[Field4] = B.[Field5]'
This is an example off the top of my head but should work
Steve

 

[tlbroadbent]

StevenK's example is very close to the correct syntax. You don't need the local server name in the query. The remote or linked server name has four parts. In this example I use dbo for the table owner name.

SELECT A.Field1, A.Field2, B.Field3
FROM database1.dbo.Table1 A
INNER JOIN linkedserver.database2.dbo.Table2 B
ON A.Field4 = B.Field5

Another possiblity is to use OpenQuery to access the linked server data. One advantage of OpenQuery is that the query runs on the linked server and this will often improve performance.

SELECT A.Field1, A.Field2, B.Field3
FROM database1.dbo.Table1 A
INNER JOIN OpenQuery(linkedserver, 'Select Field3, Field5 From database2.dbo.Table2') B
ON A.Field4 = B.Field5 Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin