Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

sql

Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

BUG : Data source not appearing

Hello,

I am trying make some imports and I am using the wizard.

I have created a new project and added 2 new datasources. One for a DB2 database and one for a SQL Server database

I select Project | SS Import and Export Wizard and the wizard launches.

When I look under datasource I can't find my 2 created datasources. I can create new ones but this is not something I want.

Any help ?

Constantijn Enders

Are you trying to create data sources, via the 'Data Sources' in the BI Project? if so, the data sources created in there will not be shared/visible in import/export wizard or the package.

Import/Export wizard has no access to project level artifacts, but is simply a way to auto-generate packages. Please try creating a package for your solution.

|||

It would be handy if the import/export wizard could access the project items. Perhaps for a next version.

I am importing from a DB2 system and setting up the oledb connection requires at least 3 screens.

You can only import multiple tables and/or views OR a single query. (not multiple queries)

If you have 10 queries, you have to setup that connection 10 times

Just my 2 cents

CE

Buffer Exception

When running a package created on my local machine i get no errors at all but when i try to run the same package on the server i get an error specifying Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

I have tried changing the defaultbuffersize of the data flow task but this makes no difference. I think that a buffer size for a particular column is being exceed but i cannot find anywhere to set this property.

Has anyone else struck this error?

What key things are different between your local development box and your server? Different locale settings, 32 to 64 bit perhaps?

Browsing Cube

Hi,

I have created, populate and processed a cube successfully and can also browse it successfully on one server. But when I deploy this cube to another server I can create, populate and process the cube sucessfully but when I go to browse the cube through SQL Management Studio I recive the following error:

The query could not be processed: o OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000.

I have checked the security and it all appears to be the same, I am using the domain administrator on both servers.

Have you any idea what my issue might be?

Your help will be greatly appreciated.

Cheers,

Hi,

I resolved this issue myself and am position what my resolution so that i may hopefully help someone else if they are to encounter the same problem.

Resolution:

I set the authentication of the datasources being used by the cube to SQL authentication and then used the SQL server 'sa' account as the user.

|||This would imply that you are using ROLAP or HOLAP storage. You should not need to use the "sa" account. A user with db_datareader rights should be enough. The "sa" user has too many rights and really should not be used for anything other than administrative tasks.|||

Hi Darren,

All dimensions of the cube are using plain MOLAP and the cube itself is using plain MOLAP also. If you have any suggestions on how I could browse the cube with using 'sa' as the datasources user then that would be great as I also think as you mentioned that 'sa' should only be used for administration purposes.

Cheers,

|||

MOLAP storage only needs a SQL connection at processing time. When you browse it does not need a connection to the relational source. You could try using SQL Profiler to see what is going on. Do you have pro-active caching or something like that configured?

browsing cube

Hi

I have created a cube. I want to show it to the clients that do not have the Business Intellegence development studio or visual studio installed. Is there any other method of browsing a deployed cube?

Thanks in advance.

-padu

Probably the most widely available client is Excel.

Mosha has one of the best lists of SSAS clients on his wesite http://www.mosha.com/msolap/util.htm

|||Excel is a good choice. Also, you can generate a local cube file based on your data and let Excel connect directly to it. This way you can show the cube and data to the clients on a machine that doesn't run (and has no network connection to) the AS server.|||

Thanks guys,

This was really helpful.

-padu.

Browsing Cube

Hi,

I have created, populate and processed a cube successfully and can also browse it successfully on one server. But when I deploy this cube to another server I can create, populate and process the cube sucessfully but when I go to browse the cube through SQL Management Studio I recive the following error:

The query could not be processed: o OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000.

I have checked the security and it all appears to be the same, I am using the domain administrator on both servers.

Have you any idea what my issue might be?

Your help will be greatly appreciated.

Cheers,

Hi,

I resolved this issue myself and am position what my resolution so that i may hopefully help someone else if they are to encounter the same problem.

Resolution:

I set the authentication of the datasources being used by the cube to SQL authentication and then used the SQL server 'sa' account as the user.

