| Stored Proc dataset not returned [message #168612] |
Thu, 08 June 2006 12:05  |
Eclipse User |
|
|
|
Originally posted by: keith.chappelow.siemens.com
I have two stored procedures in MS SqlServer that return data using SQL
Query Analyzer but BIRT does not get any data in the dataset.
I first noticed this when creating the dataset and trying to do a preview.
I then modified the stored procedure to just do a select statement and
created a report with the fields. With just the "fake" select statement
every works but as soon as I restore the stored procedure to what it
should really be returning I get no data returned.
I have returned data with the stored procedure using the SqlServer jdbc
driver so I don't think that is the problem.
I can see no errors logged.
Here is the report definition for the dataset:
<?xml version="1.0" encoding="UTF-8"?>
<!-- Written by Eclipse BIRT 2.0 -->
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.0.1 Build
<20060222-1204></property>
<property name="units">in</property>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
id="4">
<property
name="odaDriverClass">com.microsoft.jdbc.sqlserver.SQLServerDriver </property>
<property
name="odaURL">jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=LMS</property>
<property name="odaUser">LMS</property>
<encrypted-property
name="odaPassword">TE1T</encrypted-property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.SPSelectDataSet "
name="Data Set" id="5">
<property name="dataSource">Data Source</property>
<list-property name="parameters">
<structure>
<property name="name">StartDate</property>
<property name="dataType">string</property>
<property name="position">1</property>
<expression name="defaultValue">'05/04/2006
7:00'</expression>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">StopDate</property>
<property name="dataType">string</property>
<property name="position">2</property>
<expression name="defaultValue">'05/04/2006
12:00'</expression>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">SortControllerName</property>
<property name="dataType">string</property>
<property name="position">3</property>
<expression name="defaultValue">'ALL'</expression>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<property name="queryText">{call
LMS.GetLogicalDivertSumFromMC(?,?,?)} </property>
</oda-data-set>
</data-sets>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<value-of>new
Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
</report>
Here is the stored procedure:
CREATE PROCEDURE LMS.GetLogicalDivertSumFromMC
(
@aStartDate udt_CharDateTime,
@aStopDate udt_CharDateTime,
@aSortControllerName VARCHAR(30)
)
AS
/*
** Revision Information:
** $Source:
//Grrp403a/DEPT1/SPE/Data/Product_Components/LMS/03-Developm ent/Code/Source/Database/SP/rcs/GetLogicalDivertSumFromMC.SQ L
$
** $Id: GetLogicalDivertSumFromMC.SQL 1.1 2006/02/14 15:24:10Z guntihs
Developing $
** $Name: SD_5.3_QC PD3.1SP1 PD3.1B445 PD3.1B428 $
**
*/
BEGIN
-- Create a temporary table to put the data to be queried an returned
in to.
CREATE TABLE #TempTable
(
LogicalDestination VARCHAR(32),
PhysicalDestinationID VARCHAR(10),
PhysicalDestination VARCHAR(32),
ScannerID INT,
SortControllerName VARCHAR(32),
Data INT DEFAULT 0
)
-- Preload the data into the temporary table
INSERT IGNORE INTO #TempTable( LogicalDestination, PhysicalDestinationID,
PhysicalDestination, ScannerID, SortControllerName)
SELECT LDM.LogicalDestinationID, P.PhysicalDestinationID,
P.PhysicalDestination, CDM.ScannerID, SC.SortControllerName
FROM SortDirector.SortD.PhysicalDestination P
INNER JOIN SortDirector.SortD.ScannerParameters SP ON P.ScannerID
= SP.ScannerID
INNER JOIN SortDirector.SortD.SortController SC ON
SP.SortControllerID = SC.SortControllerID
INNER JOIN SortDirector.SortD.ControllerDestinationMap CDM ON
P.PhysicalDestinationID = CDM.PhysicalDestinationID
AND P.ScannerID = CDM.ScannerID
INNER JOIN SortDirector.SortD.LogicalDestinationMap LDM ON
LDM.ControllerDestinationID = CDM.ControllerDestinationID
AND CDM.ScannerID = LDM.ScannerID
WHERE @aSortControllerName = 'ALL' OR SC.SortControllerName =
@aSortControllerName
UPDATE #TempTable
SET Data = Total
FROM #TempTable INNER JOIN
( SELECT ClientID , GroupType , Tag, SUM(Data) Total FROM
LMS.StatisticsLog WITH (NOLOCK)
WHERE SubGroupType = 'PDESTARRIVAL'
AND TIMESTAMP >= @aStartDate AND TIMESTAMP <= @aStopDate
AND (@aSortControllerName = 'ALL' OR ClientID =
@aSortControllerName)
GROUP BY ClientID, GroupType, Tag ) Stat
ON ClientID = SortControllerName
AND GroupType = ScannerID
AND Tag = PhysicalDestinationID
SELECT LogicalDestination, ScannerID, PhysicalDestination, Data
LogicalDestinationCount FROM #TempTable
END
This is an example of calling the stored procedure in Query Analyzer:
DECLARE @RC int
DECLARE @aStartDate varchar(50)
DECLARE @aStopDate varchar(50)
DECLARE @aSortControllerName varchar(30)
SELECT @aStartDate = '05/04/2006 7:00'
SELECT @aStopDate = '05/04/2006 12:00'
SELECT @aSortControllerName = 'ALL'
EXEC @RC = [LMS].[LMS].[GetLogicalDivertSumFromMC] @aStartDate,
@aStopDate, @aSortControllerName
And the data returned:
LogicalDestination ScannerID PhysicalDestination
LogicalDestinationCount
-------------------------------- -----------
-------------------------------- -----------------------
Door1 301 1011
0
Door10 301 2021
0
Door2 301 1022
0
|
|
|
|
| Re: Stored Proc dataset not returned [message #168687 is a reply to message #168623] |
Thu, 08 June 2006 15:05  |
| Eclipse User |
|
|
|
Originally posted by: keith.chappelow.siemens.com
I found what was causing the problem. I added "SET NOCOUNT ON" in the
stored procedure. What was happening is the rowcount for the temporary
table was being interpreted as the data set.
|
|
|
Powered by
FUDForum. Page generated in 0.08136 seconds
Home