CODEWITHSUNDEEP
asp.netsql-serversql-server-2008t-sql
James123
James123

Reputation: 11652

Dynamic stored procedure in different SQL servers?

My client wants to change servers from the application, I mean enter new SQL servers name from application and search keywords in the SQL server database tables. I have Stored Producer which is working fine for one SQL server.

If client changes SQL server name in the application level then My connection string in the application will not work. Because database in the connection string that I used is available for one server.

How can I handle this situation? . Is there anyway sp that will be dynamic? P

Upvotes: 1

Views: 216

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280449

Well in one server you could create a synonym that points at the other server. Assuming you have linked servers, on Server1:

CREATE SYNONYM dbo.ProcedureName FOR Server2_Linked.DatabaseName.dbo.ProcedureName;

Now when the app calls dbo.ProcedureName in Server1, unbeknownst to it, it is actually executing on Server2.

If this isn't what you're after I think you'll have to elaborate in your question a bit.

Upvotes: 1

Related Questions