Crystal Report on Remoting Problem.

Hi All,

Following is the scenario i am working on.
1. There are some .RPT files which reside on server.
2. Client will request to see the report, in that case RPT file will
be dynamically loaded on server , dataset is passed as datasource to
report and then that report is streamed to client using
ExportToStream method.
3. Client will receive this stream and reconstruct Report Document
and will display in viewer.
4. Client Server communication is mentained using .Net remoting.

Everything works fine in stand alone mode. Problem is in remoting
mode.
Problem occurs at the time of loading report file dynamically,
following is the exception i get.

The type
CrystalDecisions.CrystalReports.Engine.LoadSaveReportException in
Assembly CrystalDecisions.Shared, Version=9.1.5000.0,
Culture=neutral, PublicKeyToken=692fbea5521e1304 is not marked as
serializable.

Server stack trace:
at
System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitSe
rialize(Object obj, ISurrogateSelector surrogateSelector,
StreamingContext context, SerObjectInfoInit serObjectInfoInit,
IFormatterConverter converter)
at
System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.Serial
ize(Object obj, ISurrogateSelector surrogateSelector,
StreamingContext context, SerObjectInfoInit serObjectInfoInit,
IFormatterConverter converter)
at
System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize
(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean
fCheck)
at
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serial
ize(Stream serializationStream, Object graph, Header[] headers,
Boolean fCheck)
at
System.Runtime.Remoting.Channels.BinaryServerFormatterSink.SerializeR
esponse(IServerResponseChannelSinkStack sinkStack, IMessage msg,
ITransportHeaders& headers, Stream& stream)
at
System.Runtime.Remoting.Channels.BinaryServerFormatterSink.ProcessMes
sage(IServerChannelSinkStack sinkStack, IMessage requestMsg,
ITransportHeaders requestHeaders, Stream requestStream, IMessage&
responseMsg, ITransportHeaders& responseHeaders, Stream&
responseStream)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage
(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke
(MessageData& msgData, Int32 type)
at Hebron.Common.Interfaces.IFeesReportController.GetInvoice
(ArrayList oArrylst)
at Hebron.ReportClient.ReportTool.PrintReport(ReportName name,
ArrayList oArry)
at Hebron.FeesClient.ReviewInvoices.btnPrint_Click(Object sender,
EventArgs e)Normally if an object is serializable then you can just add an attribute [serialziable] to your class and it works.

Crystal Report Document Object is not Serializable as per the Business Object documentation(atleast till version 10). During remoting the object needs to be serialized and that is why you get the error. Try to find out from Business Objects support team if you can, at all serialize the object ? Will face the same issue if using ASP.NET "StateServer" session and try to save the Report Document object in session.

Frank

Cross Referance dB objects accross store procs

We are in the midst of cleaning up database01. There are objects in database01 that are widely used in store procedures that reside in database02.

Problem is: I need to know which tables and view from database01 are in use by store procedures in database02. This is important because if I accidently decommission tables that are currently in use by production class s-procedures, it will not be pretty. A sample output could look like:

StoreProc UsingObject
sp001 ctblCodeName
sp002 tblUnitSales
so003 tblSalesTrans
.......... .................

I am not a programmer and none of our programmers here claim that there is any solution to this problem without spending thousands on s/w licenses. Any simple solution + code snippet that will help me resolve this problem by myself would be incredibly valuable.

In disstress and frustration . . . .

JJOSHI

Hi JJOSHI,

There is no full-proof method of finding this information, though you could try a couple of things to get some pointers:

1) Check the sys.sql_dependencies catalog view for information (see books online for more information, if this is Sql 2005)

2) Try searching through the syscomments table (which is the table that holds the definition for all code modules in Sql)

Note that this doesn't capture references of things like foreign keys, constraints, etc....

There are some 3rd party components that try to give you this information, but MS doesn't guarantee this information, and supportability would be only through that company.

For searching through the syscomments, you could try something like this (NOT SUPPORTED IN ANY WAY BY ANYONE):

declare @.name varchar(500), @.sql varchar(1000)

select @.name = '%' + '<put object name here>' + '%'

-- Any jobs with it?

select j.name as JobName, s.step_name, s.command, s.database_name, j.enabled

from msdb.dbo.sysjobsteps s

join msdb.dbo.sysjobs j

on j.job_id = s.job_id

where command like(@.name)

-- Any db's reference it?

select @.sql = 'use [?] select object_name(id) as objectname, db_name() as dbname, substring(text, patindex(''' + @.name + ''', text) - 20,150) from syscomments where text like(''' + @.name + ''')'

exec master.dbo.sp_MSforeachdb @.sql

|||

To add to what has already been said, in 2005, you can use the object_definition function. I had written a blog about this a while back (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry) to cover something like this. Since object_definition includes the whole object text in a single part (syscomments breaks it up into chunks in 2000) you can use a query like:

DECLARE @.value nvarchar(128)
SET @.value = '<databaseName>.'

SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0

The replaces get rid of brackets [] and then if you are looking for the database name plus the dot, you should find all references.

"This is important because if I accidently decommission tables that are currently in use by production class s-procedures, it will not be pretty."

I certainly hope you are planning on testing this first :) At the very least, you are demonstrating one great thing about susing all stored procedure access. Easy searching the code for references.