Poor performance: SQL Server 2000 & Analysis Services with 20% aggregations

I would really appreaciate some advice:

We use SQL 2000 and want to move new hardware but we're experiecing performance problems on the new hardware compared to the old hardware.

Old hardware spec: Entry level intel server, 64b Pentium, 4GB RAM, 1TB Raid 5 SATA , 64 bit Windows 2003

New hardware:HP Intel Zeon Server 3.4 GhZ * 2, 64bit Windows 2003, 6GB RAM, Fibre Optic SAN - 1TG Raid 5

Software on both the same:SQL Server 2000, sp4, 32bit

Data volumes: 15GB DB, 12Cubes, 4virtual, 20dimensions-biggest leave member count of 350000

Problem: Huge performance difference between the entry level server and the new hardware. Huge improvement in performance on the new hardware when populating the DB and running the DTS packages, however, the munite we try and use aggregation the servers comes to an halt. The design of the DB is not our concern as it has been tried and tested many times over and has always given us realatively good performance.

Building the cubes with No aggregation: 4h on new. Then using 20% Aggregation: Indefinately on new vs 19h on old

Hi Jeanne,

check the AS-Properties in the AS-Manager-Tab > Process-Tab

Read-Ahead-Buffer should be 16 MB (or more?)

Process-Buffer-Size is on my machine 2000 MB (!)

Under Environment-Tab

Max Threads: 8

Minimum reserved Ram: 1024

My machine:

2 Xeon 3,6 GHz Hyperthreading, 4 GB RAM, Windows 2003 Enterprise Sp1, SQL 2000 SP4 32Bit (I am working in a governmental institution and MS Germany installed this box).

check this link: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx#EJEAE (Appendix D)

hth

Bernhard

Poll - Stored Procedure vs. Command Text?

Has anyone found a clear benefit to one over the other for complex SQL
in Reporting Services reports?
I've stayed away from stored procedures so that my report development
lifecycle can remain mostly independent from my team's database
migrations, but if there is a clear benefit, I will strongly consider
it.
Thanks!
Mike"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in
news:1123614436.830410.247520@.g14g2000cwa.googlegroups.com:
> Has anyone found a clear benefit to one over the other for complex SQL
> in Reporting Services reports?
> I've stayed away from stored procedures so that my report development
> lifecycle can remain mostly independent from my team's database
> migrations, but if there is a clear benefit, I will strongly consider
> it.
> Thanks!
> Mike
>
I've used both. I prefer to use queries. I'll only used stored procs if 1)
I'll be reusing the same query in a number of reports, and I want to
simplify my life for maintenance, or 2) I need to do some processing on
values prior to returning data.
I query data from 3rd party software and I hate to place stored procs in
those databases. It's too easy to forget them, have have the software
company blow them away during an update.

Polar Charting with Reporting Services

Does anyone know if you can do polar charting with Reporting Services? I have a need to report the polar coordinates of an element and show the polar chart of the data elements. If I can't do polar charting with Reporting Services, does anyone know of a third party product that I can utilize?

Thanks

KM

Dundas Chart controls allow polar charting (www.Dundas.com)

BobP

sql

Pointing an Analysis Services project at a different database

Hello,

Apologies once again for being new to Analysis Services. I am very grateful for the help that I have received from this forum in the past.

The scenario is that a number of cubes have been built from a number of datasource views that point to a particular database. These datasource views contain a significant number of named queries. The views are structured to resemble star schemas, but the database behind them is an OLTP database, not a data warehouse proper.

This database is the development database for the software product sold by my company. I am trying to rebuild my cubes and dimensions based on another database (a customer database). This has identical structure but different data from the development database. I have tried adding a new data source, pointing at the new database, pointing the data source views at this data source, and refreshing them (they show no changes). I then processed my cubes and dimensions, and deployed the project. The dimension data is not refreshed (e.g. branches still show as the test branches in the development database). Setting the WriteEnabled property of the dimensions to True does not correct the problem.

Does anyone know what to do about this?

Grateful for any help,

Ed Allison

Hi,

I have been doing the same. I use Visual Studio. In my project

Change the data source to point to the specific database.

In your solution select the Project menu, Project properties, Configuration Properties->Deployment->Processing Option = FULL and Deployment Mode = Deploy All. The configuration section should point to the right configuration.

HTH,

|||

That works for me, SunilN. Thanks very much for your help.

Ed Allison

Point Lables for the Bar chart

Hi friends,

