Scheduler Settings For Mailing / Generation Of Pdf Reports Only Under Conditions Of Data Being Present
Feb 11, 2008
I have a requirement wherein PDF files are being rendered from an .rdl (report definition language), through the use of a SSRS scheduler automatically. The generated PDF file is further emailed to a mailing list, through the same scheduler. However, there are situations where the PDF is generated as an empty file (under certain specified circumstances) [through the automatic scheduler run]. In this situation, it is required not to email the PDF at all.
I would appreciate an input which lets me know how to prevent the generation of the PDF file, when there are no records in the dataset that binds to the .rdl.Alternatively, is there any indicator via which the scheduler can be alerted NOT to pick up files 0 KB in size?
Hi, here is my problem: Because of some usability terms, I need to use SSRS scheduler to determine reports creation frequency (minimum frequency is once a day)I need to use an external scheduler to SSRS in order to launch reports creation daily, after some other batch tasks execution that must be completed before launching report creation. And I have no idea on how to do that properly. Can somebody help me?
While within SQL Server 2008R2/Visual Studio 2008, I created SSIS project that involves, among the rest, Script Components.... Nowadays, we moved towards SQL Server 2014 and Visual Studio 2013...Our further development of SSIS project is stacked at Script Component errors....like depicted in attachment. As synopsis of steps that we used to undertook, was drag/drop Script Component, Transformation, Add Web Service as [URL] ...., Resolve in order to set up correct Using statement....But, if we try to BUILD, it pop ups errors beneath... How to escape i.e., how to build it now in SQL Server 2014 and Visual Studio 2013?
Hi....We are generating reports in Excel and PDF and the reports are accessed from a web app interface..
the problem is that when the users print a report generated in Excel from the web app it prints in a veritcal split on paper...This is because the width exceeds the paper width....We do not want to redesign reports ...Is there any method to squeeze Excel data into the availiable page width ( This happens automatically in PDF so issues with it).....Can it be done in code ?
i have installed SQL RS and have a directory listing in the Reports directory via my browser. CAn anyone tell me which file / setting needs tweaking please?
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId) WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
Hello. I have been challanged to build an application that keeps track of inventory and dates of every item that needs inspection on regular intervals. The original application was a .NET windows application and i have been tasked to "port" and improve it to a .NET C# web application. The original database was in access and i have imported everyting to SQL and all the data is in a single table. So far the table is relatively small so i decided to keep the current database design. What i need to do is to select all the items that are comming up for inspection in the comming months and present them. Each item has a uniqe article number (artnr) with the item name, category and location of the item. The location is the part i would like each items to be presented by. Basicly i would like to retrive artnr, name, type and present x number of gridviews or a sorted presentation where each item is shown inside each gw/whatever by location. I also considered moving all locations to it's own table and connect it with a PK/FK to the prinmary article table but i dont know if this will help me or not. Basicly i have no idea how i would present the items by location where each gridview/somethign else is it's own location. I could really use som help with how i can retrive and present the data so it is showed in the above manner.
(Sorry this was posted to VWD2005 Express by mistake) I have created a simple project using ASP.NET 2.0 which enables a user to login. I created the login user using the Web Administration Tool in Visual Studio 2005. I created everything locally and it worked. Locally I have 2 databases. One containing normal data (which my site uses to populate a GridView control) and the other being ASPNETDB.MDF - which contains membership (login user) data. These 2 databases sit in the App_Data folder which is in the root of the project. I have a remote database which is hosted in a shared environment. This is just the one database and it appears to have all tables in the one database (membership tables and normal tables - such as the one I use to populate my GridView control). I uploaded the website and using the Database Publishing Wizard I uploaded the database data to the shared host. I also changed the connection string to point to my remote database. If I visit the website the GridView is populating correctly (pulling data from the remote database) but when I try to login it crashes. The error is "Access denied creating App_Data sub directory". The membership login details for the user I created are in the remote database as I have compared the data in it to the data in my ASPNETDB.MDF local database. If I launch from within Visual Studio 2005 then it allows me to login perfectly well. Please does anybody know why it won't let me login when I access it via the website? Many thanks for any help, Graham
hi can anyone help me out, i have one issue that is how i have to check while adding new user details to database table weather that user name is exist in that table or not. for my application i'm using oracle database and C#.Net. table is g_users and columns are: Name Null? Type ----------------------------------------- -------- ---------------------------- USERID NOT NULL VARCHAR2(5) NAME VARCHAR2(20) EMPID VARCHAR2(5) USERNAME NOT NULL VARCHAR2(30) MOBILE NUMBER(12) EMAILID VARCHAR2(20) DOMAIN NOT NULL VARCHAR2(30) i need to check from c# programming before inserting a new row into table weather username is already exists are not. how to check this one?
I'm writing my first .net app in VB.net. I can connect to my database, but get the following error when I try to set a label value. (My code is listed below) What am I missing? Exception Details: System.InvalidOperationException: Invalid attempt to read when no data is present.Source Error:
Line 19: Line 20: reader = comm.ExecuteReader() Line 21: EmployeesLabel.Text = reader.Item("tkinit") Line 22: Line 23: reader.Close() <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Configuration" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim conn As SqlConnectionDim comm As SqlCommand Dim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("IntegratedDataConnectionString").ConnectionString conn = New SqlConnection(connectionString) comm = New SqlCommand("Select top 1 tkinit from tEliteTimeKeep", conn) conn.Open()
My query actually returns rows but when I run, I get this error "Invalid attempt to read when no data is present". It says that the datareader does not return any rows. Please help. Thanks in advance Sangita
I have a stored procedure that inserts a record, and then either returns an error or, when successful, returns the entire row. This is a method I use on sites I have developed in other languages when inserting or updating data; I am new to c#.
With C# I have a datareader executing the sp, and the insert works fine, however I get the error "Invalid attempt to read when no data is present." when I try to read the data that should be coming back. I have researched and updated my code so that I no longer get the error, but I still cannot get the data back. I trap the sp in profiler, ran it in anaylzer, and it works fine - data is returned. But in the code, it does not see the data.
I have modified the return results so now I just have the ID coming back and that works fine. But I would rather just return the entire row. Can this be done, and if so, how?
Could somebody please help me to stop sending scheduled report when no data is present in the report? Actually I have a daily report which is sent to the client and in a week, two or three days, data will not be there and thus it delivers blank report to the client.
So how can I stop sending blank mails to the client?
I am getting this error even though there should be data present. When I test the SQL statement in query analyzer, it returns 1 row. <%@ Import Namespace="System.Data.SqlClient" %><%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Blog" %> <script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim strConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("currentConnection").ToString 'currentConnection is defined in web.config and works when used in other pages on the siteDim dbConn As SqlConnection = New SqlConnection(strConnection) dbConn.Open() Dim strSelectCommandFirstEntry As String = "SELECT MAX(blogEntryId) as maxID FROM site_Blog"Dim cmdFirstEntry As SqlCommand = New SqlCommand(strSelectCommandFirstEntry, dbConn)Dim rdrFirstEntryData As SqlDataReader = cmdFirstEntry.ExecuteReader()Dim strFirstEntryData As Int32 = rdrFirstEntryData(0) 'error occurs here, i have also tried rdrFirstEntryData("maxID") with same errorrdrFirstEntryData.Close()dbConn.Close() </script> When debugging this code and stopping on this line: Dim strFirstEntryData As Int32 = rdrFirstEntryData(0) rdrFirstEntryData has the following values:hasRows = True, FieldCount=1, Item=In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. and when i click the refresh button...Item = Overload resolution failed because no accessible 'Item' accepts this number of arguments. I suspect that the note for "Item" is my clue to the source of the problem, but I don't know what it means. Please, help.
I'm trying to determine if the record is NULL/empty or is valid from the datareader.
objReader = strCMD.ExecuteReader objReader.Read() if objReader.IsDBNull(0) = true then...
If NULL/Empty, display no records found. If records are found, display "1 or more records have been found". I keep getting the error "Invalid attempt to read when no data is present". I'm not sure what I am doing wrong here.
Was reported to me that reports were running but data was not being displayed. After checknig the Config manager I found that if I went to the reportserver url then clicked on a report it would diplay he correct data. But when trying it on the normal website it would not display anything.
Hi there I have a dataset based on a view (the view contains an union all select). In the Data-Tab in BIDS i can execute the query, set all parameters and get the desired results. In the report though, I am getting no values.
My year-parameters are used as row-descriptions and based on that I calculate the year-values in the data region. I don't know what the problem is... should I post the rdl-file?
create table #datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) ); insert into #datatable exec ('select * from datatable where id = 1') select * from #datatable
I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.
If I paste the code DROP
TABLE #datatable after insert into #datatable exec ('select * from datatable where id = 1')
Does it gonna work to present the data in the presentation layer after removing the temp table?
The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.
I know it sounds very simple with the subject line but for me it's not.
BACK GROUND
My Client is using Crystal Reports 8.5 and SQL Server. a View is bound to this report and they use Select Expert of CR 8.5 IDE to generate Reports manually.
now there are 3 tables in Question Billing_Revenue, Billing_Revenue_History and Billing_Revenue_Revision and the Fields in Question are Interstate_Revenue and International_Revenue.
Every end of the Quarter the Billing_Revenue data is moved to Billing_Revenue_History. Billing_Revenue_Revision has the revised data if the "Filer" has submited the changed/Revised Data again to the Company.
Bill Run = is the Process they call when they generate the Reports for a perticular Billing Period. they do it every month. But sometimes they need to generate Invoices for the OLD Periods.
if we put the problem in simple words,
they want to get the Revenue Columns from Billing_Revenu if they run the "Bill Run" in the Current Quarter (Billing Cycle), else if the Billing Cycle is older than current Quarter (cycle), then they want to first check in the Revision Table and if that Filer has submitted the revised data in that Cycle then get it from there else look into History Table and if the filer is found there for that cycle than get the data from there
here's the requirement given to me by my system Analyst.
User enters list of filer ID=USER_FILER_ID and cycle ID=USER_CYCLE_ID
Get CURRENT_CYCLE= Max CYCLE_ID from Billing_Cycle Get USER_PERIOD_ID = Period ID from Billing_Cycle using USER_CYCLE_ID Get PERIOD_FIRST_CYCLE= Min Cycle ID for USER_PERIOD_ID from Billing_Cycle
For each USER_FILER_ID If USER_CYCLE_ID=CURRENT_CYCLE Pull revenue from BILLING_REVENUE /*Process stops here*/ ELSE { If USER_PERIOD_ID exists in BILLING_REVENUE_REVISIONS BRR for USER_FILER_ID
Get the max (cycle_id) from BRR to pull revenue columns with most recent filing of USER_PERIOD_ID from BRR /*one filer may have more than one revision for the same period id*/
ELSE
IF PERIOD_FIRST_CYCLE exists in BILLING_REVENUE_HISTORY for USER_FILER_ID Pull revenue information }
here's the View being used which is the First Case which is happening currently
WHERE (dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_CYCLE.CYCLE_ID) AND (BILLING_DETAILS.LINE_ITEM_TYPE IN ('SLDADJ', 'SLDC',
'RHCDC', 'RHCDADJ'))))
Current Situation. 1. They use CR 8.5 IDE to enter the parameters and this version of CR doesn't allow using Stored PRocedures. 2. I'm not very good with the Database side and Complex Queries always leave me in the middle of nowhere. But i really really need to do this 3. please give me some clue.. some idea how to resolve this.
here's the primary database diagram http://deepak.palkar.googlepages.com/Billing2.jpg
I am attempting to explain my probelm again. Please read it:
I have 3 tables. CallDetail, Call and Request. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on.
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType. The no will remain same for the same CallDetailID and ordered by the date created. However if the CallDetailId changes, the no. will increment based on the empid, Per StateNo, Per CityNo, Per CallType
For eg:
For Eg: ( Assume Call Detail id is changing for all the days) Monday - 3 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 1 for all 3 calls Tuesday - 2 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 2 for both calls Wednesday - 3 calls made for emp id 1, state SA023, city 12 and call type 2 will generate a unique id 1 for 3 calls as the call type is different than the previous day for same employee Thursday - 2 calls made for empid 2, state SA023, city 13 and call type 1 will generate unique id 1 for both the calls as combi of city and call type are different.
So the unique id has to be generated considering empid, state, city and call type, ordered by the EntryDt. EntryDt is needed because : 3 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 10.00 AM will generate a unique id 1 for all 3 calls 2 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 12.00 AM will generate a unique id 2 as the call was registered later.
Here is what I wrote with the help of a mod over here:
INSERT @Request SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL SELECT '22322362', 'Guck', 'SA023', 12, 123, Null UNION ALL SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL SELECT '225652', 'Jim', 'SA023', 12, 143, Null UNION ALL SELECT '126756', 'Jasm', 'SA023', 12, 145, Null UNION ALL SELECT '786234', 'Chuck', 'SA023', 12, 154, Null UNION ALL SELECT '66234', 'Mutuk', 'SA023', 12, 185, Null UNION ALL SELECT '2232362', 'Buck', 'SA023', 12, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT) INSERT @Call SELECT 111, 1, 12123 UNION ALL SELECT 112, 1, 12123 UNION ALL SELECT 114, 1, 12123 UNION ALL SELECT 123, 2, 12123 UNION ALL SELECT 134, 2, 12123 UNION ALL SELECT 143, 1, 6532 UNION ALL SELECT 145, 1, 6532 UNION ALL SELECT 154, 1, 6532 UNION ALL SELECT 185, 2, 6532 UNION ALL SELECT 195, 3, 6532
-- Query written with help of a helpful person here UPDATE r SET r.UniqueNo = dt.CallGroup FROM @Request r JOIN @Call c ON r.CallID = c.CallID JOIN (SELECT CallDetailID, EntryDt,EmpID, CallGroup = ROW_NUMBER() OVER (ORDER BY EntryDt ) FROM @CallDetail ) dt ON c.CallDetailID = dt.CallDetailID select * from @Request
as the call for Buck is of calltype 3 which was not done earlier. So the no starts from 1.
Also how to add the paritioning by empid, StateNo, Per CityNo, Per CallType and yet maintain the same unique no for the same calldetailid. Eg: CallGroup = ROW_NUMBER() OVER (PARTITION BY empid, state, city, calltype ORDER BY EntryDt )
I am working to archive some old data from a data warehouse using SQL server and SSIS. The data will be read and denormalized, then shipped out to a delimited text file.
The rowcount of the incoming data is significant, call it 10M+ rows per unit of work (one text file).
There are development advantages of using a stored proc for the data source - mainly ease of changing the denormalization logic as required. Wondering if there are performance advantages of an embeded query for the data source instead?
It was mentioned by one developer that when using a stored procedure, the output stream from the proc and subsequent SSIS steps cannot start until the full procedure processing is complete; i.e. the proc churns out its' result set in one big chunk.
He hinted that an embedded query does not have this same effect, but I am not sure that is accurate.
I have a data flow task in which i have multiple OLEDB sources going to a one OLEDB destination via UNION component. I also have a derived column component sitting behind the union, because there are some columns, which exist in the destination table, but are not coming from source, but I am responsible of providing values for those fields. The question is that some of those values are conditional, for eg. Lets say we have a column called RecStatusCode in the derived column component. The value for that column depends on a condition/criteria, so I need to query a db and based on the value of another field, I set the value of the recstatus code. How do i accomplish that? I am thinking expressions, but I need clear direction in steps in how to set a value of a derived column based on a criteria(sql statement). Thanks.
I have an excel sheet containing one column (ID_NO) with 400K rows. I have a database from where I have to fetch some other columns from a Netezza database. Initially I tried hardcoding all the 400K rows in the query that I wrote using filter WHERE ID IN ('1212','2334'). But after pasting all the 400K rows the query is running indefinitely.
I have imported all the ID in a SQL table (MY_LIST table). I used a DFT, and selected ODBC source, and selected my netezza server. Then in the 'Data access mode' I selected the SQL command from the dropdown.I pasted the same query that I wrote in Netezza. Is there any way to pull only for those records that I have pulled in my SQL table (MY_LIST) ?
How do i change data type settings in when building my tables? Specifically i want to reduce the number of decimal places on 'smallmoney' so it doesn't show 4 but rather 2. Thank you.
We are setting up the clients' database with transactional replication that allows the subscriber to send updates by pull subscriptions.
The plan is to create a second database so the users can keep working on the original database while we get the replication set up and working. Once we feel confident that the replication is working on the second database, the plan was to backup the old one, then detach it, and then restore the new database with the latest data from the old one. My question is whether this would preserve all the replication configurations that we set up? Since the replication adds a column to each table what would happen to that column? Or what is an alternative, which would allow us to set up the replication without disturbing the user's work, and then implement the replication with the latest data?
I am also wondering how to set the synchronization to happen once daily? I do not see where I can set that. I only see options for continuous vs on demand. Does on demand mean I can somehow schedule with an external program to run once a day?
Thanks for your patience, and I hope my questions make sense.
In SQL 2000, working with Maintenance plan wizard, what would be best settings and values should i choose in "Update Data Optimization information" window,
I tried Data Mining Add-Ins for Office 2007 - CTP December 2006. Test settings: Windows XP SP2 english with Italian regional settings, Office 2007 english (RTM), SQL Server 2005 Developer (with SP2 CTP Dec06) and Data Mining Add-ins for Office 2007 (CTP Dec06).
If I keep regional settings in Italian, I get error like this:
Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
If I change regional settings to English, the Add-in works.
I found this description as the possible cause of the problem: http://msdn2.microsoft.com/en-us/library/ms178780(vs.80).aspx - if this is the issue, it would be necessary to change the ExcelLocale1033Attribute on the component.
Is there another workaround other than to install the Office 2007 MUI?
Marco Russo http://www.sqlbi.eu http://www.sqljunkies.com/weblog/sqlbi
