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 SP2 and SQL Server SP3a

My company is preparing to install RS SP2. My SQL Servers are currently running SP3a. Is SQL Server SP4 required to run RS SP2? Thanks.

No, you do not need to upgrade to SP4 for any version of RS. It's a good idea, though.

Reporting Services Slow to wake up

My users have been complaining that the first time they run reports on Reporting Services during the day it takes 30+ seconds for the first report to run. After this initial delay they respond very quickly.

There is considerable interaction with the database through a client application prior to this so the server is not in a sleep mode. It appears to only be an issue with Reporting Services.

Any ideas on how I can get this initial delay to go away?

Thanks in advance.

If the user is accessing reports through Report Manager, reporting services relies on IIS to host it in an AppDomain. IIS will often take several seconds (duration depending on the current use and speed of the machine) to intialize this AppDomain. You can adjust the timeout period of the Report Server AppDomain through IIS. This would minimize the number of startups that have to occur.

Thanks, Jon

Reporting Services setup problem

Hi all,
I've downloaded the SQL Reporting Services eval and am trying to get
it installed but have run into the following problem:
When I get to the step for connecting to the SQL Server instance on
which the ReportServer database will reside I try and connect via SQL
Server account (sa actually). When I supply the sa, password, and
named instance of SQL Server 2000 it gives me the following error
"Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection." I've seen this when trying to connect through
QA via a Domain User Account but not with a SQL Server login.
Has anybody else had any problems similiar to this with Reporting
Services?
My environment...
Version: Microsoft SQL Server 2000 - 8.00.760
on Windows 2003 Server
Connecting to a named instance via TCP/IP
I can successfully connect to QA using the sa account but can't
complete the Reporting services install.
Thanks> When I get to the step for connecting to the SQL Server instance on
> which the ReportServer database will reside I try and connect via SQL
> Server account (sa actually). When I supply the sa, password, and
> named instance of SQL Server 2000 it gives me the following error
> "Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection." I've seen this when trying to connect through
> QA via a Domain User Account but not with a SQL Server login.
> Has anybody else had any problems similiar to this with Reporting
> Services?
This happen when SQL machine and Reporting service machine aren't on a
single domain or on a couple of trusted domain.
Try to put the same password on local administrator user on both
machine. When the setup is completed succesfully, you can change the
passwords to a different couple again...
HTH
N|||Hi there
If its a domain member server, connect as the domain administrator,
for some reason RS uses your builtin/administrator account to verify
the account BEFORE it uses the details you typed in (why? because if
it doesnt fine the user it will prompt you re its creation if you
choose to do so).
In my case, I was logged in locally to the member as administrator,
then got the trusted connection error. I can connect fine via my SQL
user, but RS simply ignored whatever i typed and didnt attempt to use
this in its first set of checks.
With the domain admin account, my SQL authentication to my
ReportServer repository then worked a treat on install.
Cheers
Ck|||I am having the same problem.
I am trying to install Reporting Services on a Windows 2000 Server Box (Box
A) in domain A.
The SQL Server 2000 - Windows 2000 Server (Box B) that I am trying to attach
to resides in domain B. I am logged into Box as the Domain Admin (A/Admini
strator).
All three options "Service Account", "Domain Account", and "SQL Login Accoun
t" fail. I get the same error as described in previous posts.
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL S
erver connection."
I want to login using "SQL Login Account".
I have tried the suggestions posted here with no luck.
Any help would be appreciated.
-- Justin Bigelow wrote: --
Hi all,
I've downloaded the SQL Reporting Services eval and am trying to get
it installed but have run into the following problem:
When I get to the step for connecting to the SQL Server instance on
which the ReportServer database will reside I try and connect via SQL
Server account (sa actually). When I supply the sa, password, and
named instance of SQL Server 2000 it gives me the following error
"Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection." I've seen this when trying to connect through
QA via a Domain User Account but not with a SQL Server login.
Has anybody else had any problems similiar to this with Reporting
Services?
My environment...
Version: Microsoft SQL Server 2000 - 8.00.760
on Windows 2003 Server
Connecting to a named instance via TCP/IP
I can successfully connect to QA using the sa account but can't
complete the Reporting services install.
Thanks|||Make sure your SQL install allows SQL logins. On your SQL box, open Query
Analyzer, and attempt to log on with your SQL credentials.
That's the error you usually get when you try to login with a SQL login, but
SQL is configured for Windows Integrated only.
Jeff
"Eric" <removethis eric dot avery at durakon dot com removethis> wrote in
message news:56092B36-FCAA-4D67-915D-7FD4CC8282B8@.microsoft.com...
> I am having the same problem.
> I am trying to install Reporting Services on a Windows 2000 Server Box
(Box A) in domain A.
> The SQL Server 2000 - Windows 2000 Server (Box B) that I am trying to
attach to resides in domain B. I am logged into Box as the Domain Admin
(A/Administrator).
> All three options "Service Account", "Domain Account", and "SQL Login
Account" fail. I get the same error as described in previous posts.
> "Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
> I want to login using "SQL Login Account".
> I have tried the suggestions posted here with no luck.
> Any help would be appreciated.
> -- Justin Bigelow wrote: --
> Hi all,
> I've downloaded the SQL Reporting Services eval and am trying to get
> it installed but have run into the following problem:
> When I get to the step for connecting to the SQL Server instance on
> which the ReportServer database will reside I try and connect via SQL
> Server account (sa actually). When I supply the sa, password, and
> named instance of SQL Server 2000 it gives me the following error
> "Login failed for user '(null)'. Reason: Not associated with a
trusted
> SQL Server connection." I've seen this when trying to connect through
> QA via a Domain User Account but not with a SQL Server login.
> Has anybody else had any problems similiar to this with Reporting
> Services?
> My environment...
> Version: Microsoft SQL Server 2000 - 8.00.760
> on Windows 2003 Server
> Connecting to a named instance via TCP/IP
> I can successfully connect to QA using the sa account but can't
> complete the Reporting services install.
> Thanks
>|||SQL Server is set-up for Mixed Mode Authentication and I have tested and suc
cessfully connected to it using ODBC/OLEDB connection strings from the box I
am trying to install SQL Server Reporting Services. I am still getting the
error as mentioned earlier
. Additional Information: Domain A has active directory, Domain B has an NT
classic domain. Domain A and Domain B are seperated by a firewall. The fir
ewall has 3 NICs - 1 going to Domain A, 1 to Domain B, and 1 to an Internet
Router. Is there something
that has to be allowed on the firewall?
Thanks!
-- Jeff Dillon wrote: --
Make sure your SQL install allows SQL logins. On your SQL box, open Query
Analyzer, and attempt to log on with your SQL credentials.
That's the error you usually get when you try to login with a SQL login, but
SQL is configured for Windows Integrated only.
Jeff
"Eric" <removethis eric dot avery at durakon dot com removethis> wrote in
message news:56092B36-FCAA-4D67-915D-7FD4CC8282B8@.microsoft.com...
> I am having the same problem.
(Box A) in domain A.
> The SQL Server 2000 - Windows 2000 Server (Box B) that I am trying to
attach to resides in domain B. I am logged into Box as the Domain Admin
(A/Administrator).
> All three options "Service Account", "Domain Account", and "SQL Login
Account" fail. I get the same error as described in previous posts.
> "Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
> it installed but have run into the following problem:
> which the ReportServer database will reside I try and connect via SQL
> Server account (sa actually). When I supply the sa, password, and
> named instance of SQL Server 2000 it gives me the following error
> "Login failed for user '(null)'. Reason: Not associated with a
trusted
> SQL Server connection." I've seen this when trying to connect through
> QA via a Domain User Account but not with a SQL Server login.
> Services?
> Version: Microsoft SQL Server 2000 - 8.00.760
> on Windows 2003 Server
> Connecting to a named instance via TCP/IP
> complete the Reporting services install.
>|||See my other thread for my progress
http://communities2.microsoft.com/c...b3-92fb1b49099d