We are using SQL Server 2005 Reporting Services for our project.In one of our reports we are showing horizontal bar chart along with the values in front the bar. for eg Graph is Asset Class VS Percentage values. We have Equity , FI , Cash , Others as Asset Class along with percentage values. In graph we are displaying Equity first. When we excecute report I can see the percentage values for FI , cash , others but not for equity.

If I change the order and get equity last then I cant see the value of Others . In short the bar which is coming first is not showing value in front of it. So where I am making mistake ?

can any one help me out ?

regards

sandy.....

As you have already discovered, this is discussed in the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=208591&SiteID=1

-- Robert

Point Label not displaying on a bar chart

I am using Reporting Services 2000.

I have one bar chart that has two values and one group. Everything works fine except when two values are returned, then the point label for the top bar never displays. Example

A (has 2 bars)

B (has 2 bars)

Only the top bar for A will not display the point label. I can change the sort order and make A appear first and then the top bar for B doesn't have the point label now. When I change the order of the values, the top bar that didn't have the label has one because it is second from the top, but the new top bar doesn't. Is this a bug or am I doing something wrong. Thanks.

Try to turn on margins for the x-axis.

-- Robert

|||

Hi Robert,

Thank you so much , your solution is working

thx again

ragards,

-sandyee

Point Label not displaying on a bar chart

I am using Reporting Services 2000.

I have one bar chart that has two values and one group. Everything works fine except when two values are returned, then the point label for the top bar never displays. Example

A (has 2 bars)

B (has 2 bars)

Only the top bar for A will not display the point label. I can change the sort order and make A appear first and then the top bar for B doesn't have the point label now. When I change the order of the values, the top bar that didn't have the label has one because it is second from the top, but the new top bar doesn't. Is this a bug or am I doing something wrong. Thanks.

Try to turn on margins for the x-axis.

-- Robert

|||

Hi Robert,

Thank you so much , your solution is working

thx again

ragards,

-sandyee

pls help with expression question

I am converting reports from business objects to reporting services. In
business objects they have a variable called ERRORS created with the
expression
= If (<Value> InList (12 , 500 , 501 , 502 , 503 , 504 , 505 , 530 , 550))
Then 1 Else 0
<value> is a field returned from a stored procedure (so cant do this in the
sql) ... how can I create this field in reporting services? I know I can
create a calculated field and was thinking about IIF(Fields!Value.value
InList( .... ), 1, 0) but I cant use InList and I am not that good with VB
so looking for suggestions. Thanks!MJT, Here's some vb code that I think will work for that.
Public Function InKnownErrors(ByVal ErrorNumber As Int32)
Dim ReturnVal As Int32 = 0
Dim ErrorList() As Int32 = {12, 500, 501, 502, 503, 504, 505, 530,
550}
If Array.BinarySearch(ErrorList, ErrorNumber) >= 0 Then
ReturnVal = 1
End If
Return ReturnVal
End Function
Expression:
=Code.InKnownErrors(450)
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:6000B282-6685-4FC1-ABE7-B1F59EAE7544@.microsoft.com...
>I am converting reports from business objects to reporting services. In
> business objects they have a variable called ERRORS created with the
> expression
> = If (<Value> InList (12 , 500 , 501 , 502 , 503 , 504 , 505 , 530 ,
> 550))
> Then 1 Else 0
> <value> is a field returned from a stored procedure (so cant do this in
> the
> sql) ... how can I create this field in reporting services? I know I can
> create a calculated field and was thinking about IIF(Fields!Value.value
> InList( .... ), 1, 0) but I cant use InList and I am not that good with
> VB
> so looking for suggestions. Thanks!
>|||Thanks Steve for the code example. I wish I could come up with those that
quickly ... someday ...
I was able to get the InStr function to work this way in the textbox:
=IIF(Instr("12 , 500 , 501, 502, 503, 504, 505, 530, 550",
Fields!Value.Value ), 1, 0)
it seems to be working so far. I am checking out the accuracy of the result
now...
so I am grateful to have a backup plan ... thanks!
"Steve MunLeeuw" wrote:
> MJT, Here's some vb code that I think will work for that.
> Public Function InKnownErrors(ByVal ErrorNumber As Int32)
> Dim ReturnVal As Int32 = 0
> Dim ErrorList() As Int32 = {12, 500, 501, 502, 503, 504, 505, 530,
> 550}
> If Array.BinarySearch(ErrorList, ErrorNumber) >= 0 Then
> ReturnVal = 1
> End If
> Return ReturnVal
> End Function
> Expression:
> =Code.InKnownErrors(450)
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:6000B282-6685-4FC1-ABE7-B1F59EAE7544@.microsoft.com...
> >I am converting reports from business objects to reporting services. In
> > business objects they have a variable called ERRORS created with the
> > expression
> > = If (<Value> InList (12 , 500 , 501 , 502 , 503 , 504 , 505 , 530 ,
> > 550))
> > Then 1 Else 0
> >
> > <value> is a field returned from a stored procedure (so cant do this in
> > the
> > sql) ... how can I create this field in reporting services? I know I can
> > create a calculated field and was thinking about IIF(Fields!Value.value
> > InList( .... ), 1, 0) but I cant use InList and I am not that good with
> > VB
> > so looking for suggestions. Thanks!
> >
>
>

