SQL Server stored procedures and the CleverPath Forest & Trees ADO .Net interface

Document ID : KB000055762
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction

The Forest & Trees ADO .Net Interface is implemented in CleverPath Forest & Trees 7.0 Service Pack 1 (SP1) for querying remote databases via HTTP. As with other general database interfaces the ADO .Net interface will allow execution of SQL Server Stored Procedures. There are several points that need to be considered when executing stored procedures. This article will examine some of the issues involved in getting stored procedures to work with the F&T ADO .Net interface.

Output Parameter Values

All stored procedures that are written to be executed via the ADO .Net interface should have 'SET NOCOUNT ON' as the first line of the procedure. This stops messages indicating the number of rows affected by a SQL statement from being returned as part of the results which may interfere with the result of the query.

All examples in this article show how this should be implemented.

Multiple Result Sets

With all Forest & Trees views using the ADO .Net interface, only the first result set of a query can be retrieved. All F&T Views are designed to receive a single result set of data only. For example the executing following example procedure will fail.

CREATE PROCEDURE test_proc
AS
SET NOCOUNT ON
SELECT * FROM table_a
SELECT * FROM table_b
GO

Only the result of a single select statement is allowed.

Temporary Table Usage

There are currently two known related issues when using temporary tables in stored procedures accessed via ADO .Net with OLEDB or ODBC that may produce a failure, these are:

  1. Executing a stored procedure with a temporary table and then performing a select from that table.
  2. Executing stored procedures containing insert, update or delete statements when using ADO .Net with ODBC.

A solution to problem 1, when using ADO .Net with OLEDB, consists of using table variables instead of temporary tables. For example if the procedure using temporary tables looked liked this:

CREATE PROCEDURE test_proc_oledb
AS
SET NOCOUNT ON
CREATE TABLE #temp (col1 VARCHAR(20), col2 VARCHAR (30))
INSERT INTO #temp VALUES('value1','value2')
SELECT * FROM #temp
GO

To use table variables instead, the procedure should be rewritten as follows:

CREATE PROCEDURE test_proc_oledb2
AS
SET NOCOUNT ON
DECLARE @temp TABLE (col1 VARCHAR (20), col2 VARCHAR (30))
INSERT INTO @temp VALUES('value1','value2')
SELECT * FROM @temp
GO

This would be the most efficient way to write the procedure having no effect on performance. It does not work however, if using ADO .Net with ODBC, due to problem 2.

A way of achieving this is to include the following lines at the beginning of the modified stored procedure instead of the view.

SET NOCOUNT ON
IF @@OPTIONS & 512 = 0
BEGIN
SET FMTONLY OFF
SET NOCOUNT ON
SET FMTONLY ON
END

Or in the view use these lines

EXEC sp_executesql N'
SET NOCOUNT ON
IF @@OPTIONS & 512 = 0
BEGIN
SET FMTONLY OFF
SET NOCOUNT ON
SET FMTONLY ON
END
EXEC test_proc_oledb2
'

These additional lines are a test to check whether FMTONLY is ON. If it is then NOCOUNT is set to ON and FMTONLY is set to OFF. For this to work correctly the procedure must be written using table variables.

There is also a way of executing the original or the modified procedure that will allow either version of the procedure to work correctly. This consists of executing the procedure in batch and suppressing the FMTONLY's returned.

To do this, it is not necessary to change the code of the procedure in any way. Just place the following commands in the F&T view to execute the original stored procedure.

EXEC sp_executesql N'
SET FMTONLY OFF
EXEC test_proc_oledb
'

By using this last method in the view, a performance penalty can arise. The change in performance should be evaluated before deploying applications in this manner. This method is not recommended but is here for completeness.

Whilst this article has been researched using SQL Server Stored Procedures the principles may apply equally to other Databases.

Sample code in this document is:

Copyright ? 2004 Computer Associates International, Inc. All rights reserved.

You have a royalty-free right to use, modify, reproduce, and distribute this sample code in any way you find useful, if you agree that Computer Associates has no warranty, obligations, or liability for any sample code.