I am running a web service which connects to a SQL Server 2012 database. When executing multiple delete queries within a short space of time (around 5 in a second), it appears random which ones are actually executed. When ran one at a time or putting a 0.5 second delay between each execution, they run perfectly.
Looking at the SQL profiler, all the query are showing as RPC:Completed even if they haven't actually deleted the row in the table. I checked the table and the data was still there, then copied and pasted the query from the Profiler into SSMS and that affected one row and delete it.
So I assume the web service is working fine and the problem is on the database side of it. Is there a way in the profiler to view if the query was a success? And what could be causing this to actually not affect the row?
No triggers. Running the same query just with different parameters. Only the data is changing usually sequentially. No other queries are running on the database just now.
I've added client site logging, it does actually return 1 if it deletes the row and 0 if it doesn't delete the row. However even when it comes up as 0, the Profiler shows it has ran the query but doesn't seem to have affected it. And when I run the query through SSMS, it does affect the row.
Not receiving any errors from the web service and the query runs fine through SSMS. Only seems to not delete when it is ran multiple times in quick succession. I do agree it is most likely targeting different rows but can't see how that happens when it runs okay in SSMS.
table structure and query
CREATE TABLE dbo.ContractDates2HumanAssets
(
iContractDate2HumanAssetID int IDENTITY(1,1) NOT NULL,
iContractDateID int NOT NULL,
iHumanAssetID int NOT NULL,
cCategory nvarchar(256) NOT NULL,
cHR_x0020_ID nvarchar(256) NOT NULL,
cCD_x0020_ID nvarchar(256) NOT NULL,
CONSTRAINT PK_ContractDates2HumanAssets PRIMARY KEY CLUSTERED
(
iContractDate2HumanAssetID ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE TABLE dbo.ContractDates
(
iContractDateID int IDENTITY(1,1) NOT NULL,
iContractID int NOT NULL,
dDate datetime NOT NULL,
cResource nvarchar(256) NOT NULL,
cCD_x0020_ID nvarchar(256) NOT NULL,
CONSTRAINT PK_ContractDates PRIMARY KEY CLUSTERED
(
iContractDateID ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
exec sp_executesql N'DELETE FROM ContractDates2HumanAssets
WHERE iContractDateID IN ((SELECT ContractDates.iContractDateID
FROM ContractDates2HumanAssets
INNER JOIN ContractDates ON ContractDates.iContractDateID = ContractDates2HumanAssets.iContractDateID
WHERE iHumanAssetID = @humanid AND iContractID = (SELECT iContractID FROM Contracts WHERE cContractNo = @id) AND dDate = @newDate))',N'@id nvarchar(6),@newDate nvarchar(10),@humanid int',@id=N'999111',@newDate=N'2018-03-16',@humanid=82
That is the query which is appearing in the profiler. After checking the table that row is still there. But copying and pasting that into SSMS and executing it, it comes back with 1 row affected and is deleted.
Table data
SELECT *
FROM ContractDates2HumanAssets
WHERE iContractDateID IN
(
(
SELECT ContractDates.iContractDateID
FROM ContractDates2HumanAssets
INNER JOIN ContractDates
ON ContractDates.iContractDateID = ContractDates2HumanAssets.iContractDateID
WHERE
iHumanAssetID = 82
AND iContractID =
(
SELECT iContractID
FROM Contracts
WHERE cContractNo = N'999111'
)
)
);
The results:
╔════════════════════════════╦═════════════════╦═══════════════╦═══════════╦══════════════╦══════════════╗ ║ iContractDate2HumanAssetID ║ iContractDateID ║ iHumanAssetID ║ cCategory ║ cHR_x0020_ID ║ cCD_x0020_ID ║ ╠════════════════════════════╬═════════════════╬═══════════════╬═══════════╬══════════════╬══════════════╣ ║ 102538 ║ 113369 ║ 82 ║ ║ ║ ║ ║ 102539 ║ 113370 ║ 82 ║ ║ ║ ║ ║ 102540 ║ 113371 ║ 82 ║ ║ ║ ║ ║ 102541 ║ 113372 ║ 82 ║ ║ ║ ║ ║ 102542 ║ 113373 ║ 82 ║ ║ ║ ║ ║ 102543 ║ 113374 ║ 82 ║ ║ ║ ║ ║ 102544 ║ 113375 ║ 82 ║ ║ ║ ║ ║ 102545 ║ 113376 ║ 82 ║ ║ ║ ║ ║ 102546 ║ 113377 ║ 82 ║ ║ ║ ║ ║ 102547 ║ 113378 ║ 82 ║ ║ ║ ║ ║ 102548 ║ 113379 ║ 82 ║ ║ ║ ║ ║ 102549 ║ 113380 ║ 82 ║ ║ ║ ║ ║ 102550 ║ 113381 ║ 82 ║ ║ ║ ║ ║ 102551 ║ 113382 ║ 82 ║ ║ ║ ║ ║ 102552 ║ 113383 ║ 82 ║ ║ ║ ║ ║ 102553 ║ 113384 ║ 82 ║ ║ ║ ║ ╚════════════════════════════╩═════════════════╩═══════════════╩═══════════╩══════════════╩══════════════╝
I ran that query to get all the relevant data, I can get the individual data per table if that is more helpful.
set dateformat YMD;at the beggining of your query string and give it a try – Josh Part Feb 16 '18 at 15:19@newDate='20180316'(remove theNand the dashes) in both SSMS and your application? Do you still get the same issue? – ypercubeᵀᴹ Feb 16 '18 at 18:14