March 18, 2009 at 6:08 am
hi adam,
thanks for these articles, they totally rock!!!
one question though:
i have lots of separate databases, which have similar sets of reports pointing at them. each db will contain it's own set of style tables.
i'd like to use the data source that the actual report is using instead of hard coding the db connection into the dll. is that possible at all?
cheers,
o
March 18, 2009 at 6:31 am
Hi Olaf,
I am pretty sure tha the answer is yes, using a config file, but I have not done this yet.
Adam
March 18, 2009 at 9:24 am
Okay, I'm going to show how little I know...
How do I set UNSAFE?
Another question -- why not send both fields, StyleType and StyleCode as parameters rather then only one and using a loop to find the other?
March 18, 2009 at 9:32 am
Hi there perlowin,
Question 1:
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = UNSAFE
- or if using Visual Studio, it is in one of the project config tabs (I don't have access to my test rig at the moment & so can't be more precise - sorry!)
Question2:
You can - it's just a variation on a theme!
Adam
March 19, 2009 at 9:06 am
Hopefully someone can help out here. I've created, compiled, and distributed the .dll to the server and my local machine.
When I create the report, it runs and looks great on my local machine. However, when I run it from the server, I get #Error back to the report. I've ran a trace on the database and it appears as though the application never even tries to access the database. It pulls an image from the same database and correctly displays it, but nothing on the formatting.
The Reporting Services is running on the same physical machine as the database and IE (Test environment). The table holding the image and the table containing the formatting settings are in the same database.
I'm sure I've missed something obvious, but haven't been able to figure it out.
Any help is greatly appreciated.
Scott B Dragoo
Database Administrator
TrippLite
Addendum: I added a MessageBox() to the .dll. When the report is ran from my local workstation the MessageBox() appears as expected. When I run the report from the server (either at the server with IE or from my workstation using IE) no appearance of the box. Does the .dll need registration with the server? Is there a software dependancy for this? Help.
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
March 19, 2009 at 9:16 am
hi again adam,
i have been playing a bit with the assembly and it works quite well.
the more i play with it, the more i wonder if it's not simpler to create a stored procedure that retrieves all styles in a dataset that then can be referenced in the report wherever needed. that would reduce the overhead of executing the vb code and it would also read the entire dataset with all styles once instead of calling the database for each function call.
i guess i must be missing something to not see the obvious advantage when using the assembly though...
thanks,
olaf
i accidentally created a new topic with the above content. tried to delete it but it wouldn't let me. so please feel free to delete this one:
March 19, 2009 at 9:30 am
I'm experiencing something similar to S B Dragoo (maybe Susan is running into this too). I haven't implemented the image part yet, only the styles. But, it works fine locally and deploys, but any styles I've set do not show up when I run the report in Report Manager. As a test, I tried to return the style values in a text box and it returns as #Error. I also ran a trace and don't see any activity.
I read on some other posts that they updated the rssvpolicy.config file and add some assert statements to the code. But, we're not doing that here. I tried it just to see if that was somehow the missing piece. Report Manager threw an error that the entire site was down. So, I'm guessing we're not supposed to do this.
Is there maybe some setup on the db side I'm overlooking?
March 19, 2009 at 10:40 am
Hi Olaf,
You can indeed use a stored procedure to retrieve style information, and then use this in the report.
It is a question of choice, you choose what you are happier with.
Adam
March 19, 2009 at 10:52 am
ok, so there's no real advantage when choosing one method over the other?
your article however has helped me to understand how easy it is to put code into an assembly instead of using the pesky Report Properties -> Code tab. but for a style based solution i think i'll work with stored procedures instead then.
thanks a lot
March 26, 2009 at 9:20 am
After many hours of google and msdn searches, I am still frustrated with this option. It works fine at the development machine in BIDS but at the server it never makes the call to the .dll and ultimately to the database.
Here is what I have in place:
In the rssrvpolicy.config at the Windows 2008 Server with SQL Server 2005 on it, I've added these lines:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCodeGroup"
Description="Code group for my data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\program files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\DynamicReportStyle.dll"
They were inserted immediately prior to the last /CodeGroup line.
Within my custom assembly prior to any sql calls I've added the following according to another msdn posting:
Dim pstate As PermissionState
pstate = PermissionState.Unrestricted
Dim psql As New SqlClientPermission(pstate)
psql.Assert()
As stated earlier, this works beautifully from the BIDS interface, but once deployed to the server - NO GO.
I've re-checked all of my deployment locations for the dll to insure the proper location.
Any help? Anybody?
Thanks in advance,
Scott B Dragoo
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
March 30, 2009 at 4:49 pm
Adam,
Was the intention of the article to have this only working locally in preview mode? There is no mention of modifying the config files or asserting permissions in the tutorial. From everything I've read online, it sounds like this is required, but it's never mentioned in this article. Sadly, I have modified these files and still can't get it to work 🙁
Are you deploying yours to the RS and running it there or running it in debugLocal mode successfully? I cannot get this to work unless I'm in preview mode.
Thank you.
April 24, 2009 at 4:22 am
Thanks Adams. The solution looks gr8.
But i face a strange problem.
In SQLServer 2005 environment (enterprise edition) when i use this DLL ,
the solution works gr8 ,
---->when we preview the report using VS and
--->when we deploy the report and view in report manager .
But in SQLServer 2005 environment (standard edition) when i use this DLL ,
--->the solution works gr8 when we preview the report using VS ,
--->but the styling dont work when we deploy the report and view in report manager 🙁 .
is this due to the enterprise VS standard edition? any idea on why this issue?
Thanks,
Arunvijay
April 24, 2009 at 5:59 am
Brilliant article Adam but.......
isn't there always a but?
How would you go about centralising report styles in a global organisation? Where each jurisidiction/site has its own server?
My initial thought was to use the datasource I am using within the reports which takes care of the differing server names already, but I can't work out/find a way to do this. Do you have any suggestions please?
Thanks again
April 24, 2009 at 7:33 am
Hi Arunvj,
I must admit that I have only used this in the Enterprise edition of SQL Server, and have not tested in standard edition. I will have to research this!
Thanks!
ADam
April 24, 2009 at 7:35 am
Hi MissKittyT,
I am using the Enterprise version of SQL Server - as you can see from Arunvijay's post,the solution does not seem to work out of the box with Standard Edition.
Which are you using?
Regards,
Adam
Viewing 15 posts - 31 through 45 (of 70 total)
You must be logged in to reply to this topic. Login to reply