-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

EVERYDAY MSBI/SQL CHALLENGES

My name is Lokesh Sharma, I have 5 years of experience working with SQL and am really interested in exploring more and more about this fascinating language. My aim here is to share some of the challenges that I faced while working with SQL and also to get feedback whether the approach I followed is an optimal one or not.

SSIS Issues : A Day of Learning

Seldom are the days when you run into complex issues but resolve them in the shortest interval of time.

Thanks to Larry Page and my fellow developers :-).

But jokes apart I learned something new today and that always feel wonderful.

Issue #1:- Unable to enlist in the distributed transaction

Read more

0 comments, 171 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 24 November 2017

SQL Query Experiment - "WHERE" & "ON"

Sometimes the mistakes you make in the coding lead you to a better understanding of a particular concept.

This happened recently with me. I accidentally replaced the 'WHERE' clause in a SQL query with "AND" and the results confused the heck out of me. 

Lets look at the coding part now.…

Read more

2 comments, 4,202 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 2 November 2017

Be Aware of CARTESIAN PRODUCT When Using Join Keys in SQL

I got a massive satisfaction last week when I was able to bring down the execution time of a sql stored procedure from > 3 hours to mere 2 minutes. It all boiled down to a missing joining key that led to a cartesian product within the tables.

Here is…

Read more

4 comments, 1,112 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 6 June 2017

Decrease Stored Proc Execution Time By Limiting the Table Size

Hi Guys,

Hope everyone is doing well. Last week, I was working on the most exciting task on which any database developer would love to work - Performance Tuning of a Stored Procedure.

Let me explain about the code that this stored procedure consisted of. The basic task was…

Read more

0 comments, 320 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 30 May 2017

Dump Data From SQL 2005 table to Excel 2016

By First looking at the topic name, does the task sound simple to you? Well, I thought so but it turned out to be quite a mammoth task with loads of learning.

The requirement is to transfer the data from a SQL Server table to an excel spreadsheet. The caveat is…

Read more

2 comments, 1,125 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 22 May 2017

Run a Job on SQL Azure (Using SSIS Package)

Requirements: A valid azure account, Local Instance of SQL Server (>= 2012), SQL Server Data Tools, Excel 2007 or above

Scenario: Dump Excel File Contents to a Table in SQL Azure

Steps:
 
 Develop SSIS Package-

  1. Drag Data Flow Task on the control flow window and double…

Read more

0 comments, 1,633 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 11 March 2017

SQL QUERY NIGHTMARE

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…

Read more

14 comments, 3,852 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 23 September 2016

SSRS 2005 Bug - Percentage calculated column when Exporting to Excel

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…

Read more

1 comments, 929 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 20 May 2016

ALTERNATIVE TO DISABLING A SQL JOB STEP (FOR SSIS PACKAGES)

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…

Read more

0 comments, 1,030 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 6 May 2016

Possible PARAMETER SNIFFING Scenario in SQL

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…

Read more

2 comments, 3,250 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 29 April 2016

FIND OBJECTS BEING REFERRED BY SSRS REPORT

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 :-

Read more

0 comments, 444 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 7 April 2016

SOME BUGS IN SQL-SSRS 2005

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…

Read more

0 comments, 276 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 1 April 2016

Handling decimal and non numeric types using Case statement

Hi Guys

Just encountered a scenario where I needed to handle numeric and non numeric types using Case statement. Here is the synopsis of challenge and solution:-


Challenge :- You have a varchar column in your input table having values such as 1.1111, 34, John.

CREATE TABLE #TEST ([UpdatedValue] VARCHAR(100))

INSERT…

Read more

6 comments, 2,447 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 6 October 2015

syntax error in textheader of stored procedure - Cause and Solution

Hi Folks,

Comments play an important part in writing any code. Especially when somebody else reads your code. So the other day I was writing a stored procedure and some comments on top to list the tasks being performed inside the stored procedure. We are using sql server 2005 and…

Read more

0 comments, 835 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 30 August 2015

Hi Folks,


Hope you all are doing good. Would like to start with praying for all those who have been affected by a deadly earthquake in Nepal. Just when it seems that life is good something like this happens, to remind us of that we are not permanent here. Death…

Read more

0 comments, 0 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 26 April 2015

Data Rendering Eye Opener SSRS

Hi Folks

Greetings to everyone!!!


So last week one of the report was having a performance issue. It had 10 datasets ,5 data regions and a report parameter. Regions displayed cascading information based on user's selection of parameter.

For example,. lets assume the parameter name to be ReportLevel, it would…

Read more

0 comments, 6,208 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 12 April 2015

Toggle Functionality Looses In excel--ssrs 2005

Hi Folks


Highlighting on the issue that I faced last week while working on reporting services 2005.

Requirement :- A data region (table) is to be displayed based on the parameter value. Table has a toggle facility on detail rows of a group like


so on export to excel it…

Read more

0 comments, 497 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 3 April 2015

Date Range Challenge in sql

Hi Folks,


When you work in a offshore - onsite setup in IT industry and if you are fortunate :-) to be a part of the offshore team then there are many compromises that you have to make. Out of those one such is to develop your code based on…

Read more

0 comments, 352 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 15 March 2015

CTE - Limitations

Hi


Wanted to explore one of the major limitation of CTE in sql 2012 i.e. it cannot be used inside if else statement . This is something basic as the definition of cte that it must be followed by a select, insert, update or delete statement.

That is we cannot…

Read more

0 comments, 1,035 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 27 July 2014

Bug In ado.net enumerator in foreach loop in SSIS 2012

Hi Greetings to everyone,


Today I am going to report a bug in SSIS 2012. In one of the packages I was using execute sql task and then using ado.net foreach loop enumerator. My execute sql task had a simple select statement like - 'select c1,c2,c3 from table' where c1,c2,c3…

Read more

0 comments, 707 reads

Posted in EVERYDAY MSBI/SQL CHALLENGES on 27 July 2014

Older posts