Solved

Running SQL Stored procedure against a linked server

Posted on 2004-03-29
4
249 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
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

Independent Software Vendors: 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

Suggested Solutions

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

713 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