Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Running SQL Stored procedure against a linked server

Posted on 2004-03-29
4
Medium Priority
?
254 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 200 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 200 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 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