[sqr-users] EXECUTE issues with work tables in ODBC/SQL Server

[David Zimmerman <dzman1a@yahoo.com>]

Env: ODBC/SQL Server on WinNT network
SQR version 8.1.0
 
Hi all,
 
I'm a newcomer to SQR, so I'm grateful to this group for hard to find 
information.  I want to share an issue that made me wonder about SQR's 
viability in my company's future before I figured out the solution.

The general situation:  I'm converting existing Crystal Reports to SQR, so one 
of the objects is to do as little work as possible on the source stored 
procedures.  Practically all the reports are based on stored procedures, which 
have many advantages. 

The issue occurs when a report's 'master' stored procedure produces one or more 
work tables for use by the report in addition to the data it returns (this was 
a common approach to report writing by some of my predecessors).  Invariably, 
the SQR report hung when a query to a work table was installed.  The Crystal 
Reports version, using the identical DSN, worked fine, however.

The cause for the hang is a transaction lockout.  Through Profiler, I could see 
that a command to "set implicit_transactions on" is made immediately on 
connection.  Once that occurs, practically any activity involving a table 
object starts a transaction.  Until the transaction is committed, access to 
affected objects is denied to other processes, including subsequent queries by 
the same user.

The solutions are:

1)  Add "Set implicit_transactions off" to the top of the stored procedure.  
This alone fixed the issue in the samples I tested.

2)  Use the SQR COMMIT command after data is returned from the master sproc, 
but before any subquery is done.

        (However, a possible hazard with this is that a COMMIT when no 
transaction exists causes an error (at least, in T-SQL it does).  I don't know 
how graceful SQR is about handling such errors)

3)  The safest and most effective point to COMMIT, I believe, is in the master 
stored procedure after the work tables have been finalized, and just before the 
SELECT statement that returns data to the report.  The SQL Server T-SQL syntax 
to clear all pending transactions is:

    While @@TranCount > 0
         COMMIT TRAN


---------------------------------
Do you Yahoo!?
Free Pop-Up Blocker - Get it now

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users