I've been working on this for about a day and a half, and I've looked through a lot of blogs and online how-to guides. On SO, I came across a number of queries pertaining to this mistake, but I didn't think they were exactly applicable to my circumstances (or, sadly, in some cases, I couldn't grasp them clearly enough to implement:P). I'm not sure if I can explain this clearly enough to be of assistance, but I'll try:
To track our resources, we have a.NET application. A stored procedure that connects to the time and billing databases is accessed by the export function, which copies a resource to the time tracking system and the billing system.
The billing system database was just transferred to a new server by me (original server: Server 2003 SP2, SQL 2005; new server: Server 2008 R2, SQL 2008 R2). I've set up a Linked Server that directs traffic to the 2008 databases. A message concerning MSDTC and RPC appeared after I altered the stored procedure to refer to the 2008 server (
http://www.safnet.com/writing/tech/archives/2007/06/server myserver.html). I set MSDTC to allow Network Access and enabled "rpc/rpc out" on the Linked Server (similar to this:
http://www.sqlwebpedia.com/content/msdtc-troubleshooting).
When I try to use the export function, I now get the following message: "This SqlTransaction has completed; it is no longer useable." What puzzles me is that when I simply run the stored procedure (via SSMS), it reports a successful completion.
Has anyone ever witnessed this? Has the configuration been configured incorrectly? The only thing I discovered after repeatedly reading the same pages is that I didn't reboot after making the MSDTC adjustments (discussed here:
http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7172223f-acbe-4472-8cdf-feec80fd2e64/).
If posting the entire stored method will be helpful, please let me know.
