Creating an All option for parameter value in GUID format

I am working with SRS 2005 SP1 which no longer has the "ALL" option available on parameters. I am trying to create an "ALL" entry in a picklist so it can be used in a where clause for a dataset. I have a dataset with a union statement that creates a list of CRM usersids and names and an entry with a dummy guid with the name "All". Parameter is defined as a string type, with a dataset providing a list of users (label field) and their corresponding GUID value (value field), along with the an "All" entry.

select systemuserid, fullname
from FilteredSystemUser
Union
Select '00000000-0000-0000-0000-000000000000' as systemuserid, ' All' as fullname
order by fullname

The issue I am running into is implementing logic in another dataset referencing my parameter.

All is fine in the where clause if it is structured "where ownerid in (@.Users)" but if I try to add logic to check for the "All" option "where (ownerid in (@.Users) or @.Users = '00000000-0000-0000-0000-000000000000') it errors out.

How do you impement "All" when you're dealing with a GUID type field? Thanks.

Have you looked at SSRS SP2? It puts the Select All option back. You can get it HERE

R

|||Thanks. We will be installing SP2.

Creating a row guid in SQL Express

I am an asp developer who is finally serious about learning .net. I have downloaded SQL Express and am working through the ADO Step by Step book. I can easily create a table and set all the columns. What I cannot do is create a row guid. The option for this is grayed out in the column properties window and when I try to set a field in the "Row Guid Column" in the right side properties window I get an error mesage stating the field must match an entry in the list.

I have set the id field to primary key and no nulls and to identity and I still cannot set as row guid. I even downloaded Northwind and these tables also do not have a rowguid and I cannot set.

I noticed this as I tried to create a data adapter. I was able to create the Select and Insert statements but the update and delete failed I think due to the rowguid issue. Any help is appreciated Thanks Brad

Hi Brad...in order to set the rowguid column attribute, the column must be of the datatype 'uniqueidentifier'...once you have a column of that datatype, you can then assign the column the rowguid attribute. Note that you can only have a single rowguid column in a single table, though you can have multiple columns of the datatype uniqueidentifier.

HTH

|||Thanks Chad I have been working with MySql the last 3 years and was not used to the uniqueidentifier column. I now know and have fixed my db.

CreateSubscription - Specify the Job Name or Get the GUID Job Name

We're using the CreateSubscription method to schedule a report for automatic delivery, using SQL Server Agent. Everything is working great. But, we had a question (or two).

Is there anyway to specify the name of the SQL Server Agent job that gets created when the CreateSubscription method is called? If so, how?
If not, is there anyway to get the GUID job name back after calling CreateSubscription?

TIA

There is no way to set the name of the SQL Agent job via the CreateSubscription method.

You can use the ListSubscriptions to get the guid and GetSubscriptionProperties to get further information.

ReportingService2005 rs = new ReportingService2005();
Subscription[] subscription = rs.ListSubscriptions(ReportAndPath, UserName);

rs.GetSubscriptionProperties(

subscription[0].SubscriptionID,

out actualExtensionSettings,

out actualDescription,

out actualActive,

out actualStatus,

out actualEventType,

out actualMatchData,

out actualParameters);

|||

Brad,

Thanks for the info. However, does using the ListSubscriptions and GetSubscriptionProperties give me the GUID name of the SQL Server Agent job?

Since there is no way to set the SQL Server Agent job to something more meaningful to end-users, the next best option for us is to provide the GUID name of the SQL Server Agent job to the end-user after is has been created. Thus, our ASP.NET app. will display to the end-user something like, "Your job has been created. The job name is xxxx." (where xxxx is the GUID job name as shown in the SQL Server list of jobs). In our situation, our end-users are 'knowledgeable' enough about our product to open SQL Server, navigate to SQL Server Agent, and find the job they just tried to create. So, we need to be able to give them some help with which job name is theirs.

Thanks.

|||

No. The guid for the SQL Agent job is not exposed through the SOAP Api's.

Why not just give them the subscription information? Instead of trying to tell them the SQL Agent Job, tell them the subscription name. "Your subscription has been created. The subscription is on "ReportX" for user "Foo" and is scheduled to send at "Time Selected". In Management Studio or Report Manager, more information is stored for the Subcription in RS then for the Job in the Agent. They can get parameter values, security info, and other information.

Just a thought.