paul walker

paul walker

  • NA
  • 30
  • 0

Running sql on different servers

Sep 21 2012 4:17 AM
Hi,
I have a .net application that runs SQL on a variety of different SQL servers.  At the moment it does this with a connection string and a hard-coded SQL statements.
This is very inflexible as it means a release of a DLL if the SQL has to change.  I would like to keep the SQL external to the application.
The obvious way of doing this is to deploy the SQL commands in stored procedures on each of the different servers (this avoids anything horrible like linked servers which our DBA's do not allow).  The trouble is that there are over 20 different servers and I really dont want to have to deploy a stored procedure to 20 different servers!!
Our DBA's have suggested an alternative....The application call a SQL agent job, that will in turn call a SSIS package that will run a SQL command, and because the SSIS package has its own configuration file where the target server can be specified, all the SSIS packages can remain on the one server.  There are two problem with this as I see it...the processing needs to run synchronously (ie the application should not continue after the SQL agent job has been started), and the resultset needs to be made available to the .net application.
I cant see how to do this.  If you have followed all this, can anyone provide any solutions?
Thanks
Paul