• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Running SQL Stored procedure against a linked server

We're developing an application using Visual Studio .NET (C#) and are working against a SQL Server database.
One of our stored procedures makes use of a dynamic SQL cursor.
In one instance we are working with data in one of the tables in the current database (where the connection is running) and in another we are accessing data from a linked server - i.e. SELECT -- FROM [SERVER2].Database1.dbo.Table1
When we try and execute the stored procedure through C# code we get errors indicating that 'MSDTC on server SERVER1 is not available'.
The code fails to complete.
What are we missing ?  (to allow the distributed query to run).
Any help would be appreciated.
Thanks,
Steve
0
StevenK
Asked:
StevenK
2 Solutions
 
eric_duncanCommented:
Does the distributed query work when you run it in the Sql Query Analyzer? If not, check to make sure that the MS DTC service is running on both sql server machines - check SERVER1 first because that's where you are getting the error. The following like may be helpful in terms of starting/stopping the MS DTC service:

http://www.microsoft.com/windows2000/en/server/help/default.asp?url=/windows2000/en/server/help/addtctsk_6dib.htm

You might also need to verify your DTS configuration. Check out the following link for information pertaining to security, network access, etc. it talks about using Oracle as the database (go figure), but the information is good anyway:

http://www.microsoft.com/windows2000/en/server/help/default.asp?url=/windows2000/en/server/help/addtccpt_0wfi.htm

If it does work in the query analyzer, but doesn't work in code, check your security settings.

Hope this helps!

Eric
0
 
SRigneyCommented:
In order for the query to run on the linked server you will need to have the user that is runningt the C# app have the correct select, update permissions on the linked server.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now