0

In order to make single interface for app getting data from different datasources I created one stored procedure where I try to keep all data access/handling routines. Current situation:

  • Sql Server sp receives fixed number of parameters and should return recordsets taken from remote servers;
  • remote servers represented as Linked Servers based on Sql Sqrver Express both sides;
  • some Linked Servers are just connection points lead to local OLE DB file datasources (others have db on Sql Server itself);
  • remote data structures vary a lot and for each Linked Server should be kept special list of queries with variables handling;

Example of query: 

select * from OPENQUERY(linkedA,
'select f1 as id,f2 as name
from  openrowset(''VFPOLEDB'',''c:\program\db\Table.FPT'';'''';
'''',''SELECT *  FROM Table  where f1=1'')')

When source is Sql Server based: 

select * from OPENQUERY(linkedB,
'select f3 as id,f4 as name from [db].[dbo].[Table] where f3=1')

As I need to handle not only connection properties (linked server name) but query parameters (db, scheme) and variables (declared and existing in local sp runtime) I have some problems:

  1. Making default queries is a mess - parameters and variables parsing makes code unreadable;
  2. Because of handling variables I possibly need to turn to sp_executesql to simplify the process;
  3. Not sure about the way of keeping query codes - should it be in config_table as xml or smth.
<Config id="1" name="linkedB" db="dblinkedB" scheme="dbo" version="1.0.1">
  <Actions>
    <Action id="0" name="Query 1">select * from OPENQUERY(linkedB,'select f3 as id,f4 as name from [db].[dbo].[Table] where f3=1')')</Action>
  </Actions>
</Config>

Question: Is there any bestpractices available?
According to subject of this topic I am looking for best way to organize query templates just passing them some parameters with possibility to use default or special code.

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
revoua
  • 2,044
  • 1
  • 21
  • 28
  • 1
    My initial reaction is... don't do application logic in the database. Why implement a data access layer in the SQL server to abstract away different implementations of the interface? Just create a specialized implementation for each data source. Linked server stuff is likely going to perform worse than directly interfacing with the server. – John Sobolewski May 29 '13 at 16:50
  • It would be great if I can connect to remote Sql Server from T-SQL but [it is possible only client side](http://stackoverflow.com/q/16777001/698082) ( – revoua May 29 '13 at 16:56

0 Answers0