Reporting Services setup problem

Hi all,
I've downloaded the SQL Reporting Services eval and am trying to get
it installed but have run into the following problem:
When I get to the step for connecting to the SQL Server instance on
which the ReportServer database will reside I try and connect via SQL
Server account (sa actually). When I supply the sa, password, and
named instance of SQL Server 2000 it gives me the following error
"Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection." I've seen this when trying to connect through
QA via a Domain User Account but not with a SQL Server login.
Has anybody else had any problems similiar to this with Reporting
Services?
My environment...
Version: Microsoft SQL Server 2000 - 8.00.760
on Windows 2003 Server
Connecting to a named instance via TCP/IP
I can successfully connect to QA using the sa account but can't
complete the Reporting services install.
Thanks> When I get to the step for connecting to the SQL Server instance on
> which the ReportServer database will reside I try and connect via SQL
> Server account (sa actually). When I supply the sa, password, and
> named instance of SQL Server 2000 it gives me the following error
> "Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection." I've seen this when trying to connect through
> QA via a Domain User Account but not with a SQL Server login.
> Has anybody else had any problems similiar to this with Reporting
> Services?
This happen when SQL machine and Reporting service machine aren't on a
single domain or on a couple of trusted domain.
Try to put the same password on local administrator user on both
machine. When the setup is completed succesfully, you can change the
passwords to a different couple again...
HTH
N|||Hi there
If its a domain member server, connect as the domain administrator,
for some reason RS uses your builtin/administrator account to verify
the account BEFORE it uses the details you typed in (why? because if
it doesnt fine the user it will prompt you re its creation if you
choose to do so).
In my case, I was logged in locally to the member as administrator,
then got the trusted connection error. I can connect fine via my SQL
user, but RS simply ignored whatever i typed and didnt attempt to use
this in its first set of checks.
With the domain admin account, my SQL authentication to my
ReportServer repository then worked a treat on install.
Cheers
Ck

Reporting Services Setup Issue

Hi All,

I am running setup.exe (RS 2000 Enterprise), and ra issue during the following step -

"specify credentials that are used at run time by ReportServer service to connect ot SQL Server Instance..."

My local SQL Server is running under "Local System" account, and I have SQL Server 2000 SP4 installed on Windows Server 2003.

Any help to resolve this setup issue is much appreciated!