|||This would imply that you are using ROLAP or HOLAP storage. You should not need to use the "sa" account. A user with db_datareader rights should be enough. The "sa" user has too many rights and really should not be used for anything other than administrative tasks.|||

Hi Darren,

All dimensions of the cube are using plain MOLAP and the cube itself is using plain MOLAP also. If you have any suggestions on how I could browse the cube with using 'sa' as the datasources user then that would be great as I also think as you mentioned that 'sa' should only be used for administration purposes.

Cheers,

|||

MOLAP storage only needs a SQL connection at processing time. When you browse it does not need a connection to the relational source. You could try using SQL Profiler to see what is going on. Do you have pro-active caching or something like that configured?

Browser Role get Admin Tools... why?

Hi there,
We're trying to set up a Browser Role on a Server for Reporting Services. We
have created the NT User, and then added a Browser role for them in RS. But
for some reason, even though when we access Reports and type in the Browser's
Credentials, all of the Admin Tools are still there and usable.
I've done the exact same thing previously and had no complications like
this. What could be causing this to happen?
Thanks
AndreThe Answer is...
the report web apps in IIS has anom access selected!
"Arkiliknam" wrote:
> Hi there,
> We're trying to set up a Browser Role on a Server for Reporting Services. We
> have created the NT User, and then added a Browser role for them in RS. But
> for some reason, even though when we access Reports and type in the Browser's
> Credentials, all of the Admin Tools are still there and usable.
> I've done the exact same thing previously and had no complications like
> this. What could be causing this to happen?
> Thanks
> Andre

browse to reports

Hi,
I have created reports on my local machine as a test.
I can view the reports fine but others can not browse to these reports on my
machine.
I am not using any code, just the front end report creating of reporting
services in Visual studio .net
How can I allow other to view these reports on my machine.
ThanksHi,
Surf to http://servername/reports, click on the properties-tab and add the
users you want to give access.
hth,
Pieter
"Farsh" <Farsh@.discussions.microsoft.com> wrote in message
news:1D124954-0038-4726-8BAC-CDBE70EC6F38@.microsoft.com...
> Hi,
> I have created reports on my local machine as a test.
> I can view the reports fine but others can not browse to these reports on
> my
> machine.
> I am not using any code, just the front end report creating of reporting
> services in Visual studio .net
> How can I allow other to view these reports on my machine.
> Thanks|||first of all u should tell whats the error ppl r getting when they r
trying to access ur report so that it could properly b pointed out n
rectified as per my understanding i believe this would b RSACCESSDENIED
error n u ve to add users login to PERMIT through REPORT MANAGER check
security features/tab in report manager
hope it ll answer|||Thanks
"Pieter van Maasdam" wrote:
> Hi,
> Surf to http://servername/reports, click on the properties-tab and add the
> users you want to give access.
> hth,
> Pieter
> "Farsh" <Farsh@.discussions.microsoft.com> wrote in message
> news:1D124954-0038-4726-8BAC-CDBE70EC6F38@.microsoft.com...
> > Hi,
> > I have created reports on my local machine as a test.
> > I can view the reports fine but others can not browse to these reports on
> > my
> > machine.
> > I am not using any code, just the front end report creating of reporting
> > services in Visual studio .net
> >
> > How can I allow other to view these reports on my machine.
> > Thanks
>
>

BROKER_QUEUE_DISABLED problems

I have created a queue and service all in the same database as follows:

CREATE QUEUE ODS.[Queue Watch];

CREATE SERVICE [Queue Watch Service]

ON QUEUE [Queue Watch];

Next I created an event Notification as:

CREATE EVENT NOTIFICATION [OrderQueueDisabled]

ON QUEUE [ODS].[Order Process Queue]

FOR BROKER_QUEUE_DISABLED

TO SERVICE 'Queue Watch Service', 'current database';

When I get the queue to disable ([ODS].[Order Process Queue] ) by setting the activated stored procedure to do a 'Select 1/0', I don't get a message in the ODS.[Queue Watch] queue. There is nothing returned from any of the following queries:

Select * from sys.transmission_queue

SELECT * FROM sys.server_event_notifications

Select * from [ODS].[Queue Watch]

select * from sys.dm_qn_subscriptions

