Inject timeout BUT it's success

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

My program uses DAAB to inject data to SQLServer using ExecNonQuery() with
stored procedure. In normal case, it works property. But in some situations
such as database backup process, I got timeout error and my program will
retry
to inject until success. In this week, I've found that my program inject
data in
backup period and it returns duplicate row after backup done. I think it
will
inject success but it returns timeout error.

Do you have any suggestion to check this, because my program has to notify
another program to process this data after injecting success.

Best regards,
Thana N.
 
Hi Thana,

What do you have inside of the SP? IS it just one INSERT statement? Possible
solution to this problem is to do insert inside of the transaction. Open
transaction in a application (not in a SP), execute query and if you get any
kind of error, then just rollback insert. Using this strategy will ensure
that data will be rolled back even in a situation you have.
 
Hi Val Mazur,

The SP contains 1 insert statement within begin transaction and commit
statement.
And thanks for your reply. Anyway, what about performance on your solution
compare with SP way?

Thanks,
Thana N.
 
Hi,

I do not think you will see any performance difference in this specific
case, but it should help resolve the issue. It looks like provider generates
a timeout error when SP execution time is very close to the timeout.
--
Val Mazur
Microsoft MVP

http://xport.mvps.org
 
Hi,

I chose SP because SP will be compiled to execution plan on server, so this
sould be faster than another. The other is sucurity issue for SP. And if I
change my program as your suggestion, it will affect a program design. So
I'm looking for another solutions to make a decision.

Thanks,
Thana N.
 
Hi,

I am not suggesting to move SQL statement outside of the SP. All you need to
do is to open a transaction outside of SP. Rest of the code will be the
same.

--
Val Mazur
Microsoft MVP

http://xport.mvps.org
 
Back
Top