| Author |
 Topic  |
|
|
juliom
Costa Rica
36 Posts |
 Posted - 01/06/2005 : 09:45:56
|
I'm evaluating Report Portal 1.5. I'm creating an SQL Report (againt SQLServer 2000 SP3a) that uses a Table variable to hold some temporary info. The idea is to load the data into this Table variable using an INSERT INTO.
It works just fine in SQL Query Analizer, but if I paste the Transact SQL into the SQL Report Designer and try to test it I just don't get any results...
This is an example of the Transact SQL I'm using...
----- Sample code begins ---------
DECLARE @SubResumen Table ([RecNum] BIGINT identity,
[Aņo] INT,
[Mes] NVARCHAR(20),
[Mes2] INT,
[Sucursal] NVARCHAR(40),
[Cliente] NVARCHAR(10),
[Monto] MONEY,
[Puntos Obtenidos] MONEY,
[Puntos Redimidos] MONEY
)
INSERT INTO @SubResumen ([Aņo], [Mes], [Mes2], [Sucursal],
[Cliente], [Monto], [Puntos Obtenidos],
[Puntos Redimidos])
SELECT r.[Aņo], 'Todos', 0, 'Todas',
r.[Cliente], SUM(r.[Monto]), SUM(r.[Puntos Obtenidos]),
SUM(r.[Puntos Redimidos])
FROM dbo.Resumen r
WHERE r.[Monto] > 0
GROUP BY r.[Aņo], r.[Cliente]
ORDER BY SUM(r.[Monto]) DESC
SELECT * FROM @SubResumen
--------- Sample code ends ------------
I've done this using temporary tables and using regular tables to hold the data RESULTING from the Insert Into and it works just fine in SQL Query Analizer but it doesn't work in the SQL Report Designer.
The other thing I tried was to create a store procedure with code and I get exactly the same.
If I remove the Insert Into from the code it works fine in the SQL Report Designer but of course I don't get the desired results...
Any Ideas???
Thanks,
Julio. |
|
|
admin
1646 Posts |
Posted - 01/09/2005 : 21:09:53
|
ReportPortal does not allow INSERT/UPDATE statement in the SQL as a measure against SQL injection attack.
Regards,
Igor |
 |
|
|
juliom
Costa Rica
36 Posts |
Posted - 01/10/2005 : 09:38:41
|
OK.. It makes sense.. but, don't you think this (the support for Insert/Update) should be something I ( as site administrator) should be able to configure???
Thanks again
quote:
Originally posted by juliom
I'm evaluating Report Portal 1.5. I'm creating an SQL Report (againt SQLServer 2000 SP3a) that uses a Table variable to hold some temporary info. The idea is to load the data into this Table variable using an INSERT INTO.
It works just fine in SQL Query Analizer, but if I paste the Transact SQL into the SQL Report Designer and try to test it I just don't get any results...
This is an example of the Transact SQL I'm using...
----- Sample code begins ---------
DECLARE @SubResumen Table ([RecNum] BIGINT identity,
[Aņo] INT,
[Mes] NVARCHAR(20),
[Mes2] INT,
[Sucursal] NVARCHAR(40),
[Cliente] NVARCHAR(10),
[Monto] MONEY,
[Puntos Obtenidos] MONEY,
[Puntos Redimidos] MONEY
)
INSERT INTO @SubResumen ([Aņo], [Mes], [Mes2], [Sucursal],
[Cliente], [Monto], [Puntos Obtenidos],
[Puntos Redimidos])
SELECT r.[Aņo], 'Todos', 0, 'Todas',
r.[Cliente], SUM(r.[Monto]), SUM(r.[Puntos Obtenidos]),
SUM(r.[Puntos Redimidos])
FROM dbo.Resumen r
WHERE r.[Monto] > 0
GROUP BY r.[Aņo], r.[Cliente]
ORDER BY SUM(r.[Monto]) DESC
SELECT * FROM @SubResumen
--------- Sample code ends ------------
I've done this using temporary tables and using regular tables to hold the data RESULTING from the Insert Into and it works just fine in SQL Query Analizer but it doesn't work in the SQL Report Designer.
The other thing I tried was to create a store procedure with code and I get exactly the same.
If I remove the Insert Into from the code it works fine in the SQL Report Designer but of course I don't get the desired results...
Any Ideas???
Thanks,
Julio.
|
|
|
|
admin
1646 Posts |
Posted - 01/10/2005 : 20:25:01
|
| As the administrator/developer you could change this rule by modifying the asp page. The logic resides in the CreateTable() function of sql\i_query.asp. |
|
|
|
juliom
Costa Rica
36 Posts |
Posted - 01/11/2005 : 10:42:14
|
Thanks.. I'll check it up...
quote:
Originally posted by juliom
I'm evaluating Report Portal 1.5. I'm creating an SQL Report (againt SQLServer 2000 SP3a) that uses a Table variable to hold some temporary info. The idea is to load the data into this Table variable using an INSERT INTO.
It works just fine in SQL Query Analizer, but if I paste the Transact SQL into the SQL Report Designer and try to test it I just don't get any results...
This is an example of the Transact SQL I'm using...
----- Sample code begins ---------
DECLARE @SubResumen Table ([RecNum] BIGINT identity,
[Aņo] INT,
[Mes] NVARCHAR(20),
[Mes2] INT,
[Sucursal] NVARCHAR(40),
[Cliente] NVARCHAR(10),
[Monto] MONEY,
[Puntos Obtenidos] MONEY,
[Puntos Redimidos] MONEY
)
INSERT INTO @SubResumen ([Aņo], [Mes], [Mes2], [Sucursal],
[Cliente], [Monto], [Puntos Obtenidos],
[Puntos Redimidos])
SELECT r.[Aņo], 'Todos', 0, 'Todas',
r.[Cliente], SUM(r.[Monto]), SUM(r.[Puntos Obtenidos]),
SUM(r.[Puntos Redimidos])
FROM dbo.Resumen r
WHERE r.[Monto] > 0
GROUP BY r.[Aņo], r.[Cliente]
ORDER BY SUM(r.[Monto]) DESC
SELECT * FROM @SubResumen
--------- Sample code ends ------------
I've done this using temporary tables and using regular tables to hold the data RESULTING from the Insert Into and it works just fine in SQL Query Analizer but it doesn't work in the SQL Report Designer.
The other thing I tried was to create a store procedure with code and I get exactly the same.
If I remove the Insert Into from the code it works fine in the SQL Report Designer but of course I don't get the desired results...
Any Ideas???
Thanks,
Julio.
|
|
|
| |
Topic |
|
|
|
Report Portal
SQL Support
