2

I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version). I have no control on server C, and I cannot contact its DBA. On server B, I can contact the DBA and ask for a view on server C, example:

Create view [dbo].[View_Stuff] as 
select * from 
openquery (SERVER_C, 'SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x')

I would like to do things like:

SELECT * FROM View_Stuff WHERE mykey=27

This view gets a lot of data from server C, because the openquery gets all the content of the table, which is not acceptable. On the other hand, I know that this would extract only one row, and would be very efficient on server C:

SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27

I considered using indexed views, but they cannot work with linked servers. Mirroring the data is possible, but I really want real time information.

What can I ask to define on server B to be able to get data efficiently and in real time from a query on server A?

  • Linked servers are never ideal for performance. Is there the possibility of using log shipping or always on to get a fairly recent local copy of the data? – James Anderson Oct 28 '14 at 11:39
  • You are right about performance, those are not options. Believe me, it's annoying :-) – carlo.borreo Oct 28 '14 at 11:41
  • I am not even sure that server C is Microsoft SQL Server, it might be Oracle. – carlo.borreo Oct 28 '14 at 11:46
  • You mention mirroring is possible. You could go down the route of mirroring the databases to your server and then using snapshots to read the data but it can be a bit of work to automate that. I expect the linked servers are your only option. What is the query for? – James Anderson Oct 28 '14 at 11:47
  • I wrote a simple PHP script that reads the list of views on server B, and for each of them it creates an equivalent table on my server A, then copies the data. This is what I call mirroring. Problem is, the data transfer takes hours, and I want to access information in real time. – carlo.borreo Oct 28 '14 at 11:52
0

You can use dynamic SQL to run a query remotely, and return the output of the query to the intermediate server, and finally the calling server, along the lines of:

DECLARE @cmd nvarchar(max);
SET @cmd = 'DECLARE @cmd nvarchar(max);
SET @cmd = ''CREATE TABLE #t (name sysname);
INSERT INTO #t (name)
EXEC tempdb.sys.sp_executesql N''''SELECT o.name 
FROM sys.objects o 
WHERE o.object_id > 0'''';
SELECT *
FROM #t;'';
EXEC [SERVER_B].tempdb.sys.sp_executesql @cmd;'
EXEC [SERVER_A].tempdb.sys.sp_executesql @cmd;

I know it's not exactly elegant, but in a pinch it will do what you want far more optimally than querying a view over a linked server double-hop.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.