-Lawrence

you trying setup RS 2000 Developer

because you using Server Operation System

end which page erroring

|||You need to provide credentials that Report Server services will use in order to access the catalog at run-time. You can choose to have the services impersonate their associated accounts, or you can specify separate Windows or SQL credentials. If you specify separate credentials, these credentials will be stored (encrypted) on the server config file.|||

I specified Windows & SQL credentials, but they both failed with the error message I mentioned. I am able to login to SQL Server with those account, so I am not sure why RS installation is rejecting them. Any ideas why?

-Lawrence

|||

Hello All,

I'm still encountering errors during install of RS 2000 on Windows Server 2003. Error message is in regards to operating system not match up with RS 2000 requirement. I thought RS 2000 can be installed on Windows Server 2003, but MS could be wrong. Let me know if any of you have a workaround.

Thanks,

-Lawrence

Reporting Services Setup Issue

Hi All,

I am running setup.exe (RS 2000 Enterprise), and ra issue during the following step -

"specify credentials that are used at run time by ReportServer service to connect ot SQL Server Instance..."

My local SQL Server is running under "Local System" account, and I have SQL Server 2000 SP4 installed on Windows Server 2003.

Any help to resolve this setup issue is much appreciated!

-Lawrence

you trying setup RS 2000 Developer

because you using Server Operation System

end which page erroring

|||You need to provide credentials that Report Server services will use in order to access the catalog at run-time. You can choose to have the services impersonate their associated accounts, or you can specify separate Windows or SQL credentials. If you specify separate credentials, these credentials will be stored (encrypted) on the server config file.|||

I specified Windows & SQL credentials, but they both failed with the error message I mentioned. I am able to login to SQL Server with those account, so I am not sure why RS installation is rejecting them. Any ideas why?

-Lawrence

|||

Hello All,

I'm still encountering errors during install of RS 2000 on Windows Server 2003. Error message is in regards to operating system not match up with RS 2000 requirement. I thought RS 2000 can be installed on Windows Server 2003, but MS could be wrong. Let me know if any of you have a workaround.

Thanks,

-Lawrence

sql

Reporting Services setup

I installed and started playing around with Reporting Services and realized that you are required to run IIS on your SQLserver in order to use RS. Is this 100% true?
Please tell me there is an alternative...this is huge roadblock due to company policy.

Thanks.Nevermind ...I found the answer here:MSDN library-Reporting Services

reporting services reporting

I am new to reporting services and I have heard that there are reports
available to report on reporting services. IE. Which reports are run, how
long they run, how often they are run... Can somebody tell me where to
find those reports?
Thanks,
RonYes, these are the execution log reports which come as a sample on the RS
2000 installation CD. See MSDN for more details on how to use them:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_88gy.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <ron@.myalias.postalias> wrote in message
news:Xns96EDA8BD514E9WBT39212565@.207.46.248.16...
>I am new to reporting services and I have heard that there are reports
> available to report on reporting services. IE. Which reports are run, how
> long they run, how often they are run... Can somebody tell me where to
> find those reports?
> Thanks,
> Ron|||"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
news:efmnDW6zFHA.3720@.TK2MSFTNGP14.phx.gbl:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmi
> n/htm/arp_rslogfiles_v1_88gy.asp
Thanks!!!!!

Reporting Services report

Just wondering if anyone would know why a report would work fine for around
a week, running within 6-7 seconds, then suddenly take 25 minutes to run?
Doing an update stats appears to cure the problem. The source database is
loaded several times each day. Running the SQL statement directly in SQL
Management studio, even before the update stats is done, retrieves the data
in seconds, so it wouldn't appear to be a SQL problem. I have seen articles
hinting at reporting services having its own statistics collection? but only
where cubes are involved, there is no cube in the data this report needs. We
are running SQL server 2005 service pack 2.
thanks for any help in advance
Mikey :o)Is this against a stored procedure. RS does not have its own statistics
collection against SQL Server (I have no experience with cubes but I don't
it there as well). There is an issue for some stored procedures (I haven't
seen this but it might occur more in your type of environment). If a stored
procedure you might try using the Recompile option on the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mikey :0)" <Mikey0@.discussions.microsoft.com> wrote in message
news:F8215493-7817-47F5-9DD8-6E3C1026A692@.microsoft.com...
> Just wondering if anyone would know why a report would work fine for
> around
> a week, running within 6-7 seconds, then suddenly take 25 minutes to run?
> Doing an update stats appears to cure the problem. The source database is
> loaded several times each day. Running the SQL statement directly in SQL
> Management studio, even before the update stats is done, retrieves the
> data
> in seconds, so it wouldn't appear to be a SQL problem. I have seen
> articles
> hinting at reporting services having its own statistics collection? but
> only
> where cubes are involved, there is no cube in the data this report needs.
> We
> are running SQL server 2005 service pack 2.
> thanks for any help in advance
> Mikey :o)|||Bruce, thanks for responding, the report currently doesn't use a stored
procedure, it was one of the options I was considering as a work-around as it
appears the it might be something that Reporting services is doing with the
SQL command? I was thinking that by taking the SQL out of the report and
instead calling a Stored procedure, it would not be able to change the sql.
This is backed up by the fact that when this report runs slowly, the SQL can
be executed in query analyser with no problems.
"Bruce L-C [MVP]" wrote:
> Is this against a stored procedure. RS does not have its own statistics
> collection against SQL Server (I have no experience with cubes but I don't
> it there as well). There is an issue for some stored procedures (I haven't
> seen this but it might occur more in your type of environment). If a stored
> procedure you might try using the Recompile option on the stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mikey :0)" <Mikey0@.discussions.microsoft.com> wrote in message
> news:F8215493-7817-47F5-9DD8-6E3C1026A692@.microsoft.com...
> > Just wondering if anyone would know why a report would work fine for
> > around
> > a week, running within 6-7 seconds, then suddenly take 25 minutes to run?
> > Doing an update stats appears to cure the problem. The source database is
> > loaded several times each day. Running the SQL statement directly in SQL
> > Management studio, even before the update stats is done, retrieves the
> > data
> > in seconds, so it wouldn't appear to be a SQL problem. I have seen
> > articles
> > hinting at reporting services having its own statistics collection? but
> > only
> > where cubes are involved, there is no cube in the data this report needs.
> > We
> > are running SQL server 2005 service pack 2.
> >
> > thanks for any help in advance
> >
> > Mikey :o)
>
>

