reporting services stored procedure help

Hi all,
I need help using stored procedures in Reporting Services. I have a
dataset set to type stored procedure and when I run the sp it doesn't
return all the data that it should. When I run it in query analyzer it
returns approxiamately 4500 rows, but in RS it only returns around
1000. Is there a maximum number of rows that RS can return? Does
anyone have any idea what the problem could be? I've looked everywhere
and can't seem to find any answers.
Thanks in advance.
MissyAre you sure you are running the same sproc? from the same environement? do a
test by adding a top 10 to your sproc and check the results.|||Kyriakos wrote:
> Are you sure you are running the same sproc? from the same environement? do a
> test by adding a top 10 to your sproc and check the results.
The results are correct that I am getting, I am just not getting all
the results. I am also having trouble using the parameters with the
sproc. I am using temp tables in my sproc, would that mess everything
up?|||There is not a maximum number of rows (if rendering in PDF or Excel there
might be a practical limit, Excel has a limit of 65K).
First, no problem with temp tables. I use them all the time
One thing that could be happening is an artifact of the development
environment. If you don't change the values of the parameter (at least as
far as the preview tab, I'm not sure about the dataset tab) it uses data
that it caches. Look in the directory where you .rdl files are. You will see
a file called filename.rdl.data. You can delete the file and make sure that
it is requerying the database.
You mentioned that you are having trouble with passing parameters. You can
hard code the parameters. You can either click on ..., parameters tab, set
the mapping to an expression, and put in the value. OR you can do this:
pr_myprocedurename 'somevalue',1.0,'some'
I.e. you can put exactly the same in as you do in query analyzer. In this
way you can make sure that it isn't a parameter mapping/passing problem.
Doing this you might want to switch to generic query design mode. There is a
button to the right of the ... that allows you to do this.
And, you might have to switch from stored procedure to text to hard code the
parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156427092.932270.196010@.75g2000cwc.googlegroups.com...
> Hi all,
> I need help using stored procedures in Reporting Services. I have a
> dataset set to type stored procedure and when I run the sp it doesn't
> return all the data that it should. When I run it in query analyzer it
> returns approxiamately 4500 rows, but in RS it only returns around
> 1000. Is there a maximum number of rows that RS can return? Does
> anyone have any idea what the problem could be? I've looked everywhere
> and can't seem to find any answers.
> Thanks in advance.
> Missy
>|||Thanks Bruce. In my sproc do I need to specify the parameters after I
create the sproc and before the if and select statements? If so, when I
try to run the sproc in RS it still is only pulling the records that
have a district (one of my fields) name what begins with the letter A.
So it is missing tons of records. Any ideas? Thanks for your help.
Missy|||Does your stored procedure have parameters? Is RS recognizing the
parameters? When you execute from the dataset you should be prompted to fill
in the query parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156435417.812409.21310@.m79g2000cwm.googlegroups.com...
> Thanks Bruce. In my sproc do I need to specify the parameters after I
> create the sproc and before the if and select statements? If so, when I
> try to run the sproc in RS it still is only pulling the records that
> have a district (one of my fields) name what begins with the letter A.
> So it is missing tons of records. Any ideas? Thanks for your help.
> Missy
>|||yes it is recognizing the parameters in my sproc. I'm going to post my
sproc, this is my first time ever using a sproc, so I could have it all
wrong. :)
CREATE PROCEDURE [dbo].[meterquery]
@.district char
as
BEGIN
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
/******************* BEGIN CREATE TABLES **************************/
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
INTO #TmpTable1
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID =Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
#TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
INTO #ZonesInDelSys
FROM productioncache..cache_zonerelations ZR
INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
WHERE 1=0
--select * from #ZonesInDelSys
SELECT DISTINCT DelSysID, District, FieldOffice
INTO #DelSysHier
FROM #ZonesInDelSys
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
District, FieldOffice)
SELECT ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
INTO #MetersInDelSys
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE 1=0
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
INTO #TempAttr
FROM #MetersInDelSys A
INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
ha.HdrHID=A.GasMeterHID
WHERE 1=0
/************* END CREATE TABLES ***********************************/
INSERT INTO #TmpTable1 WITH (TABLOCKX)
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=1
AND (Typ1.HdrTypeCode = 'Zone')
AND (H.HdrInactive = 0)
AND (Mp1.HdrInactive = 0)
AND (CfgHist.DsConfigEffEnd = '12/31/2078')
AND CfgHist.DsObsolete=0
--and root.DelSysHID in(1754810,1711525)
GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
CfgHist.DelSysTID
ORDER BY H.HdrName
--Zones in DelSys
INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
FROM productioncache..cache_zonerelations ZR (NOLOCK)
INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
--DelSys Enertia Hierarchy
INSERT INTO #DelSysHier WITH (TABLOCKX)
SELECT DISTINCT
DelSysID
, District
, FieldOffice
FROM #ZonesInDelSys (TABLOCKX)
truncate table #MetersInDelSys
--Gas Meters in DelSys
INSERT INTO #MetersInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
INSERT INTO #TempAttr WITH (TABLOCKX)
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=A.GasMeterHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
--SELECT where only 1 meter in DelSys
SELECT A.District, @.district as districtname
, A.FieldOffice
, A.DelSysID
, A.DelSysCode
, A.DelSysName
, A.ZoneID
, HZ.HdrName
, A.GasMeterHID
, HG.HdrName as MeterName
, A.MeterCode
, A.MeterType
, A.MeterAttr
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
HG.HdrHID=A.GasMeterHID
INNER JOIN (SELECT DelSysID
, count(*) as num
FROM #MetersInDelSys
GROUP BY DelSysID
HAVING count(*) = 1) G1 ON
G1.DelSysID=A.DelSysID
where @.district=a.district
/**************** CLEAN UP TEMP TABLES**********************/
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
END
Then inside RS I select a dataset and set the command to stored
procedure. I then go to the layout tab and specify a parameter called
district and it pulls from the sproc query district name. if that
makes sense...|||Several things are problematic. First, my guess is you first wrote this in
query analyzer and then turned it into a stored procedure. SQL Server cleans
up for you. You never ever have to drop temp tables manually. Let SQL Server
handle that. Remove all of that at the beginning of your stored procedure.
Next, do not manually drop your temp tables at the end of your stored
procedure. That can definitely cause problems with RS. You should just let
it fall out of scope and let SQL Server manage the temp tables lifespan.
So, the drop tables at the beginning are just unnecessary. The drop tables
at the end are dangerous (from the perspective of Reporting Services).
Remove every single drop table and see what happens. The rest of it looks
good.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156437458.749343.239350@.m79g2000cwm.googlegroups.com...
> yes it is recognizing the parameters in my sproc. I'm going to post my
> sproc, this is my first time ever using a sproc, so I could have it all
> wrong. :)
>
> CREATE PROCEDURE [dbo].[meterquery]
> @.district char
> as
> BEGIN
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> /******************* BEGIN CREATE TABLES **************************/
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> INTO #TmpTable1
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID => Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
> #TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
>
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> INTO #ZonesInDelSys
> FROM productioncache..cache_zonerelations ZR
> INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
> WHERE 1=0
> --select * from #ZonesInDelSys
> SELECT DISTINCT DelSysID, District, FieldOffice
> INTO #DelSysHier
> FROM #ZonesInDelSys
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
> District, FieldOffice)
>
> SELECT ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> INTO #MetersInDelSys
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE 1=0
>
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> INTO #TempAttr
> FROM #MetersInDelSys A
> INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
> ha.HdrHID=A.GasMeterHID
> WHERE 1=0
> /************* END CREATE TABLES ***********************************/
> INSERT INTO #TmpTable1 WITH (TABLOCKX)
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=1
> AND (Typ1.HdrTypeCode = 'Zone')
> AND (H.HdrInactive = 0)
> AND (Mp1.HdrInactive = 0)
> AND (CfgHist.DsConfigEffEnd = '12/31/2078')
> AND CfgHist.DsObsolete=0
> --and root.DelSysHID in(1754810,1711525)
> GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
> CfgHist.DelSysTID
> ORDER BY H.HdrName
>
> --Zones in DelSys
> INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> FROM productioncache..cache_zonerelations ZR (NOLOCK)
> INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
> WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
>
> --DelSys Enertia Hierarchy
> INSERT INTO #DelSysHier WITH (TABLOCKX)
> SELECT DISTINCT
> DelSysID
> , District
> , FieldOffice
> FROM #ZonesInDelSys (TABLOCKX)
> truncate table #MetersInDelSys
> --Gas Meters in DelSys
> INSERT INTO #MetersInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
> INSERT INTO #TempAttr WITH (TABLOCKX)
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=A.GasMeterHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
>
> --SELECT where only 1 meter in DelSys
> SELECT A.District, @.district as districtname
> , A.FieldOffice
> , A.DelSysID
> , A.DelSysCode
> , A.DelSysName
> , A.ZoneID
> , HZ.HdrName
> , A.GasMeterHID
> , HG.HdrName as MeterName
> , A.MeterCode
> , A.MeterType
> , A.MeterAttr
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
> INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
> HG.HdrHID=A.GasMeterHID
> INNER JOIN (SELECT DelSysID
> , count(*) as num
> FROM #MetersInDelSys
> GROUP BY DelSysID
> HAVING count(*) = 1) G1 ON
> G1.DelSysID=A.DelSysID
> where @.district=a.district
>
> /**************** CLEAN UP TEMP TABLES**********************/
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> END
>
> Then inside RS I select a dataset and set the command to stored
> procedure. I then go to the layout tab and specify a parameter called
> district and it pulls from the sproc query district name. if that
> makes sense...
>|||It's working yay!!! The only thing I have left to do is figure out the
report parameters. Do I need to specify them in my sproc? Or can I
create them through RS?
Thanks so much!|||Your stored procedure has a parameter. This is the query parameter. If you
are using SQL Server then RS recognizes the parameter and creates a
corresponding report parameter and maps to it. So when you create a dataset
based on the stored procedure you will then have a report parameter called
district created. I notice you had a parameter question in another posting.
I suggest starting with a new report so you don't have problems left over
from before.
Your query parameters have this mapping occur automatically but you can also
change the mappng to an expression by clicking on the ..., parameters tab.
Then if the report parameter is not needed you go to the layout tab, report
menu->report parameters and delete the now unused parameter (this is not
necessary in your case).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156441135.234902.131430@.i3g2000cwc.googlegroups.com...
> It's working yay!!! The only thing I have left to do is figure out the
> report parameters. Do I need to specify them in my sproc? Or can I
> create them through RS?
> Thanks so much!
>

