Invoking an assembly before a report is rendered

Hi All,
I want to implement a sort of licensing policy for the reports
developed by me. For this I have created my own custom assembly. I
want to invoke the assembly just before the report is rendered so that
I can check if the license is valid and then allow the report to be
rendered, else a default page.
Thanxs in anticipation.You might want to check out this section in BOL (OnInit):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_7gma.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"CoolGuyFromPune" <raviful@.hotmail.com> wrote in message
news:eed4d072.0407050640.7cc929cd@.posting.google.com...
> Hi All,
> I want to implement a sort of licensing policy for the reports
> developed by me. For this I have created my own custom assembly. I
> want to invoke the assembly just before the report is rendered so that
> I can check if the license is valid and then allow the report to be
> rendered, else a default page.
> Thanxs in anticipation.sql

Invoking a SQL Server user-defined function via SQL passed to Informix

==========
Background
==========
My colleague and I are using MS Reporting Services to create reports based upon data extracted from an Informix database. Inseveral reports, we have the need to parse the contents of a variable length field in order to extract a variable lengthforeign key value.

Note that the key value is of variable length and embedded within a string that's also of variable length.

==========
Question
==========
Given the fact that we aren't allowed to add anything within the back-end Informix environment, we wrote a SQL Server userdefined function that can extract a variable length "key" value from a variable length string. My question is as follows:

Is it even possible to invoke a SQL Server user defined function in our SQL statement (passed to Informix) within the context of Reporting Services?

==========
Environment Notes
==========
- We are NOT allowed to add anything within the back-end Informix db environment.

- We are connecting via an Informix ODBC driver with a linked Informix server defined in our SQL Server environment.

- We've tried defining the user-defined function within the "master", "ReportServer" and "ReportServerTempDB" databases with the appropriate permissions set.

==========
Error Messages
==========
We've received the following error message after trying to invoke the SQL Server user defined function:

