asked on

Linked server stored procedure

Hi

I have to call stored procedure on remote server using Linked server(SQL). stored procedure expects 10 parameters to be passed. It also raise exception if something goes wrong. Would it be good practice to call linked server stored procedure directly from C# code or i should have stored procedure on local server sql that in turn calls linked server stored procedure. Can somebody suggest how to do it? i.e syntax and snippet

Thanks

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vivekkumarSharma

You can call it in either way.

But
I think you should have stored procedure on local SQL server that in turn calls linked server stored procedure.
If you call multiple servers from your program, It will leads to confusion and will create extra efforts to maintain multiple connection string.

Christopher Gordon

If I understand your question,

If you have appropriate permissions, I would create an additional connectionstring in the web.config file to call the stored procedure to the additional SQL Server box directly. Just wrap that call in a standard Try Catch block in your C# code.

lludden

If possible, call the server directly. A linked server just adds unnecessary complexity.

BAKADY

This works for me,... let me now if you have problem running this:

EXEC yourDatabaseAtLinkedServer.dbo.yourStoredProcedure 'parametersIfRequired', 1, 2, 3, ...
AT YourLinkedServer
GO

Open in new window

http://msdn.microsoft.com/de-de/library/ms188332.aspx