Datasets and MS Sql (OpenInsight Specific) [Revelation On-Line Wiki]

Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community

At 08 JUN 1998 07:48:44PM Duane Pekse (Aldata) wrote:

OpenInsight Specific

We are trying to get our program switched over from an ODBC connection

to a native MS SQL Server connection. Here is our problem.

- We open the window. It is attached to a dataset. We have recompiled both the dataset and the window since we switched to

MS SQL

- The window opens fine, and will close again if we do that immediately. Once we read a record, however, we cannot close the window anymore. The DSOClear event will trigger, but then the computer locks up. The only way out of it is to reboot the computer.

We are using MS SQL Server v6.5 (with v6.0 16bit connection dlls)and OI v3.6. Does anyone know why this is happening? Can anyone help, please? Microsoft is winning again…
     Duane Pekse, Developer
     Aldata Software Management Inc.

At 09 JUN 1998 05:21PM pazpurua wrote:

are you using the odbc 16 bits or 32 bits

At 09 JUN 1998 06:11PM Duane Pekse Aldata wrote:

16 bit

At 12 JUN 1998 07:21AM Cameron Revelation wrote:

Duane,

If you search on SQL Server in the discussion, you will find some files I posted which may help. There are several DLL's which (the wrong versions of them) can cause the behavior you described.

Secondly, if you want to methodically track down what is causing the problem, you can use the query window to simulate what the DataSet is doing. My guess is that you have a BEGIN TRAN being issued when the connection is created and a ROLLBACK TRAN either implicitly (by the server) or explicitly (by OpenInsight) being executed before closing the connection. You can copy the select script from the DataSet into the query window to test executing it.

Hope this helps, let me know how it goes,

Cameron Purdy

Revelation Software

At 12 JUN 1998 01:40PM Duane Pekse Aldata wrote:

Cameron,
In response to your comment on the dll's that I am using to connect.  They are as follows:
	w3dblib.dll  163526 bytes 02/02/92
	dnmp3.dll 10944 bytes 1/26/94
	netapi.dll 106960 bytes 7/11/95
These are the same dlls that you have posted before as being the most reliable ones.
As you suggested, I tried each of the scripts that are in the dataset seperately in the Query window.  I did not have any problems with any of them, they all executed without a hitch, and the window closed again afterwards to.  As you suggested, I also think that the problem is a ROLLBACK_TRANS command, but I can't find it.  Here is a list of all of the events that get fired by the window that the dataset is attached to.
ON OPEN (start mdi_child command)
DSOInstance
Create
Activated
ON CLOSE (button that triggers the Close, or the sys x button)
Close
DSOClear
ON EDIT (enter an existing key and tab off of key field)
Read
DSOExecute
ON INSERT (enter a non-existant key and tab off of the key field)
Read
DSOExecute
DSOInsert
SysMsg
ON SAVE (Directly triggering the Write)
Write
DSOCommit
Clear
DSOClear
NoteClear
Order of events Results
Open, Close		OK
Open,...Close		Lockup
If any of the other events are fired between the open and close events, the computer appears to hang up, OpenEngine stops responding, and you have to reboot. I'm guessing that there is a ROLLBACK in the DSOClear event, and for some reason that command has encountered a lock on the table, and it is waiting for the lock to resolve. The only thing I can see that could be locking it is the DSOExecute command. So, if I'm right, then the rollback cannot happen until the window closes, but the window will not close until the rollback is finished. This is a "Do while the computer is on" loop.
Is my interpretation of the events correct, or have I missed something.  If I'm right, why is the table being locked so that the rollback cannot happen?  Is there a way to (temporarily) remove the rollback command to see if it is the culprit?  
Note: This problem only occurs if we connect using the SQL Server connection.  When we connect using the ODBC to get to SQL Server, everything works fine.  But why introduce that extra (buggy) layer if we don't need it?
		Duane
		Aldata Software

At 12 JUN 1998 02:41PM Dsig (SigSolutions) wrote:

pmfji ..

have you checked your ODBC definition AND your connection object. Be sure that trans if off on both of these..

dsig

David Tod Sigafoos ~ SigSOlutions

[email protected] cis:70302,77 voice:503-639-8080

At 15 JUN 1998 06:14PM Cameron Revelation wrote:

Duane,

Just a couple other ideas:

1) Use CS_SPY … it will show current connections and DataSets … that will help determine if two connections are blocking each other.

2) Try without transactions being on … or do a RETURN 0 from the DSOClear event.

It may be something I can fix here, but we are just getting MS SQL 6.5 up and running and we are short some doc. (I believe you had some other 6.5 issues regarding data types?)

Cameron Purdy

Revelation Software

View this thread on the forum...