SELECT field1, field2, field3, dbo.udf_GetStringElement(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Identifier length exceeds the maximum allowed by this version of the server.

We receive the following error message after creating a new SQL Server user defined function using a shorter name:

SELECT field1, field2, field3, dbo.gse(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Routine (dbo.gse) can not be resolved.

Any feedback would be appreciated,

ndm

have you tried using a custom code function for it ?|||

Hey, thanks for the reply.

I'm not sure I understand what you mean by "custom code function". The user defined functions I mentioned, "udf_GetStringElement", "gse", are user defined functions that I wrote. It's not one of the "system" functions (e.g. "fn_isreplmergeagent") found in master.

I merely created the function WITHIN the context of the "master" db and eventually within "Report Server" and "ReportServerTempDB" in an attempt to invoke the function within a Report Services SQL statement.

|||By Custom Code function I mean the custom code vb.net functions you canwrite under Report -> Properties -> Code tab. You canreplicate the logic of your user defined function you have in sql intovb.net.
what xactly foes your user defined function do ?
|||

Ah, I understand what you're saying about simply replicating/writing the string parsing/extraction logic within the Report Services environment, but our issue isn't really a (post-data aggregation) formatting problem.

Our SQL Server user defined function parses a variable length string in order to extract a variable length "foreign key" value that we'll need to use for joining data from several Informix tables.

Unless there's another approach of which I'm unaware, I'm assuming that we need to parse this string DURING the SQL execution in order to perform the requisite join(s).

Appreciate the feedback,

ndm

|||I didnt understand the situation completely..you have the SELECT stmt from Informix db but the UDF is in SQL Server right ? does the udf do anything other than string parsing ? looks like its a little complicated and we could be hitting the limitations ( one of the many) of RS.|||

Correct; the SELECT statement (defined within shared Report Services dataset) is passed to Informix and our custom UDF is in SQL Server.

More specifically, I've tried placing our custom SQL Server UDF within several of the SQL Server databases ("master", "ReportServer" and "ReportServerTempDB"). When I attempt to invoke the UDF within the SQL statement passed to Informix, I receive the aforementioned error messages.

The key point is that we need to be able to parse DURING the SQL execution in order to perform some requisite join(s).

Within a pure SQL Server environment, I can access the UDF from master (or, for that matter, any other SQL Server db so long as the appropriate permissions are set). I'm not even sure if what we're trying to accomplish is possible since the SQL statement is being passed to Informix. I was wondering if there was some "Reporting Services" method for invoking our custom UDF defined WITHIN the SQL Server realm.

Again, I appreciate the reply.

ndm

|||

The SQL statement you have is executed on Informix DB engine and it wouldnt understand the SQL UDF..unless you make a specific connection to SQL for which it may not be possible in 1 SELECT stmt..you prbly might need to use a set of stmts..If the UDF is only doing some string parsing I would recommend moving it into RS custom code and rewriting it in vb.net. Cant think of anything else..

|||

I would create a DTS package that is scheduled to get the data from Informix and populate the Reporting Service database. Try the urls below for more info. Hope this helps.

http://www.sqldts.com

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/infmxsql.mspx#EDAA

Kind regards,

Gift Peddie

|||

Yes, I'm thinking that we may have to take a less directextract+dump+cleanse route (Informix -> MS SQL Server -> MS Reporting Services) for this scenario. We were trying to avoid taking snapshots of the data and having to maintain a secondary (albeit temporary) data store.

Your recommendation merits further consideration.

Thanks for the feedback,

ndm

Invalid object name 'ReportServerTempDB.dbo.PersistedStream'

Hi,
I've restored my report server 2005 from serverA to serverB and
Reporting Services will run and I can access the reports, but the
above error will pop whenever I try to run a report. I followed the
steps outlined below - where did I go wrong?
1) backed up the encryption key on serverA
2) backed up the report server databases on both servers
3) shut down RS on serverB
4) detached the reportserver database on serverB
5) renamed reportserver.mdf and ldf to reportserver.mdf_old and
reportserver.ldf_old
6) restored reportserver.mdf and ldf from serverA onto serverB
7) started reporting services on serverB - failed to initialize
8) restored encryption key and then deleted the row from
REPORTSERVER.DBO.KEYS where machinename = 'serverB'
9) restarted reporting services and the pages displayed
10) accessed the report that I wanted to view and the error above
popped
11) tried deleting and re-creating reportserverTempDB by dropping and
recreating from the CatalogTemp.sql file from the install - made sure
that the collations matched
Here is the error text:
ReportingServicesService!runningjobs!4!4/3/2007-09:17:42:: e ERROR:
Error in timer Database Cleanup (NT Service) :
System.Data.SqlClient.SqlException: Invalid object name
'ReportServerTempDB.dbo.PersistedStream'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DatabaseCleanupTimer.CleanBatch()
at
Microsoft.ReportingServices.Library.DatabaseCleanupTimer.DoTimerAction()
at
Microsoft.ReportingServices.Diagnostics.TimerActionBase.TimerAction(Object
unused)On Apr 3, 10:41 am, "Tim" <timgoldenst...@.msn.com> wrote:
> Hi,
> I've restored my report server 2005 from serverA to serverB and
> Reporting Services will run and I can access the reports, but the
> above error will pop whenever I try to run a report. I followed the
> steps outlined below - where did I go wrong?
> 1) backed up the encryption key on serverA
> 2) backed up the report server databases on both servers
> 3) shut down RS on serverB
> 4) detached the reportserver database on serverB
> 5) renamed reportserver.mdf and ldf to reportserver.mdf_old and
> reportserver.ldf_old
> 6) restored reportserver.mdf and ldf from serverA onto serverB
> 7) started reporting services on serverB - failed to initialize
> 8) restored encryption key and then deleted the row from
> REPORTSERVER.DBO.KEYS where machinename => 'serverB'
> 9) restarted reporting services and the pages displayed
> 10) accessed the report that I wanted to view and the error above
> popped
> 11) tried deleting and re-creating reportserverTempDB by dropping and
> recreating from the CatalogTemp.sql file from the install - made sure
> that the collations matched
> Here is the error text:
> ReportingServicesService!runningjobs!4!4/3/2007-09:17:42:: e ERROR:
> Error in timer Database Cleanup (NT Service) :
> System.Data.SqlClient.SqlException: Invalid object name
> 'ReportServerTempDB.dbo.PersistedStream'.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
> stateObj)
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
> async)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method, DbAsyncResult result)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.DatabaseCleanupTimer.CleanBatch()
> at
> Microsoft.ReportingServices.Library.DatabaseCleanupTimer.DoTimerAction()
> at
> Microsoft.ReportingServices.Diagnostics.TimerActionBase.TimerAction(Object
> unused)
This MS article might be of assistance.
http://msdn2.microsoft.com/en-us/library/ms159093.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique - it turned out to be a permissions issue on the
ReportServerTempDB in that RSExecRole role

Invalid Descriptor Ind

I am using Crystal Reports 8.0 in Visual Basic 6.0 With SQL Server 7.0.
When I pass the parameters to the report to see the results the following
error message appears
"ODBC Error : [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index"
Can anyone help me.
Saqib
Looks like a SQLGetData error
This artical may helps
http://msdn.microsoft.com/library/de...us/odbc/htm/od
bcusing_sqlbindcol.asp
...
There are a number of restrictions on using SQLGetData. In general, columns
accessed with SQLGetData:
Must be accessed in order of increasing column number (because of the way
the columns of a result set are read from the data source). For example, it
is an error to call SQLGetData for column 5 and then call it for column 4.
Cannot be bound.
Must have a higher column number than the last bound column. For example,
if the last bound column is column 3, it is an error to call SQLGetData for
column 2. For this reason, applications should be careful to place long
data columns at the end of the select list.

