How to add parameters and filters?

Hi,

I would like to ask a few questions about Reporting Services.

1. Can we add new parameters (by code on VS2005) at runtime when the report it's on a server? Since the job of the Report Viewer only allow to get/set parameters and not add new parameter, i wonder if there is a way to add parameter without adding manually on the report design mode.

2. Can i add some filter to change the data show on the report? (To be more specific, I didnt mean to add filter at the design mode on VS2005, I want to be able to add filter by code)

3. Can i change the query (dataset) of a report at runtime (once again i mean by code) which it's on the reportserver? (Since we can change the query on a rdlc report , i wonder if we can do the same on a rdl.)

Thanks in advance

1. No, you can only change the # of parameters by republishing the RDL.

2. No, you can only add filters in the RDL. You can affect the filter by runtime parameters, though.

3. You can change the connection string at runtime via a parameter, but not the query. That would require republishing an updated RDL.

BTW, you can call SetReportDefinition programatically, so you can achieve all your objectives roundabout by crafting an updated RDL and republishing in code or with script.

How to add CLR dll after restore db on another server? thanks

I use VS2005 to deploy my CLR dll, it is easy. But after I copy db over to another server, the new server doesn't have driver W, so I need put dlls on Driver C. I think I have to relink assembly with those dlls. How should I do that, I tried Alter Assembly add file, but it doesn't work, the following is a script I get from one of my assembly. The dll is emaillib.dll, I could not find it in script. I have copied the dll to c:\emaillib.dll, so how can I link it back? I don;t want to drop the assembly since if I do that, I have to drop all CLR objects of the assembly first.

CREATE ASSEMBLY [EmailLib]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000......

WITH PERMISSION_SET = UNSAFE

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000000F00000048000000000000000D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF38C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF.... bery long

AS N'EmailLib.pdb'

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D2E5265666C656374696F6E3B0D0A7573696E672053797374656D2E52756E74696D652E436F6D70696C657253657276696365733B0D0A7573696E672053797374656D2E52756E74696D652E496E7465726F7053657276696365733B0D0A7573696E672053797374656D2E446174612E53716C3B0D0A0D0A2F2F2047656E6572616C20496E666F726D6174696F6E2061626F757420616E20617373656D626C7920697320636F6E74726F6C6C6564207468726F7567682074686520666F6C6C6F77696E670D0A2F2F20736574206F6620617474726962757465732E204368616E6765207468657365206174747269627574652076616C75657320746F206D6F646966792074686520696E666F726D6174696F6E0D0A2F2F206173736F636961746564207769746820616E20617373656D626C792E0D0A5B617373656D626C793A20417373656D626C795469746C652822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C794465736372697074696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6E66696775726174696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6D70616E7928224D656D6C696E6B20496E632E22295D0D0A5B617373656D626C793A20417373656D626C7950726F647563742822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C79436F707972696768742822436F7079726967687420C2A9204D656D6C696E6B20496E632E203230303722295D0D0A5B617373656D626C793A20417373656D626C7954726164656D61726B282222295D0D0A5B617373656D626C793A20417373656D626C7943756C74757265282222295D0D0A0D0A5B617373656D626C793A20436F6D56697369626C652866616C7365295D0D0A0D0A2F2F0D0A2F2F2056657273696F6E20696E666F726D6174696F6E20666F7220616E20617373656D626C7920636F6E7369737473206F662074686520666F6C6C6F77696E6720666F75722076616C7565733A0D0A2F2F0D0A2F2F2020202020204D616A6F722056657273696F6E0D0A2F2F2020202020204D696E6F722056657273696F6E0D0A2F2F2020202020204275696C64204E756D6265720D0A2F2F2020202020205265766973696F6E0D0A2F2F0D0A2F2F20596F752063616E207370656369667920616C6C207468652076616C756573206F7220796F752063616E2064656661756C7420746865205265766973696F6E20616E64204275696C64204E756D626572730D0A2F2F206279207573696E672074686520272A272061732073686F776E2062656C6F773A0D0A5B617373656D626C793A20417373656D626C7956657273696F6E2822312E302E2A22295D0D0A0D0A
AS N'Properties\AssemblyInfo.cs'

GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyProjectRoot', @.value=N'W:\ablelink\ableclr\EmailLib' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'

If your assembly is already deployed, adn you then copy the db over to some other server (detach, re-attach), then your assembly is there as well. The assemblies you are using from within SQL Server is not file based, but located in the database.

Niels
|||

No, I use backup and restore.

When I run my CLR functions, I get errors like

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65886. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'datetimelib, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

|||First of all, it looks like your assembly is in the restored database. Do: select * from sys.assemblies and see if yhe assembly is not there.

If it is, it looks like you have a permission problem, i.e. the database in question is not set up properly for the permission set the assembly requires. I assume the assembly is originally created with EXTERNAL_ACCESS or UNSAFE? If so, set up the datbase in the same way you did it originally.

Niels
|||I've been having a similar problem. Whenever I restore a database, where the restored database contains CLR stored procedures, I get the error you have (the stored procedures in the database worked fine though before being backedup/restored)

I've found this bug report which might be useful to you.
The workaround doesn't seem to solve my problem as I'm having the same issue even when it is on the same server:

http://support.microsoft.com/kb/918040

Looking at the output of:

Code Snippet

select * from sys.assemblies

select * from sys.assembly_files

everything looks identical before and after the backup/restore. The owner of the database is also the same as before the restore.

I'm not sure what's causing this, but I'd appreciate any tips from someone having similar problems.

How to add CLR dll after restore db on another server? thanks

I use VS2005 to deploy my CLR dll, it is easy. But after I copy db over to another server, the new server doesn't have driver W, so I need put dlls on Driver C. I think I have to relink assembly with those dlls. How should I do that, I tried Alter Assembly add file, but it doesn't work, the following is a script I get from one of my assembly. The dll is emaillib.dll, I could not find it in script. I have copied the dll to c:\emaillib.dll, so how can I link it back? I don;t want to drop the assembly since if I do that, I have to drop all CLR objects of the assembly first.

CREATE ASSEMBLY [EmailLib]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000......

WITH PERMISSION_SET = UNSAFE

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A445300000000020000020000000F00000048000000000000000D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF38C0FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF.... bery long

AS N'EmailLib.pdb'

GO
ALTER ASSEMBLY [EmailLib]
ADD FILE FROM 0xEFBBBF7573696E672053797374656D2E5265666C656374696F6E3B0D0A7573696E672053797374656D2E52756E74696D652E436F6D70696C657253657276696365733B0D0A7573696E672053797374656D2E52756E74696D652E496E7465726F7053657276696365733B0D0A7573696E672053797374656D2E446174612E53716C3B0D0A0D0A2F2F2047656E6572616C20496E666F726D6174696F6E2061626F757420616E20617373656D626C7920697320636F6E74726F6C6C6564207468726F7567682074686520666F6C6C6F77696E670D0A2F2F20736574206F6620617474726962757465732E204368616E6765207468657365206174747269627574652076616C75657320746F206D6F646966792074686520696E666F726D6174696F6E0D0A2F2F206173736F636961746564207769746820616E20617373656D626C792E0D0A5B617373656D626C793A20417373656D626C795469746C652822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C794465736372697074696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6E66696775726174696F6E282222295D0D0A5B617373656D626C793A20417373656D626C79436F6D70616E7928224D656D6C696E6B20496E632E22295D0D0A5B617373656D626C793A20417373656D626C7950726F647563742822456D61696C4C696222295D0D0A5B617373656D626C793A20417373656D626C79436F707972696768742822436F7079726967687420C2A9204D656D6C696E6B20496E632E203230303722295D0D0A5B617373656D626C793A20417373656D626C7954726164656D61726B282222295D0D0A5B617373656D626C793A20417373656D626C7943756C74757265282222295D0D0A0D0A5B617373656D626C793A20436F6D56697369626C652866616C7365295D0D0A0D0A2F2F0D0A2F2F2056657273696F6E20696E666F726D6174696F6E20666F7220616E20617373656D626C7920636F6E7369737473206F662074686520666F6C6C6F77696E6720666F75722076616C7565733A0D0A2F2F0D0A2F2F2020202020204D616A6F722056657273696F6E0D0A2F2F2020202020204D696E6F722056657273696F6E0D0A2F2F2020202020204275696C64204E756D6265720D0A2F2F2020202020205265766973696F6E0D0A2F2F0D0A2F2F20596F752063616E207370656369667920616C6C207468652076616C756573206F7220796F752063616E2064656661756C7420746865205265766973696F6E20616E64204275696C64204E756D626572730D0A2F2F206279207573696E672074686520272A272061732073686F776E2062656C6F773A0D0A5B617373656D626C793A20417373656D626C7956657273696F6E2822312E302E2A22295D0D0A0D0A
AS N'Properties\AssemblyInfo.cs'

GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyProjectRoot', @.value=N'W:\ablelink\ableclr\EmailLib' , @.level0type=N'ASSEMBLY',@.level0name=N'EmailLib'

If your assembly is already deployed, adn you then copy the db over to some other server (detach, re-attach), then your assembly is there as well. The assemblies you are using from within SQL Server is not file based, but located in the database.

Niels
|||

No, I use backup and restore.

When I run my CLR functions, I get errors like

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65886. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'datetimelib, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

|||First of all, it looks like your assembly is in the restored database. Do: select * from sys.assemblies and see if yhe assembly is not there.

If it is, it looks like you have a permission problem, i.e. the database in question is not set up properly for the permission set the assembly requires. I assume the assembly is originally created with EXTERNAL_ACCESS or UNSAFE? If so, set up the datbase in the same way you did it originally.

Niels
|||I've been having a similar problem. Whenever I restore a database, where the restored database contains CLR stored procedures, I get the error you have (the stored procedures in the database worked fine though before being backedup/restored)

I've found this bug report which might be useful to you.
The workaround doesn't seem to solve my problem as I'm having the same issue even when it is on the same server:

http://support.microsoft.com/kb/918040

Looking at the output of:

Code Snippet

select * from sys.assemblies

select * from sys.assembly_files

everything looks identical before and after the backup/restore. The owner of the database is also the same as before the restore.

I'm not sure what's causing this, but I'd appreciate any tips from someone having similar problems.

How to add a new column to a Dataset

I have had to add a new column to a mobile 5 database in VS2005. I obviously have added it in the wrong way. I have two applications that use the same db. The .net application accesses the table with the new column fine., this is where I added it first. When I try to add the column to the Compact framework (Pocket Pc) app I get this error when I try to run the application. In both applications the Server explorer shows the new column.

The column name is not valid. [ Node name (if any) = ,Column name = GUID ]

As long as I remove the GUID column from the tableadapter.fill sql the program runs the same as before.( even though the sql executes in the query designer with the GUID column in the sql statement.)

How do I add the column so that the Dataset.designer.cs gets updated and can load the new column?

Thanks I hope this make sense.

Jon Stroh

Rename the column to something other than "GUID"...

how to access web.config

I am using vs2005 and have a solution file with 4 projects, 2 of which are presentation layers (admin sections), 1 is a business logic layer, the other a data access layer.

The data access layer project is simply a class library that will provide data access functionality. Im using a dataset (xsd) to create my data access objects. My question is how do I select a connection string from the web config file (which is in each of the presentation layer projects) as the data access layer is a class library there is no web.config. Using the connection string selector in the dataset wizard doesnt show any available connection strings.

Reference the System.Confiuration dll and you can use the System.Configuration classes to obtain this data.
In 1.1 you use the ConfigurationSettings class
in 2.0 you utilise the System.ConfigurationManager and this supports the System.ConfigurationManager.ConnectionStrings for <ConnectionStrings> entries.