SQL SERVER – Hotfixes for SQL Server 2014


Microsoft released the hotfixes for the latest version of MS SQL Server – SQL Server 2014 CU 4. The new build number for this cumulative update 4 (CU 4)  is 12.0.2430.0

The full list of hotfixes provided in this release are compiled in the excel file. Download SQL Server 2014 CU 4 hot fixes excel file.

Hotfixes Statistics

Fix area No Of Fixes
SQL Service 13
Analysis Services 10
Reporting Services 8
Management Tools 7
SQL performance 5
DQS 3
Integration Services 3
Data Quality Services (DQS) 1
SQL connectivity 1
High Availability 1
BI Azure 1
In-Memory OLTP 1
Grand Total 54

Download Hotfix now

Source: http://support.microsoft.com/kb/2999197

Categories: SQL Server

SQL SERVER – Fix the Report Builder bug in SQL Server 2008 R2 SP 3


Microsoft recently send this workaround to fix the issue in SQL Server 2008 R2 SP 3 for Report Builder launch

Issue:
If you installed SQL Server 2008 R2, have upgraded it to Service Pack 2 and then applied Service Pack 3, then Report Builder will fail to launch.

workarounds:

Client side workaround:
Install and run Report Builder 3.0 of SQL Server 2008 R2 RTM from the following location: http://www.microsoft.com/en-pk/download/details.aspx?id=6116
Or
Install and run Report Builder of SQL Server 2014 from the following location:

http://www.microsoft.com/en-us/download/details.aspx?id=42301

Server side workaround:
Uninstall Service Pack 3 then uninstall Service Pack 2 and then reinstall Service Pack 3.

Full detail is in Here

Categories: SQL Server

SQL SERVER – Declare Mulitple Variables in Single Line


This may be a simple thing, but sometime new developers are difficult to find this information readily. So, thought of adding it in this website.

Question
How to declare and initialize variables in single or multiple lines?

Answer:

Declaring variables – Multiple lines

DECLARE @int_i AS INT;
DECLARE @chr_Name AS VARCHAR(50);

Declare and Initialize variables – Multiple lines

DECLARE @int_i AS INT = 1;
DECLARE @chr_Name AS VARCHAR(50) = ‘Test’;

Declare variables in single line

DECLARE @int_i AS INT, @chr_Name AS VARCHAR(50);

Declare and Initialize variables in single line

DECLARE @int_i AS INT = 1, @chr_Name AS VARCHAR(50) = ‘Test’;

That is it, You have learned to declare and initialize variables in MS SQL Server.

Categories: SQL Server Tags:

Unpivot Transformation with Example- SSIS


This blog post is just to give a step by step instruction to work with Unpivot Transformation in SSIS.

Basics:

Unpivot Transformation is used to convert unnormalized dataset into normalized dataset. Database Normalization.

Example:

For instance, we have got a data as show in the below image and we want to normalize the data to make the user table compact.

pic1

In this dataset, Column 1 contains Customer names, column 2 to 6 contains product names and each data cell represents the quantity purchased by the customer. But to store this data in the RDBMS, we need to convert this dataset into normalized structure. So, In normalizes structure this data will looks like this.

pic2

Now, we are going to implement this scenario in using SSIS.

Table Creation:

CREATE TABLE [dbo].[tblPurchase](
[Customer] [nchar](10) NULL,
[Ham] [nchar](10) NULL,
[Soda] [nchar](10) NULL,
[Milk] [nchar](10) NULL,
[Beer] [nchar](10) NULL,
[Chips] [nchar](10) NULL
) ON [PRIMARY]

Insert rows:

INSERT INTO [dbo].[tblPurchase]
([Customer],[Ham],[Soda],[Milk],[Beer],[Chips])
VALUES
(‘Kate’,’2′, ’6′, ’1′, ’12′,NULL),
(‘Fred’,NULL,NULL,’3′,’24′,’2′),
(‘Robert’,NULL, ’1′,’2′,’3′,’1′),
(‘John’,’3′,’4′,NULL,’30′,’2′),
(‘Dan’,’2′,’2′,’3′,’1′,’4′);

Step 1: Create a new package