Invalid Descriptor Ind

I am using Crystal Reports 8.0 in Visual Basic 6.0 With SQL Server 7.0.
When I pass the parameters to the report to see the results the following
error message appears
"ODBC Error : [Microsoft][ODBC SQL Server Driver]Invalid Descriptor
Index"
Can anyone help me.
SaqibLooks like a SQLGetData error
This artical may helps
http://msdn.microsoft.com/library/d...-us/odbc/htm/od
bcusing_sqlbindcol.asp
...
There are a number of restrictions on using SQLGetData. In general, columns
accessed with SQLGetData:
Must be accessed in order of increasing column number (because of the way
the columns of a result set are read from the data source). For example, it
is an error to call SQLGetData for column 5 and then call it for column 4.
Cannot be bound.
Must have a higher column number than the last bound column. For example,
if the last bound column is column 3, it is an error to call SQLGetData for
column 2. For this reason, applications should be careful to place long
data columns at the end of the select list.

invalid character when I browse Reports

When I go to inetmgr
=> Web Sites
=> Reports
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error
and then click the Refresh button, or try again later.
----
A name was started with an invalid character. Error processing
resource 'http://localhost/Reports/'. Line 1, Position 2
<%@. Page language="c#" Codebehind="Home.aspx.cs"
AutoEventWireup="false"
Inherits="Microsoft.ReportingServices.UI.HomePag...
When I go to inetmgr
=> Web Sites
=> ReportServer
Reporting Services Error
----
The report server has encountered a configuration error. See the
report server log files for more information.
(rsServerConfigurationError)
Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.
3\Reporting Services\ReportServer\RSReportServer.config' is denied.
----
SQL Server Reporting Services
Any ideas welcome,
BryanThis is a wild guess but check and make sure that the Reports and
ReportServer websites in IIS are setup for the correct version of the dotnet
framework. For RS 2000 it should be 1.1 for RS 2005 it needs to be 2.0.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1193425095.280637.260080@.d55g2000hsg.googlegroups.com...
> When I go to inetmgr
> => Web Sites
> => Reports
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
> and then click the Refresh button, or try again later.
>
> ----
> A name was started with an invalid character. Error processing
> resource 'http://localhost/Reports/'. Line 1, Position 2
> <%@. Page language="c#" Codebehind="Home.aspx.cs"
> AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.HomePag...
> When I go to inetmgr
> => Web Sites
> => ReportServer
> Reporting Services Error
> ----
> The report server has encountered a configuration error. See the
> report server log files for more information.
> (rsServerConfigurationError)
> Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.
> 3\Reporting Services\ReportServer\RSReportServer.config' is denied.
> ----
> SQL Server Reporting Services
> Any ideas welcome,
> Bryan
>

Introduction to OLAP CUBES

Hi,

I was wondering if I could get some help in how to get started with designing OLAP CUBES. I have done many other reports using matrix also. I wanted to use this technology in one of my reports so that my users can drag and drop data the way they want it, just like a pivot table in excel. Thanks,

-Rohit

Hi rogupta,

What you need is Analysis Services. I suggest you get familiar with terms like fact tables and dimension tables. Building a cube isn't that hard to learn but you need to do some excercises. Also get familiar with denormalizing tables, the way you build the tables is important for drilling down in the cube.

The following provides some samples if you're using SQL Server 2005: http://www.microsoft.com/downloads/details.aspx?FamilyID=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

Also have a look at http://www.microsoft.com/sql/solutions/bi/bianalysis.mspx for SQL Server 2000

Good luck!

Worf

Internet reporting

