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 Projects

I'm trying to find out if it's possible to install VS 2005 and only have the Business Intelligence Project type available? In other words, no Websites, VB.Net, etc...?

Thanks in advance.

If you don't install VS 2005 at all, and only install BI Development Studio during the SQL Server 2005 install, you will get the VS shell with BI project types only.

-- Robert

Reporting Services Project Problem

Hi
Every Time I Try To Start Any Type Of Report Server Project Out Of Visual
Studio 2005 I Get The Following Error:
Could not load file or assembly 'Microsoft.ReportingServices.Designer,
Version=9.0.242.0, Culture=neutral, PublicKeyToken=87987fvf5665vv91' or one
of its dependencies. The system cannot find the file specified.
I'm Running SQL Dev Server 2005
Any idea what I screwed up'
ThanksHello Tom,
If the project was built with an older version (Beta) of the .NET Runtime
then you need to have the version installed in order for your app to run.
The alternative is to rebuild the component using the newer .NET Runtime
and this would be the recommended thing to do. After that you should no
longer be getting this error.
If the issue persists, you may want to consider reinstall .Net 2.0 and
repair VS 2005 to test the situation.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "TomH" <tofoz2@.newsgroups.nospam>
>Subject: Reporting Services Project Problem
>Date: Mon, 23 Jan 2006 09:24:26 -0500
>Lines: 17
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>Message-ID: <#HWDziCIGHA.604@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: 66.216.157.250.static.dejazzd.com 66.216.157.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67308
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hi
>Every Time I Try To Start Any Type Of Report Server Project Out Of Visual
>Studio 2005 I Get The Following Error:
>Could not load file or assembly 'Microsoft.ReportingServices.Designer,
>Version=9.0.242.0, Culture=neutral, PublicKeyToken=87987fvf5665vv91' or one
>of its dependencies. The system cannot find the file specified.
>
>I'm Running SQL Dev Server 2005
>Any idea what I screwed up'
>Thanks
>
>|||Tom, I have the same problem. Was wondering if you were able to
resolve this issue?
Thanks,
Adam
TomH wrote:
> Hi
> Every Time I Try To Start Any Type Of Report Server Project Out Of Visual
> Studio 2005 I Get The Following Error:
> Could not load file or assembly 'Microsoft.ReportingServices.Designer,
> Version=9.0.242.0, Culture=neutral, PublicKeyToken=87987fvf5665vv91' or one
> of its dependencies. The system cannot find the file specified.
>
> I'm Running SQL Dev Server 2005
> Any idea what I screwed up'
> Thankssql

Reporting Services Error

I was trying to export to excel one of the reports from Report Manager
when I encountered this error:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
Access to the path "C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting
Services\RSTempFiles\RSFile_8179fb2e-a742-4fbf-b01f-72c2647b3ed0" is
denied.
Anybody who has an idea what it means and what caused it?
Thanks in advance and have a nice day!Hi
It could be a few things. it is most likely that either the disk is
full or that the ASPNET and SYSTEM users do not have required
permissions on the specified folder. Have you changed permissions on
this folder at all?
Kulgan.|||Are you trying to export to an old version of Excel? I've gotten similar
errors trying to export to Excel 97. XP ( 2002 ) works fine.
"bench" wrote:
> I was trying to export to excel one of the reports from Report Manager
> when I encountered this error:
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException
> was thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException
> was thrown.
> Access to the path "C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting
> Services\RSTempFiles\RSFile_8179fb2e-a742-4fbf-b01f-72c2647b3ed0" is
> denied.
> Anybody who has an idea what it means and what caused it?
> Thanks in advance and have a nice day!
>|||Actually no, i have not changed permissions on this folder. It's
interesting because I have 7 reports and this error only occurs upon
export of one report.|||I am not quite sure. I am exporting from the Report Manager itself. The
version of Excel in my PC is Excel 2003. And one more thing is, I have
7 reports and this error occurs only upon export of one these reports.|||May be you already have an answer by now but are you able to export it to PDF
or other format?
"bench" wrote:
> I am not quite sure. I am exporting from the Report Manager itself. The
> version of Excel in my PC is Excel 2003. And one more thing is, I have
> 7 reports and this error occurs only upon export of one these reports.
>|||Hi I'm also getting "Excel Rendering error" while exporting to excel. i'm
having office 2000 with service pack 3. Can any one help me
Saravanan
"Atul Mehta" wrote:
> May be you already have an answer by now but are you able to export it to PDF
> or other format?
> "bench" wrote:
> > I am not quite sure. I am exporting from the Report Manager itself. The
> > version of Excel in my PC is Excel 2003. And one more thing is, I have
> > 7 reports and this error occurs only upon export of one these reports.
> >
> >|||Do you mean to say that you have Office 2000 on the Report Server? If that's
the case it won't work. You need either office XP or Office 2003 on the
Report server for Excel export to work.
"Saravanan" wrote:
> Hi I'm also getting "Excel Rendering error" while exporting to excel. i'm
> having office 2000 with service pack 3. Can any one help me
> Saravanan
> "Atul Mehta" wrote:
> > May be you already have an answer by now but are you able to export it to PDF
> > or other format?
> >
> > "bench" wrote:
> >
> > > I am not quite sure. I am exporting from the Report Manager itself. The
> > > version of Excel in my PC is Excel 2003. And one more thing is, I have
> > > 7 reports and this error occurs only upon export of one these reports.
> > >
> > >|||I'm getting error message
"Microsoft.Reportingservices.ReportRendering.ReportRendering Exception" was
thrown."(rrRenderingError)
"Microsoft.Reportingservices.ReportRendering.ReportRendering Exception" was
thrown."
"Excel Rendering Extension:Invalid index in the index path"
This is the error i'm getting when i'm trying to export to excel.please
guide me to solve this problem.
"Atul Mehta" wrote:
> Do you mean to say that you have Office 2000 on the Report Server? If that's
> the case it won't work. You need either office XP or Office 2003 on the
> Report server for Excel export to work.
> "Saravanan" wrote:
> >
> > Hi I'm also getting "Excel Rendering error" while exporting to excel. i'm
> > having office 2000 with service pack 3. Can any one help me
> >
> > Saravanan
> > "Atul Mehta" wrote:
> >
> > > May be you already have an answer by now but are you able to export it to PDF
> > > or other format?
> > >
> > > "bench" wrote:
> > >
> > > > I am not quite sure. I am exporting from the Report Manager itself. The
> > > > version of Excel in my PC is Excel 2003. And one more thing is, I have
> > > > 7 reports and this error occurs only upon export of one these reports.
> > > >
> > > >

Reporting Services Error

Hi Friends,
When I am browse http://localhost/ReportServer/Pages/ReportViewer.aspx , I
get this type error below.
Reporting Services Error
----
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Exception of type 'System.Web.HttpUnhandledException' was thrown.
The source of the report definition has not been specified
----
SQL Server Reporting ServicesHi, Alex!
It seems to me that something bad is with your datasource. Either with the
reportserver database itself or the datasources of your reports are badly
configured. Can you specify more details?
"Alex Smith" wrote:
> Hi Friends,
> When I am browse http://localhost/ReportServer/Pages/ReportViewer.aspx , I
> get this type error below.
>
> Reporting Services Error
> ----
> An internal error occurred on the report server. See the error log for more
> details. (rsInternalError) Get Online Help
> Exception of type 'System.Web.HttpUnhandledException' was thrown.
> The source of the report definition has not been specified
> ----
> SQL Server Reporting Services
>
>
>