I'm using a settings table to configure on which servers a specific kind of data is stored, and I would like to do this as dynamically as possible without having to add the server as a linked server because these settings can be changed by superusers in an application.

create procedure dbo.getData
    declare @server nvarchar(50) = dbo.getSetting('DATA_SERVER')
         , @user nvarchar(50) = dbo.getSetting('DATA_USER')
         , @password nvarchar(50) = dbo.getSetting('DATA_PASSWORD')
         , @database nvarchar(50) = dbo.getSetting('DATA_DATABASE')

    -- some how connect to the server and make a select on the datatable

Is this possible, or what are my alternatives in doing so?

  • In reality the scenario isn't as simple as described, this is just the over simplification of a larger setup, lets just stick to it that the dynamic stuff needs to be in this procedure ;) – Staeff May 20 '14 at 12:52
  • I think I've hat a kind of good idea for this problem, use a CLR procedure with parameters sqltext, server, user password, db and let the CLR handle the connection, what are you thinking about that? – Staeff May 20 '14 at 12:58
  • 3
    Why a CLR procedure? What is triggering the CLR procedure to be called in the first place? A web page? Some C# application? Why don't you put your logic in the application, which already has the capability to build connection strings quite easily? – Aaron Bertrand May 20 '14 at 13:12
  • The CLR is triggered by this procedure to load data from another server defined in the settings table. This procedure will be called by another CLR, but the settings have to be dynamic and this procedure should be easily changable by the admin of our customer, this is the reason why we didn't put the whole logic in the CLR. Also I see no reason in putting in further logic, because the environment for this won't change on our behalves, the requirement is to deliver the data from this procedure. – Staeff May 21 '14 at 7:16

Your Answer

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

Browse other questions tagged or ask your own question.