Reporting Services Rendering is Slow

I use the web service as well as intergrated url link to display, but both
display the same characteristics. The first run is extreemly slow. We
could be talking up to 1 minute to generate a pretty basic report.
When the same report with a differnet parameter or even a different report
is run the display is immediate.
The server it sits on has not been rebooted, but if i run the same report
the next day then it runs as if its the first time its run.
Is there some kind of setting I've missed in IIS or SRS that is obviously
unloading all the compiled referneces to SRS. I understand that .net
application are slow on first execution due to compiling etc.
Can anyone help?the first time you access Reporting Service, IIS must compile the webpages.
a w3wp process starts and compile the page and then start the rendering.
after some times, the application pool is killed to released the resources
on the server.
So go in your application pool (in IIS) and change the recycle parameters.
and also you can create a scheduled task to wakeup Reporting Services.
"Namshub" <Ricchard.Pullen@.Southend.nhs.uk.RemoveMyspam> wrote in message
news:Oi#tWk8iHHA.4516@.TK2MSFTNGP03.phx.gbl...
>I use the web service as well as intergrated url link to display, but both
>display the same characteristics. The first run is extreemly slow. We
>could be talking up to 1 minute to generate a pretty basic report.
> When the same report with a differnet parameter or even a different report
> is run the display is immediate.
> The server it sits on has not been rebooted, but if i run the same report
> the next day then it runs as if its the first time its run.
> Is there some kind of setting I've missed in IIS or SRS that is obviously
> unloading all the compiled referneces to SRS. I understand that .net
> application are slow on first execution due to compiling etc.
> Can anyone help?
>
>|||maybe you should stop using MDB as a database
"Namshub" <Ricchard.Pullen@.Southend.nhs.uk.RemoveMyspam> wrote in message
news:Oi%23tWk8iHHA.4516@.TK2MSFTNGP03.phx.gbl...
> I use the web service as well as intergrated url link to display, but
both
> display the same characteristics. The first run is extreemly slow. We
> could be talking up to 1 minute to generate a pretty basic report.
> When the same report with a differnet parameter or even a different report
> is run the display is immediate.
> The server it sits on has not been rebooted, but if i run the same report
> the next day then it runs as if its the first time its run.
> Is there some kind of setting I've missed in IIS or SRS that is obviously
> unloading all the compiled referneces to SRS. I understand that .net
> application are slow on first execution due to compiling etc.
> Can anyone help?
>
>|||Snotty but not the reason. Note that it is the first time that he has
problems, not subsequent times. This is totally an IIS issue and the fact
that asp.net application (which is what RS is) gets unloaded.
Detailed instructions for solution is: Go to IIS Manager, under Applocation
Pools node, right click "DefaultAppPool" in which the Reporting Server work
process is running, select properties. On "Performace" tag, you will see,
by default, the app pool will shut down if > being idle for 20 min. You can
extend this time to 8x60min 480min, so that the app pool will not shut down
for a regular working day. However, the first report reader of the day,
will hit the delay. You may schedule a dummy report at beginning of a work
day for this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aaron Kempf" <akempf@.dol.wa.gov> wrote in message
news:uAzPTfdjHHA.208@.TK2MSFTNGP05.phx.gbl...
> maybe you should stop using MDB as a database
>
> "Namshub" <Ricchard.Pullen@.Southend.nhs.uk.RemoveMyspam> wrote in message
> news:Oi%23tWk8iHHA.4516@.TK2MSFTNGP03.phx.gbl...
>> I use the web service as well as intergrated url link to display, but
> both
>> display the same characteristics. The first run is extreemly slow. We
>> could be talking up to 1 minute to generate a pretty basic report.
>> When the same report with a differnet parameter or even a different
>> report
>> is run the display is immediate.
>> The server it sits on has not been rebooted, but if i run the same report
>> the next day then it runs as if its the first time its run.
>> Is there some kind of setting I've missed in IIS or SRS that is obviously
>> unloading all the compiled referneces to SRS. I understand that .net
>> application are slow on first execution due to compiling etc.
>> Can anyone help?
>>
>

Reporting Services Rendering

We use Reporting Services for most of our reporting needs internally. However, we do have several products that we ship out into the field that run off local databases. These applications require reporting but often are not big enough for SQL Server and rarely run on a server with IIS.

Since MS has made .RDL an open xml standard does anyone know if there are any client rendering engines being developed? We would like the ability to develop all of our reports in .RDL and have them rendered in Reporting Services or in our standalone apps that run off MSDE or Access. Currently this is not possible. But, I think that someone could write a client renderer (like Crystal Reports has) to allow the use of .RDL files off a local data source without SQL Server and Web Services.

Thanks for any info on the subject.

SmokeAs far as I understand it, the .rdl files can only be put onto a SQL Server. Basically, it seems that Microsoft wants to give you the ability to access those reports through just about anything (ASP.Net, jsp pages) that can connect to an XML web service, but the actual files themselves are proprietary to just SQL Server boxes with the reporting services installed. Have you ever thought about creating those reports on a server and connecting to them through those programs across the internet and encrypting them?

reporting services receiving an error

When I try and run a report in mom2005 I get the following message:
Reporting Services Error
----
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set 'ComputerList'. (rsErrorExecutingCommand) Get Online Help
Invalid object name 'SC_Class_Rel_Computer-Exchange_View'.
My software is up to date with all patches and packs.. I am really stuck. could someone please help.. Thanks Lisa
----
--
Message posted via http://www.sqlmonster.comI ran into the same problem.
Here is what I found to fix it:
http://groups-beta.google.com/group/microsoft.public.mom/browse_frm/thread/160cc0d914b28ae4/d4fb34cf84beb0e9?q=SystemCenterReporting+log+file&_done=%2Fgroups%3Fq%3DSystemCenterReporting+log+file%26&_doneTitle=Back+to+Search&&d#d4fb34cf84beb0e9
"Lisa Roy via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:0a8a6903bc44493cbf3e9412dcab6ac6@.SQLMonster.com...
> When I try and run a report in mom2005 I get the following message:
>
> Reporting Services Error
> ----
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Query execution failed for data set 'ComputerList'.
> (rsErrorExecutingCommand) Get Online Help
> Invalid object name 'SC_Class_Rel_Computer-Exchange_View'.
> My software is up to date with all patches and packs.. I am really stuck.
> could someone please help.. Thanks Lisa
> ----
> --
> Message posted via http://www.sqlmonster.com

Reporting Services Over the Internet

Can anybody help.
I want to run reporting services on a web server (DMZ and Firewall) and use
NT authentication, but when i try to run a report and the data source falls
over saying it can not found the login.
I have created the login (test) on both the Domain and web server (same
password)
Regards
Gary
Direct e-mail gary.baker@.mentec.co.ukI assume you've tried SQL Login instead of NT Auth for your data source?
And the SQL box and web server are both in the same domain?
Jeff
"Gary Baker" <gary.baker@.mentec.co.uk> wrote in message
news:OpKj#IllEHA.2820@.TK2MSFTNGP15.phx.gbl...
> Can anybody help.
> I want to run reporting services on a web server (DMZ and Firewall) and
use
> NT authentication, but when i try to run a report and the data source
falls
> over saying it can not found the login.
> I have created the login (test) on both the Domain and web server (same
> password)
> Regards
>
> Gary
>
> Direct e-mail gary.baker@.mentec.co.uk
>

Reporting services offline