All of this is running the same database.

.

The rollback handler is a "poison message detection" mechanism. It detects rollbacks after a message has been RECEIVEd from a queue. It should not be understood as an "activated stored proc abort detection" mechanism.

In your case, you aborted having never RECEIVEd a message so the mechanism did not fire.

The same mechanism works for both activated stored procs and external applications that are written using ADO, OLEDB, ODBC, etc.

|||Sorry I didn't point that out but I did recieve a message and it ran until the queue was disabled (5 times). Shouldn't the Notification show up in the Dynamic Managed view regardless?|||

Start the profiler and check (ie. turn on) the broker monitoring events. You should see a trace message like this:

EventClass = Broker:Message Undeliverable

This message could not be delivered because the targeted service does not support the service contract. Targeted service: 'Queue Watch Service', service contract:
'http://schemas.microsoft.com/SQL/Notifications/PostEventNotification'.

If you subscribe to one of SQL Server events, the targetted service, in your case the "Queue Watch Service", must support the message contract used by SQL Server.

Change your CREATE SERVICE to look like this:

create SERVICE [Queue Watch Service]

ON QUEUE ODS.[Queue Watch]

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

|||

Btw, your original code snippet included several "SELECTs ..." at the end. I'm guessing you were using one (both?) of the following to confirm that your CREATE EVENT worked:

SELECT * FROM sys.server_event_notifications

select * from sys.dm_qn_subscriptions

Neither of them will contain it. sys.dm_qn_subscription is a view that contains rows (subscriptions) resulting from usage of the query notifications feature. sys.server_event notifications is a view that contains rows resulting from CREATE EVENT calls, but for server-scoped events. The particular event you are subscribing to receive (BROKER_QUEUE_DISABLED) is a database-scoped event. The view that will contain a row representing this CREATE EVENT call is:

select * from sys.event_notifications

|||That worked. Thanks Gerald. Sorry for the long delay. We all need a vacation now and then.

BROKER_QUEUE_DISABLED problems

I have created a queue and service all in the same database as follows:

CREATE QUEUE ODS.[Queue Watch];

CREATE SERVICE [Queue Watch Service]

ON QUEUE [Queue Watch];

Next I created an event Notification as:

CREATE EVENT NOTIFICATION [OrderQueueDisabled]

ON QUEUE [ODS].[Order Process Queue]

FOR BROKER_QUEUE_DISABLED

TO SERVICE 'Queue Watch Service', 'current database';

When I get the queue to disable ([ODS].[Order Process Queue] ) by setting the activated stored procedure to do a 'Select 1/0', I don't get a message in the ODS.[Queue Watch] queue. There is nothing returned from any of the following queries:

Select * from sys.transmission_queue

SELECT * FROM sys.server_event_notifications

Select * from [ODS].[Queue Watch]

select * from sys.dm_qn_subscriptions

All of this is running the same database.

.

The rollback handler is a "poison message detection" mechanism. It detects rollbacks after a message has been RECEIVEd from a queue. It should not be understood as an "activated stored proc abort detection" mechanism.

In your case, you aborted having never RECEIVEd a message so the mechanism did not fire.

The same mechanism works for both activated stored procs and external applications that are written using ADO, OLEDB, ODBC, etc.

|||Sorry I didn't point that out but I did recieve a message and it ran until the queue was disabled (5 times). Shouldn't the Notification show up in the Dynamic Managed view regardless?|||

Start the profiler and check (ie. turn on) the broker monitoring events. You should see a trace message like this:

EventClass = Broker:Message Undeliverable

This message could not be delivered because the targeted service does not support the service contract. Targeted service: 'Queue Watch Service', service contract:
'http://schemas.microsoft.com/SQL/Notifications/PostEventNotification'.

If you subscribe to one of SQL Server events, the targetted service, in your case the "Queue Watch Service", must support the message contract used by SQL Server.

Change your CREATE SERVICE to look like this:

create SERVICE [Queue Watch Service]

ON QUEUE ODS.[Queue Watch]

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

|||

Btw, your original code snippet included several "SELECTs ..." at the end. I'm guessing you were using one (both?) of the following to confirm that your CREATE EVENT worked:

