[Resolved]Bug with return data from Dynamic Stored Procedure

[Cristiano]

Hello,

I have a problem with Devart driver, where I have a stored procedure in SQL Server 2008 that returns records through instruction Exec (@ cmd) with dynamic SQL.

In Delphi, Activate the stored procedure the error occurs: Cursor not returned from Query.

Because this message occurs if the procedure in Sql Server Management Studio returns the records correctly?

I use Delphi XE2 and already tested with the components TSQLDataSet, and TSQLQuery TStoredProc.

Below I am sending the code to someone able to create the structure in the database and test with Delphi.

- Creates the schemas

CREATE SCHEMA Emp1
GO
CREATE SCHEMA Emp2
GO
CREATE SCHEMA Emp3
GO

- Creates the tables
CREATE TABLE Emp1.Atividade (IDUsuario INT Anything VARCHAR (100))
CREATE TABLE Emp2.Atividade (IDUsuario INT Anything VARCHAR (100))
CREATE TABLE Emp3.Atividade (IDUsuario INT Anything VARCHAR (100))
CREATE TABLE dbo.Usuario (INT IDUsuario, UserName VARCHAR (20))

- Insert the data
Dbo.Usuario INSERT INTO VALUES (1, 'User 1')
Dbo.Usuario INSERT INTO VALUES (2, 'User 2')
Dbo.Usuario INSERT INTO VALUES (4, 'User 4')

Emp1.Atividade INSERT INTO VALUES (1, 'user activities Company 1')
Emp2.Atividade INSERT INTO VALUES (2, 'user activities Company 2')
Emp3.Atividade INSERT INTO VALUES (4, 'User Company Activities 3')



- Create a Procedure to return the records to the Program



ALTER PROCEDURE [dbo]. [Test]

AS

BEGIN

- SET NOCOUNT ON added to Prevent extra result sets from

- Interfering with SELECT statements.

SET NOCOUNT ON;

- Build a string concatenation of commands

DECLARE @ Table VARCHAR (MAX)

SET @ Table = (

SELECT 'SELECT * FROM' + SCHEMA_NAME (SCHEMA_ID) +

. 'UNION ALL Activity' The Name FROM sys.tables

WHERE name = 'Activity'

FOR XML RAW ('Table'), ROOT ('tables'))

SET @ Table =

