Solved

Running SQL Stored procedure against a linked server

Posted on 2004-03-29
4
251 Views
Last Modified: 2010-05-18
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
Comment
Question by:StevenK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Accepted Solution

by:
eric_duncan earned 50 total points
ID: 10703588
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
 
LVL 15

Assisted Solution

by:SRigney
SRigney earned 50 total points
ID: 10704793
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question