Step 2: Add a Data Flow Task in to package and Go to Data Flow environment

Step 3: Add a OLE DB source task to configure to read the table.

pic3

Step 4: Add Unpivot transformation. This task is used to convert unnormalized dataset to normalized dataset. Unpivot Transformation Editor.

pic4

Notice that the customer column is removed from the pivot input column and setup the output column as quantity and the product column as pivot key. So, we will get the output of Product and its quantity for each customer in multiple rows.

Note: All null value quantity rows will be removed by this transformation.

Step 5: Use the OLE destination column to store the unpivoted output in the normalized table. In this example, I am using the union all transformation as the destination and added data viewer to browse the output in the pipeline.

pic5

Step 6: Execute the package

pic6

This output show that the unpivoted dataset is in normalized structure.

Now, You can easily normalize any unnomalized dataset using Unpivot Transformation task in SSIS. I hope you have enjoyed reading this article. So, please share your thoughts and question in the comment section.

Reference link

Execute Stored Procedure with Input and Output Parameters and Return Status in SSIS


In this blog post, I would like to show a simple example on how to handle or use the stored procedure that has  input , output parameter and return status value in SSIS.

Create Stored Procedure in SQL Server

CREATE PROCEDURE usp_TestProcedure
    @MyInput varchar(100),
    @MyOutput varchar(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @MyOutput = ‘SQL Server Rider’;
    RETURN 5;
END
GO

This user procedure has two parameters input and output. I also returns a return status. All these parameters and return values are imaginary and has no purpose.

Execute usp_TestProcedure in Query window

DECLARE @Output AS VARCHAR(100)
EXEC [dbo].[usp_TestProcedure] ‘Blogger’, @Output OUTPUT
PRINT @Output

@Output is the character variable and also act as an output parameter variable to bring back the value of the @MyOutput parameter in the stored procedure.

To execute the same stored procedure in the SSIS, we are going to use Execute SQL Task. So, We need to create three variables in the package to execute this stored procedure.

Step 0: Create SSIS solution in SS

Step 1: Add 3 package variables with correct data type.

pic1

Step 2: Add and Configure the SQL Execute Tast

pic2

Configure Database connection, Stored procedure and its parameters.

pic3

SQL Statement: exec ? = usp_TestProcedure ?,? OUTPUT

Map variables to Input/output parameters and return value .

pic4

Step 3: Executing the package and watch the variable’s value during run-time.

pic5

Package execution succeeded by calling the stored procedure.

Common mistake

We need to Map the parameters in sequential order. It means, in this example parameter 0 is return value and 1 is the input and 2 is the output parameter.

Categories: Uncategorized

Upcoming End of Day support for Microsoft SQL Server Products


Download latest and up-to-date list of SQL Server versions and its support dates. Click here to download (CSV file)

It is always important to know what versions of SQL Server running in our environment to keep the business healthy and  to avoid unintended issues such as security risks, system failure, performance issues and etc.  Keeping the server up-to-date will give the best solution to business for complex customer problems.

This is the list of the end of mainstream support for SQL Server 2008 and SQL Server 2008 R2

pic1

So, go check the system and update or upgrade the old versions.

Thanks for reading.

SQL Server 2008 R2 SP2 CU #13 – Final Update


Cumulative Update (CU) #13 has been released for SQL Server 2008 R2 SP2. It is the final update for this version because the mainstream support ends on July 8, 2014.

Following are the fix for the BI services in CU #13

Analysis Services

  1. FIX: Memory leak occurs during Process Update on a dimension in SSAS 2008 R2
  2. FIX: Incorrect result and exception occur when you use MDX query to do session cube grouping in SSAS 2008 R2

Full list of CU downloads for SQL Server 2008 R2 SP2

CU#13 KB Article
CU#12 KB Article
CU#11 KB Article
CU#10 KB Article
CU#9 KB Article
CU#8 KB Article
CU#7 KB Article
CU#6 KB Article
CU#5 KB Article
CU#4 KB Article
CU#3 KB Article
CU#2 KB Article
CU#1 KB Article

Categories: Hotfix Tags: ,
Follow

Get every new post delivered to your Inbox.

Join 335 other followers

%d bloggers like this: