How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - Using XML Source in SSIS vs Script Source to parse xml files. I've been using the XML Source to process a large nested XML file. In my case, the contents need to be placed into several tables, so it's a little different from your situation. On the other hand, my input XML is actually much more complex than it really needs to be. It's organized for ease of creation, not ease of consumption. If I had used that external-facing schema directly, the XML Source would have produced 149 outputs and 149 error outputs (yes, one hundred fourty-nine). My solution was to run the XML through a transform to flatten it. This left me with only (!) 38 outputs and 38 error outputs. Perhaps you can flatten your XML with an XSL transform before your XML source consumes it. You could use an XML Task to do this. Just have the task run before your data flow. Also, be aware that there are several issues with the XML Source. Most of these appear to be proportional to the number of outputs, so you'll do well to limit the number of outputs. Also, note that the XML transform completely invalidates its external metadata whenever it notices that the schema has changed. Even a trivial change will cause it to invalidate everything, requiring you to fix the metadata for all subsequent components. This usually just takes time and care, but once it forgot the names of the columns, which cost a day to fix. Good luck. There don't appear to be many people using the XML Source. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 2
    - Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime But I want to have the substrings from FILENAME into the variable @temp11filename. I want to mask out 2007-05-06 18:18:00 from filename_wast_erth1_20070506_1818_all.txt. 2007-05-06 18:18:00 is the date I want to use as argument when getting the rows. @temp11filename = SUBSTRING ( FILENAME , 21 , 4 )+ '-' + SUBSTRING ( FILENAME , 25 , 2 )+ '-' + SUBSTRING ( FILENAME , 27 , 2 )+ ' ' + SUBSTRING ( FILENAME , 30 , 2 )+ ':' + SUBSTRING ( FILENAME , 32 , 2 )+ ':00' , Here @temp11filename is 2007-05-06 18:18:00 I want to use @temp11filename to find out and then get all rows between @ theEndTime AND @theCurTime Am I thinking wrong Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 3
    - Radio button http://www.devx.com/dotnet/Article/30424/0 Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 4
    - Transfer object task error. Hi, i am trying to transfer objects from SQL Server 2000 DB to SQL 2005 DB. i have copy schema to true and i am only copying tables. when i have tried first time it worked fine but in next time it start to give error. here is the error description. [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". Thanks, Haroon Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 5
    - MIgrating Existing WorkFlow Data using SSIS Hi, I have posted the same query in the WF group too. Thought I'll post the same here. We have an existing application which has some WorkFlow Data. We need to migrate this data using SSIS. Now the problem here is that we have our Workflow facade class which inturn calls the WorkFlow Runtime. We have created the methods which will create the new WorkFlows. TO add this DLL in our SCript Component Reference, we added a strong name to my Facade.dll and deployed it to GAC. The system.WorkFlow DLL's are also present there. Everytime I run this Package, I get the following error "System.IO.FileNotFoundException: Could not load assembly...". What am I missing Thanks Soni Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 6
    - OLAP Error - Excel Pivot Hiya, We're having exactly the same problem here. Also, with SP3 recently installed. It was fine before. So far, I haven't found any other leads as to what's happened. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 7
    - Cannot connect to SQL Express from WEB Application on internet See this http://msdn2.microsoft.com/en-us/library/ms175483.aspx to enable so. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 8
    - plain text mails One option is to run Profiler as you are trying to send an email from the GUI. That way, you can find out exactly what is being sent to the database. That may help. Tim Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 9
    - The Device is not ready error when Executing package on the server No problem... moving to SSIS forum. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 10
    - Unable to install SQL Server 2008 on Windows Vista Hi, I have successfully installed the SQL Server 2008 June CTP side by side an SQL Server 2005 SP 2 installation on Windows Server 2008 Beta 3. I know that SQL2000 installation does not let the SQL2008 installation package to start installing but SQL 2005 does not effect the database engine of SQL 2008 to install on the same server. But the client tools does not install on the same computer where SQL 2005 client tools reside. So uninstalling the client components of SQL2005 may solve the problem before installing the SQL 2008 client tools. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 11
    - Query Plan in SQL Server 2005 SP2 Unfortunately, we do not do a good job of optimizing queries that contain large number of value in the IN list. Part of the problem is the high compilation costs as the number of values increases. This affects plan choices also. If you provide index hint then lot of the logic is short circuited. In this case, it just so happens that using the index results in a good plan. It is hard to say if that will be case for all possible IN list values. Next, I am not sure how you are sending the query to SQL Server. It is very inefficient to send queries like this. Is this being generated from an application How is the user able to select this many values There are also multiple problems apart from the performance issue. For each such unique query, there will be a unique plan because of the difference in the IN list values. This affects your plan cache size too. And it looks like the size of your SQL statement can be very large so it affects performance and also inhibits different connections from sharing the same plan. You should consider the following options: 1. Create a temporary table and optionally index the value column 2. Use BULK INSERT or BCP or BulkCopy API to dump the IN list values into temporary table 3. Write the query to use the temporary table. The query plan can now be shared across connections and independent of the values in the temporary table You can also create a SP that takes the IN list values as a blob (string of bytes) and parse it using SUBSTRING/numbers table. The link below describes lot of these techniques. http://www.sommarskog.se/arrays-in-sql.html So can you describe your query generation process more Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 12
    - How to Locate the SSIS Installation? That means that the full SSIS client hasn't been installed. Just the Visual Studio tools. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 13
    - Un-Pivot transform crashes BIDS (Serious Issue) You mean it works fine after you removed and added back the component please provide the details on how you fixed and marked the thread as answered. That would help other people having similar issues Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 14
    - Transform collate fields Hi, if you want to sync them to the database default, use the following command: ALTER TABLE SomeTable ALTER COLUMN Test CHAR ( 10 ) COLLATE DATABASE_DEFAULT HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 15
    - Msg 137, Level 15, State 2, Line 8 / Must declare the scalar variable "@Interest". I got a bit confused to be honnest. I should have used a join. Anyway, I have sorted the problem, Iam only using 1 table since there aren't that many fields. Thanks a lot for your help. Jean-Philippe Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 16
    - logistic regression model formual Hi Bogdan, Thanks for the very useful information. Could you also provide examples of how to compute the InputNormalizedValue (i.e. z-score) for Discrete attributes (NODE_DISTRIBUTION.VALUETYPE = 4) In this case, it would seem that the NODE_DISTRIBUTION.PROBABILITY represents the MEAN, but NODE_DISTRIBUTION.VARIANCE is always set to 0. I tried computing the STDEV based on a Bernoulli distribution with the specified probabilities, but the resulting z-scores didn't seem to jive with a MS SQL example I saw online ( http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1509.aspx ). Also, just to verify, for Continuous attributes (NODE_DISTRIBUTION.VALUETYPE = 3), the z-score would equal (Input Value - NODE_DISTRIBUTION.ATTRIBUTE_VALUE) / SQRT(NODE_DISTRIBUTION.VARIANCE), correct Thanks, Doug Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
- Next
  - 1
    - How to format cell / data apprearecnce under a table Hello All, I uploaded custtable under the database, the data looks fine except that the name that apprears has a lot of distance e.g it should be : firstname lastname however the format appears very strange: firstname lastname firstname lastname fistname lastname Same is the case with the address, I need to adjust or format the apperance that appears on the cell. Is there a way/ sql statement to format the data under the table so that the apprearence looks okay. I will really appreciate any sort of help on this one. Thanks, Rashi Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 2
    - SSIS Script Task Here's another example I posted a while back, maybe it will help. http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx The variable should be typed as an object. Did you try the suggestion from the other thread to use a script task to see if the variable was set properly Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 3
    - Data Conversion Question I am using the instructions at the following link to create an update/insert load: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx My input data has values that are 8 bit signed integers DT_I8. My output table uses BIGINT for the same fields. Now I know that the conversion in SSIS is BIGINT = DT_STR. The question I have is this. On the INSERT side of the OLE DB Command I did not have to convert the DT_I8 values to write to the table. HOWEVER on the UPDATE side, the update errored out UNTIL I did a data conversion to DT_STR of the same fields. Why is it different between the UPDATE side and the INSERT side Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 4
    - How to flush data stored in SSIS buffer Real-time is a difficult term to define. I think SSIS can push data to a destination fast enough to qualify as real-time for most applications. I think what really determines the appropriateness of SSIS for a real-time application is what your source data is. Scenarios that I've been involved with have had message-based sources, and in those cases I've skipped SSIS and consumed the source data with Service Broker. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 5
    - How to hide URL when display reports in reporting services Hi , How can I hide the URL information when I open a report, some with parameters and some do not have parameters. Is there anyway or properties in RS that I can set to have it hide all the important information, like datasource, credentials, parameters, and display the report like a aspx page instead Or does this need to have special programming to make this happen and if so, what do I need to do thanks very much Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 6
    - Question on calculated members Hi, PedroCGD, Thank you for your advices. So as you are saying, the analysis services engine will be able to locate the proportion of the cube easily whenever the MDX query is launched against the cube Thank you. Best regards, Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 7
    - Cannot start SQL Server Browser The key one is "The SQLBrowser service port is unavailable for listening, or invalid.". So what happens is SQL Browser starts up and enumerates all network interfaces and attempts to bind to UDP port 1434 on each one found. If it cannot bind to any of the network interfaces, you will get the above error. So run this from a command prompt first to locate the process id bound to UDP 1434 -> netstat -aon -p UDP Look for things bound to 1434, like for example PID 4736 below: Proto Local Address Foreign Address State PID UDP 0.0.0.0:445 *:* 4 UDP 0.0.0.0:500 *:* 1156 UDP 0.0.0.0:1025 *:* 1792 UDP 0.0.0.0:1026 *:* 1792 UDP 0.0.0.0:1027 *:* 1792 UDP 0.0.0.0:1434 *:* 4736 ... Next look up the PID in Task Manager to see what process this is. If it's a SQL Server process try shutting down all SQL Server instances and then restart SQL Browser. If it is some other process let me know what it is. It is also possible that SQLBrowser did not find any network interfaces to bind to, for example if the computer has no NICs. Let me know if this is a NIC-less PC. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 8
    - Multiple Database Files: Good or neutral? I have inherited some responsibilities for which I'm not really qualified, so I'll push on through and maybe not totally fall down. Assume 10 50GB databases, each in a single MDF file. All these MDF files reside on the C drive (the only drive on the system), running SQL 2005 in a 32-bit Windows 2003 or later, 8GB RAM. The C drive is 6 physical disks in RAID 5, say about 1.0 TB or so. We have 4 dual-core processors on the box. We have limited simultaneous users, initally about 8 users doing very heavy write on all tables in any one database. Later, we have about 15 users connecting via Web interface, and doing very heavy read and light writing. Each of the 10 or so database has this lifecycle: Heavy write for about 2 weeks (load data) then heavy read for about 1 month (research and search data), then nothing ever again (db is taken offline). Of course, this is not enough information to go on, but let's just go on it anyway. My TempDB, Log (simple recovery), Index etc is all on the same RAID 5 drive (C). I have two basic questions I'd love to hear feedback on: 1. Is there any real advantage to creating 8 Data files for my database (one per processor core) 2. Given that the hardware people here REALLY don't want to change anything, what should I fight for first: a. Separate drive for LOG files b. Separate drive for TempDB c. Something else Thanks in advance. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 9
    - ntext columns truncated to 32K when processed by Integration Services yes. It works fine. (retrieving from C#) Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 10
    - BCP to a table partition I'm based on Books Online for explanation : First the partition have to be built as BOL say The syntax of bcp is explained in BOL too. if we run bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c bcp will insert all the data from Currecy.dat file in AdventureWorks.Sales.Currency2 table the process of inserting data in the partitions is transparent for user and is based on that boundary value. So , to insert data in that partition the Currecy.dat file have to be built something like this ID AnyData Etc.. 1 20030215 hjhjhj 2 20030215 lftdfhh 3 20030216 yuhjhq2kn Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 11
    - junk character within FLAT FILE Antony Kumar wrote: The textpad shows like the following = SUB(^Z = 26 = 0x1A) what i have to do next Thanks Jamie. Textpad will give you the ascii code of that character. Once you have the ascii character cross-reference it with this list: http://www.asciitable.com/ and you'll know what character it is. -Jamie Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 12
    - SQL Server Licensing Hello, I've read the Microsoft documentation as well as these forums and I'd just like to clarify my situation: I have an IIS server that hosts applications to over 100 possible users (that use Windows Authentication), and those applications connect to an SQL Server 2005 Standard (one Quad-Core CPU). In this case, it'd be cheaper getting a CPU license, correct Or can I get a device CAL for the IIS server and that's it From what I understood from the MS document you linked, a multitiered architecture still needs licenses on a per-user (or per-user device) basis, right Thanks in advance Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 13
    - can we pass dataset dynamically??? Hi, yes by either using the coded dataset which is passed through the API or using the SQL Client by specifying an dynamic Data Source like : ="Data Source=" & Parameters!DataSource.Value (...etc...) HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 14
    - DeadLock - INSERT with foreign keys You need to set the isolation level to SNAPSHOT. In order to do so you must first set the ALLOW_SNAPSHOT_ISOLATION database option to ON. Here is the command to do this: Code Snippet ALTER DATABASE MyDBName SET ALLOW_SNAPSHOT_ISOLATION ON Next, add this statement at the beginning of your procedure: Code Snippet SET TRANSACTION ISOLATION LEVEL SNAPSHOT I hope this solves your problem. Best regards, Sami Samir Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 15
    - Accessing Custom Properties in UI I am attempting to set my custom properties in the UI I have created for my custom transformation. I can access them in the ProcessInput, but if I try to assign them a new value in my UI the values dont change. I set the properties up in ProvideComponentProperties public override void ProvideComponentProperties() { // Perform component setup operations ComponentMetaData.UsesDispositions = false ; // Add Input IDTSInput90 input = ComponentMetaData.InputCollection.New(); input.Name = "TrimInput" ; input.ErrorRowDisposition = DTSRowDisposition .RD_NotUsed; // Add Output IDTSOutput90 output = ComponentMetaData.OutputCollection.New(); output.Name = "TrimOutput" ; output.SynchronousInputID = input.ID; IDTSCustomProperty90 Trimproperty = ComponentMetaData.CustomPropertyCollection.New(); Trimproperty.Name = "Trim Values" ; Trimproperty.Description = "Selected Trim Values" ; IDTSCustomProperty90 Colproperty = ComponentMetaData.CustomPropertyCollection.New(); Colproperty.Name = "Col Names" ; Colproperty.Description = "Column Names" ; } btn Click event in UI form IDTSCustomProperty90 Trimproperty = _dtsComponentMetaData.CustomPropertyCollection[ "Trim Values" ]; Trimproperty.Value = sConcatTrim; IDTSCustomProperty90 Colproperty = _dtsComponentMetaData.CustomPropertyCollection[ "Col Names" ]; Colproperty.Value = sConcatColNames; Any suggestions or please point me to any example would be greatly appreciated. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service
  - 16
    - accidentally populate tables into System databases/master database Thank you All, I just deleted all the talbes were accidentally created in System/Master Database in sql server 2005. Sql server seems working fine. Tag: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service

Puneet_18

In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out.

I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body).

Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method.

Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution).

Thanks.

Top

Re: How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service

Jens K. Suessmeyer

After the report execution you will already have the rendered format, so you won��t be able to access the data properties anymore. There are two tings that came in mind as a solution:

1.Dirty solution: See how big the reports gets in size, if there is no data available for the parameter, if the rendered format has more byte than this threshold send it to the receipient.

2.Clenaer solution: Create your own data extension. http://msdn2.microsoft.com/en-us/library/aa237575(SQL.80).aspx Then you will be able to have access over the data before the report is rendered and in addition the query has to be done only one time.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Top