Pls Help - can't install Sql Server Reporting Services

I have Sql Server Personal Edition SP3 installed on my PC. I get an error
message when trying to install the reporting services trial package. Does
the reporting services not work with a personal edition of Sql Server?
The error message is "The edition of the report server database is not
supported on the edition of Sql Server 2000 you have chosen".
Thanks,Paul, I will be interested to see any responses you receive I have been
experiencing the same problems for the past 2 days. With what appear to be
the exact same setup you are running XP SP2, SQL 2000 SP3a and trial edition
of reporting services.
"Paul Tilson" wrote:
> I have Sql Server Personal Edition SP3 installed on my PC. I get an error
> message when trying to install the reporting services trial package. Does
> the reporting services not work with a personal edition of Sql Server?
> The error message is "The edition of the report server database is not
> supported on the edition of Sql Server 2000 you have chosen".
> Thanks,
>
>|||The System Requirements page at
http://www.microsoft.com/sql/reporting/productinfo/sysreqs.asp says that
only Standard, Enterprise, and Developer Editions are supported. If you're
just doing this for trial purposes, how about installing the Evaluation
Edition of SQL Server while you're testing?
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Tilson" <tilsonpa@.yahoo.com> wrote in message
news:10o3egfkodqaic4@.corp.supernews.com...
>I have Sql Server Personal Edition SP3 installed on my PC. I get an error
>message when trying to install the reporting services trial package. Does
>the reporting services not work with a personal edition of Sql Server?
> The error message is "The edition of the report server database is not
> supported on the edition of Sql Server 2000 you have chosen".
> Thanks,
>

Plot data as line (is disabled on <Edit Chart Value> dialog)

I have a requirement to plot a line on the Area chart, and am wondering if
Reporting Services even allows us to do that ?
Right now, I am plotting the data using marker (X) however, it does not give
me adequate information of where does this particular Customer record fall on
an Area chart that has Lower Quartiles, Median and Upper Quartiles
(represented by filled area).
Thanks, in advance.Please read this thread, which describes how to combine Area & Line chart:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=fc74b95f-3746-4ac8-bbda-cebc857dc8f7&sloc=en-us
There is also an alternative approach for achieving this type of chart. A
sample is posted in this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=37f3946e-55a7-4b2f-8af5-5c0290a7e4b5&sloc=en-us
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:52C60EAB-560F-4C2F-991D-D61FEE50C0C7@.microsoft.com...
> I have a requirement to plot a line on the Area chart, and am wondering if
> Reporting Services even allows us to do that ?
> Right now, I am plotting the data using marker (X) however, it does not
give
> me adequate information of where does this particular Customer record fall
on
> an Area chart that has Lower Quartiles, Median and Upper Quartiles
> (represented by filled area).
> Thanks, in advance.

Please suggest!

Hi,
While using Reporting services, I have come across the following situation
where the grouping cirteria for the report is defined by the user using ASPX
page.
My problem is, I am not sure how I can send the the grouping preferences to
the Reporting service(I can't send this data as a part of Report parameters
as there is no limit on the number of groups permitted).
Thanks,
RajRaj,
Reports are cheap, and take up no space at all. So make as many reports as
you need that will satisfy your grouping options. On the ASPX have the user
select the grouping options, and your will the call the appropriate report by
name.
rwiethorn
"Raj" wrote:
> Hi,
> While using Reporting services, I have come across the following situation
> where the grouping cirteria for the report is defined by the user using ASPX
> page.
> My problem is, I am not sure how I can send the the grouping preferences to
> the Reporting service(I can't send this data as a part of Report parameters
> as there is no limit on the number of groups permitted).
> Thanks,
> Raj|||Thanks for the reply, rwiethorn.
The solution that you have suggested may not be too practical in my
application as the grouping is quite dynamic and its impossible to create all
the reports at design time.
Thanks again,
Raj
"rwiethorn" wrote:
> Raj,
> Reports are cheap, and take up no space at all. So make as many reports as
> you need that will satisfy your grouping options. On the ASPX have the user
> select the grouping options, and your will the call the appropriate report by
> name.
>
> rwiethorn
>
> "Raj" wrote:
> > Hi,
> >
> > While using Reporting services, I have come across the following situation
> > where the grouping cirteria for the report is defined by the user using ASPX
> > page.
> >
> > My problem is, I am not sure how I can send the the grouping preferences to
> > the Reporting service(I can't send this data as a part of Report parameters
> > as there is no limit on the number of groups permitted).
> >
> > Thanks,
> > Raj

