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
Solved

Running SQL Stored procedure against a linked server

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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