Reporting Services Stored Procedure Dataset

I have a big SQL Stored Procedure which works with a cursor inside of it. During the procedure the data is inserted into a table and at the end is a SELECT statement from that table. The problem is that when i create a dataset with that stored procedure and i run it in the Data tab i get the correct select, but in the Fields section of the Report I don't get the fields from the last SELECT, but the fields from the cursor. Am I doing something wrong or is this a bug and how can i fix it.
Thanks!does it return the correct result set when you run it from QA ?|||Yes, it does return the correct dataset. I am using sql 2005 and if I have the procedure like his:

DECLARE Mycusor CURSOR FOR SELECT Field1, Field2, ...
OPEN CURSOR ...
FETCH NEXT ...
WHILE @.@.FETCH_STATUS > 0 ...
INSERT INTO tmp ...

SELECT * FROM tmp

If the procedure is like this then in Sql 2005 when i run it i get the data from tmp and in Reporting Services i get a dataset containg Field1, Field2 with no rows in it.
Do you have any idea?|||dont you have a close/deallocate cursor ? heres a template for a cursor.

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT
OPEN rs
fetch next from rs into
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 -- your DML stmts.
FETCH NEXT FROM rs INTO
END
close rs
deallocate rs

and you would need to do a select after deallocating the cursor.