Please Provide INSIGHT - why Winforms or WebParts with RS?

I would appreciate some clairvoyance on this subject:
Why is it necessary to use WINFORMS or WEBPARTS to execute a Reporting Services Report? My naive understanding is that RS is an enterprise reporting tool...
If I want to develop an OLTP application I use something different and if I need to print (via RS) I provide a button that goes to a "un" hyperlink that then provides me the rich functionality of RS.....
So it seems for sure I am missing something here - please and sinncerely give me some insight!
Best reagrds,
Joe

Sorry for the delayed posting, hopefully it can still be useful.

I'm notentirelysure I understand your post so bear with me;
In fact you don't need to use WinForms nor WebParts, you can simply provide the user a url that directly points to the report or let's say an Excel output...

Sample url for Excel output:
http://<your server>/reportserver?/<your path>/<your report name>t&rs:Command=Render&rs:Format=XML&rc:Parameters=False&rc:XSLT=<your xslt file>.xslt&rc:MIMEType=application/vnd.ms-excel&rc:FileExtension=xls
Sample url for a Report output:
http://<your server>/Reportserver?/<your path>/<your report name>&rs:Command=Render
On the other hand you can create your own custom interface to output the reports in the way you see fit, such as data-filled dropdowns or any other control. You can also have any of these controls interact with the report itself
Unfortunately that's a bit more involved and I will not detail it in this post, I may in the near future write an article on this.

Please Help: SQL service cannot start

Hi
I created an account to run MS SQL services and give logon
locally and log on as service permission. Installation was
successful with this service account.
After a few hours later, SQL Agent service hang and cannot
restart. Error message is applicaion fail to initialize.
Therefore MS SQL server service also cannot restart.
When I use LocalSystem to start SQL services, everything
is fine.But I need to use service account because of
security reasons.
When I login with this service account, it immedialy
logout. I cannot login at all.
MS SQL 2000 standard edition with SP3.
Any suggestion is appreciated.
ThanksHi,
Add the user to "power users" group and try.
Thanks
Hari
MCDBA
"Ben" <anonymous@.discussions.microsoft.com> wrote in message
news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
> Hi
> I created an account to run MS SQL services and give logon
> locally and log on as service permission. Installation was
> successful with this service account.
> After a few hours later, SQL Agent service hang and cannot
> restart. Error message is applicaion fail to initialize.
> Therefore MS SQL server service also cannot restart.
> When I use LocalSystem to start SQL services, everything
> is fine.But I need to use service account because of
> security reasons.
> When I login with this service account, it immedialy
> logout. I cannot login at all.
> MS SQL 2000 standard edition with SP3.
> Any suggestion is appreciated.
> Thanks
>|||If when you login using thi sacount your session is terminated there is =clearly something wrong withthe account in some way. have you checked =the event log after your failed loginattempt - any error message =displayed?
Mike john
"Ben" <anonymous@.discussions.microsoft.com> wrote in message =news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
> Hi
> > I created an account to run MS SQL services and give logon > locally and log on as service permission. Installation was > successful with this service account.
> > After a few hours later, SQL Agent service hang and cannot > restart. Error message is applicaion fail to initialize.
> Therefore MS SQL server service also cannot restart.
> When I use LocalSystem to start SQL services, everything > is fine.But I need to use service account because of > security reasons.
> > When I login with this service account, it immedialy > logout. I cannot login at all. > > MS SQL 2000 standard edition with SP3.
> > Any suggestion is appreciated.
> > Thanks
> >|||Hi ben,
If i understand properly, this is what you need... please
refer the article -
http://support.microsoft.com/default.aspx?kbid=283811
it clearly says what permissions u need for the os level
id to start sqlserver.
but to have a short cut - please have an id in the
administrator's group to start mssql server if u do not
want to start with localsystem
regards,
bharath
mcdba
>--Original Message--
>Hi,
>Add the user to "power users" group and try.
>Thanks
>Hari
>MCDBA
>
>"Ben" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00a101c3b6a0$e3136eb0$a301280a@.phx.gbl...
>> Hi
>> I created an account to run MS SQL services and give
logon
>> locally and log on as service permission. Installation
was
>> successful with this service account.
>> After a few hours later, SQL Agent service hang and
cannot
>> restart. Error message is applicaion fail to initialize.
>> Therefore MS SQL server service also cannot restart.
>> When I use LocalSystem to start SQL services, everything
>> is fine.But I need to use service account because of
>> security reasons.
>> When I login with this service account, it immedialy
>> logout. I cannot login at all.
>> MS SQL 2000 standard edition with SP3.
>> Any suggestion is appreciated.
>> Thanks
>>
>
>.
>

