Sql-server – SQL server 2000 transactions with the same time

sql serversql-server-2000timestamp

I have a BIG problem here.
I'm creating a program that can be looping if the connection to the server is broken.
But when the program is running for a few months, I found a bug on SQL server 2000.

I found some data that had more than one transaction with the same date and time.

Is it possible that sql2k server has a transaction with the same date and time in one table?

PS : I use getdate to fill that field.

Hope there's explanation and solution for my problem.

Best Answer

It won't be a bug: it's a flaw in your logic.

If you're looping, then it's possible that you are opening transactions on top of another transactions. That is, @@TRANCOUNT keeps incrementing. You may be incorrectly looping on say, a client timeout, that doesn't actually disconnect.

In your loop, in client code, you should test for an open transaction and rollback. Or use SET XACT_ABORT ON to force full rollbacks. See https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 for more

It's hard to be more precise at this stage.

Related Solutions

Sql-server – SQL Server 2000 : List and Disable all triggers

SQL Server 2000 has ALTER TABLE foo ENABLE TRIGGER ALL too

And to generate the required ALTER TABLE script use this

SELECT
    DISTINCT 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' /*DISABLE*/ ENABLE TRIGGER ALL'
FROM
    sysobjects
WHERE
    xtype = 'TR'

I wouldn't bother testing status of the triggers, personally. A disabled trigger should not exist IMHO. Not least because OBJECTPROPERTY does not support it in SQL Server 2000

Edit:

My mistake, OBJECTPROPERTY in SQL Server 2000 has ExecIsTriggerDisabled as per comment. So you can track status before and after

Sql-server – SQL Server 2000: Can’t DROP VIEW

Run SQL Profiler to identify who, where etc. You have various columns with hostname, ntusername etc that will allow you to see usage.

For example: is if just one client or user who notices? Are they running a different ADP?

The view call can be embedded in some Form properties too as the RecordSource for that form, or as the source for some control. It may not be in VBA. You'd have to write some VBA to iterate through the forms, controls and reports testing assorted properties.

Best Answer

Related Solutions

Sql-server – SQL Server 2000 : List and Disable all triggers

Sql-server – SQL Server 2000: Can’t DROP VIEW

Related Question