Query dataset locally from code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Henry99 - 24 Dec 2006 21:58 GMT

Hi all,

I need to get 20 rows of data from the SQL Server DB to put in 20 Textboxes
always one item of one row.
It's about Text in various languages, so each language needs 20 textstrings.
I don't want to call the Stored Procedure 20 times.
So I thought in calling it one time, get the data out once of the DB and
refer to it in queries local to the report 20 times.

I tried it with an Assembly. This worked, but I needed to configure the
assembly's trust etc.
This is speaking against a copy/paste deployment to the client's Reporting
Server.

Is it possible to create a standard data set in the Report and refer in some
form to it locally?
E.g.: Dataset dsData takes one time the 20 rows.
In a textbox I call a function like =code.getValue("item17")
and in the code section I query the dsData with something like
function getValue (item as string) as string
return (select col2 from dsData where col1 = item)
end function

Thanks in advance, Henry

Reply to this Message

Wei Lu [MSFT] - 25 Dec 2006 03:12 GMT

Hello Henry,

I have suggested the following article to you.

http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx

You do not need to configure the trust level if it not required.

I hope this will be some help.

The Assembly is more functional than the Embeded Code in Reporting Services.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Henry99 - 27 Dec 2006 09:03 GMT

Hi Wei Lu,

thank you for your answer.
But, as written above, I've already programmed an Assembly that works, but
the client DENIES the use of an Assembly.
I know also, that the <Code> part of the RDL-File is compiled implicitely
into an Assembly by ReportServer on load, so I cant access the outside world
(like the filesystem or the SQL-Server) from within the custom Code without
elevating its trustlevel.

I know also, that it is possible to handover e.g. Parameters to the code to
manipulate it programatically.
Like:
Function GetValue(reportParameters as Parameters) As Object
do sth with the Parameters
return sthelse
end function

The question was:
Since via a Data Source and a DataSet I've already the data available.
CAN I do anything like above with the Parameters, but here with the dataset?
E.g. giving a whole Dataset to a Function (best byRef) to do a select from?

Function GetValue(dsData as Dataset) As Object
return (select sth from dsData where ...)
end function

Thanks in advance, Henry

Reply to this Message

Wei Lu [MSFT] - 27 Dec 2006 10:06 GMT

Hello Henry,

Unfortunately, you could not refer the dataset in the code.

I would like to know the concern from your client of using the assembly.
You could only grant some proper trust for runing the report.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Henry99 - 27 Dec 2006 10:39 GMT

Dear Wei Lu,

it might be possible to access the file system, as in your
XML-Assembly-Example that you sent me, but getting data out from an
SQL-Server DB is a big trouble.
The info under your hint:
http://msdn2.microsoft.com/en-gb/library/aa179521(SQL.80).aspx
is far from sufficient.

Only after lots of hours and the following resources, I got it to work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dn
grfCodeAccessSecurityInSQLServer2000ReportingServices.asp
http://forums.microsoft.com/msdn/showpost.aspx?postid=139787&siteid=1
Microsoft Official Course 2840A, Implementing Security for Applications

So, in the very end I needed to give:
- fulltrust via UrlMembershipCondition
Error message: "Cannot generate Hash"
- Give the assembly a strong name
Error message:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
... failed. (rsRuntimeErrorI-nExpression)
- Adjust CAS
- Assert
System.Data.SqlClient.SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
It didn't work until now!!!
So, worse, I had to install the Assembly in the GAC.
and with this give: AllowPartiallyTrustedCallers()

With our client was agreed a copy/paste-deployment.
All this procedure is FAR from that!

Any ideas?

Thank you, Henry

Reply to this Message