Report SnapShot Creation Programmatically Through Java
Jan 5, 2007
Hi,
I am facing some issues while creating report snapshots programmatically using axis (Java platform for creating and deploying web services applications).
To create a report snapshot what should be the methods called ? I tried using UpdateReportExecutionSnapshot() but didn't find any parameters to set the schedule information which would say when the execute the report snapshot. Can you please guide me for this ?
Also I had one more doubt regarding report snapshots. If a parameterized report is configured as snapshot then we would require to set default parameters for the report.
Is there a way to create snapshots of the same report with different parameters ?
eg : The employee information report displays the information based on the employee id taken as a parameter. So is it possible to create snapshots with different employee id's ?
How can we programmatically cause a Manual Report History Snapshot to be created.
I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters.
I have created a package which simply imports data from a flat file to a SQL Server table. But I need to incorporate a data conversion component by which I may change the source-destination column mapping programmatically. So what I thought that I need to add a data conversion component into the dataflow task. After adding this component (I found its component id as {C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}) I have created a path which establishes the mapping between output columns of source component and the input columns of data conversion component. Now I am not sure how to establish the mapping between the data conversion component€™s input column collection and output column collection.
I am giving my code snippet here, IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New(); sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}"; €¦ €¦ €¦. // Code for configuring the source data flow component
IDTSComponentMetaData90 conversionDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();// creating data conversion conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";// This is the GUID for data conversion component CManagedComponentWrapper conversionInstance = conversionDataFlowComponent.Instantiate();//Instantiate conversionInstance.ProvideComponentProperties(); // Now creating a path to connet the source and conversion IDTSPath90 fPath = dataFlowTask.PathCollection.New(); fPath.AttachPathAndPropagateNotifications( sourceDataFlowComponent.OutputCollection[0], conversionDataFlowComponent.InputCollection[0]); // Sould I need to accuire connect for data conversion? Im not sure conversionInstance.AcquireConnections(null); conversionInstance.ReinitializeMetaData(); // Get the input collection IDTSInput90 input = conversionDataFlowComponent.InputCollection[0]; IDTSVirtualInput90 vInput = input.GetVirtualInput(); foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection){ conversionInstance.SetUsageType( input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY); }
.. . // Well here I am stucked. What I need to do here to establish a map // between conversionDataFlowComponent.InputCollection[0] and // conversionDataFlowComponent.OutputCollection[0]?
As you can see I am just away from creating the mapping between input and output collection. Can anybody give me an idea how can I achieve this?
I will appreciate all kind of suggestions and comments.
sql version 9.00.3042 on both source and dest servers. the publication has a single article, a table w/ an xml column. when the initial snapshot runs we get the following, any ideas?
Error messages: Source: Microsoft.SqlServer.Smo Target Site: System.Collections.Specialized.StringCollection ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[]) Message: Script failed for Table 'dbo.GatewayHeader'. Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable) at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0) Get help: http://help/0 Source: Microsoft.SqlServer.Smo Target Site: Void CheckTargetVersion(Microsoft.SqlServer.Management.Smo.SqlServerVersionInternal, Microsoft.SqlServer.Management.Smo.SqlServerVersionInternal) Message: Either the object or one of its properties is not supported on the target server version. Stack: at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CheckTargetVersion(SqlServerVersionInternal targetVersion, SqlServerVersionInternal upperLimit) at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.GetTypeDefinitionScript(ScriptingOptions so, SqlSmoObject oObj, String sTypeNameProperty, Boolean bSquareBraketsForNative) at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.AppendScriptTypeDefinition(StringBuilder sb, ScriptingOptions so, SqlSmoObject oObj, SqlDataType sqlDataType) at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes) at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb) at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects) at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) (Source: Microsoft.SqlServer.Smo, Error number: 0) Get help: http://help/0
I want to generate a new snapshot using stored procedures. I want to wait for the snapshot files to be created and then execute a stored procedure. What's the best way to determine that the snapshot has completed successfully? I thought of doing something like:
however I can't put the results of that proc into a temp table because I get this error: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72 An INSERT EXEC statement cannot be nested.
I'm finalizing our merge replication apps but we encountered some problem with the setup. We need to create a parameterized filter publication of 30+ articles. Most of the articles/tables contains large amount of existing data. One of them contains more than 20 millions records. during the creation of snapshot, the snapshot agent runs for a while and it will hang the process. The whole system cant seem to response anymore.
I tried on the other 25 articles with less data, everything was fine. is 20 million too much for snapshot?
I'm running SQL Server 2005 SP2 64bit under Windows Server 2003 64 bit The system is the replication distributor as well. Transactional replication.
But I can't create the snapshot!
I have run snapshot.exe repeteadly with all parameters in a command prompt and it crashes at various BCP percentages. The error is like this: 2007-10-16 00:38:56.32 [10%] Bulk copied snapshot data for article 'Dictionary (part 14 of 20)' (1656 rows). 2007-10-16 00:38:56.32 [10%] The replication agent had encountered an exception. 2007-10-16 00:38:56.32 Source: Replication 2007-10-16 00:38:56.32 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException 2007-10-16 00:38:56.32 Exception Message: Data conversion failed 2007-10-16 00:38:56.32 Message Code: 02007-10-16 00:38:56.32 2007-10-16 00:38:56.32 Call Stack: 2007-10-16 00:38:56.32 Microsoft.SqlServer.Replication.NativeSqlConnectionException: Data conversion failed 2007-10-16 00:38:56.32 at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper) 2007-10-16 00:38:56.32 at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause) 2007-10-16 00:38:56.32 at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem) 2007-10-16 00:38:56.32 at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc() 2007-10-16 00:38:56.32 at Microsoft.SqlServer.Replication.MainWorkerThread.AgentThreadProc() 2007-10-16 00:38:56.32 at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() 2007-10-16 00:38:56.33 [10%] Bulk copied snapshot data for article 'Dictionary (part 15 of 20)' (1487 rows). 2007-10-16 00:38:56.33 [10%] Bulk copying snapshot data for article 'Dictionary (part 20 of 20)'
the fact that BCP continues to copy data after the error makes me believe the crash occurs somewhere else.
How can I isolate the offending table? Can I capture the BCP commands issued?
Based on jax-ws 2.0 technology, I built a java client application to access a report via the web service interface of the SQL Server Reporting Services 2005.
I used the jax-ws "wsimport" tool to generate the java classes needed to call the web services (The class generation is based on the wsdl and I used this one http://serverName/reportserver/reportexecution2005.asmx?wsdl) and wrote the following sample code :
ReportExecutionService service = new ReportExecutionService(); ReportExecutionServiceSoap rs = service.getReportExecutionServiceSoap();
String reportPath = "HelloWorld";
String format = "MHTML"; String devInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"; Holder<byte[]> result = new Holder<byte[]>(); Holder<String> extension = new Holder<String>(); Holder<String> mimeType = new Holder<String>(); Holder<String> encoding = new Holder<String>(); Holder<ArrayOfWarning> warnings = new Holder<ArrayOfWarning>(); Holder<ArrayOfString> streamIDs = new Holder<ArrayOfString>();
The trouble is that an exception is caught on the call to the render method
javax.xml.ws.soap.SOAPFaultException: The session identifier is missing. A session identifier is required for this operation. ---> The session identifier is missing. A session identifier is required for this operation. I found on the web (in C# examples) that this SessionId is supposed to be contained in the ExecutionId attribute of an
ExecutionHeader object. The ReportExecutionServiceSoap class (my rs object in the example) does not offer any way to directly set this SessionId or to associate an ExceptionHeader before calling the render method.
I have a report which have multiple parameters. By default all the parameters are selected. Now I am scheduling a Cache with Report Execution Snapshot. Now if I try to view the report, it disables the prompt selection. It means user cant change the selection. Is it the expeceted behaviour of the exectuion snapshot? Thanks, S Suresh
HI everyone; i have a problem , is there a possible to pass a dataset to a ssrs report programmatically ? or at runtime without specifying it at design time ...
What we'd like to do is programmatically generate and maintain a report model based on how the individual install is configured. I've found some documentation that makes me believe this is possible, but I'm hoping someone can nudge me in the right direction. The ReportingServices2005 class has several model related methods (CreateModel) and I found the .xsd for the semantic model XML file (http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd), but no real documentation on how to put together a Report Model document from scratch. Looking at the files generated from BIDS is somewhat overwhelming and I have no idea where I'd pull most of the information.
Finally, I want to create Report Model(.smdl) file without Report Model Designer.
I want to Implement CreateModel Method to create new model, any source code sample..??
Any help that can be provided would be greatly appreciated. Thanks!
I want to add some customized text to the report programmatically before the report is rendered. As it has to be done for all the reports, I don't want to do it report by report. For example, the report needs to display user selected filters. Any thoughts?
I'm working on custom report manager. It manages "report entities" and "report templates" (actually, RDLs uploaded on the server) and stores one-to-many relation between them. The task is to store "MasterEntityID" report parameter in every RDL and keep it up in actual state whether RDL is being assigned to another entity or new RDL is being uploaded and assigned. I've covered the first issue with SetReportParameters() web method, but how should I deal with the second one? Uploaded RDL may be short of the param, so I have to add it programmatically while uploading.
I am struggling to programmatically render my report to a pdf without displaying it in a viewer and would appreciate a pointer in the right direction.
My code seems to work and the report is found and everything is honkey-dorey until I try to actually load the report.. lemme show: (please excuse the bit of a mess since this is just a test form)
// Authenticate to the Web service using Windows credentials rs.Credentials = System.Net.CredentialCache.DefaultCredentials; rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
FindReport(); //Create a variable containing the selected item selItem = MyCatalogItem.Item; try { // Get if any parameters needed. _parameters = rs.GetReportParameters(selItem.Path , _historyID, _forRendering, _values, _credentials );
// Load the selected report. rsExecService.ExecutionInfo ei = rsExec.LoadReport(selItem.Path, historyID);
// Prepare report parameter. // Set the parameters for the report needed. rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[5];
// Place to include the parameter.. if (_parameters.Length > 0) { parameters[0] = new rsExecService.ParameterValue(); parameters[0].Value = pQuoteID.ToString(); parameters[0].Label = "Quote ID"; parameters[0].Name = "cor_Quote_id";
} rsExec.SetExecutionParameters(parameters, "en-us"); results = rsExec.Render(format, deviceInfo, out mimeType, out encoding, out warnings, out streamIDs);
// Create a file stream and write the report to it using (FileStream stream = File.OpenWrite(fileName)) { stream.Write(results, 0, results.Length); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
Right, It bombs out at the section I marked in blue and gives me this HUGE message :
"System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sqlserver/2005/01/12/reporting/reportingservices/LoadReport. at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest() at System.Web.Services.Protocols.SoapServerProtocol.RouteRequest(SoapServerMessage message) at System.Web.Services.Protocols.SoapServerProtocol.Initialize() at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)"
Could this be related to credentials or something? Any advice welcome. Thanks Mates
I'm creating ssrs reports via the web service render method & would like to be able to determine when a report has no data.
Currently what I'm doing is rendering the report twice - once as a pdf (the format that the report needs to be in) and once as a csv. I then check the csv for a specific string placed in the norows property of the report table.
Is there a better way of doing this? It seems to me that this would have been a good candidate to include in the warnings array.
Between this issue & the hacks required to get data into the header I'm thinking that I should maybe reconsider some other reporting options...
I've been working on an application that uploads an RDL to Reporting Services (through the SOAP webservice method CreateReport) programmatically. I'm having difficulty setting up the data source properties for my uploaded report. In particular the Data Source Credentials property.
The datasource for my report doesn't require credentials. By default after I upload the report to Reporting Services, the Data Source Credentials property is set to "Credentials supplied by the user running the report". How do I go about setting the Data Source Credentials property to "Credentials are not required" programmatically through the webservice?
I have a report with a parameter that I would like to have created every night but also allow a user to change the default parameter and rerun it if they need to . When the Snapshot is opened, the parameter box is disabled. How can I work around this?
I have a report that has a snapshot enabled. I made some changes to my report and redeployed it, but when I view the report from the Report Manager web site the old version is getting used. Apparently the snapshot includes the rdl and the data and not just the data. Can anyone else confirm this? If this is the case, a new snapshot must be created after the report format is modified.
my problem is , i want to deploy my rdl and rds file which i make in the sql 2005 reporting services. Now i want to deploy this rdl through asp.net coding using language VB i hope someone is help me Thanks Rahul Sinha
I have created many shared datsources and reprots at http://localhost/resportserver.
I am trying to access these reports from ReportViewer in the web application. Actually I want to use these reports for different customers by just changing the datsource programitically.
I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:
This version of the Web Services Description Language (WSDL) is not supported.
Is there a way to schedule report snapshots and still give users the option to change the parameters? I found that report parameters are disabled when I set the execution options to use a snapshot. I fixed the problem by setting the report to use cached data that expires on a custom schedule and by scheduling an e-mail subscription that creates a new report cache soon after the cache expires. I'm guessing there must be a better way to provide good report performance with the default parameters as well as flexibility.
We have a datawarehouse that's built nightly through a number of Integration Services packages. I have several packages that compile and aggreage data used by a set of reports constructed and presented using Reporting Services. I would like to augment the packages to incorporate a step that will create a snapshot for each report. Can anyone provide a set of pointers or sample code to accomplish this?
The data is static and this method would allow the reports to be constructed before the first user runs them (caching would require them to be run once). I would also like to delete the snapshot if a report extract needed to be run again. This can occur in some occassions. I'd like to automate this as much as possible.
I imagine I'de be able to do this using a script task in SSIS that interactes with the reporting services web service in some way.
Hello, This is my first post, and I'm hoping you all can help.
Using Reporting Services 2005, I have several reports that use embedded images. All images render fine when the report execution is set to: 1) Always run this report with the most recent data 1a) Do not cache temporary copies of this report
However, when I change the execution to either a Cache or a Snapshot, the images and some charts render as red "X" placeholders. This is sometimes remedied when the user clicks the page refresh button, but not always.
Of course, I could just have all the concurrent users use the uncached report that hits the OLAP server, but that would be highly inefficient, and just plain slow.
Dear All- i am facing a problem with them creation of the execution Snapshot for my report
simply when i try to create a one and after choose the schedule setting and i press OK to finish an error appear mention "The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting) (Report Services SOAP Proxy Source)"
i like to mention also i choose the datasource credentials is the redio button Windos integrated security and also the reporting services and the database are exit on the same machine
i hope someone help soon as our work is affected due to this issue
I have a Performance issue in ssrs report , query takes execution time 1 to 2 min. When I add the non clustered index for one of the table columns which takes only 2 sec to execute the query. But as from my environment I couldn't able to add the index due to these changes will effect for all reports. Without adding indexes which takes 2 min to run that report. My question is:
Is there any way to add the non-clustered index on the report itself like if a non clustered index exists "execute the query" else create a non clustered  index and run that report with newly created index in report itself like a temp table concept. How to add this concept in this below query:Final Query without non clustered index, takes 2 min to execution time ---
with cte1 ( MovementDate, MovementTypeDesc, CDCNumber, MovementTime, LocationReportMovement, OtherLocationCode,
I want to receive some reports by email every time the datawarehouse refreshes its content. The data warehouse is being refreshed on a daily basis but the exact time varies between 9:30 am and 11:30 am.
I have created the 4 reports in reporting services and set the execution property of each report to
Render this report from a report execution snapshot
and then created 4 subscriptions for email delivery based on processing option When the report content is refreshed
In an Integration Services package I have 4 Script steps (VB.net) that perform an UpdateReportExececutionSnapshot for each of the reports.
The problem is that these steps fail in a random order every time the package runs. Sometimes they all run fine , but then one or two fail with timeout error. So the emails are not being sent.
I am currently using SQL Server 2005 SP2.
If anyone has an idea on this issue, please let me know.