SQLTeam.com | Weblogs | Forums

Strangest thing... for little old me anyway

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!

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

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

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

cn1.Execute
Is that vb.net or what?

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

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?

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?

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.

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

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!!!

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