Error using Linked-Server (query still returns resultset )

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DCPS - 08 Jul 2008 16:43 GMT

I am having an issue when using Linked-Server. When running a query
from ServerA (sql 2000) pulling from ServerB(sql 2000) and dump the
results into a temp table I don't get any errors. The issue is that
when run on ServerB I get a conversion error. So, what appears to be
happening is SQL is processing the resultset and throwing it over to
ServerA as soon as it encounters the error. Without researching I
would have thought it completed successfully. The problem is that I
didn't receive all the records on ServerA.

Is there a reason it didn't roll back the whole transaction when an
error was reached? A setting somewhere? I checked all settings on
linked-server and server sides (sp_configure) and don't see an issue.
I've never seen SQL just sent all the data up to an error and then
complete.

Any help would be great. I did google this but didn't find any posts
like this so I thought I would give it a try.

Thanks in advance,
CT

Reply to this Message

info@onlyhd.tv - 09 Jul 2008 17:46 GMT

Do you have Distributed Transaction Co-coordinator enabled on both
ends? Also, I googled "linked server transaction" and got some good
results, I don't have your specific error so I don't know which if any
article will help.

Good luck

Erik

Reply to this Message

DCPS - 09 Jul 2008 18:44 GMT

On Jul 9, 12:46 pm, i...@onlyhd.tv wrote:

> Do you have Distributed Transaction Co-coordinator enabled on both
> ends? Also, I googled "linked server transaction" and got some good
[quoted text clipped - 4 lines]
>
> Erik

Thanks Erik,

Yes both MS DTC are up and running. That's the thing is I never
receive an error. SQL just returns what results it has at the time of
the error and never specifies that there was an error. My concern is
only that if anyone was to make this a process and the process
continue to return a result set even though an error occurred it could
cause some issues. My only guess is that the error occurred on the
remote server and because the error was out of scope it was never seen
or reporting to the server running the script through the linked
server.

IE:
**serverA is a linked server
** this statement is executed on serverB
** when run on ServerA it does show the conversion error

SELECT STUID
INTO tmpStuID
FROM serverA.DB.dbo.TABLE1
WHERE CONVERT(NUMERIC, STUID) = 111

* The actual error occurs on the converstion of the varchar to
numeric. No big deal, there was an invalid character in STUID. The
catch is that instead of sending the error to ServerB and stopping the
tmpStuID from getting created, it went ahead and created the table
with records up to the one that produced the error. So... in my
tmpStuID table I only had 28,000 instead of 100,000 that I was
expecting. The tmpStuID is basically a working table used for other
purposes so this discrepency could cause some issues.

I've used linked servers quite a bit and never realized that it would
encounter an error, end process and return what ever it had processed
up to that point. I thought that was odd.

Reply to this Message

Mano Nymous - 30 Jul 2008 12:24 GMT

Did you ever find out a solution for this problem?

Reply to this Message