What are the limitations to using the Embedded VB.NET Code within a RDL?
Is it possible to control the IE Browser features via the Embedded Code?
JeffThere are no syntax limitations in VB code used in report expressions and
Code element. You can also call custom assemblies that must only be managed
but could be writting in VB, C# or even managed C++, etc...
If I correctly understand your question you want to write a code hosted
inside a report that will run on client and control browser via ActiveX
interface. This is not possible because all code inside report is always
executed on server and never passed back to the client for execution.
RS also does not support embedding scripts into HTML output. If it was
allowed it would be a security hole...
You can write your own application that will host IE Browser control and
will talk to report server to retrieve reports.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:C762FC98-66B6-4358-9E91-28F2B3129475@.microsoft.com...
> What are the limitations to using the Embedded VB.NET Code within a RDL?
> Is it possible to control the IE Browser features via the Embedded Code?
> Jeff
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Thursday, March 29, 2012
Embedded Select vs. UDF
Why is using an embedded SELECT statement faster than using an embedded UDF that has the same exact SQL code within?
Example (not syntax corrected)
Ex1:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)
is much faster than something like
Ex2:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (dbo.FN_myUDF(@.vmyIndex))
Given that dbo.FN_myUDF has the same code as the embedded select in the first example.
TIA,
KBActually I'm getting quite an opposite result:
select * from authors where au_id = (select au_id from authors where au_lname = 'Dull')
vs.
create function dbo.fn_get_au_id (
@.lname varchar(50) ) returns char(11)
as begin
declare @.au_id char(11)
select @.au_id = au_id from authors where au_lname = @.lname
return @.au_id
end
go
+
select * from authors where au_id = dbo.fn_get_au_id('Dull')
The first results in: Table 'authors'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0.
The second yields: Table 'authors'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.|||Perhaps because SQL Server is able to take your poorly written code:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)
...and transform it into the much more efficient:
SELECT myValue1, myValue2
FROM myTable
INNER JOIN someTable on myTable.aValue = someTable.Value
WHERE someTable.myIndex = 800
...prior to executing it. The optimizer will streamline your statement whenever it can, and thus make use of any indexes on the tables and only make one pass through the subtable. Strictly following your code logic would result in a pass through someTable for every record in myTable, which is also what occurs when you call the UDF. The compiler wants none of that nonsense and fixes your code before executing it.|||Well, I just used the same sample code as you (Mr. Lindman) provided and converted it to my previously posted comparative case...and...hmmmmm...It is actually WORSE than the other two:
select * from authors a
inner join authors b
on a.au_id = b.au_id
where b.au_lname = 'Dull'
results in: Table 'authors'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.
In fact, even JOIN hints don't make any difference. Ironic, isn't it? ;)|||My bad. I was thinking he had the function call in his select clause. In the WHERE clause the UDF is only executed once. I get faster results with the UDF as well, though with a larger dataset I get identical results and execution plans using either the subquery method or the join method.sql
Example (not syntax corrected)
Ex1:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)
is much faster than something like
Ex2:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (dbo.FN_myUDF(@.vmyIndex))
Given that dbo.FN_myUDF has the same code as the embedded select in the first example.
TIA,
KBActually I'm getting quite an opposite result:
select * from authors where au_id = (select au_id from authors where au_lname = 'Dull')
vs.
create function dbo.fn_get_au_id (
@.lname varchar(50) ) returns char(11)
as begin
declare @.au_id char(11)
select @.au_id = au_id from authors where au_lname = @.lname
return @.au_id
end
go
+
select * from authors where au_id = dbo.fn_get_au_id('Dull')
The first results in: Table 'authors'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0.
The second yields: Table 'authors'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.|||Perhaps because SQL Server is able to take your poorly written code:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)
...and transform it into the much more efficient:
SELECT myValue1, myValue2
FROM myTable
INNER JOIN someTable on myTable.aValue = someTable.Value
WHERE someTable.myIndex = 800
...prior to executing it. The optimizer will streamline your statement whenever it can, and thus make use of any indexes on the tables and only make one pass through the subtable. Strictly following your code logic would result in a pass through someTable for every record in myTable, which is also what occurs when you call the UDF. The compiler wants none of that nonsense and fixes your code before executing it.|||Well, I just used the same sample code as you (Mr. Lindman) provided and converted it to my previously posted comparative case...and...hmmmmm...It is actually WORSE than the other two:
select * from authors a
inner join authors b
on a.au_id = b.au_id
where b.au_lname = 'Dull'
results in: Table 'authors'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.
In fact, even JOIN hints don't make any difference. Ironic, isn't it? ;)|||My bad. I was thinking he had the function call in his select clause. In the WHERE clause the UDF is only executed once. I get faster results with the UDF as well, though with a larger dataset I get identical results and execution plans using either the subquery method or the join method.sql
Tuesday, March 27, 2012
Embedded code: what's the trick?
I am trying to put some embedded Visual Basic code into my report (RS 2005), using the "Code" section in the report properties.
Here's the function I'm trying to use:
Public Shared Function ReportTotal(ByVal IsUnitCost As Boolean,ByVal TotalDirectCosts As Double, ByVal SalaryBenefitsTotal As Double, ByVal IndirectRate As Double, ByVal SS_Screened As Integer, ByVal UnitRate As Double) As Double
If IsUnitCost Then
Return SS_Screened * UnitRate
Else
Return TotalDirectCosts + SalaryBenefitsTotal * IndirectRate
End If
End Function
Supposedly, after entering this you can access it in the expression for a field by typing the following: =Code.ReportTotal(... parameters ...)
However, when I try this, "ReportTalk" does not show up in the intellisense after I type "Code.". If I type it in anyway, a red squiggly line shows up under it, and if I attempt to preview the report, I get the following error: "The definition of report [report name] is invalid. Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException' was thrown."
This is not a very helpful error message! I tried with and without "Public" and "Shared" and got the same thing.
What am I doing wrong?
Unfortunately, Custom code is not compiled until the report is published, so any custom methods will not show up in intellisense or be resolved (which results in the red underline) by the report designer.
If you are using RS 2005, then it should be evident in the exception message if the custom code generated the error. So, inorder to help you debug the issue I have a few questions:
What version of Reporting Services are you using?|||
Oh OK, you are right -- when I deploy the report, my code does work! Thank you very much.
You'd think there would be ONE mention of this little fact in SOME documentation, somewhere! It never would have occurred to me that the error was occurring only on preview.
In the SQL Server books online, the following is the entire entry for How to Add Code to a Report:
1. On the Report menu, click Report Properties.
Note: If the Report menu is not available, click within the report design area.
2. On the Code tab, in Custom code, type the code.
I kid you not. Anyway, thank you very much!
Embedded Code: whats the trick?
OK, I give up -- I can't get this to work. I am trying to put the following function into my report (RS 2005):
Public Shared Function ReportTotal(ByVal IsUnitCost As Boolean,ByVal TotalDirectCosts As Double, ByVal SalaryBenefitsTotal As Double, ByVal IndirectRate As Double, ByVal SS_Screened As Integer, ByVal UnitRate As Double) As Double
If IsUnitCost Then
Return SS_Screened * UnitRate
Else
Return TotalDirectCosts + SalaryBenefitsTotal * IndirectRate
End If
End Function
Supposedly, to use this code you put something like this for the expression in one of your fields: =Code.ReportTotal(...paramaters...)
However, when I type in "Code.", "ReportTotal" is not in the list provided by the Intellisense. If I type it in anyway, there is a red squiggly line under "ReportTotal", and then if I try to preview the report, I get an error: "The definition of report [report name] is invalid. Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException' is thrown".
Real helpful. Does this mean there's an error in the Visual Basic? I tried to access the function improperly? What?
I tried with and without "Public", with and without "Shared". Same result.
Can anyone help??
I'm not familiar yet with 2005, but in 2000, there is a tab in the 'Report Properties' dialogue under the 'Report' menu.
If you put the code in there, IE:
Function FormatDateInterval(ByVal value As DateTime) As String
Return Year(value) & "-" & Month(value)
End Function
You can call the function by using something like:
= Code.FormatDateInterval(First(Fields!SaleDate.Value, "Foreclosures"))
Like I said, it works well in 2000. Hope it helps.
|||Actually I found the answer. My code was fine -- it just doesn't get compiled until you deploy the report. I was testing it out in "preview" mode, and that's why it wasn't working.
Needless to say, this little feature is not documented anywhere!
sqlEmbedded code: what's the trick?
I am trying to put some embedded Visual Basic code into my report (RS 2005), using the "Code" section in the report properties.
Here's the function I'm trying to use:
Public Shared Function ReportTotal(ByVal IsUnitCost As Boolean,ByVal TotalDirectCosts As Double, ByVal SalaryBenefitsTotal As Double, ByVal IndirectRate As Double, ByVal SS_Screened As Integer, ByVal UnitRate As Double) As Double
If IsUnitCost Then
Return SS_Screened * UnitRate
Else
Return TotalDirectCosts + SalaryBenefitsTotal * IndirectRate
End If
End Function
Supposedly, after entering this you can access it in the expression for a field by typing the following: =Code.ReportTotal(... parameters ...)
However, when I try this, "ReportTalk" does not show up in the intellisense after I type "Code.". If I type it in anyway, a red squiggly line shows up under it, and if I attempt to preview the report, I get the following error: "The definition of report [report name] is invalid. Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException' was thrown."
This is not a very helpful error message! I tried with and without "Public" and "Shared" and got the same thing.
What am I doing wrong?
Unfortunately, Custom code is not compiled until the report is published, so any custom methods will not show up in intellisense or be resolved (which results in the red underline) by the report designer.
If you are using RS 2005, then it should be evident in the exception message if the custom code generated the error. So, inorder to help you debug the issue I have a few questions:
What version of Reporting Services are you using?|||
Oh OK, you are right -- when I deploy the report, my code does work! Thank you very much.
You'd think there would be ONE mention of this little fact in SOME documentation, somewhere! It never would have occurred to me that the error was occurring only on preview.
In the SQL Server books online, the following is the entire entry for How to Add Code to a Report:
1. On the Report menu, click Report Properties.
Note: If the Report menu is not available, click within the report design area.
2. On the Code tab, in Custom code, type the code.
I kid you not. Anyway, thank you very much!
embedded code to check parameter
Hi..
i have 2 parameters, start_date and end_date. is it possible to use
embedded code to prompt a message box if start_date is later than end
date ?
so far from what i've read, embedded code are called using the globally
defined 'Code' member. but in my case where i need to check the
parameters, how do i call the embedded code when user click the View
Report button ?
thanksss...The first thing that came to my mind is this:
1) Are you creating this report to run off a .NET webform? If you are,
you can make the check there and bring up the appropriate UI
2) The store procedure/or database call can also check the dates and
then either rerturn results, or one result saying "Error: Begin date is
after the end date" or if thta happens use default paramteres.
3) Also, you can edit report code to check the dates and either
hide/show (using the visible properties) , and display proper error
handling there.|||thanks Sorcerdon..
1) yes, the reports will run on .net webform. but where do i check?
2) the stored procedure will return a dataset.. so if i were to put a
one-line error message, the columns for table referencing the dataset
will be missing and hence caused an error when i preview report. or
have i done it incorrectly?
3) but the stored procedure will also be run if use the visibility
properties, no?
thanks again|||i manage to get the msg box out but without the caption.. i tried the
following code:
System.Windows.Forms.MessageBox.Show("Date Range From must be earlier
or the same as Date Range To","Error","OK","","")
but i received this error:
Overload resolution failed because no accessible 'Show' can be called
without a narrowing conversion:
i checked in msdn website and don't see what's wrong with my syntax...
anyhow, is there a way not to run the query for dataset since the dates
will not return result.
thanks..|||ops... i have another problem... the messagebox won't show when i view
it after deployment.
i have 2 parameters, start_date and end_date. is it possible to use
embedded code to prompt a message box if start_date is later than end
date ?
so far from what i've read, embedded code are called using the globally
defined 'Code' member. but in my case where i need to check the
parameters, how do i call the embedded code when user click the View
Report button ?
thanksss...The first thing that came to my mind is this:
1) Are you creating this report to run off a .NET webform? If you are,
you can make the check there and bring up the appropriate UI
2) The store procedure/or database call can also check the dates and
then either rerturn results, or one result saying "Error: Begin date is
after the end date" or if thta happens use default paramteres.
3) Also, you can edit report code to check the dates and either
hide/show (using the visible properties) , and display proper error
handling there.|||thanks Sorcerdon..
1) yes, the reports will run on .net webform. but where do i check?
2) the stored procedure will return a dataset.. so if i were to put a
one-line error message, the columns for table referencing the dataset
will be missing and hence caused an error when i preview report. or
have i done it incorrectly?
3) but the stored procedure will also be run if use the visibility
properties, no?
thanks again|||i manage to get the msg box out but without the caption.. i tried the
following code:
System.Windows.Forms.MessageBox.Show("Date Range From must be earlier
or the same as Date Range To","Error","OK","","")
but i received this error:
Overload resolution failed because no accessible 'Show' can be called
without a narrowing conversion:
i checked in msdn website and don't see what's wrong with my syntax...
anyhow, is there a way not to run the query for dataset since the dates
will not return result.
thanks..|||ops... i have another problem... the messagebox won't show when i view
it after deployment.
Embedded Code Executing SQL
I'm trying to execute a stored procedure in the header section of a report
(so I can't use a dataset in the data tab) using the shared datasource of the
report. Does anyone have a simple example executing SQL from the embedded
code of a report?Bryan,
You can use the objects from the detail section to show in the header
or...try to have a select statement in the code behind function.
if u are okay with the first line, just pop up again, i will check and give
u the code
"Bryan" wrote:
> I'm trying to execute a stored procedure in the header section of a report
> (so I can't use a dataset in the data tab) using the shared datasource of the
> report. Does anyone have a simple example executing SQL from the embedded
> code of a report?
(so I can't use a dataset in the data tab) using the shared datasource of the
report. Does anyone have a simple example executing SQL from the embedded
code of a report?Bryan,
You can use the objects from the detail section to show in the header
or...try to have a select statement in the code behind function.
if u are okay with the first line, just pop up again, i will check and give
u the code
"Bryan" wrote:
> I'm trying to execute a stored procedure in the header section of a report
> (so I can't use a dataset in the data tab) using the shared datasource of the
> report. Does anyone have a simple example executing SQL from the embedded
> code of a report?
Embedded code didnt work
In the code tab of Report Properties window, I pasted the following VB code:
Public Function Divide(Numerator As Double, Denominator As Double)
If Denominator = 0 Then
Return 0
Else
Return Numerator/Denominator
End If
End Function
But when I tried to reference the above function using "Code." in my expression, I saw an "Unrecognized Identifier" when I put my mouse onto this function in the expression edit window. Why? What I did is just copy the above function to the code tab and then start to reference it in the expression, what did I missed? Thanks for your opinion.
Change your function to:
Function Divide(Numerator As Double, Denominator As Double) As Double
If Denominator = 0 Then
Return 0
Else
Return Numerator/Denominator
End If
End Function
When you call it, use:
Code.Divide(10, 5)
Embedded code
Is it possible use a dataset from the embedded code? 
Thanks!
What I mean is:
Is it possible that from within a report (through the embedded code) get a hold of another dataset and make a query with the data from a specific cell as parameters? Please answer!
Thanks again
sqlEmbedded Code
I have read several posts but still cannot get to the answer and I
think it is something very basic and simple.
I have one report with embedded code that is running a sqlconnection,
sqlcommand, and sqldatareader to get two different fields from a query
to build a string to return back to the text field in the report. It
works just fine on my developer computer in the Preview mode but when I
deploy to the Report Server, the text field that is using the
referenced code displays a #Error.
I have seen that people pull this data out to a separate Assembly and
can reference it out in other reports, but I am just looking at making
this one report work with embedded code and it seems that I am
overlooking something.
Any help would be appreciated.
JoeI haven't done this but the issue is one of security. It works on your
development environment because you have rights to everything. I don't think
there is any way to do this as code behind. I think you have to create an
assembly so you can give rights to it.
What are you doing, it is possible that you can have another query for an
additional dataset or use a sub report and do this without code.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <joe.falk@.apptecusa.com> wrote in message
news:1125341439.906488.60130@.o13g2000cwo.googlegroups.com...
>I have read several posts but still cannot get to the answer and I
> think it is something very basic and simple.
> I have one report with embedded code that is running a sqlconnection,
> sqlcommand, and sqldatareader to get two different fields from a query
> to build a string to return back to the text field in the report. It
> works just fine on my developer computer in the Preview mode but when I
> deploy to the Report Server, the text field that is using the
> referenced code displays a #Error.
> I have seen that people pull this data out to a separate Assembly and
> can reference it out in other reports, but I am just looking at making
> this one report work with embedded code and it seems that I am
> overlooking something.
> Any help would be appreciated.
> Joe
>|||What I am trying to do is pull an integer and a true/false value from a
query that has a parameter on it. The report shares the parameter and
let's the report reader select from a list of names that the report
will return data. One of the data pieces I would like to display in a
text field with 4 columns and show a "*" if the T/F is true. I am using
a table object and have a text field with the code in the Report
Properties to pull that data as one block of data. I tried using a List
object but it would only display the data in one long strip instead of
columns. If I can't put code in the report, what good is that function?
Joe|||You can put code in behind the report. What you can't do is violate security
(which accessing the database directly does). If you want to go directly
against the database from code then you have to create an assembly and give
it the correct security assignments.
I think you can do what you want here by dropping a subreport into the field
instead of calling code.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <joe.falk@.apptecusa.com> wrote in message
news:1125353292.661926.156290@.g47g2000cwa.googlegroups.com...
> What I am trying to do is pull an integer and a true/false value from a
> query that has a parameter on it. The report shares the parameter and
> let's the report reader select from a list of names that the report
> will return data. One of the data pieces I would like to display in a
> text field with 4 columns and show a "*" if the T/F is true. I am using
> a table object and have a text field with the code in the Report
> Properties to pull that data as one block of data. I tried using a List
> object but it would only display the data in one long strip instead of
> columns. If I can't put code in the report, what good is that function?
> Joe
>|||It looks like creating a separate assembly is my best bet then. I wish
that it could be as easy as a subreport, but I cannot seem to get a
string of data rows to fill in anything more than a single column of
data and not a set of 4 or 5 columns by 4 or 5 rows. I have a finite
set of data that I am pulling "Select top 20" and no matter which
object I select, I cannot get it to display in multiple columns.
Thanks for your clarification on the reason why this particular code
will not work in embedded code.
Joe
Bruce L-C [MVP] wrote:
> You can put code in behind the report. What you can't do is violate security
> (which accessing the database directly does). If you want to go directly
> against the database from code then you have to create an assembly and give
> it the correct security assignments.
> I think you can do what you want here by dropping a subreport into the field
> instead of calling code.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Joe" <joe.falk@.apptecusa.com> wrote in message
> news:1125353292.661926.156290@.g47g2000cwa.googlegroups.com...
> > What I am trying to do is pull an integer and a true/false value from a
> > query that has a parameter on it. The report shares the parameter and
> > let's the report reader select from a list of names that the report
> > will return data. One of the data pieces I would like to display in a
> > text field with 4 columns and show a "*" if the T/F is true. I am using
> > a table object and have a text field with the code in the Report
> > Properties to pull that data as one block of data. I tried using a List
> > object but it would only display the data in one long strip instead of
> > columns. If I can't put code in the report, what good is that function?
> >
> > Joe
> >
think it is something very basic and simple.
I have one report with embedded code that is running a sqlconnection,
sqlcommand, and sqldatareader to get two different fields from a query
to build a string to return back to the text field in the report. It
works just fine on my developer computer in the Preview mode but when I
deploy to the Report Server, the text field that is using the
referenced code displays a #Error.
I have seen that people pull this data out to a separate Assembly and
can reference it out in other reports, but I am just looking at making
this one report work with embedded code and it seems that I am
overlooking something.
Any help would be appreciated.
JoeI haven't done this but the issue is one of security. It works on your
development environment because you have rights to everything. I don't think
there is any way to do this as code behind. I think you have to create an
assembly so you can give rights to it.
What are you doing, it is possible that you can have another query for an
additional dataset or use a sub report and do this without code.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <joe.falk@.apptecusa.com> wrote in message
news:1125341439.906488.60130@.o13g2000cwo.googlegroups.com...
>I have read several posts but still cannot get to the answer and I
> think it is something very basic and simple.
> I have one report with embedded code that is running a sqlconnection,
> sqlcommand, and sqldatareader to get two different fields from a query
> to build a string to return back to the text field in the report. It
> works just fine on my developer computer in the Preview mode but when I
> deploy to the Report Server, the text field that is using the
> referenced code displays a #Error.
> I have seen that people pull this data out to a separate Assembly and
> can reference it out in other reports, but I am just looking at making
> this one report work with embedded code and it seems that I am
> overlooking something.
> Any help would be appreciated.
> Joe
>|||What I am trying to do is pull an integer and a true/false value from a
query that has a parameter on it. The report shares the parameter and
let's the report reader select from a list of names that the report
will return data. One of the data pieces I would like to display in a
text field with 4 columns and show a "*" if the T/F is true. I am using
a table object and have a text field with the code in the Report
Properties to pull that data as one block of data. I tried using a List
object but it would only display the data in one long strip instead of
columns. If I can't put code in the report, what good is that function?
Joe|||You can put code in behind the report. What you can't do is violate security
(which accessing the database directly does). If you want to go directly
against the database from code then you have to create an assembly and give
it the correct security assignments.
I think you can do what you want here by dropping a subreport into the field
instead of calling code.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <joe.falk@.apptecusa.com> wrote in message
news:1125353292.661926.156290@.g47g2000cwa.googlegroups.com...
> What I am trying to do is pull an integer and a true/false value from a
> query that has a parameter on it. The report shares the parameter and
> let's the report reader select from a list of names that the report
> will return data. One of the data pieces I would like to display in a
> text field with 4 columns and show a "*" if the T/F is true. I am using
> a table object and have a text field with the code in the Report
> Properties to pull that data as one block of data. I tried using a List
> object but it would only display the data in one long strip instead of
> columns. If I can't put code in the report, what good is that function?
> Joe
>|||It looks like creating a separate assembly is my best bet then. I wish
that it could be as easy as a subreport, but I cannot seem to get a
string of data rows to fill in anything more than a single column of
data and not a set of 4 or 5 columns by 4 or 5 rows. I have a finite
set of data that I am pulling "Select top 20" and no matter which
object I select, I cannot get it to display in multiple columns.
Thanks for your clarification on the reason why this particular code
will not work in embedded code.
Joe
Bruce L-C [MVP] wrote:
> You can put code in behind the report. What you can't do is violate security
> (which accessing the database directly does). If you want to go directly
> against the database from code then you have to create an assembly and give
> it the correct security assignments.
> I think you can do what you want here by dropping a subreport into the field
> instead of calling code.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Joe" <joe.falk@.apptecusa.com> wrote in message
> news:1125353292.661926.156290@.g47g2000cwa.googlegroups.com...
> > What I am trying to do is pull an integer and a true/false value from a
> > query that has a parameter on it. The report shares the parameter and
> > let's the report reader select from a list of names that the report
> > will return data. One of the data pieces I would like to display in a
> > text field with 4 columns and show a "*" if the T/F is true. I am using
> > a table object and have a text field with the code in the Report
> > Properties to pull that data as one block of data. I tried using a List
> > object but it would only display the data in one long strip instead of
> > columns. If I can't put code in the report, what good is that function?
> >
> > Joe
> >
Embedded Code
Hi all,
I have a report in which I use some embedded code.
The assemblies I use are standard .NET assemblies.
In my PC the rendering of the report goes fine, but when i try to render
this report on another machine, the fields that call the embedded code
(code!some_function) do not render correctly.
Any help would be appreciated,
thnks!
GoncaloYour assembly dll files must be copied to the ReportServer\bin folder of the
report server for your reports to work after being deployed to the server. If
you are previewing from Report Designer, the assembly dll files must be
copied to the 80\Tools\Report Designer folder also.
Charles Kangai, MCDBA, MCT
"Goncalo" wrote:
> Hi all,
> I have a report in which I use some embedded code.
> The assemblies I use are standard .NET assemblies.
> In my PC the rendering of the report goes fine, but when i try to render
> this report on another machine, the fields that call the embedded code
> (code!some_function) do not render correctly.
> Any help would be appreciated,
> thnks!
> Goncalo
>|||Thanks Charles, but I already did that and it still doesn't work.
The dll's I use are System.Data.dll and System.Web.dll.
Could it be that I must set some kind of permissions or perhaps change the
web.config file to add these references?
Thanks in advance,
Gonçalo
"Charles Kangai" wrote:
> Your assembly dll files must be copied to the ReportServer\bin folder of the
> report server for your reports to work after being deployed to the server. If
> you are previewing from Report Designer, the assembly dll files must be
> copied to the 80\Tools\Report Designer folder also.
> Charles Kangai, MCDBA, MCT
> "Goncalo" wrote:
> > Hi all,
> > I have a report in which I use some embedded code.
> > The assemblies I use are standard .NET assemblies.
> >
> > In my PC the rendering of the report goes fine, but when i try to render
> > this report on another machine, the fields that call the embedded code
> > (code!some_function) do not render correctly.
> >
> > Any help would be appreciated,
> > thnks!
> > Goncalo
> >|||If you are using the built-in .NET assembly dll's you do not need to copy the
files. However, you may need to add references to them in your report. Access
the Report Properties screen by right-clicking the report selector and
choosing Properties. On the References tab, add references to any DLLs you
use in your report.
Charles Kangai, MCDBA, MCT
"Goncalo" wrote:
> Thanks Charles, but I already did that and it still doesn't work.
> The dll's I use are System.Data.dll and System.Web.dll.
> Could it be that I must set some kind of permissions or perhaps change the
> web.config file to add these references?
> Thanks in advance,
> Gonçalo
> "Charles Kangai" wrote:
> > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > report server for your reports to work after being deployed to the server. If
> > you are previewing from Report Designer, the assembly dll files must be
> > copied to the 80\Tools\Report Designer folder also.
> >
> > Charles Kangai, MCDBA, MCT
> >
> > "Goncalo" wrote:
> >
> > > Hi all,
> > > I have a report in which I use some embedded code.
> > > The assemblies I use are standard .NET assemblies.
> > >
> > > In my PC the rendering of the report goes fine, but when i try to render
> > > this report on another machine, the fields that call the embedded code
> > > (code!some_function) do not render correctly.
> > >
> > > Any help would be appreciated,
> > > thnks!
> > > Goncalo
> > >|||Thanks once again Charles! I have done that already but it didn't work either.
I know now what is going wrong.
In my custom code I use a connection to a database. When I render the report
with the Report Designer everything goes fine but when I deploy it to the
ReportServer I get an exception:
"System.Security.SecurityException: Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
PermissionToken permToken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
checkFrames, Int32 unrestrictedOverride)
at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
constr)
at System.Data.SqlClient.SqlConnection.Open()
at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
Angola\Relatorios\Bibliotecas
Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
Must I edit some .config file so that I can get those permissions?
Thanks in advance,
Goncalo
"Charles Kangai" wrote:
> If you are using the built-in .NET assembly dll's you do not need to copy the
> files. However, you may need to add references to them in your report. Access
> the Report Properties screen by right-clicking the report selector and
> choosing Properties. On the References tab, add references to any DLLs you
> use in your report.
> Charles Kangai, MCDBA, MCT
> "Goncalo" wrote:
> > Thanks Charles, but I already did that and it still doesn't work.
> > The dll's I use are System.Data.dll and System.Web.dll.
> > Could it be that I must set some kind of permissions or perhaps change the
> > web.config file to add these references?
> >
> > Thanks in advance,
> > Gonçalo
> >
> > "Charles Kangai" wrote:
> >
> > > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > > report server for your reports to work after being deployed to the server. If
> > > you are previewing from Report Designer, the assembly dll files must be
> > > copied to the 80\Tools\Report Designer folder also.
> > >
> > > Charles Kangai, MCDBA, MCT
> > >
> > > "Goncalo" wrote:
> > >
> > > > Hi all,
> > > > I have a report in which I use some embedded code.
> > > > The assemblies I use are standard .NET assemblies.
> > > >
> > > > In my PC the rendering of the report goes fine, but when i try to render
> > > > this report on another machine, the fields that call the embedded code
> > > > (code!some_function) do not render correctly.
> > > >
> > > > Any help would be appreciated,
> > > > thnks!
> > > > Goncalo
> > > >|||This is a well documented scenario. You are trying to access an external
resource from your code. By default SQLRS does not allow this unless you
configure code access security. Code running on your report server is only
granted Execute permission, by default, which means that only in-memory
computations are allowed. Access to files, databases, URLs, etc. are not
allowed. Please look up the documentation to see how to configure code access
security. You will need to do three things:
1) Modify your code to assert the database access permission.
2) configure the rspreviewpolicy.config file in the Report Designer folder
if you want to test your permissions in the Designer (viewing report using
Debug/Start).
3) configure the rssvrpolicy.config file in the ReportServer folder if you
are going to deploy the report that uses your assembly.
Charles Kangai, MCDBA, MCT
"Goncalo" wrote:
> Thanks once again Charles! I have done that already but it didn't work either.
> I know now what is going wrong.
> In my custom code I use a connection to a database. When I render the report
> with the Report Designer everything goes fine but when I deploy it to the
> ReportServer I get an exception:
> "System.Security.SecurityException: Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> PermissionToken permToken)
> at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
> checkFrames, Int32 unrestrictedOverride)
> at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Demand()
> at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
> constr)
> at System.Data.SqlClient.SqlConnection.Open()
> at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
> xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
> Angola\Relatorios\Bibliotecas
> Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
>
> Must I edit some .config file so that I can get those permissions?
> Thanks in advance,
> Goncalo
>
> "Charles Kangai" wrote:
> > If you are using the built-in .NET assembly dll's you do not need to copy the
> > files. However, you may need to add references to them in your report. Access
> > the Report Properties screen by right-clicking the report selector and
> > choosing Properties. On the References tab, add references to any DLLs you
> > use in your report.
> >
> > Charles Kangai, MCDBA, MCT
> >
> > "Goncalo" wrote:
> >
> > > Thanks Charles, but I already did that and it still doesn't work.
> > > The dll's I use are System.Data.dll and System.Web.dll.
> > > Could it be that I must set some kind of permissions or perhaps change the
> > > web.config file to add these references?
> > >
> > > Thanks in advance,
> > > Gonçalo
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > > > report server for your reports to work after being deployed to the server. If
> > > > you are previewing from Report Designer, the assembly dll files must be
> > > > copied to the 80\Tools\Report Designer folder also.
> > > >
> > > > Charles Kangai, MCDBA, MCT
> > > >
> > > > "Goncalo" wrote:
> > > >
> > > > > Hi all,
> > > > > I have a report in which I use some embedded code.
> > > > > The assemblies I use are standard .NET assemblies.
> > > > >
> > > > > In my PC the rendering of the report goes fine, but when i try to render
> > > > > this report on another machine, the fields that call the embedded code
> > > > > (code!some_function) do not render correctly.
> > > > >
> > > > > Any help would be appreciated,
> > > > > thnks!
> > > > > Goncalo
> > > > >|||I have the same issue did you get a resolution to this?
Regards
Toby.
"Goncalo" <Goncalo@.discussions.microsoft.com> wrote in message
news:A1F2CBB5-5B79-4C85-B2BA-A09C13E1109B@.microsoft.com...
> Thanks once again Charles! I have done that already but it didn't work
> either.
> I know now what is going wrong.
> In my custom code I use a connection to a database. When I render the
> report
> with the Report Designer everything goes fine but when I deploy it to the
> ReportServer I get an exception:
> "System.Security.SecurityException: Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> PermissionToken permToken)
> at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
> checkFrames, Int32 unrestrictedOverride)
> at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Demand()
> at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
> constr)
> at System.Data.SqlClient.SqlConnection.Open()
> at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
> xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
> Angola\Relatorios\Bibliotecas
> Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
>
> Must I edit some .config file so that I can get those permissions?
> Thanks in advance,
> Goncalo
>
> "Charles Kangai" wrote:
>> If you are using the built-in .NET assembly dll's you do not need to copy
>> the
>> files. However, you may need to add references to them in your report.
>> Access
>> the Report Properties screen by right-clicking the report selector and
>> choosing Properties. On the References tab, add references to any DLLs
>> you
>> use in your report.
>> Charles Kangai, MCDBA, MCT
>> "Goncalo" wrote:
>> > Thanks Charles, but I already did that and it still doesn't work.
>> > The dll's I use are System.Data.dll and System.Web.dll.
>> > Could it be that I must set some kind of permissions or perhaps change
>> > the
>> > web.config file to add these references?
>> >
>> > Thanks in advance,
>> > Gonçalo
>> >
>> > "Charles Kangai" wrote:
>> >
>> > > Your assembly dll files must be copied to the ReportServer\bin folder
>> > > of the
>> > > report server for your reports to work after being deployed to the
>> > > server. If
>> > > you are previewing from Report Designer, the assembly dll files must
>> > > be
>> > > copied to the 80\Tools\Report Designer folder also.
>> > >
>> > > Charles Kangai, MCDBA, MCT
>> > >
>> > > "Goncalo" wrote:
>> > >
>> > > > Hi all,
>> > > > I have a report in which I use some embedded code.
>> > > > The assemblies I use are standard .NET assemblies.
>> > > >
>> > > > In my PC the rendering of the report goes fine, but when i try to
>> > > > render
>> > > > this report on another machine, the fields that call the embedded
>> > > > code
>> > > > (code!some_function) do not render correctly.
>> > > >
>> > > > Any help would be appreciated,
>> > > > thnks!
>> > > > Goncalo
>> > > >|||@.charles kangai: can you also explain how this must be done? Your kind of
answer is the one i like most!!! Especially it includes wrong hints as in
your answer!
In my case the following helps:
The .net code must be modified by write a line direct above your function
like this:
<PermissionSet(SecurityAction.Assert, Unrestricted:=True)> _
Public Function InitOracleConn() As Boolean
...
End Function
The rssrvpolicy.config and RSReportServer.config must be modified. You need
something more as described in the documentation. I did it in the following
way:
I added this code to RSReportServer.config before the </Configuration> Tag:
---
<CustomAssemblies>
<Default>
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution"
/>
</PermissionSet>
</Default>
</CustomAssemblies>
<CustomAssemblies>
<Assembly Name="AIDAReportTranslator, Version=1.0.1.2, Culture=neutral,
PublicKeyToken=null">
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
</PermissionSet>
</Assembly>
<Default>
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution"
/>
</PermissionSet>
</Default>
</CustomAssemblies>
---
In the rssrvpolicy.config I added this code before the
</NamedPermissionSets> Tag
---
<PermissionSet class="NamedPermissionSet"
version="1"
Name="MyNewFilePermissionSet"
Description="A special permission set that grants read access to my file.">
<IPermission class="Microsoft.Data.Odbc.OdbcPermission"
version="1"
Flags="Assertion, Execution"/>
</PermissionSet>
--
and I added the following code group after the last code group:
---
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyNewCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Programme\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\AIDAReportTranslator.dll"/>
</CodeGroup>
---
The file rspreviewpolicy.config must not be modified ! ! !
Ths
"Charles Kangai" wrote:
> This is a well documented scenario. You are trying to access an external
> resource from your code. By default SQLRS does not allow this unless you
> configure code access security. Code running on your report server is only
> granted Execute permission, by default, which means that only in-memory
> computations are allowed. Access to files, databases, URLs, etc. are not
> allowed. Please look up the documentation to see how to configure code access
> security. You will need to do three things:
> 1) Modify your code to assert the database access permission.
> 2) configure the rspreviewpolicy.config file in the Report Designer folder
> if you want to test your permissions in the Designer (viewing report using
> Debug/Start).
> 3) configure the rssvrpolicy.config file in the ReportServer folder if you
> are going to deploy the report that uses your assembly.
> Charles Kangai, MCDBA, MCT
> "Goncalo" wrote:
> > Thanks once again Charles! I have done that already but it didn't work either.
> > I know now what is going wrong.
> > In my custom code I use a connection to a database. When I render the report
> > with the Report Designer everything goes fine but when I deploy it to the
> > ReportServer I get an exception:
> >
> > "System.Security.SecurityException: Request for the permission of type
> > System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> > Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> > at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> > grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> > PermissionToken permToken)
> > at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> > permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
> > checkFrames, Int32 unrestrictedOverride)
> > at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> > cap, StackCrawlMark& stackMark)
> > at System.Security.CodeAccessPermission.Demand()
> > at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
> > constr)
> > at System.Data.SqlClient.SqlConnection.Open()
> > at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
> > xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
> > Angola\Relatorios\Bibliotecas
> > Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
> >
> >
> > Must I edit some .config file so that I can get those permissions?
> >
> > Thanks in advance,
> > Goncalo
> >
> >
> > "Charles Kangai" wrote:
> >
> > > If you are using the built-in .NET assembly dll's you do not need to copy the
> > > files. However, you may need to add references to them in your report. Access
> > > the Report Properties screen by right-clicking the report selector and
> > > choosing Properties. On the References tab, add references to any DLLs you
> > > use in your report.
> > >
> > > Charles Kangai, MCDBA, MCT
> > >
> > > "Goncalo" wrote:
> > >
> > > > Thanks Charles, but I already did that and it still doesn't work.
> > > > The dll's I use are System.Data.dll and System.Web.dll.
> > > > Could it be that I must set some kind of permissions or perhaps change the
> > > > web.config file to add these references?
> > > >
> > > > Thanks in advance,
> > > > Gonçalo
> > > >
> > > > "Charles Kangai" wrote:
> > > >
> > > > > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > > > > report server for your reports to work after being deployed to the server. If
> > > > > you are previewing from Report Designer, the assembly dll files must be
> > > > > copied to the 80\Tools\Report Designer folder also.
> > > > >
> > > > > Charles Kangai, MCDBA, MCT
> > > > >
> > > > > "Goncalo" wrote:
> > > > >
> > > > > > Hi all,
> > > > > > I have a report in which I use some embedded code.
> > > > > > The assemblies I use are standard .NET assemblies.
> > > > > >
> > > > > > In my PC the rendering of the report goes fine, but when i try to render
> > > > > > this report on another machine, the fields that call the embedded code
> > > > > > (code!some_function) do not render correctly.
> > > > > >
> > > > > > Any help would be appreciated,
> > > > > > thnks!
> > > > > > Goncalo
> > > > > >
I have a report in which I use some embedded code.
The assemblies I use are standard .NET assemblies.
In my PC the rendering of the report goes fine, but when i try to render
this report on another machine, the fields that call the embedded code
(code!some_function) do not render correctly.
Any help would be appreciated,
thnks!
GoncaloYour assembly dll files must be copied to the ReportServer\bin folder of the
report server for your reports to work after being deployed to the server. If
you are previewing from Report Designer, the assembly dll files must be
copied to the 80\Tools\Report Designer folder also.
Charles Kangai, MCDBA, MCT
"Goncalo" wrote:
> Hi all,
> I have a report in which I use some embedded code.
> The assemblies I use are standard .NET assemblies.
> In my PC the rendering of the report goes fine, but when i try to render
> this report on another machine, the fields that call the embedded code
> (code!some_function) do not render correctly.
> Any help would be appreciated,
> thnks!
> Goncalo
>|||Thanks Charles, but I already did that and it still doesn't work.
The dll's I use are System.Data.dll and System.Web.dll.
Could it be that I must set some kind of permissions or perhaps change the
web.config file to add these references?
Thanks in advance,
Gonçalo
"Charles Kangai" wrote:
> Your assembly dll files must be copied to the ReportServer\bin folder of the
> report server for your reports to work after being deployed to the server. If
> you are previewing from Report Designer, the assembly dll files must be
> copied to the 80\Tools\Report Designer folder also.
> Charles Kangai, MCDBA, MCT
> "Goncalo" wrote:
> > Hi all,
> > I have a report in which I use some embedded code.
> > The assemblies I use are standard .NET assemblies.
> >
> > In my PC the rendering of the report goes fine, but when i try to render
> > this report on another machine, the fields that call the embedded code
> > (code!some_function) do not render correctly.
> >
> > Any help would be appreciated,
> > thnks!
> > Goncalo
> >|||If you are using the built-in .NET assembly dll's you do not need to copy the
files. However, you may need to add references to them in your report. Access
the Report Properties screen by right-clicking the report selector and
choosing Properties. On the References tab, add references to any DLLs you
use in your report.
Charles Kangai, MCDBA, MCT
"Goncalo" wrote:
> Thanks Charles, but I already did that and it still doesn't work.
> The dll's I use are System.Data.dll and System.Web.dll.
> Could it be that I must set some kind of permissions or perhaps change the
> web.config file to add these references?
> Thanks in advance,
> Gonçalo
> "Charles Kangai" wrote:
> > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > report server for your reports to work after being deployed to the server. If
> > you are previewing from Report Designer, the assembly dll files must be
> > copied to the 80\Tools\Report Designer folder also.
> >
> > Charles Kangai, MCDBA, MCT
> >
> > "Goncalo" wrote:
> >
> > > Hi all,
> > > I have a report in which I use some embedded code.
> > > The assemblies I use are standard .NET assemblies.
> > >
> > > In my PC the rendering of the report goes fine, but when i try to render
> > > this report on another machine, the fields that call the embedded code
> > > (code!some_function) do not render correctly.
> > >
> > > Any help would be appreciated,
> > > thnks!
> > > Goncalo
> > >|||Thanks once again Charles! I have done that already but it didn't work either.
I know now what is going wrong.
In my custom code I use a connection to a database. When I render the report
with the Report Designer everything goes fine but when I deploy it to the
ReportServer I get an exception:
"System.Security.SecurityException: Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
PermissionToken permToken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
checkFrames, Int32 unrestrictedOverride)
at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
constr)
at System.Data.SqlClient.SqlConnection.Open()
at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
Angola\Relatorios\Bibliotecas
Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
Must I edit some .config file so that I can get those permissions?
Thanks in advance,
Goncalo
"Charles Kangai" wrote:
> If you are using the built-in .NET assembly dll's you do not need to copy the
> files. However, you may need to add references to them in your report. Access
> the Report Properties screen by right-clicking the report selector and
> choosing Properties. On the References tab, add references to any DLLs you
> use in your report.
> Charles Kangai, MCDBA, MCT
> "Goncalo" wrote:
> > Thanks Charles, but I already did that and it still doesn't work.
> > The dll's I use are System.Data.dll and System.Web.dll.
> > Could it be that I must set some kind of permissions or perhaps change the
> > web.config file to add these references?
> >
> > Thanks in advance,
> > Gonçalo
> >
> > "Charles Kangai" wrote:
> >
> > > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > > report server for your reports to work after being deployed to the server. If
> > > you are previewing from Report Designer, the assembly dll files must be
> > > copied to the 80\Tools\Report Designer folder also.
> > >
> > > Charles Kangai, MCDBA, MCT
> > >
> > > "Goncalo" wrote:
> > >
> > > > Hi all,
> > > > I have a report in which I use some embedded code.
> > > > The assemblies I use are standard .NET assemblies.
> > > >
> > > > In my PC the rendering of the report goes fine, but when i try to render
> > > > this report on another machine, the fields that call the embedded code
> > > > (code!some_function) do not render correctly.
> > > >
> > > > Any help would be appreciated,
> > > > thnks!
> > > > Goncalo
> > > >|||This is a well documented scenario. You are trying to access an external
resource from your code. By default SQLRS does not allow this unless you
configure code access security. Code running on your report server is only
granted Execute permission, by default, which means that only in-memory
computations are allowed. Access to files, databases, URLs, etc. are not
allowed. Please look up the documentation to see how to configure code access
security. You will need to do three things:
1) Modify your code to assert the database access permission.
2) configure the rspreviewpolicy.config file in the Report Designer folder
if you want to test your permissions in the Designer (viewing report using
Debug/Start).
3) configure the rssvrpolicy.config file in the ReportServer folder if you
are going to deploy the report that uses your assembly.
Charles Kangai, MCDBA, MCT
"Goncalo" wrote:
> Thanks once again Charles! I have done that already but it didn't work either.
> I know now what is going wrong.
> In my custom code I use a connection to a database. When I render the report
> with the Report Designer everything goes fine but when I deploy it to the
> ReportServer I get an exception:
> "System.Security.SecurityException: Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> PermissionToken permToken)
> at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
> checkFrames, Int32 unrestrictedOverride)
> at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Demand()
> at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
> constr)
> at System.Data.SqlClient.SqlConnection.Open()
> at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
> xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
> Angola\Relatorios\Bibliotecas
> Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
>
> Must I edit some .config file so that I can get those permissions?
> Thanks in advance,
> Goncalo
>
> "Charles Kangai" wrote:
> > If you are using the built-in .NET assembly dll's you do not need to copy the
> > files. However, you may need to add references to them in your report. Access
> > the Report Properties screen by right-clicking the report selector and
> > choosing Properties. On the References tab, add references to any DLLs you
> > use in your report.
> >
> > Charles Kangai, MCDBA, MCT
> >
> > "Goncalo" wrote:
> >
> > > Thanks Charles, but I already did that and it still doesn't work.
> > > The dll's I use are System.Data.dll and System.Web.dll.
> > > Could it be that I must set some kind of permissions or perhaps change the
> > > web.config file to add these references?
> > >
> > > Thanks in advance,
> > > Gonçalo
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > > > report server for your reports to work after being deployed to the server. If
> > > > you are previewing from Report Designer, the assembly dll files must be
> > > > copied to the 80\Tools\Report Designer folder also.
> > > >
> > > > Charles Kangai, MCDBA, MCT
> > > >
> > > > "Goncalo" wrote:
> > > >
> > > > > Hi all,
> > > > > I have a report in which I use some embedded code.
> > > > > The assemblies I use are standard .NET assemblies.
> > > > >
> > > > > In my PC the rendering of the report goes fine, but when i try to render
> > > > > this report on another machine, the fields that call the embedded code
> > > > > (code!some_function) do not render correctly.
> > > > >
> > > > > Any help would be appreciated,
> > > > > thnks!
> > > > > Goncalo
> > > > >|||I have the same issue did you get a resolution to this?
Regards
Toby.
"Goncalo" <Goncalo@.discussions.microsoft.com> wrote in message
news:A1F2CBB5-5B79-4C85-B2BA-A09C13E1109B@.microsoft.com...
> Thanks once again Charles! I have done that already but it didn't work
> either.
> I know now what is going wrong.
> In my custom code I use a connection to a database. When I render the
> report
> with the Report Designer everything goes fine but when I deploy it to the
> ReportServer I get an exception:
> "System.Security.SecurityException: Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> PermissionToken permToken)
> at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
> checkFrames, Int32 unrestrictedOverride)
> at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Demand()
> at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
> constr)
> at System.Data.SqlClient.SqlConnection.Open()
> at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
> xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
> Angola\Relatorios\Bibliotecas
> Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
>
> Must I edit some .config file so that I can get those permissions?
> Thanks in advance,
> Goncalo
>
> "Charles Kangai" wrote:
>> If you are using the built-in .NET assembly dll's you do not need to copy
>> the
>> files. However, you may need to add references to them in your report.
>> Access
>> the Report Properties screen by right-clicking the report selector and
>> choosing Properties. On the References tab, add references to any DLLs
>> you
>> use in your report.
>> Charles Kangai, MCDBA, MCT
>> "Goncalo" wrote:
>> > Thanks Charles, but I already did that and it still doesn't work.
>> > The dll's I use are System.Data.dll and System.Web.dll.
>> > Could it be that I must set some kind of permissions or perhaps change
>> > the
>> > web.config file to add these references?
>> >
>> > Thanks in advance,
>> > Gonçalo
>> >
>> > "Charles Kangai" wrote:
>> >
>> > > Your assembly dll files must be copied to the ReportServer\bin folder
>> > > of the
>> > > report server for your reports to work after being deployed to the
>> > > server. If
>> > > you are previewing from Report Designer, the assembly dll files must
>> > > be
>> > > copied to the 80\Tools\Report Designer folder also.
>> > >
>> > > Charles Kangai, MCDBA, MCT
>> > >
>> > > "Goncalo" wrote:
>> > >
>> > > > Hi all,
>> > > > I have a report in which I use some embedded code.
>> > > > The assemblies I use are standard .NET assemblies.
>> > > >
>> > > > In my PC the rendering of the report goes fine, but when i try to
>> > > > render
>> > > > this report on another machine, the fields that call the embedded
>> > > > code
>> > > > (code!some_function) do not render correctly.
>> > > >
>> > > > Any help would be appreciated,
>> > > > thnks!
>> > > > Goncalo
>> > > >|||@.charles kangai: can you also explain how this must be done? Your kind of
answer is the one i like most!!! Especially it includes wrong hints as in
your answer!
In my case the following helps:
The .net code must be modified by write a line direct above your function
like this:
<PermissionSet(SecurityAction.Assert, Unrestricted:=True)> _
Public Function InitOracleConn() As Boolean
...
End Function
The rssrvpolicy.config and RSReportServer.config must be modified. You need
something more as described in the documentation. I did it in the following
way:
I added this code to RSReportServer.config before the </Configuration> Tag:
---
<CustomAssemblies>
<Default>
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution"
/>
</PermissionSet>
</Default>
</CustomAssemblies>
<CustomAssemblies>
<Assembly Name="AIDAReportTranslator, Version=1.0.1.2, Culture=neutral,
PublicKeyToken=null">
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
</PermissionSet>
</Assembly>
<Default>
<PermissionSet class="System.Security.PermissionSet">
<IPermission
class="System.Security.Permissions.SecurityPermission"
version="1"
Flags="Execution"
/>
</PermissionSet>
</Default>
</CustomAssemblies>
---
In the rssrvpolicy.config I added this code before the
</NamedPermissionSets> Tag
---
<PermissionSet class="NamedPermissionSet"
version="1"
Name="MyNewFilePermissionSet"
Description="A special permission set that grants read access to my file.">
<IPermission class="Microsoft.Data.Odbc.OdbcPermission"
version="1"
Flags="Assertion, Execution"/>
</PermissionSet>
--
and I added the following code group after the last code group:
---
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyNewCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Programme\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\AIDAReportTranslator.dll"/>
</CodeGroup>
---
The file rspreviewpolicy.config must not be modified ! ! !
Ths
"Charles Kangai" wrote:
> This is a well documented scenario. You are trying to access an external
> resource from your code. By default SQLRS does not allow this unless you
> configure code access security. Code running on your report server is only
> granted Execute permission, by default, which means that only in-memory
> computations are allowed. Access to files, databases, URLs, etc. are not
> allowed. Please look up the documentation to see how to configure code access
> security. You will need to do three things:
> 1) Modify your code to assert the database access permission.
> 2) configure the rspreviewpolicy.config file in the Report Designer folder
> if you want to test your permissions in the Designer (viewing report using
> Debug/Start).
> 3) configure the rssvrpolicy.config file in the ReportServer folder if you
> are going to deploy the report that uses your assembly.
> Charles Kangai, MCDBA, MCT
> "Goncalo" wrote:
> > Thanks once again Charles! I have done that already but it didn't work either.
> > I know now what is going wrong.
> > In my custom code I use a connection to a database. When I render the report
> > with the Report Designer everything goes fine but when I deploy it to the
> > ReportServer I get an exception:
> >
> > "System.Security.SecurityException: Request for the permission of type
> > System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> > Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> > at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet
> > grantedSet, PermissionSet deniedSet, CodeAccessPermission demand,
> > PermissionToken permToken)
> > at System.Security.CodeAccessSecurityEngine.Check(PermissionToken
> > permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32
> > checkFrames, Int32 unrestrictedOverride)
> > at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission
> > cap, StackCrawlMark& stackMark)
> > at System.Security.CodeAccessPermission.Demand()
> > at System.Data.SqlClient.SqlConnectionString.Demand(SqlConnectionString
> > constr)
> > at System.Data.SqlClient.SqlConnection.Open()
> > at CalculosDiversos.CalculosDiversos.cambios(String xMoeda1, String
> > xMoeda2, Int32 iAno, Int32 iMes) in E:\Projectos\Clientes\Consulado
> > Angola\Relatorios\Bibliotecas
> > Auxiliares\CalculosDiversos\CalculosDiversos.vb:line 254"
> >
> >
> > Must I edit some .config file so that I can get those permissions?
> >
> > Thanks in advance,
> > Goncalo
> >
> >
> > "Charles Kangai" wrote:
> >
> > > If you are using the built-in .NET assembly dll's you do not need to copy the
> > > files. However, you may need to add references to them in your report. Access
> > > the Report Properties screen by right-clicking the report selector and
> > > choosing Properties. On the References tab, add references to any DLLs you
> > > use in your report.
> > >
> > > Charles Kangai, MCDBA, MCT
> > >
> > > "Goncalo" wrote:
> > >
> > > > Thanks Charles, but I already did that and it still doesn't work.
> > > > The dll's I use are System.Data.dll and System.Web.dll.
> > > > Could it be that I must set some kind of permissions or perhaps change the
> > > > web.config file to add these references?
> > > >
> > > > Thanks in advance,
> > > > Gonçalo
> > > >
> > > > "Charles Kangai" wrote:
> > > >
> > > > > Your assembly dll files must be copied to the ReportServer\bin folder of the
> > > > > report server for your reports to work after being deployed to the server. If
> > > > > you are previewing from Report Designer, the assembly dll files must be
> > > > > copied to the 80\Tools\Report Designer folder also.
> > > > >
> > > > > Charles Kangai, MCDBA, MCT
> > > > >
> > > > > "Goncalo" wrote:
> > > > >
> > > > > > Hi all,
> > > > > > I have a report in which I use some embedded code.
> > > > > > The assemblies I use are standard .NET assemblies.
> > > > > >
> > > > > > In my PC the rendering of the report goes fine, but when i try to render
> > > > > > this report on another machine, the fields that call the embedded code
> > > > > > (code!some_function) do not render correctly.
> > > > > >
> > > > > > Any help would be appreciated,
> > > > > > thnks!
> > > > > > Goncalo
> > > > > >
Embadded code in VB.Net
Hello everyone!
We have a small problem with one report ,that include VB code.
The code - Vb function ,that connect to Active Directory and return some
value
from DB
When I run my report in local machine I got correctly results,
when I deploy the report - I have #Error.
In the custom code I did all necessary references .
Please help me and sorry for my EnglishOn the server, by default, all code has execute-only privileges. You need to
create a custom assembly to hold this code and then grant addition
permissions to it. See
http://msdn.microsoft.com/SQL/sqlwarehouse/ReportingServices/default.aspx?pull=/library/en-us/dnsql2k/html/dngrfcodeaccesssecurityinsqlserver2000reportingservices.asp.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vikont" <Vikont@.discussions.microsoft.com> wrote in message
news:2328E5C7-7A39-45D4-953E-A79B9B2FF1FD@.microsoft.com...
> Hello everyone!
> We have a small problem with one report ,that include VB code.
> The code - Vb function ,that connect to Active Directory and return some
> value
> from DB
> When I run my report in local machine I got correctly results,
> when I deploy the report - I have #Error.
> In the custom code I did all necessary references .
> Please help me and sorry for my English|||Did you follow the guidance in
http://msdn.microsoft.com/SQL/sqlwarehouse/ReportingServices/default.aspx?pull=/library/en-us/dnsql2k/html/dngrfcodeaccesssecurityinsqlserver2000reportingservices.asp?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vikont" <Vikont@.discussions.microsoft.com> wrote in message
news:2328E5C7-7A39-45D4-953E-A79B9B2FF1FD@.microsoft.com...
> Hello everyone!
> We have a small problem with one report ,that include VB code.
> The code - Vb function ,that connect to Active Directory and return some
> value
> from DB
> When I run my report in local machine I got correctly results,
> when I deploy the report - I have #Error.
> In the custom code I did all necessary references .
> Please help me and sorry for my English
We have a small problem with one report ,that include VB code.
The code - Vb function ,that connect to Active Directory and return some
value
from DB
When I run my report in local machine I got correctly results,
when I deploy the report - I have #Error.
In the custom code I did all necessary references .
Please help me and sorry for my EnglishOn the server, by default, all code has execute-only privileges. You need to
create a custom assembly to hold this code and then grant addition
permissions to it. See
http://msdn.microsoft.com/SQL/sqlwarehouse/ReportingServices/default.aspx?pull=/library/en-us/dnsql2k/html/dngrfcodeaccesssecurityinsqlserver2000reportingservices.asp.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vikont" <Vikont@.discussions.microsoft.com> wrote in message
news:2328E5C7-7A39-45D4-953E-A79B9B2FF1FD@.microsoft.com...
> Hello everyone!
> We have a small problem with one report ,that include VB code.
> The code - Vb function ,that connect to Active Directory and return some
> value
> from DB
> When I run my report in local machine I got correctly results,
> when I deploy the report - I have #Error.
> In the custom code I did all necessary references .
> Please help me and sorry for my English|||Did you follow the guidance in
http://msdn.microsoft.com/SQL/sqlwarehouse/ReportingServices/default.aspx?pull=/library/en-us/dnsql2k/html/dngrfcodeaccesssecurityinsqlserver2000reportingservices.asp?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vikont" <Vikont@.discussions.microsoft.com> wrote in message
news:2328E5C7-7A39-45D4-953E-A79B9B2FF1FD@.microsoft.com...
> Hello everyone!
> We have a small problem with one report ,that include VB code.
> The code - Vb function ,that connect to Active Directory and return some
> value
> from DB
> When I run my report in local machine I got correctly results,
> when I deploy the report - I have #Error.
> In the custom code I did all necessary references .
> Please help me and sorry for my English
Sunday, February 26, 2012
else if statement
I have this function and is given me the error below the code and want to know which is my mistake
create function pt
(@.idpt INT,
@.option INT)
Returns Real
AS
Begin
if (@.option=1)
Begin
Return ( select sqrt(a.px*a.px + a.py*a.py)
from electron as a
where @.idpt = a.idap)
end
else if(@.option=2)
Begin
Return ( select sqrt(a.px*a.px + a.py*a.py)
from muon as a
where @.idpt = a.idap)
End
else
Begin
Return ( select sqrt(a.px*a.px + a.py*a.py)
from jet as a
where @.idpt = a.idap)
end
end
Msg 455, Level 16, State 2, Procedure pt, Line 22
The last statement included within a function must be a return statement.
Try putting a dummy "RETURN NULL" after the large IF block. I think the compiler isn't realizing that at least one of the 3 return statements you've got will always be called, and it's throwing a fit. The extra RETURN will never get called, but it'll at least keep the compiler happy.
|||
Try the code below.
Chris
Code Snippet
CREATE FUNCTION pt (@.idpt INT, @.option INT)
RETURNS REAL
AS
BEGIN
DECLARE @.Output REAL
IF (@.option = 1)
BEGIN
SELECT @.Output = SQRT(a.px * a.px + a.py * a.py)
FROM electron AS a
WHERE @.idpt = a.idap
END
ELSE
IF (@.option = 2)
BEGIN
SELECT @.Output = SQRT(a.px * a.px + a.py * a.py)
FROM muon AS a
WHERE @.idpt = a.idap
END
ELSE
BEGIN
SELECT @.Output = SQRT(a.px * a.px + a.py * a.py)
FROM jet AS a
WHERE @.idpt = a.idap
END
RETURN @.Output
END
Friday, February 17, 2012
Efficient Code round 2
I have taken the recommendations that I was given with my
first posting of this procdure code. I greatly
appreciate the help. I have written my 2nd attempt and
would like to get feedback on this version for anything
else that can optimize the queries.
Here is the code:
-- Procedure to Update DR Compliance values for all
Servers
-- *** This update handles records with a DRServiceLevel
of None for all RACriticalities
UPDATE AudServerDR
SET DRCompliance = 'NA',
RACompliance = 'NA',
TestCompliance = 'NA',
TotalCompliance = 'NA'
WHERE DRServiceLevel = 'None'
-- *** This update a RACriticality CASE of Critiacal,
Essential or Vital
UPDATE AudServerDR
SET DRCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
THEN 'Yes'
ELSE 'No'
END,
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END,
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
RAEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
AND RAName <> ''
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE varid IN (SELECT VARID
FROM AudServerDR
WHERE RACriticality IN
('Essential', 'Critical', 'Vital'))
AND DRServiceLevel <> 'None'
-- *** This update a RACriticality CASE of New Device
UPDATE AudServerDR
SET DRCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
THEN 'Yes'
ELSE 'No'
END,
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(dd, 30,
RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END,
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(dd, 30,
RAEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
AND RAName <> ''
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE varid IN (SELECT VARID
FROM AudServerDR
WHERE RACriticality = 'New Device')
AND DRServiceLevel <> 'None'
-- *** This update a RACriticality CASE of Non-Critical
UPDATE audServerDR
SET DRCompliance = 'NA',
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD
(mm, 12, RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = 'NA',
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <=
DATEADD(mm, 12, RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE VARID IN (SELECT VARID
FROM audServerDR
WHERE RACriticality = 'Non-critical')Much better, but you still missed the point of the CASE Expressions.
Roll all of these UPDATEs into one UPDATE with case expressions that
return ('yes', 'no,' N/A'). If that looks too complex, then at least
consolidate the ones after the mass 'N/A' update, like this:
UPDATE AudServerDR
SET DRCompliance
= CASE
WHEN DRPlanName <> ''
AND DRSignName <> ''
AND (CURRENT_TIMESTAMP <= DATEADD(mm, 12, DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate))
OR (CURRENT_TIMESTAMP <= DATEADD(mm, 6,DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate))
THEN 'Yes'
ELSE 'No'
END,
.
WHERE var_id
IN (SELECT var_id
FROM AudServerDR
WHERE RACriticality
IN ('Essential', 'Critical', 'Vital'))
AND DRServiceLevel <> 'None' ;
The goal is to make as few passes over the table as possible.|||On Tue, 12 Apr 2005 07:52:13 -0700, Jazz wrote:
>I have taken the recommendations that I was given with my
>first posting of this procdure code. I greatly
>appreciate the help. I have written my 2nd attempt and
>would like to get feedback on this version for anything
>else that can optimize the queries.
(snip)
Hi Jazz,
I had started an attempt to rewrite this to do all updates in one single
pass, but I found that some things are unclear:
(snip)
>WHERE varid IN (SELECT VARID
> FROM AudServerDR
> WHERE RACriticality IN
>('Essential', 'Critical', 'Vital'))
> AND DRServiceLevel <> 'None'
(snip)
If varid the primary key column in AudServerDR? If yes, then this is
unnecessary complex. If no, then I don't understand your queries.
Note - this question could have been avoided if you had posted DDL (that
is: CREATE TABLE statement, including constraints and properties) for
the table.
(snip)
>-- *** This update a RACriticality CASE of Non-Critical
>UPDATE audServerDR
>SET DRCompliance = 'NA',
> RACompliance = CASE
(snip)
>WHERE VARID IN (SELECT VARID
> FROM audServerDR
> WHERE RACriticality = 'Non-critical')
Here, I am missing the "AND DRServiceLevel <> 'None'" part that was
present in the previous subqueries. Does this imply that this UPDATE
will overwrite some of the rows already affected by the very first
update (the one that sets all compliance columns to 'NA' for rows with
service level equal to 'None')? Have you forgotten to include it but
should it be there? Or is there some constraint on your data that makes
the combination of RACriticality 'Non-critical' and ServiceLevel 'None'
impossible?
Answer these questions, and I think I'll be able to cough up an UPDATE
statement that makes all required modifications in one single pass.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
first posting of this procdure code. I greatly
appreciate the help. I have written my 2nd attempt and
would like to get feedback on this version for anything
else that can optimize the queries.
Here is the code:
-- Procedure to Update DR Compliance values for all
Servers
-- *** This update handles records with a DRServiceLevel
of None for all RACriticalities
UPDATE AudServerDR
SET DRCompliance = 'NA',
RACompliance = 'NA',
TestCompliance = 'NA',
TotalCompliance = 'NA'
WHERE DRServiceLevel = 'None'
-- *** This update a RACriticality CASE of Critiacal,
Essential or Vital
UPDATE AudServerDR
SET DRCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
THEN 'Yes'
ELSE 'No'
END,
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END,
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
RAEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
AND RAName <> ''
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE varid IN (SELECT VARID
FROM AudServerDR
WHERE RACriticality IN
('Essential', 'Critical', 'Vital'))
AND DRServiceLevel <> 'None'
-- *** This update a RACriticality CASE of New Device
UPDATE AudServerDR
SET DRCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
THEN 'Yes'
ELSE 'No'
END,
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(dd, 30,
RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END,
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(dd, 30,
RAEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
AND RAName <> ''
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE varid IN (SELECT VARID
FROM AudServerDR
WHERE RACriticality = 'New Device')
AND DRServiceLevel <> 'None'
-- *** This update a RACriticality CASE of Non-Critical
UPDATE audServerDR
SET DRCompliance = 'NA',
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD
(mm, 12, RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = 'NA',
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <=
DATEADD(mm, 12, RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE VARID IN (SELECT VARID
FROM audServerDR
WHERE RACriticality = 'Non-critical')Much better, but you still missed the point of the CASE Expressions.
Roll all of these UPDATEs into one UPDATE with case expressions that
return ('yes', 'no,' N/A'). If that looks too complex, then at least
consolidate the ones after the mass 'N/A' update, like this:
UPDATE AudServerDR
SET DRCompliance
= CASE
WHEN DRPlanName <> ''
AND DRSignName <> ''
AND (CURRENT_TIMESTAMP <= DATEADD(mm, 12, DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate))
OR (CURRENT_TIMESTAMP <= DATEADD(mm, 6,DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate))
THEN 'Yes'
ELSE 'No'
END,
.
WHERE var_id
IN (SELECT var_id
FROM AudServerDR
WHERE RACriticality
IN ('Essential', 'Critical', 'Vital'))
AND DRServiceLevel <> 'None' ;
The goal is to make as few passes over the table as possible.|||On Tue, 12 Apr 2005 07:52:13 -0700, Jazz wrote:
>I have taken the recommendations that I was given with my
>first posting of this procdure code. I greatly
>appreciate the help. I have written my 2nd attempt and
>would like to get feedback on this version for anything
>else that can optimize the queries.
(snip)
Hi Jazz,
I had started an attempt to rewrite this to do all updates in one single
pass, but I found that some things are unclear:
(snip)
>WHERE varid IN (SELECT VARID
> FROM AudServerDR
> WHERE RACriticality IN
>('Essential', 'Critical', 'Vital'))
> AND DRServiceLevel <> 'None'
(snip)
If varid the primary key column in AudServerDR? If yes, then this is
unnecessary complex. If no, then I don't understand your queries.
Note - this question could have been avoided if you had posted DDL (that
is: CREATE TABLE statement, including constraints and properties) for
the table.
(snip)
>-- *** This update a RACriticality CASE of Non-Critical
>UPDATE audServerDR
>SET DRCompliance = 'NA',
> RACompliance = CASE
(snip)
>WHERE VARID IN (SELECT VARID
> FROM audServerDR
> WHERE RACriticality = 'Non-critical')
Here, I am missing the "AND DRServiceLevel <> 'None'" part that was
present in the previous subqueries. Does this imply that this UPDATE
will overwrite some of the rows already affected by the very first
update (the one that sets all compliance columns to 'NA' for rows with
service level equal to 'None')? Have you forgotten to include it but
should it be there? Or is there some constraint on your data that makes
the combination of RACriticality 'Non-critical' and ServiceLevel 'None'
impossible?
Answer these questions, and I think I'll be able to cough up an UPDATE
statement that makes all required modifications in one single pass.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)