Please help. How to set a Parameter in SSRS in runtime? Parameters!myParam.Value property is rea

You can set the default value of a reporting services parameter by any expression.

But with code I'm NOT allowed to do:

Parameters!myParam.Value = CDate("01.01.2007")

This is cause Value property is read-only.

So the question would be:

Are there any way I set a parameter value in runtime ?

Hope anyone can help...

Use VB.Net or C# code (Report -> Report Properties -> Code tab) to store the parameter in some shared variable. Use this variable to set and retrieve value whenever and wherever you need in the report by using the syntax Code.MySharedVar

Shyam

|||

Hi Shyam,

I have tried that also, but the problem is that the calendar control turn into a drop-down combo control when you apply either code or connect it to a dataset.

The parameter is a datetime type. As long as you add logic to the "default value" it showns the calendar control, if you add anything to "available values" the calendar disappears and a combo is shown. Any ida what I can do to keep it displaying the calendar control ?

|||

I guess you are raising a new issue now. I dont think your issue can be solved at the reporting services level.

PLEASE HELP! Unable to connect to SQL Database from Report Man

Hi,
I am working with SQL Reporting services 2000... The report is unable to
connect to a remote datasource as I try to preview it. The database uses both
SQL and Windows Auth.
1. Created a shared datasource in my project
2. Deployed it on the server. Used OverwriteDataSource = false
3. Create a new shared datasource on the server. Gave a connection string
with no username and password.
4. Selected "Credentials stored securely in the report server" and then
entered the SQL server login and passoword there.
5. Changed the report properties in the manager to use this datasource.
6. While trying to "View" via report manager, I am getting the error message
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
Get Online Help
SQL Server does not exist or access denied.
CAN SOMEONE PLEASE HELP ME!!!!!!I too had this problem. The way that I fixed it was by making sure that I
could connect to the SQL server in question from the server that hosted the
reporting services website. It was the that I realized two very important
things.
1. The user profile that reporting services uses to run as a service needs
SQL server access.
2. The user profile that I entered as the login for the report needed to be
qualified with a domain name if it was a windows account.
These may not be your problem, but they were mine. I hope this helps
"PV" wrote:
> Hi,
> I am working with SQL Reporting services 2000... The report is unable to
> connect to a remote datasource as I try to preview it. The database uses both
> SQL and Windows Auth.
> 1. Created a shared datasource in my project
> 2. Deployed it on the server. Used OverwriteDataSource = false
> 3. Create a new shared datasource on the server. Gave a connection string
> with no username and password.
> 4. Selected "Credentials stored securely in the report server" and then
> entered the SQL server login and passoword there.
> 5. Changed the report properties in the manager to use this datasource.
> 6. While trying to "View" via report manager, I am getting the error message
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
> Get Online Help
> SQL Server does not exist or access denied.
>
> CAN SOMEONE PLEASE HELP ME!!!!!!|||I confirmed this... Found out that I could connect from the Reporting
services machine to my SQL Server using the same SQL credentials...
Also gave access to the DB to the user that the Report Manager runs under...
But this still occurs.
"Nikolai Sonin" wrote:
> I too had this problem. The way that I fixed it was by making sure that I
> could connect to the SQL server in question from the server that hosted the
> reporting services website. It was the that I realized two very important
> things.
> 1. The user profile that reporting services uses to run as a service needs
> SQL server access.
> 2. The user profile that I entered as the login for the report needed to be
> qualified with a domain name if it was a windows account.
> These may not be your problem, but they were mine. I hope this helps
>
> "PV" wrote:
> > Hi,
> > I am working with SQL Reporting services 2000... The report is unable to
> > connect to a remote datasource as I try to preview it. The database uses both
> > SQL and Windows Auth.
> >
> > 1. Created a shared datasource in my project
> > 2. Deployed it on the server. Used OverwriteDataSource = false
> > 3. Create a new shared datasource on the server. Gave a connection string
> > with no username and password.
> > 4. Selected "Credentials stored securely in the report server" and then
> > entered the SQL server login and passoword there.
> > 5. Changed the report properties in the manager to use this datasource.
> > 6. While trying to "View" via report manager, I am getting the error message
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
> > Get Online Help
> > SQL Server does not exist or access denied.
> >
> >
> > CAN SOMEONE PLEASE HELP ME!!!!!!|||I'm having a very similar problem, but I have two reports using the
datascource and rendering no problem and a third that gets the
rsErrorOpeningConnection error. This report previews just fine in VS, but
when not when deployed to the server. Anyone know if RS is does some sort of
credential caching?
"PV" wrote:
> I confirmed this... Found out that I could connect from the Reporting
> services machine to my SQL Server using the same SQL credentials...
> Also gave access to the DB to the user that the Report Manager runs under...
> But this still occurs.
>
> "Nikolai Sonin" wrote:
> > I too had this problem. The way that I fixed it was by making sure that I
> > could connect to the SQL server in question from the server that hosted the
> > reporting services website. It was the that I realized two very important
> > things.
> > 1. The user profile that reporting services uses to run as a service needs
> > SQL server access.
> > 2. The user profile that I entered as the login for the report needed to be
> > qualified with a domain name if it was a windows account.
> > These may not be your problem, but they were mine. I hope this helps
> >
> >
> > "PV" wrote:
> >
> > > Hi,
> > > I am working with SQL Reporting services 2000... The report is unable to
> > > connect to a remote datasource as I try to preview it. The database uses both
> > > SQL and Windows Auth.
> > >
> > > 1. Created a shared datasource in my project
> > > 2. Deployed it on the server. Used OverwriteDataSource = false
> > > 3. Create a new shared datasource on the server. Gave a connection string
> > > with no username and password.
> > > 4. Selected "Credentials stored securely in the report server" and then
> > > entered the SQL server login and passoword there.
> > > 5. Changed the report properties in the manager to use this datasource.
> > > 6. While trying to "View" via report manager, I am getting the error message
> > >
> > > An error has occurred during report processing. (rsProcessingAborted) Get
> > > Online Help
> > > Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
> > > Get Online Help
> > > SQL Server does not exist or access denied.
> > >
> > >
> > > CAN SOMEONE PLEASE HELP ME!!!!!!|||There is no credential caching between data sources. Often this is some sort
of network name resolution issue.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leef" <Leef@.discussions.microsoft.com> wrote in message
news:D69B9ED5-00A6-4ECF-9501-C269709E90B8@.microsoft.com...
> I'm having a very similar problem, but I have two reports using the
> datascource and rendering no problem and a third that gets the
> rsErrorOpeningConnection error. This report previews just fine in VS, but
> when not when deployed to the server. Anyone know if RS is does some sort
> of
> credential caching?
> "PV" wrote:
>> I confirmed this... Found out that I could connect from the Reporting
>> services machine to my SQL Server using the same SQL credentials...
>> Also gave access to the DB to the user that the Report Manager runs
>> under...
>> But this still occurs.
>>
>> "Nikolai Sonin" wrote:
>> > I too had this problem. The way that I fixed it was by making sure
>> > that I
>> > could connect to the SQL server in question from the server that hosted
>> > the
>> > reporting services website. It was the that I realized two very
>> > important
>> > things.
>> > 1. The user profile that reporting services uses to run as a service
>> > needs
>> > SQL server access.
>> > 2. The user profile that I entered as the login for the report needed
>> > to be
>> > qualified with a domain name if it was a windows account.
>> > These may not be your problem, but they were mine. I hope this helps
>> >
>> >
>> > "PV" wrote:
>> >
>> > > Hi,
>> > > I am working with SQL Reporting services 2000... The report is
>> > > unable to
>> > > connect to a remote datasource as I try to preview it. The database
>> > > uses both
>> > > SQL and Windows Auth.
>> > >
>> > > 1. Created a shared datasource in my project
>> > > 2. Deployed it on the server. Used OverwriteDataSource = false
>> > > 3. Create a new shared datasource on the server. Gave a connection
>> > > string
>> > > with no username and password.
>> > > 4. Selected "Credentials stored securely in the report server" and
>> > > then
>> > > entered the SQL server login and passoword there.
>> > > 5. Changed the report properties in the manager to use this
>> > > datasource.
>> > > 6. While trying to "View" via report manager, I am getting the error
>> > > message
>> > >
>> > > An error has occurred during report processing. (rsProcessingAborted)
>> > > Get
>> > > Online Help
>> > > Cannot create a connection to data source 'xxxx'.
>> > > (rsErrorOpeningConnection)
>> > > Get Online Help
>> > > SQL Server does not exist or access denied.
>> > >
>> > >
>> > > CAN SOMEONE PLEASE HELP ME!!!!!!|||Thanks! I finally figure it out.
It was a permissions problem, but was masked because I thought it was using
the shared datasource. Instead it was using a report specific datasource that
had the same name. The way I discovered this was by going and taking a look
at the XML code of the problem report.
"Brian Welcker [MSFT]" wrote:
> There is no credential caching between data sources. Often this is some sort
> of network name resolution issue.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Leef" <Leef@.discussions.microsoft.com> wrote in message
> news:D69B9ED5-00A6-4ECF-9501-C269709E90B8@.microsoft.com...
> > I'm having a very similar problem, but I have two reports using the
> > datascource and rendering no problem and a third that gets the
> > rsErrorOpeningConnection error. This report previews just fine in VS, but
> > when not when deployed to the server. Anyone know if RS is does some sort
> > of
> > credential caching?
> >
> > "PV" wrote:
> >
> >> I confirmed this... Found out that I could connect from the Reporting
> >> services machine to my SQL Server using the same SQL credentials...
> >>
> >> Also gave access to the DB to the user that the Report Manager runs
> >> under...
> >>
> >> But this still occurs.
> >>
> >>
> >>
> >> "Nikolai Sonin" wrote:
> >>
> >> > I too had this problem. The way that I fixed it was by making sure
> >> > that I
> >> > could connect to the SQL server in question from the server that hosted
> >> > the
> >> > reporting services website. It was the that I realized two very
> >> > important
> >> > things.
> >> > 1. The user profile that reporting services uses to run as a service
> >> > needs
> >> > SQL server access.
> >> > 2. The user profile that I entered as the login for the report needed
> >> > to be
> >> > qualified with a domain name if it was a windows account.
> >> > These may not be your problem, but they were mine. I hope this helps
> >> >
> >> >
> >> > "PV" wrote:
> >> >
> >> > > Hi,
> >> > > I am working with SQL Reporting services 2000... The report is
> >> > > unable to
> >> > > connect to a remote datasource as I try to preview it. The database
> >> > > uses both
> >> > > SQL and Windows Auth.
> >> > >
> >> > > 1. Created a shared datasource in my project
> >> > > 2. Deployed it on the server. Used OverwriteDataSource = false
> >> > > 3. Create a new shared datasource on the server. Gave a connection
> >> > > string
> >> > > with no username and password.
> >> > > 4. Selected "Credentials stored securely in the report server" and
> >> > > then
> >> > > entered the SQL server login and passoword there.
> >> > > 5. Changed the report properties in the manager to use this
> >> > > datasource.
> >> > > 6. While trying to "View" via report manager, I am getting the error
> >> > > message
> >> > >
> >> > > An error has occurred during report processing. (rsProcessingAborted)
> >> > > Get
> >> > > Online Help
> >> > > Cannot create a connection to data source 'xxxx'.
> >> > > (rsErrorOpeningConnection)
> >> > > Get Online Help
> >> > > SQL Server does not exist or access denied.
> >> > >
> >> > >
> >> > > CAN SOMEONE PLEASE HELP ME!!!!!!
>
>