hth

Reporting Services Groups

Iâ'm somewhat new to Reporting Services. I have a report that uses a stored
procedure â?¦problem Iâ'm having is using the Groups to sort the parent and
child data correctly.
The data looks like this:
Parent ID Child ID Date Comment
1 0 10/1/2004 â'Parent comment 1â'
2 1 10/2/2004 â'child comment 1â'
3 1 10/3/2004 â'child comment 2â'
4 0 10/3/2004 â'Parent comment 2â'
5 4 10/4/2004 â'child comment 1â'
I want the report grouped like this:
Parent comment 1 10/1/2004
child comment 1 10/2/2004
child comment 2 10/3/2004
Parent comment 2 10/3/2004
child comment 1 10/4/2004
Any help is much appreciated.
Thanks
--
sandersonPlease read this section in RS BOL about "Recursive Hierarchies":
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp
The important part is the "Parent" property on groupings.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"sanderson" <sanderson@.discussions.microsoft.com> wrote in message
news:BFF955C8-6A25-4C81-B087-1F28645302AC@.microsoft.com...
> I'm somewhat new to Reporting Services. I have a report that uses a
stored
> procedure .problem I'm having is using the Groups to sort the parent and
> child data correctly.
> The data looks like this:
> Parent ID Child ID Date Comment
> 1 0 10/1/2004 "Parent comment 1"
> 2 1 10/2/2004 "child comment 1"
> 3 1 10/3/2004 "child comment 2"
> 4 0 10/3/2004 "Parent comment 2"
> 5 4 10/4/2004 "child comment 1"
>
> I want the report grouped like this:
> Parent comment 1 10/1/2004
> child comment 1 10/2/2004
> child comment 2 10/3/2004
>
> Parent comment 2 10/3/2004
> child comment 1 10/4/2004
>
> Any help is much appreciated.
> Thanks
> --
> sanderson|||I read the section and did exactly the same... however i always get
this warning message.
"Build complete -- 0 errors, 0 warnings
The group expression expression used in grouping
'table1_Details_Group' returned a data type that is not valid.
Preview complete -- 0 errors, 1 warnings"
And it returned only one row of record.
I have a productcategory table which is recursive. A category can have
subcategories and also belongs to a parent category. My parent category
has null value which indicate the top-most level i.e. the parent.
Even if i include "...where parentcategoryid is not null" , it will
still display the message.
May i know why? And how do I correct this error...and get it to display
accordingly.
I started using Reporting Service 2-3 months ago.
I would really appreciate if someone could assist me on this.
Thanks in advance.|||Hi I too got the same warning, the field that I used in the grouping is a unique identifier (ProjectID). When I replace it with ProjectName, it worked without any warning.
However, I want to use the ID rather that the name as the IDs are Unique but names need not be.
Any help is appreciated.
-Srinivas
From http://www.google.com/search?hl=en&q="The+group+expression+expression+used+in+grouping"+"returned+a+data+type+that+is+not+valid
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Reporting Services Error -- Could not find stored procedure 'spPM_ProjectManager'

Hi All,

I built this report same way like the other reports but in different database. When preview in visual stuodio, it is fine. When deploy to the server, I got this message:

Report Services Error

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'ProjectManager'. (rsErrorExecutingCommand) Get Online Help Could not find stored procedure 'spPM_ReportManager'.

Do you know what cause the error?

Thanks in advance for your help!

Rabbie

Basically the error message is telling you it cant find the stored procedure. I would verify that the shared datasource this report is using in the designer is deployed to the reporting server. If it does exist, I would redeploy it anyway just in case.|||

Somehow the shared datasource's xml file missing initial catalog key/value pair in <ConnectString> tag. Even redeploy the shared datasource, xml file still did not change. I had to go to the server to change the value.

Thanks for your tip, Jonathan!

|||is the s.p. in the new db?