SELECT * FROM sys.server_event_notifications

select * from sys.dm_qn_subscriptions

Neither of them will contain it. sys.dm_qn_subscription is a view that contains rows (subscriptions) resulting from usage of the query notifications feature. sys.server_event notifications is a view that contains rows resulting from CREATE EVENT calls, but for server-scoped events. The particular event you are subscribing to receive (BROKER_QUEUE_DISABLED) is a database-scoped event. The view that will contain a row representing this CREATE EVENT call is:

select * from sys.event_notifications

|||That worked. Thanks Gerald. Sorry for the long delay. We all need a vacation now and then.

Breaking of Sub Report?

Hi All,

I have created a Report that include 10 other sub reports. The problem is that if there is not enough space for that sub report on the page, it goes to the next page. Also, if the content of sub report is huge, it removes the page header details so that it can fit into one page.

Is there any way that the sub report breaks on its own and there is no wastage of space? I guess that will solve my problem of page header details as well.

If anyone has worked on such issue, please let me know.

Any help will be greatly appreciated.

TIA!!!

Hi,

Anyone has got any clue?

Regards!!!

BPA save Best Practice

I have created a BPA best practice, I now want to save it to begin scanning. The BPA User Guide says to 'Click the Save Best Practice' link to save the created Best Practice. On the BPA Create Best Practice window I don't see any Save Best Practice link
. What am I missing?
Thanks,
Terry
What screen resolution are you using ? If it's less than 1024X768 you might
have trouble seeing it, it's right off the bottom of the screen. You should
be able to get to it by pressing Tab when on the rules section or increasing
your screen resolution
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Terry Serena" <tserena@.duqlight.com.NO_SPAM> wrote in message
news:3616EF03-AFA2-4DE6-9BDB-69541EAABB29@.microsoft.com...
> I have created a BPA best practice, I now want to save it to begin
scanning. The BPA User Guide says to 'Click the Save Best Practice' link to
save the created Best Practice. On the BPA Create Best Practice window I
don't see any Save Best Practice link. What am I missing?
> Thanks,
> Terry
|||Jasper,
Thanks, changing my resolution solved my problem.
Terry

Box name and default SQL Server name

I had created SQL 2000 on Win 2000 with the default box
name. What happens to SQL 2000's default name if the
box's name gets changed in future ? Will it also take up
the new name of the box?
ThanksHi,
No, You have drop and create the server name.
Look into sp_dropserver ,sp_addserver in Bol.
Thanks
Hari
MCDBA
"kanthi" <anonymous@.discussions.microsoft.com> wrote in message
news:023701c3b103$22e83f50$a001280a@.phx.gbl...
> I had created SQL 2000 on Win 2000 with the default box
> name. What happens to SQL 2000's default name if the
> box's name gets changed in future ? Will it also take up
> the new name of the box?
> Thanks|||I had a problem because of this and got help off some forum It caused me
problems with an existing replication subscription here is what had to do
to fix it.
In QA use SELECT @.@.ServerName
This will show you the stored SQL servername (It will be your old one)
Then use sp_dropserver 'YourOldServerName', 'droplogins'
Stop and re-start SQL Server
Then do SELECT @.@.ServerNAme again in QA
This should return a null
Then use sp_addserver 'NewServerName', 'local'
Make sure the local parameter is there !
Stop and re-start SQL Server again
Then use SELECT @.@.ServerName again in QA
This should now be your new server name
Hope this helps
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ODplQtQsDHA.572@.TK2MSFTNGP11.phx.gbl...
> Hi,
> No, You have drop and create the server name.
> Look into sp_dropserver ,sp_addserver in Bol.
> Thanks
> Hari
> MCDBA
>
> "kanthi" <anonymous@.discussions.microsoft.com> wrote in message
> news:023701c3b103$22e83f50$a001280a@.phx.gbl...
> > I had created SQL 2000 on Win 2000 with the default box
> > name. What happens to SQL 2000's default name if the
> > box's name gets changed in future ? Will it also take up
> > the new name of the box?
> >
> > Thanks
>