Please Help! Service Pack update Cannot Verify User with SA login

I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
Services & Manager,Decision Service Objects and Client Components. Afterwards
I tried to run the update that MS put out for Analysis Services and in the
install you have two options: Use user logged on authentication or use SQL
authentication and provide the SA password. I do this and it always says it
could not verify user even though I have even changed the password and know
that it is correct. I am running SQL in Mixed Mode Authentication if this
makes a difference?Also, Service Pack 3 was already installed on this server for SQL, But
Analysis Services and Manager was not. So I installed them. So Do I still
need to run the update. There are 3 different files for updateing SQL. 1.
Database Components
2. Analysis Services
3. Desktop Engine
But I get the error that I mentioned when I run the analysis update!
"Jay" wrote:
> I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
> Services & Manager,Decision Service Objects and Client Components. Afterwards
> I tried to run the update that MS put out for Analysis Services and in the
> install you have two options: Use user logged on authentication or use SQL
> authentication and provide the SA password. I do this and it always says it
> could not verify user even though I have even changed the password and know
> that it is correct. I am running SQL in Mixed Mode Authentication if this
> makes a difference?

Please help! Report Builder model from UDM?

This is my second posting. Please help!
Hello,
I have created a cube in Analysis Services and made many business logic
changes in the Data Source View and dimensions. Can I use this to build
a model for the Report builder? I believe they call this UDM? People will
create ad-hoc reports in report builder.
I know I can use the UDM to create reports in VS.
Thank youto generate a report builder model based on an Analysis Database.
Simply go on the reports web site (http://localhost/reports)
create a new connection to your AS server
click "generate model"
and you have a model based on your OLAP cubes.
Also,
you can create your own model based on the DSV, this model will execute SQL
statements against your source database instead of using the AS server and
MDX statements.
but you have some manual work to do to insure that the model works fine for
your needs. in another hand you have a better control of what you'll display
to the end users (by playing with detailed property)
"ystueker" <ystueker@.discussions.microsoft.com> wrote in message
news:7E96DAE4-F823-4160-A7C1-C8D45772EE6B@.microsoft.com...
> This is my second posting. Please help!
> Hello,
> I have created a cube in Analysis Services and made many business logic
> changes in the Data Source View and dimensions. Can I use this to build
> a model for the Report builder? I believe they call this UDM? People will
> create ad-hoc reports in report builder.
> I know I can use the UDM to create reports in VS.
> Thank you
>|||Thank you. I have tried that but my option for generate model is grayed out'
"Jéjé" wrote:
> to generate a report builder model based on an Analysis Database.
> Simply go on the reports web site (http://localhost/reports)
> create a new connection to your AS server
> click "generate model"
> and you have a model based on your OLAP cubes.
> Also,
> you can create your own model based on the DSV, this model will execute SQL
> statements against your source database instead of using the AS server and
> MDX statements.
> but you have some manual work to do to insure that the model works fine for
> your needs. in another hand you have a better control of what you'll display
> to the end users (by playing with detailed property)
> "ystueker" <ystueker@.discussions.microsoft.com> wrote in message
> news:7E96DAE4-F823-4160-A7C1-C8D45772EE6B@.microsoft.com...
> > This is my second posting. Please help!
> >
> > Hello,
> > I have created a cube in Analysis Services and made many business logic
> > changes in the Data Source View and dimensions. Can I use this to build
> > a model for the Report builder? I believe they call this UDM? People will
> > create ad-hoc reports in report builder.
> >
> > I know I can use the UDM to create reports in VS.
> >
> > Thank you
> >
>
>|||mmm...
you have the same option using the management interface (windows management
application, not web based)
what is your connection string to access your AS server? the one is grayed.
"ystueker" <ystueker@.discussions.microsoft.com> wrote in message
news:4FB35270-504A-41A3-91D0-469A4014018C@.microsoft.com...
> Thank you. I have tried that but my option for generate model is grayed
> out'
> "Jéjé" wrote:
>> to generate a report builder model based on an Analysis Database.
>> Simply go on the reports web site (http://localhost/reports)
>> create a new connection to your AS server
>> click "generate model"
>> and you have a model based on your OLAP cubes.
>> Also,
>> you can create your own model based on the DSV, this model will execute
>> SQL
>> statements against your source database instead of using the AS server
>> and
>> MDX statements.
>> but you have some manual work to do to insure that the model works fine
>> for
>> your needs. in another hand you have a better control of what you'll
>> display
>> to the end users (by playing with detailed property)
>> "ystueker" <ystueker@.discussions.microsoft.com> wrote in message
>> news:7E96DAE4-F823-4160-A7C1-C8D45772EE6B@.microsoft.com...
>> > This is my second posting. Please help!
>> >
>> > Hello,
>> > I have created a cube in Analysis Services and made many business logic
>> > changes in the Data Source View and dimensions. Can I use this to build
>> > a model for the Report builder? I believe they call this UDM? People
>> > will
>> > create ad-hoc reports in report builder.
>> >
>> > I know I can use the UDM to create reports in VS.
>> >
>> > Thank you
>> >
>>