Strangest thing... for little old me anyway

Transact-SQL
1

On a SQL Server 2014, I send a single query which is supposed to insert 2 lines in Table1 of Database1 as well as 2 lines in Table2 of Database2 :

cn1.Execute "Insert Into DB1.DBO.Table1 (a,b) Values (1,1) " &
"Insert Into DB1.DBO.Table1 (a,b) Values (2,2) " &
"Insert Into DB2.DBO.Table2 (c,d) Values (1,1) " &
"Insert Into DB2.DBO.Table2 (c,d) Values (2,2) "

In the vast majority of cases, it works perfectly. But I have observed a few cases where the results end up being:

Table 1 a:1 b:1
Table 1 a:2 b:2
Table 2 c:2 d:2

In other words, out of the 2 inserts to Table2, the first one is completely skipped!!! What the heck?

Would anyone have any explanation for such behavior and what might be done to avoid it?

Thanks!

2

On table2, some type of trigger and/or an IGNORE_DUP_KEY index.

3

Please use a meaningful thread title. Your post was quite clear.

4

Nope. No triggers exists on the DB and IGNORE_DUP_KEY is not used. Insert simply failed without apparent reason between 2 other inserts.

5

cn1.Execute
Is that vb.net or what?

6

Has happened to me when a,b is unique constraint ignoring duplicate value

7

Mateen: Nope, no constraints in the table.

Yosiasz: Actually, it's an old VB6 application which hasn't been converted yet. Connection cursor is client-side. But would that make a difference in how the command is executed by the provider?

8

I tell old stories about vb6 to my grand kids :laughing:

so if it is vb6 it has been around a while, if so why suddenly this problem arises, what has changed?

9

Ah, but good stories they must be...! As long a MS supports COM, the larger applications are on the back-burner. Why do today what you can do tomorrow, right?

So anyway, this is what changed. They originally coded this:

cn1.Execute "Insert Into DB1.DBO.Table1 (a,b) Values (1,1) "
cn1.Execute "Insert Into DB1.DBO.Table1 (a,b) Values (2,2) "
cn2.Execute "Insert Into DB2.DBO.Table2 (c,d) Values (1,1) "
cn2.Execute "Insert Into DB2.DBO.Table2 (c,d) Values (2,2) "

and it was changed to:

cn1.Execute "Insert Into DB1.DBO.Table1 (a,b) Values (1,1) " &
"Insert Into DB1.DBO.Table1 (a,b) Values (2,2) " &
"Insert Into DB2.DBO.Table2 (c,d) Values (1,1) " &
"Insert Into DB2.DBO.Table2 (c,d) Values (2,2) "

Given that this bothers the server 4 times, the 4 execs were simply grouped into 1. In principle, it shouldn't make any difference w.r.t. the expected result.

10

it shouldn't => famous last words Why not use a stored procedure? actually you might have found a bug with cn1.execute

11

Ya, actually, that would be a very reasonable thing to do in order to remove a variable in the diagnostic process. Then, if the problem persists, we know that it's not computer-related or cursor-related. I normally never give any thought to stored procs unless it's worth pre-compiling a time-consuming query. That's why it's so great to have someone else ring in with a fresh mindset.

Thank you so much for your contribution, it was really appreciated!!!

12

yeah so create a type for your values(1,1)

CREATE TYPE [dbo].[budgetresourcesType] AS TABLE(
	[budgetid] [int] NOT NULL,
	[salarygraderoleid] [int] NOT NULL
)

then use that type in your stored procedure


ALTER proc [dbo].[budgetresources_iudp]    
(    
 @currentuser varchar(50),      
 @tvp budgetresourcesType readonly 
)    
as    
/*  
	 windchaser 9/3/2020	created
*/  
begin  
    INSERT INTO [dbo].budgetresources
			   (budgetid
			   ,salarygraderoleid
			   ,[createddate]
			   ,[createdby])
    select budgetid,
	       salarygraderoleid,
		   GETDATE(),
		   @currentuser
	  from @tvp
end    
GO

then call the proc from vb6

1 Like