We have developped a Delphi 3.0 (maintenance packs installed) application
which uses cached updates to access a MS SQL 6.5 (service pack 4 installed)
database.
The application can insert a new record that has all kinds of fields
including a blob. Another field called ItemID (int) is set to 0. The table
on the server has a trigger that will create a unique ID and update the
ItemID field. Everything works fine on our server, but when we try to run
it against a server located at one of our customers we get the error
"Record/Key" deleted when we insert a record that has a blob that is not
empty. If the blob is empty the problem does not occure.
We have used SQL monitor to log the sql commands between the program and
the server and we have noticed the following important differences:
1) It seems that our server is storing both the "normal" and the blob data
in a single statement (our server - line 27). The other server seems to
require more statements (customer server - lines 36 to 67).
2) As far as we can see it is line 67 (customer server) that causes the
problem. It is trying to select a record with an ItemID value that matches
the value originally supplied to the SQL server. However this value is
modified by the trigger and cannot be found by SQL server. This is why we
get the Record/Key deleted error.
Important notes:
-----------------------
As far as we can see the SQL software and NT Server software on both
servers is identical.
We have connected to both servers from the same Workstation using the same
program, BDE, IDAPI, etc...
Our Workstation is connected to our server with a network (TCP/IP) and we
connecting to our customer using a RAS connection.
Is this a timing problem? Can this be a server property? Can we solve this
using another trigger or stored procedure?
All suggestions are welcome!!!
Kind regards
Alain Sienaert
e-mail: sienaert.cor...@skynet.be
Our Server
========
16 17:16:04 SQL Prepare: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
17 17:16:04 SQL Blob IO: MSSQL - Return blob data
18 17:16:04 SQL Data In: MSSQL - Param = 1, Name = ItemID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
19 17:16:04 SQL Data In: MSSQL - Param = 2, Name = Number, Type =
fldZSTRING, Precision = 25, Scale = 0, Data = CAFD11
20 17:16:04 SQL Data In: MSSQL - Param = 3, Name = Title, Type =
fldZSTRING, Precision = 80, Scale = 0, Data = Test
21 17:16:04 SQL Data In: MSSQL - Param = 4, Name = Status, Type =
fldZSTRING, Precision = 20, Scale = 0, Data = Pending
22 17:16:04 SQL Data In: MSSQL - Param = 5, Name = ProjectLeaderID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000002
23 17:16:04 SQL Data In: MSSQL - Param = 7, Name = BusinessSegmentID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000012
24 17:16:04 SQL Data In: MSSQL - Param = 8, Name = Archive, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
25 17:16:04 SQL Data In: MSSQL - Param = 9, Name = LastUpdate, Type =
fldTIMESTAMP, Precision = 0, Scale = 0, Data = 1/14/1998 17:15:59:3000000
26 17:16:04 SQL Data In: MSSQL - Param = 10, Name = ServerID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 1
27 17:16:04 SQL Execute: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
54 17:16:04 SQL Stmt: MSSQL - Close
55 17:16:04 SQL Transact: MSSQL - XACT Commit
56 17:16:04 SQL Prepare: MSSQL - COMMIT TRAN
57 17:16:04 SQL Execute: MSSQL - COMMIT TRAN
64 17:16:04 SQL Stmt: MSSQL - Close
65 17:16:04 SQL Stmt: MSSQL - Close
Customer Server
===============
16 15:00:59 SQL Prepare: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
17 15:00:59 SQL Data In: MSSQL - Param = 1, Name = ItemID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
18 15:00:59 SQL Data In: MSSQL - Param = 2, Name = Number, Type =
fldZSTRING, Precision = 25, Scale = 0, Data = CAFDTN16
19 15:00:59 SQL Data In: MSSQL - Param = 3, Name = Title, Type =
fldZSTRING, Precision = 80, Scale = 0, Data = Test
20 15:00:59 SQL Data In: MSSQL - Param = 4, Name = Status, Type =
fldZSTRING, Precision = 20, Scale = 0, Data = Pending
21 15:00:59 SQL Data In: MSSQL - Param = 5, Name = ProjectLeaderID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000001
22 15:00:59 SQL Data In: MSSQL - Param = 7, Name = BusinessSegmentID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000012
23 15:00:59 SQL Data In: MSSQL - Param = 8, Name = Archive, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
24 15:00:59 SQL Data In: MSSQL - Param = 9, Name = LastUpdate, Type =
fldTIMESTAMP, Precision = 0, Scale = 0, Data = 1/14/1998 15:0:40:693000000
25 15:00:59 SQL Data In: MSSQL - Param = 10, Name = ServerID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 2
26 15:00:59 SQL Transact: MSSQL - XACT Begin
27 15:00:59 SQL Prepare: MSSQL - SAVE TRAN BODAPI
28 15:00:59 SQL Execute: MSSQL - SAVE TRAN BODAPI
35 15:00:59 SQL Stmt: MSSQL - Close
36 15:00:59 SQL Execute: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
63 15:01:00 SQL Stmt: MSSQL - Close
64 15:01:00 SQL Prepare: MSSQL - SELECT TeamMembers FROM projects
WHERE ItemID=:1
65 15:01:00 SQL Data In: MSSQL - Param = 1, Name = ItemID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
66 15:01:00 SQL Misc: MSSQL - Set statement type
67 15:01:00 SQL Execute: MSSQL - SELECT TeamMembers FROM projects
WHERE ItemID=:1
74 15:01:00 SQL Blob IO: MSSQL - Return blob data
75 15:01:00 SQL Stmt: MSSQL - Fetch
78 15:01:00 SQL Stmt: MSSQL - EOF
79 15:01:00 SQL Stmt: MSSQL - Close
80 15:01:00 SQL Blob IO: MSSQL - Start Store
81 15:01:00 SQL Prepare: MSSQL - ROLLBACK TRAN BODAPI
82 15:01:00 SQL Execute: MSSQL - ROLLBACK TRAN BODAPI
89 15:01:00 SQL Stmt: MSSQL - Close
