iLearn

Last week, we faced a basic and yet the only major problem that sql developers/admin had to deal with and yes your guess is right, It was with SQL QUERY RUNNING SLOW.  I will be explaining the problem below, however I want everyone reading this to help me find answers because I still haven't found one! One more amazing aspect of the issue was that it worked fairly quickly in Development but was running like a tortoise in Production even though the number of records were practically the same. I'll start by throwing some light on to the execution plan, so that we narrow down to the code which was the culprit here. The code is enclosed into a stored proc. On contacting my dba to enquire about the issue, I got the following replay:- I must hand it to our dba to provide such a detailed response.  So my job was now to look at the code and modify it. What I did was to shrink the calender table into a temp table consisting of few rows that would speed up the operation. Bel

Nature of the Issue Faced :- Say you have 3 columns in a tablix/table on the report. There are 3 textboxes in each of these columns. Let us give them names as textbox1, textbox2 and textbox3. Textbox3 should be textbox1 - textbox2. We are using following formula to achieve this :- textbox3.Value = ReportItems!textbox1.Value -  ReportItems!textbox2.Value textbox1 and textbox2 have decimal values rounded off to 3 decimal places (e.g.12.412). On the report we had set the format of the cell to be 'P1' (eg 12.4) Now that we know the scenario, here is what the issue is - Everything works fine inside the cell of the report, however as soon as to export to excel the place where you have expression shows some arbitrary value like _385 -  _386 textbox1 has value of 76.0% and textbox2 has value of 74.1% in the above example! Solution :- Changing the formula to Round ( ReportItems!textbox1.Value,3) -  Round(ReportItems!textbox2.Value,3) rectified the issue. Cannot quite

For relational data-warehouses where you have the data load scheduled during off hours, timing of different steps in a sql job plays a vital role to decide whether to carry on with the load or postpone it for the evening. We as a team recently encountered a scenario where we had to disable the execution of a ssis package (which ran through a sql job) because the current time crossed the threshold time. Now there are various ways to indirectly disable or bypass a job step, however all of these require admin's intervention. We needed an automated process wherein the package/load should not execute if the time limit is crossed. Solution - One of my manager came up with a logic which helped us tackle this situation. Here is how the package looked like:- Execute SQL Task that you see here controls whether the rest of the package should execute or not. Here is the script we used inside :- Select current time >=  thresholdtime then 1 else 0 end as intTimeCheckover This

When you undertake some performance improvement task, you think it is frustrating however at the same time you tend to learn more. So the other day we had to brain storm on one of the similar issues where the database stored procedure was returning data quickly as compared to the SSRS report (using same stored procedure) which seemed to be taking ages to run and display data.  The initial investigation and their results were:- Finding out from excutionlog table where the issue is. Turned out that 'TimeDataRetrieval' was considerably high for previous runs of the report. This proved that the real cause was indeed the slow performance of the stored procedure. Checked to see whether it could be related to temp database. On connecting with the database admin this was ruled out. Checked to see the cpu utilization at the report server. Nothing significant was found. After doing all these steps,  I thought that this issue might be related to the parameter sniffing. Paramet

Hi Everyone In the daily team meeting today, we had a requirement to find out the stored procedure used/referenced by SSRS reports. My Team Lead suggest why cant we write a sql to search the xml of the reports. Upon googling I came across this beautiful article by :- https://www.mssqltips.com/sqlservertip/3693/find-tables-columns-or-stored-procedures-used-in-a-sql-server-reporting-services-report/ Suggest everyone to go through this to understand the code. Here is the snipped which would give list of sp's that are used inside a report :- Declare @ReportName VARCHAR ( 100 ) Set @ReportName = 'QuarterlySalesPerformance' ; WITH XMLNAMESPACES ( DEFAULT    ' http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition ' , 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition ) SELECT   distinct xmlcolumn . value ( '(Query/CommandText)[1]' , 'VARCHAR(2500)'

Hi Guys I know sql 2005 is getting outdated and there would be no support from Microsoft from this month. But lets not  go on that road, because client is ready to take the risk. So as a result we are still working on sql 2005. However by working with so called soon to be a outdated version gives you a chance to come  across  various bugs with the technology. I am listing few of them which I encountered last week or so:- 1.      I have a stored proc like this :- create proc reporting.sp_test as select * from test exec reporting.sp_test As you can see this is a stored proc belonging to a schema 'reporting' and referring to a table with default schema 'dbo'. On executing it I get a error message which says that - test does not exist. However when I tested this in sql 2008, it works fine. It seems the behaviour changed in 2008. It looks like in 2005 the default schema is the schema of the caller (in this case the SP) and in 2008 it's defau