Hi all,
Can any one help me how reporting services work offline .I heard in
Sqlserver2005 reporting services ,there is a component which ables to run
reporting services on client system also.
Please kindly help me.
I am very much in need of it.
thanks in advance to all
miikuHi,
The components you mention are not included in SQL Server 2005 but in Visual
Studio 2005.
Best regards,
Jordi Rambla
MVP SQL Server (Reporting Services)
Solid Quality Learning (http://www.solidqualitylearning.com)
"aka" <aka@.discussions.microsoft.com> escribió en el mensaje
news:C0C2FDC8-D4B8-423E-87A7-A28332127C10@.microsoft.com...
> Hi all,
> Can any one help me how reporting services work offline .I heard in
> Sqlserver2005 reporting services ,there is a component which ables to run
> reporting services on client system also.
> Please kindly help me.
> I am very much in need of it.
> thanks in advance to all
> miiku|||Hi Jordi Rambla,
Thank you so much for your reply. Can you please provide me more details
of that components present in Visualstudio 2005. I am very much in need of
help.
Please kindly help me..
thanks in advance
miiku
"Jordi Rambla" wrote:
> Hi,
> The components you mention are not included in SQL Server 2005 but in Visual
> Studio 2005.
> Best regards,
> Jordi Rambla
> MVP SQL Server (Reporting Services)
> Solid Quality Learning (http://www.solidqualitylearning.com)
>
> "aka" <aka@.discussions.microsoft.com> escribió en el mensaje
> news:C0C2FDC8-D4B8-423E-87A7-A28332127C10@.microsoft.com...
> > Hi all,
> > Can any one help me how reporting services work offline .I heard in
> > Sqlserver2005 reporting services ,there is a component which ables to run
> > reporting services on client system also.
> > Please kindly help me.
> > I am very much in need of it.
> > thanks in advance to all
> > miiku
>
>

Reporting Services June CTP

Does anyone know if the June CTP will run on the release version of .NET 2.0 currently I have this running on version 2.0.50215 but i need to remove that to install the release version of .NET 2.0 but I dont want to uninstall the June CTP of SQL 2005 yet.

Thanks in advance
MartinI'm not positive, but I don't think you're going to be able to do this - The June CTP is based on Whidbey Beta 2, and I'm betting a fair number of changes happened between B2 and RTM...

Reporting Services in Express SP2

Can reports I created in SQL Server 2005 using Reporting Services run on SQL
Express 2005 with SP2? I am a developer and some of our clients use SQL
Express. Thank you.
DavidOn Feb 27, 4:38 pm, "David" <dlch...@.lifetimeinc.com> wrote:
> Can reports I created in SQL Server 2005 using Reporting Services run on SQL
> Express 2005 with SP2? I am a developer and some of our clients use SQL
> Express. Thank you.
> David
Prior to SP2, this was no problem. I would assume the same afterwards;
however, someone else may know for certain.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I thought it still requires and SQL Server license, correct? So if a small
company only uses SQL Express then I think they cannot install Reporting
Services. Can anyone verify that for me or point me to documentation?
Thanks.
David
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1172635810.340693.99970@.8g2000cwh.googlegroups.com...
> On Feb 27, 4:38 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>> Can reports I created in SQL Server 2005 using Reporting Services run on
>> SQL
>> Express 2005 with SP2? I am a developer and some of our clients use SQL
>> Express. Thank you.
>> David
> Prior to SP2, this was no problem. I would assume the same afterwards;
> however, someone else may know for certain.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||If the data is local to SQL Server Express there is a version of SQL Server
Express that comes with RS.
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
http://msdn.microsoft.com/vstudio/express/sql/
It is free with the one caveat. The data being reported off of is local
(i.e. on express as well). You cannot use this to report off of other SQL
Servers, or Oracle or etc etc.
"David" <dlchase@.lifetimeinc.com> wrote in message
news:eWXCZj0WHHA.4240@.TK2MSFTNGP06.phx.gbl...
>I thought it still requires and SQL Server license, correct? So if a small
>company only uses SQL Express then I think they cannot install Reporting
>Services. Can anyone verify that for me or point me to documentation?
>Thanks.
> David
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1172635810.340693.99970@.8g2000cwh.googlegroups.com...
>> On Feb 27, 4:38 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>> Can reports I created in SQL Server 2005 using Reporting Services run on
>> SQL
>> Express 2005 with SP2? I am a developer and some of our clients use SQL
>> Express. Thank you.
>> David
>> Prior to SP2, this was no problem. I would assume the same afterwards;
>> however, someone else may know for certain.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>|||That is ok as this will be the only database reported on. Are all output
formats available (PDF, Excel, etc.)? Thanks.
David
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uJ09dY1WHHA.1200@.TK2MSFTNGP04.phx.gbl...
> If the data is local to SQL Server Express there is a version of SQL
> Server Express that comes with RS.
> http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
> http://msdn.microsoft.com/vstudio/express/sql/
> It is free with the one caveat. The data being reported off of is local
> (i.e. on express as well). You cannot use this to report off of other SQL
> Servers, or Oracle or etc etc.
>
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:eWXCZj0WHHA.4240@.TK2MSFTNGP06.phx.gbl...
>>I thought it still requires and SQL Server license, correct? So if a
>>small company only uses SQL Express then I think they cannot install
>>Reporting Services. Can anyone verify that for me or point me to
>>documentation? Thanks.
>> David
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1172635810.340693.99970@.8g2000cwh.googlegroups.com...
>> On Feb 27, 4:38 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>> Can reports I created in SQL Server 2005 using Reporting Services run
>> on SQL
>> Express 2005 with SP2? I am a developer and some of our clients use
>> SQL
>> Express. Thank you.
>> David
>> Prior to SP2, this was no problem. I would assume the same afterwards;
>> however, someone else may know for certain.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>
>|||http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <dlchase@.lifetimeinc.com> wrote in message
news:%2349vdu1WHHA.4624@.TK2MSFTNGP03.phx.gbl...
> That is ok as this will be the only database reported on. Are all output
> formats available (PDF, Excel, etc.)? Thanks.
> David
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uJ09dY1WHHA.1200@.TK2MSFTNGP04.phx.gbl...
>> If the data is local to SQL Server Express there is a version of SQL
>> Server Express that comes with RS.
>> http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
>> http://msdn.microsoft.com/vstudio/express/sql/
>> It is free with the one caveat. The data being reported off of is local
>> (i.e. on express as well). You cannot use this to report off of other SQL
>> Servers, or Oracle or etc etc.
>>
>> "David" <dlchase@.lifetimeinc.com> wrote in message
>> news:eWXCZj0WHHA.4240@.TK2MSFTNGP06.phx.gbl...
>>I thought it still requires and SQL Server license, correct? So if a
>>small company only uses SQL Express then I think they cannot install
>>Reporting Services. Can anyone verify that for me or point me to
>>documentation? Thanks.
>> David
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1172635810.340693.99970@.8g2000cwh.googlegroups.com...
>> On Feb 27, 4:38 pm, "David" <dlch...@.lifetimeinc.com> wrote:
>> Can reports I created in SQL Server 2005 using Reporting Services run
>> on SQL
>> Express 2005 with SP2? I am a developer and some of our clients use
>> SQL
>> Express. Thank you.
>> David
>> Prior to SP2, this was no problem. I would assume the same afterwards;
>> however, someone else may know for certain.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>
>>
>

Reporting Services Help

I am trying to use Report builder in both SQL 2005 and SQL 2000( which does not exist in the version). I am trying to run the reports off of my created vies.
Cany one one explain how to do this.

My process is I open VS 2005 Business Itellgence and select Report Model.
Then I selecet my datasource , then my Data source view, then I create my Model.

However, it only allows me to view mt Tables, not my created views or created Queries.

I need to know how to get my views to be accessible and also is Reporting Model Available for SQL 2000 reporting Services and if not why.

thanks.

danjaman:

also is Reporting Model Available for SQL 2000 reporting Services and if not why.

Report Model is not available for SSRS 2000. It is only available for SSRS 2005

Reporting Services General

Hi
There are some questions about the Microsoft Reporting Services:
1. We have a SQL Server 2000, but can I also run this reporting service on a
SQL 7 ?
2. Can I set the positions of the objects (eg. rectangle) dynmically? (in
asp)
greetz patrickYou can use SQL 7 as the place the data for the report resides but RS needs
SQL Server 2000 sp3a to run (you can hit many many different databases for
the source of the data: Sybase, DB2, Oracle, SQL Server etc).
As far as #2, you don't want to go down that path. Although technically it
is possible it would be a royal pain. You would have to generate the RDL
(report definition language, the report has an XML schema that defines it)
on the fly, deploy it. Have it be unique name because otherwise if multiple
users are trying to get to a report they could get the wrong one, etc.
RS has lots of things that occur dynamically (can grow etc) so it might be
that the reason you want to dynamically set the position of the object can
be done in some other way.
Bruce L-C
"Patrick" <horusspam@.yahoo.de> wrote in message
news:%23yxWk1xnEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi
> There are some questions about the Microsoft Reporting Services:
> 1. We have a SQL Server 2000, but can I also run this reporting service on
a
> SQL 7 ?
> 2. Can I set the positions of the objects (eg. rectangle) dynmically? (in
> asp)
> greetz patrick
>|||Ok, thank you for your answer.
Can you recommand me another (web-)reporting tool?
I'm looking forward to hearing from you
greetz
horus
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:OxMxC6xnEHA.3900@.TK2MSFTNGP10.phx.gbl...
> You can use SQL 7 as the place the data for the report resides but RS
needs
> SQL Server 2000 sp3a to run (you can hit many many different databases for
> the source of the data: Sybase, DB2, Oracle, SQL Server etc).
> As far as #2, you don't want to go down that path. Although technically it
> is possible it would be a royal pain. You would have to generate the RDL
> (report definition language, the report has an XML schema that defines it)
> on the fly, deploy it. Have it be unique name because otherwise if
multiple
> users are trying to get to a report they could get the wrong one, etc.
> RS has lots of things that occur dynamically (can grow etc) so it might be
> that the reason you want to dynamically set the position of the object can
> be done in some other way.
> Bruce L-C
> "Patrick" <horusspam@.yahoo.de> wrote in message
> news:%23yxWk1xnEHA.592@.TK2MSFTNGP11.phx.gbl...
> > Hi
> >
> > There are some questions about the Microsoft Reporting Services:
> >
> > 1. We have a SQL Server 2000, but can I also run this reporting service
on
> a
> > SQL 7 ?
> > 2. Can I set the positions of the objects (eg. rectangle) dynmically?
(in
> > asp)
> >
> > greetz patrick
> >
> >
>|||From your post it is not clear why you feel you need another tool. Is SQL 7
a deal buster? Is it the issue with positioning the objects?
Bruce L-C
"Patrick" <horusspam@.yahoo.de> wrote in message
news:ec$zT9xnEHA.3908@.TK2MSFTNGP09.phx.gbl...
> Ok, thank you for your answer.
> Can you recommand me another (web-)reporting tool?
> I'm looking forward to hearing from you
> greetz
> horus
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
> news:OxMxC6xnEHA.3900@.TK2MSFTNGP10.phx.gbl...
> > You can use SQL 7 as the place the data for the report resides but RS
> needs
> > SQL Server 2000 sp3a to run (you can hit many many different databases
for
> > the source of the data: Sybase, DB2, Oracle, SQL Server etc).
> >
> > As far as #2, you don't want to go down that path. Although technically
it
> > is possible it would be a royal pain. You would have to generate the RDL
> > (report definition language, the report has an XML schema that defines
it)
> > on the fly, deploy it. Have it be unique name because otherwise if
> multiple
> > users are trying to get to a report they could get the wrong one, etc.
> >
> > RS has lots of things that occur dynamically (can grow etc) so it might
be
> > that the reason you want to dynamically set the position of the object
can
> > be done in some other way.
> >
> > Bruce L-C
> >
> > "Patrick" <horusspam@.yahoo.de> wrote in message
> > news:%23yxWk1xnEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > Hi
> > >
> > > There are some questions about the Microsoft Reporting Services:
> > >
> > > 1. We have a SQL Server 2000, but can I also run this reporting
service
> on
> > a
> > > SQL 7 ?
> > > 2. Can I set the positions of the objects (eg. rectangle) dynmically?
> (in
> > > asp)
> > >
> > > greetz patrick
> > >
> > >
> >
> >
>|||I need a reporting tool, where i can dynamically set the position of
objects.
To color them and export to PDF.
The reports have to be webbased.
Do you know any programm, accomplish these criteriums?
Thank you
Patrick
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:OPB8kPynEHA.4004@.TK2MSFTNGP10.phx.gbl...
> From your post it is not clear why you feel you need another tool. Is SQL
7
> a deal buster? Is it the issue with positioning the objects?
> Bruce L-C
> "Patrick" <horusspam@.yahoo.de> wrote in message
> news:ec$zT9xnEHA.3908@.TK2MSFTNGP09.phx.gbl...
> > Ok, thank you for your answer.
> >
> > Can you recommand me another (web-)reporting tool?
> > I'm looking forward to hearing from you
> >
> > greetz
> > horus
> > "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> schrieb im
Newsbeitrag
> > news:OxMxC6xnEHA.3900@.TK2MSFTNGP10.phx.gbl...
> > > You can use SQL 7 as the place the data for the report resides but RS
> > needs
> > > SQL Server 2000 sp3a to run (you can hit many many different databases
> for
> > > the source of the data: Sybase, DB2, Oracle, SQL Server etc).
> > >
> > > As far as #2, you don't want to go down that path. Although
technically
> it
> > > is possible it would be a royal pain. You would have to generate the
RDL
> > > (report definition language, the report has an XML schema that defines
> it)
> > > on the fly, deploy it. Have it be unique name because otherwise if
> > multiple
> > > users are trying to get to a report they could get the wrong one, etc.
> > >
> > > RS has lots of things that occur dynamically (can grow etc) so it
might
> be
> > > that the reason you want to dynamically set the position of the object
> can
> > > be done in some other way.
> > >
> > > Bruce L-C
> > >
> > > "Patrick" <horusspam@.yahoo.de> wrote in message
> > > news:%23yxWk1xnEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > > Hi
> > > >
> > > > There are some questions about the Microsoft Reporting Services:
> > > >
> > > > 1. We have a SQL Server 2000, but can I also run this reporting
> service
> > on
> > > a
> > > > SQL 7 ?
> > > > 2. Can I set the positions of the objects (eg. rectangle)
dynmically?
> > (in
> > > > asp)
> > > >
> > > > greetz patrick
> > > >
> > > >
> > >
> > >
> >
> >
>

Reporting Services Error-URGENT!!!

This is critical. The company I am in runs 3 reports that calculates huge amounts of data and it takes about 45 minutes to run. Unfortunately I get the following error after about 30 minutes. I need to fix this today. No one is cancelling the report it just appears to time out.

Any clue guys?

Reporting Services Error
--

An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set 'DataSet1'. (rsErrorExecutingCommand) Get Online Help
Operation cancelled by user.

Go to the Report Manager and go into site settings. There are settings for report execution timeout.
Hope this helps
-JW

|||thanks that was obvious! I changed it and ran the report again. I will know soon if that fixed it.|||For 2 of the 3 reports that take bout 45 minute to run, changing the timeout setting DID work.

However, the last report takes about one hour to run and the page comes up with an error that says "Page Not Found." I extended the IIS properties for the reporting services to 3 hour for session state and asp script timeout.

Any clues on this one?|||Do you have a timeout set on the query? This is in the dataset properties.

Also, instead of running the report live, try running it and delivering the output to a file share. This will tell you if it is IIS.|||How much of that data is being processed on the server using Stored Procedures and Views? If large datasets are being sent to the client for processing, then the root problem isn't the timeout, it's the design of excessive data processing on the client (in this case Reporting Services is the client) that is likely at fault. This is also one potential situation where analysis services could be used to cache and preprocess the data. Your 45 minute report might be transformed to run in a matter of seconds. An excessively long running report may be the real problem.|||

hi Alex,

how do you solve the rsErrorExecutingCommand error finally? coz i also encounter the same problem as you. appreciate if you could share your solution, thanks

rgds,Catherine

|||

I face the same issue. When a user runs the report himself through http://server/reports and the report runs for more than 60 minutes (1 hour), then , even the execution timeout is set to "NEVER", the report stops exaction in 1 hour with exception:

Exception information example:
Exception type: ReportServerException
Exception message: Execution '3bu40lywe1c3dfuyo4a5in45' cannot be found (rsExecutionNotFound)

The walkaround is to make a subscription that generates this report. For example my report was saved to a server share after 1,5 hours of execution.