I've recently set up SQL Server 2005 and have implemented Report Services /
Report Manager for users to be able to view reports via our intranet. Many
of the users are remote users, from time to time, and I would like for these
users to see the same reports from the Internet (preferrably being able to
use their same Windows Auth login). Our SQL Server is a separate machine
from our web server, and the current Report Services and Manager is set up on
the SQL Server. Can I set up the Report Manager on either the SQL Server,
web server, or both for users to be able to view reports from both the
intranet and Internet? If so, how? I'd also prefer this to be seamless, so
that the user doesn't know the difference between intranet and Internet
reports (if their is a difference).I suggest you go through this first in your online books
search for "Configuring a Report Server for Internet Access" I think your
problem will be solved after going through this.
Amarnath
"Kat" wrote:
> I've recently set up SQL Server 2005 and have implemented Report Services /
> Report Manager for users to be able to view reports via our intranet. Many
> of the users are remote users, from time to time, and I would like for these
> users to see the same reports from the Internet (preferrably being able to
> use their same Windows Auth login). Our SQL Server is a separate machine
> from our web server, and the current Report Services and Manager is set up on
> the SQL Server. Can I set up the Report Manager on either the SQL Server,
> web server, or both for users to be able to view reports from both the
> intranet and Internet? If so, how? I'd also prefer this to be seamless, so
> that the user doesn't know the difference between intranet and Internet
> reports (if their is a difference).|||Thank you for the suggestion. I've already read this books-online entry, and
many others as well. They did not answer my question and that's why I came
to the newsgroup.
"Amarnath" wrote:
> I suggest you go through this first in your online books
> search for "Configuring a Report Server for Internet Access" I think your
> problem will be solved after going through this.
> Amarnath
>
> "Kat" wrote:
> > I've recently set up SQL Server 2005 and have implemented Report Services /
> > Report Manager for users to be able to view reports via our intranet. Many
> > of the users are remote users, from time to time, and I would like for these
> > users to see the same reports from the Internet (preferrably being able to
> > use their same Windows Auth login). Our SQL Server is a separate machine
> > from our web server, and the current Report Services and Manager is set up on
> > the SQL Server. Can I set up the Report Manager on either the SQL Server,
> > web server, or both for users to be able to view reports from both the
> > intranet and Internet? If so, how? I'd also prefer this to be seamless, so
> > that the user doesn't know the difference between intranet and Internet
> > reports (if their is a difference).|||Is there no one else that can offer some insight to my questions?
"Kat" wrote:
> I've recently set up SQL Server 2005 and have implemented Report Services /
> Report Manager for users to be able to view reports via our intranet. Many
> of the users are remote users, from time to time, and I would like for these
> users to see the same reports from the Internet (preferrably being able to
> use their same Windows Auth login). Our SQL Server is a separate machine
> from our web server, and the current Report Services and Manager is set up on
> the SQL Server. Can I set up the Report Manager on either the SQL Server,
> web server, or both for users to be able to view reports from both the
> intranet and Internet? If so, how? I'd also prefer this to be seamless, so
> that the user doesn't know the difference between intranet and Internet
> reports (if their is a difference).

Internet integration

Is there a way to embed reports in an INTERNET app (ASP.NET) using the following requirements:

- Get reports from Report Server (with toolbar and parameters)
- Security authentication/authorisation comes from ASP.NET web app (single sign-on, not Windows authentication, but custom)

In brief, I just want to host reports in an ASP.NET app, as they appear in the html viewer, but embedded in the app, on the Internet. URL access is perfect, but we require a single sign on with CUSTOM security.

Problem : how to integrate the security context of the web app and the rs, rendering reports WITHIN the web app.
Problems I am experiencing:

- Using an IFRAME to render the reports requires that the browser has direct access to the report server. We don't want this unless we can authenticate/authorise the user once only, through our custom security interface.
- Using the asp.net web app to call the API Render method will not render binary content such as images, unless we use the mhtml rendering format. How do we get the asp.net app to call the api and return an html page with all the correct links to images stored on the server?
Ideas:
Should we develop a custom Security extension?
Any help would be greatly appreciated.

Have you looked at the report execution web service? It seems to fit all of your requirements:

http://msdn2.microsoft.com/en-us/library/ms155071.aspx

|||

Our client has identical requirements - in short, is it possible to fulfill these requirements with RS2000? RS2005 is not an option.

The only compromise appears to be using web-services rendering, but this loses all interactivity functionality such as paging, drill through, document map and collapsible regions - you would also lose the ActiveX control unless you added script code. To me, this represented the best compromise.

Is there a way to fulfill the original requirements? The toolbar is mandatory.

|||

HI,

Thanks for your response. Can I render html (not mhtml) from the web service? How does it deal with binary content such as images, which would be in a separate file in html? I presume you can render only a stream of info, not two streams?

Actually, I admit to overlooking the functionality provided by report viewer. I discovered yesterday that it operates in two "modes" : Remote or local. Remote uses reports straight off the server (.rdl) and shows them beautifully, just as they are rendered in html by report manager (including toolbar and params). Local, however, uses a differnent mode of operation - the reports are rendered from rdlc files within your application without the parameters. You can pass parameters programmatically to local reports, but that's a lot of programming (duplication of effort - hence, my problem).

Incidentally, we solved the security problem by using remote mode with report viewer. The web.config has <identity impersonate="true">. We also set the username and password attributes of the identity tag, and set up a user account on the box, corresponding to the user identified in <identity>. The user account was configured to be allowed to access the report on the report server (report manager security setup) So the ASP.NET web app impersonates for our web clients and retrieves the relevant report, based upon our web app's security system. Our web app could use the SOAP API to iterate all reports from ReportServer and manage the reports through it's custom security interface.
In sum : USE THE REPORT VIEWER IN REMOTE MODE !

I hope this helps other confused souls out there.