REPLACE (

REPLACE (

REPLACE (

REPLACE (@ Table, '','')

, 'UNION ALL ','') Tables

, '','')

DECLARE @ cmd VARCHAR (MAX)

SET @ cmd = '; Activities The WITH (' + @ Tables + ')' + CHAR (10) + CHAR (10)

SET @ cmd = @ cmd + 'SELECT * FROM User INNER JOIN The S ON S. The Activities The IDUsuario IDUsuario = A.'

PRINT @ cmd

EXEC (@ cmd)

END

[AndreyZ]

Hello,

The script to create the Test stored procedure cannot be executed, it contains a lot of errors. Please perform the following steps:
- go to Microsoft SQL Server Management Studio;
- find the Test stored procedure in the list of stored procedures;
- left click on the Test stored procedure and choose "Script Stored Procedure as"->"Create to"->"New Query Editor Window";
- in the opened window select all text and post it here.

[Cristiano]

Hi,

The code of the stored procedure Test:

/****** Object: StoredProcedure [dbo].[Test] Script Date: 12/19/2011 11:34:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Test]
AS
BEGIN
-- SET NOCOUNT ON added to Prevent extra result sets from interfering with SELECT statements.

SET NOCOUNT ON

DECLARE @Tabelas VARCHAR(MAX)
SET @Tabelas = (
SELECT 'SELECT * FROM ' + SCHEMA_NAME(SCHEMA_ID) +
'.Atividade UNION ALL ' As Nome FROM sys.tables
WHERE name = 'Atividade'
FOR XML RAW('Tabela'),ROOT('Tabelas'))

SET @Tabelas =
REPLACE(
REPLACE(
REPLACE(
REPLACE(@Tabelas,'','')
,' UNION ALL ','')
,'','')

DECLARE @cmd VARCHAR(MAX)
SET @cmd = ';WITH Atividades As (' + @Tabelas + ')' + CHAR(10) + CHAR(10)
SET @cmd = @cmd + 'SELECT s.idusuario, s.nomeusuario FROM Usuario As S INNER JOIN Atividades As A ON S.IDUsuario = A.IDUsuario'

EXEC(@cmd)
END
GO

[AndreyZ]

I cannot reproduce the problem. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com.

[Cristiano]

Hi AndreyZ!

To simulate the problem is easy:
1) Create the following structure in Sql Server 2008 in a database:

-- Creates the schemas

CREATE SCHEMA Emp1
GO
CREATE SCHEMA Emp2
GO
CREATE SCHEMA Emp3
GO

-- Creates the tables
CREATE TABLE Emp1.Atividade (IDUsuario INT Anything VARCHAR (100))
CREATE TABLE Emp2.Atividade (IDUsuario INT Anything VARCHAR (100))
CREATE TABLE Emp3.Atividade (IDUsuario INT Anything VARCHAR (100))
CREATE TABLE dbo.Usuario (INT IDUsuario, UserName VARCHAR (20))

-- Insert the data
Dbo.Usuario INSERT INTO VALUES (1, 'User 1')
Dbo.Usuario INSERT INTO VALUES (2, 'User 2')
Dbo.Usuario INSERT INTO VALUES (4, 'User 4')

Emp1.Atividade INSERT INTO VALUES (1, 'user activities Company 1')
Emp2.Atividade INSERT INTO VALUES (2, 'user activities Company 2')
Emp3.Atividade INSERT INTO VALUES (4, 'User Company Activities 3')

-- Create Procedure

CREATE PROCEDURE [dbo].[Test]
AS
BEGIN

SET NOCOUNT ON

DECLARE @Tabelas VARCHAR(MAX)
SET @Tabelas = (
SELECT 'SELECT * FROM ' + SCHEMA_NAME(SCHEMA_ID) +
'.Atividade UNION ALL ' As Nome FROM sys.tables
WHERE name = 'Atividade'
FOR XML RAW('Tabela'),ROOT('Tabelas'))

SET @Tabelas =
REPLACE(
REPLACE(
REPLACE(
REPLACE(@Tabelas,'','')
,' UNION ALL ','')
,'','')

DECLARE @cmd VARCHAR(MAX)
SET @cmd = ';WITH Atividades As (' + @Tabelas + ')' + CHAR(10) + CHAR(10)
SET @cmd = @cmd + 'SELECT s.idusuario, s.nomeusuario FROM Usuario As S INNER JOIN Atividades As A ON S.IDUsuario = A.IDUsuario'

EXEC(@cmd)
END

2) In DelphiXE 2 create a simple VCL Application form and place a component TSQLConnection and TSQLDataSet.
3) Set the connection through the TSQLConnection
4) Set the TSQLConnection to TSQLDataSet
5) In the CommandText property of the TSQLDataSet set: Exec dbo.Test
6) Open the TSQLDataSet: Activate property = True -> error occurs: Cursor not Returned from Query.

There is a bug, because in SQL Server 2008 Management Studio, when performing the procedure is returned correctly records that the selection is performed.

Thanks for your help..

[AndreyZ]

I have reproduced the problem. This problem occurs because the user you connect to SQL Server with doesn't have enough rights to get data from the sys.tables system table. To solve the problem, you should grant the SELECT rights for the dbo.Usuario, Emp1.Atividade, Emp2.Atividade, and Emp3.Atividade tables to the user you connect to SQL Server with.

[Cristiano]

Very Thanks AndreyZ!! Now work ok!

[AndreyZ]

It's good to see that the problem was solved. If any other questions come up, please contact us.