June 30, 2009 at 10:35 am
Dear all,
I am trying to fetch data from a remote server via a remote stored procedure call and everything is fine until I try to store the result anywhere...
If I try something like this, it's fine
EXEC LinkedServer.Db.Schema.MyProc @parm1 = ...
Unfortunately, if I try to store that result into a temporary table, there is a DTC kicking in and my servers are not configured for that (and I'd rather not).
INSERT INTO #MyTable(col1, col2,...)
EXEC LinkedServer.Db.Schema.MyProc @parm1 = ...
On my 2008 development server, I got away by setting the linked server property "Enable Promotion of Distributed Transactions" to false but my production server is 2005 and does not have this option... Damn!
In one case, I managed to fool the thing by calling OPENQUERY within a Dynamic sql but I could not specify any param in the OpenQuery so I needed to have one RPC per possible parameter.
Not great but I got away with it.
This time, my Remote Procedure is more complex and need to use temporary tables itself and OpenQuery does not seem to accepts this...
I suspect it fails some parsing on the remote server.
I feel like banging my head on a wall... :angry:
Can anyone think of a solution to this problem... or should I really bang my head on that wall?
Many Thanks
Eric
June 30, 2009 at 12:35 pm
Are you getting out a single row of data or multiples? If a single row then I think you can use OUTPUT parameters.
On the remote side you might hae it write into a local table there and then read that table, then clean it, but I'm not sure about your concurrency situation..
Can I ask what the issue with using DTC is?
CEWII
June 30, 2009 at 2:47 pm
several rows.
Funny enough, I thought about writing to a local table too.
In theory there should be only one such request at a time but I could return some kind of key through the procedure return status.
Then I can fetch against that key in the table.
I think I'll do that!
Many Thanks
PS: I forgot to answer your other question...
What's wrong with DTC...
I work as a contractor with people who don't always know what they are doing and I think it's a lot safer that way with DTC not working because they would abuse this. Last week, I identified a remote query returning 470 million rows (!) and I dread to think what they might do if we had DTC on...
To make things worse, we also use the same server for Development, PreProd and Production (...) so I am not to keen to play with the server settings...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply