Post reply

Update of Table via Remote View may generate wrong Field Entries in MSSQL 2000 SP 4

  • May 17, 2006 at 6:27 am

    #91282

    hi guys, all comments welcome!

    I am quite sure, that I observe this behaviour only with SP 4 not with SP 3a instances of SQL Server

    Server A launches this query into a remote view [System] targetting a table [tSystem] on Server B (this query is included in a stored procedure, if run from e.g. SQL Query Analyzer ad hoc, the bug is not observed)

    --.... (within stored procedure)

    DECLARE @LabID int, @SystemID int

    SET @SystemID = 11

    SET @LabID = 6

    UPDATE [System]

    SET LabID = @LabID

    WHERE [ID] = @SystemID

    AND LabID <> @LabID

    --....

    When tracing Server B with all products in Service Pack 3a, the following call is correctly generated and executed across the standard SQL-Server "Linked Server" connection:

    exec sp_prepexec @P1 output,

    N'@P1 int,@P2 int,@P3 int',

    N'UPDATE "tsn_remos0"."dbo"."tsystem" SET LabID = @P1 FROM "tsn_remos0"."dbo"."tsystem" Tbl1001 WHERE Tbl1001."ID"=@P2 AND Tbl1001."LabID"<>@P3',

    6, 11, 6

    ...cool !

     

     

    When tracing Server B with all products in Service Pack 4, the following call with only 2 dynamic parameters and an evaluated parameter is generated by SQL-Server and is executed:

    exec sp_prepexec @P1 output,

    N'@P1 int,@P2 int', N'UPDATE "tsn_remos0"."dbo"."tsystem" SET LabID = (6) FROM "tsn_remos0"."dbo"."tsystem" Tbl1001 WHERE Tbl1001."ID"=@P1 AND Tbl1001."LabID"<>@P2',

    11, 6

    This looks more or less acceptable, HOWEVER, next call of same update with different parameters (@SystemID=99, @LabID=27) will arrive such:

    exec sp_prepexec @P1 output,

    N'@P1 int,@P2 int', N'UPDATE "tsn_remos0"."dbo"."tsystem" SET LabID = (6) FROM "tsn_remos0"."dbo"."tsystem" Tbl1001 WHERE Tbl1001."ID"=@P1 AND Tbl1001."LabID"<>@P2',

    99

    , 27

    Database row for SystemID=99 will have faulty LabID=6 assigned

    And poor database user sees his data slowly and hideously twisting away :-((

  • May 18, 2006 at 11:37 am

    #638822

    What hotfix on SP4 are you using?

    Tim S

    Edit: I am using build 2187

    http://support.microsoft.com/kb/916287/

    I just wonder if I have the problem.

  • May 19, 2006 at 1:43 am

    #638919

    it's put up by our infrastructure folks, so I am not quite sure --- Enterprise Mgr shows properties either as 8.00.2039 (SP4) or 8.00.194 (RTM); to my knowledge, no hotfixes are applied; b.t.w. MS promised to open a case in this matter

    cheers, Markus

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply