server Update Error

Hi,
I have a linked server that is having problems when I run an Update query
against it.
I have a stored procedure in ServerA that updates records on my linked
server, ServerB.
This stored procedure accepts 2 pieces of criteria and applies the Update to
seven tables in the procedure.
A representative line would be:
UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I pass in the two parameters and lets say for @.ImportID I pass in the value
410, when the Procedure is run, it will set the ImportID field to a
different number.
The different number almost always seems to be a number that was ONCE in
this field for a since deleted record.
This behavior is identical in the 7 different tables in this stored
procedure. It happens when I use a .NET Command object (OLEDBCommand in this
case) as well as
when I run the procedure through the Query Analyzer. The only time it does
not happen is when I run a local version of the stored procedure on the
linked server (ServerB) itself:
UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I have restarted my servers, shrunk the database, inspected and re-inspected
my Command objects parameters and can not get this odd behavior to go away.
I should note that no errors are returned and all my Select and Delete
queries seem to work fine. But it seems like old values are being restored.
Any ideas?Hi,
Both the Linked Server and the server that calls the linked server are XP
Pro with SQL2k.
As for an error message, there is none. The proc completes without error,
even in QA but when you inspect the results in the tables, you see that the
incorrect values were applied.
"Pat Brisbine" <PatBrisbine@.discussions.microsoft.com> wrote in message
news:0C3AE65A-C357-4CB4-820E-C38A7311CD8F@.microsoft.com...
> What operating system are you running on? What is the exact error message
> that you are getting?
> "Walter" wrote:
> > Hi,
> > I have a linked server that is having problems when I run an Update
query
> > against it.
> >
> > I have a stored procedure in ServerA that updates records on my linked
> > server, ServerB.
> > This stored procedure accepts 2 pieces of criteria and applies the
Update to
> > seven tables in the procedure.
> >
> > A representative line would be:
> >
> > UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> > ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> >
> > I pass in the two parameters and lets say for @.ImportID I pass in the
value
> > 410, when the Procedure is run, it will set the ImportID field to a
> > different number.
> > The different number almost always seems to be a number that was ONCE in
> > this field for a since deleted record.
> >
> > This behavior is identical in the 7 different tables in this stored
> > procedure. It happens when I use a .NET Command object (OLEDBCommand in
this
> > case) as well as
> > when I run the procedure through the Query Analyzer. The only time it
does
> > not happen is when I run a local version of the stored procedure on the
> > linked server (ServerB) itself:
> >
> > UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND
BILLG_INVC_ID=> > @.BillingInvoice
> >
> > I have restarted my servers, shrunk the database, inspected and
re-inspected
> > my Command objects parameters and can not get this odd behavior to go
away.
> > I should note that no errors are returned and all my Select and Delete
> > queries seem to work fine. But it seems like old values are being
restored.
> >
> > Any ideas?
> >
> >
> >|||> The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
So even when you run the stored proc through query analyzer on ServerA you
do not get the expected results?
It is very strage that you only get the expected results on ServerB. Have
you tried debugging in query analyzer or putting multiple print statements in
to track the values through the life of the procedure?
If you could post the entire stored proc it might help.
"Walter" wrote:
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?
>
>|||Walter,
Could this article help you?
http://support.microsoft.com/default.aspx?scid=kb;en-us;293328
BTW, It says "This problem was first corrected in SQL Server 2000 Service Pack 1"
HTH,
Igor
"Walter" <never@.mind.com> wrote in message news:<OGDykJWhEHA.704@.TK2MSFTNGP12.phx.gbl>...
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?