Friday, December 12, 2014

How to fetch all numeric values from the string?

In this article I will show how to fetch all numeric values from a string.

I am creating a User defined function which will return all numeric values from the supplied string.

Function Code:

CREATE FUNCTION dbo.FetchNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END

Example to Fetching Numeric Values from String:

SELECT dbo.FetchNumeric('jvhew832gj3hgj45')
I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Thursday, December 4, 2014

Error attaching MdxStepByStep.mdf for SQL Server 2008 R2

In This article I will show how to attach MDXStepByStep database. I have seen many people face problem when attaching the MDXStepByStep database using the attach_db.sql script.

When you execute attach_db.sql script it gives the below error.

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Microsoft Press\MDX SBS\Setup\SQL Server\MdxStepByStep.mdf". Operating system error 5: "5(Access is denied.)".

Please follow the below steps to attach the MDXStepByStep database:

Step 1: Copy only MDXStepByStep.mdf file from “C:\Microsoft Press\MDX SBS\Setup\SQL Server” to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”.

Step 2: Login to your SQL Server with your appropriate credentials, make sure your account must have permission to create database.

Step 3: Right Click on the Database and click on Attach, as shown below.



Step 4: Once you click on attach button it will open a new window. You have to click on add button and then select the MDXStepByStep.mdf file and click ok. You can find below the screenshot.



Step 5: You have added the MDXStepByStep.mdf file but MDXStepByStep.ldf file will be missing for this database because you didn’t copy that file in setp 1. In this step you have to remove the missing file.
Select MDXStepByStep.ldf file and click on remove button and click Ok as shown below.

 

Congratulations you have successfully attached the MDXStepByStep Database.

I hope you enjoyed the article don't forget to hit the Facebook Page Like Button. Please leave your comments below if there are any questions.

Monday, December 1, 2014

Sql Server Reporting Services (SSRS) Architecture

Reporting Services is one of the core component of MS SQL Server. Reporting Services is a server based reporting platform that provides reporting functionality to the user.Report Server is central component of Reporting Services. In this article we will try to understand how SSRS works in native mode deployment.

Reporting Services Report Server runs in one of two deployment modes:
1.       Native Mode
2.       Sharepoint Mode

You can’t switch Report Server mode from one to another  so user has to decide in which mode Reporting Services should be configured at the time of Reporting Services installation. Native mode is the default mode for Report server.

In Native mode Report Server is a stand alone application server that provides all viewing, processing and delivering of the reports and report model. Report Server is implemented as a Microsoft Windows Service called as Report Server.

Native mode reporting services Report Server 3 tier architecture is given below.

Report Server consists of a pair of Processing Engines and five special purposes of extensions which handle Authentication, Report Processing, Rendering, Data Processing and Delivery operation.

Let’s discuss each component from the above architecture.

Tier 3 (Client Application in Presentation layer)

User sends request to report server for Report Processing, Report Scheduling and Delivery etc from any of the client application present in presentation layer i.e Report Manager, Report Builder, Report Designer.

Tier 2 (Report Server Components)

1- Programmatic Interface:

Programmatic interface process all the requests sent to Report Server from the client application present in presentation layer. This includes requests from Report manager, Scheduling and Delivery Processor, Report Design Tools and Third party Tools. Programmatic Interface uses Internet Information Services (IIS) to receive the requests.

When a request arrives at the report server in the form of SOAP (Simple Object Access Protocol) and http request; Programmatic Interface interacts with the Report Server database in response to the request. When a report is requested the programmatic Interface initialize the Report Processor and when a Report Scheduling and Delivery is requested the programmatic Interface initialize the Scheduling and Delivery Processor.

2- Report Processor:

When a report is send from the presentation layer, it will reach to the programmatic layer. Programmatic layer will initialize the Report Processor. Report processor will retrieve the report definition or model information then combines layout information with the data from data processing extension and renders it in the requested format.

Authentication, Report Processing, Rendering , Data Processing and Delivery Extensions

The report server supports five types of extensions, authentication extensions, report processing extensions, rendering extensions, data processing extensions and delivery extensions. A report server requires at least one authentication extension, rendering extension and data processing extension. Delivery and custom report processing extensions are optional.

3- The Scheduling and Delivery Processor

The Scheduling and Delivery Processor processes reports triggered from a schedule, and delivers reports to target destinations.

Tier 1 (Data Layer)

1- Report Server Database:

Reporting Server Database is a SQL Server database. This is created at the time of Reporting Services configuration. It stores Reporting Services data such as Report Definitions, Report Metadata, Cached Reports and snapshots. It also stored security settings, encrypted data, scheduling -delivering and extension information and folder hierarchy. This data is accessed through Report Server. Report Server Database can provide internal storage information for a single reporting services or multiple report server.

2- Data Sources:

This is the data which user wants to represent in reports.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Content reference is msdn.

Sunday, November 2, 2014

Read and Export Excel data from nth Row in SSIS

This post shows how we can read and export data from excel starting from nth row.

In SSIS we can read excel data starting from any number of row. Considering a scenario we have a excel file like below screen and we need to read data of Row 2 to Row 15.
To do this I create an Excel Connection manager in my SSIS package. I created a Data Flow Task in the control tab. On Data Flow tab I have following components. 


Right Click on the Excel Source and select properties.

In the OpenRowset you will find the sheet name from which you are extracting the data. You have to make modification here to read the data from the nth row or range.

As you can see from the below Image I have already updated the OpenRowset value to
Sheet1$A2:A15


Now double click on the excel source and click on preview button, as you can see from the below Image data was read from the row 2 to row 15.


I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Check IsNumeric() with Derived Column Transform in SSIS


In SSIS we don’t have ISNUMERIC () equivalent within the SSIS expression language. You can't use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn't mean you can't check to see if a field is numeric using the SSIS expression language. Using script task we can check this, using SQL command in Excel ( SELECT F1 FROM [SHEET1$] WHERE ISNUMERIC(F1)=-1) source we can check this, but in this tutorial I will show how we can check if a field is numeric without script task.

Create a sample excel file as shown below



As you can see from the above data our column has both numeric and string data. Now I want to check which row is numeric.

As I said before, there is a way we can use a Derived Column Transform (or Conditional Split) to check if a field is numeric.

Steps:
1-      Create Excel source connection
2-      Drag a Derived Column

After dragging in a Derived Column Transform into your Data Flow Task, create a new column to be added as a new column to your data flow. Give it a meaningful name to the new column and use this expression:

(DT_I4)Column == (DT_I4) Column? 1 : 0



Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to ignore failure on Error, as seen here:
  

Optionally, you could choose to redirect rows that are not numeric to the Error output of the Derived Column and then handle those rows there.

3-      Add another derived column and add data viewer on it.

Package should look like this.



On Execution you should get the below output.



You can see that the rows that are not numeric have a NULL value for the IsNumeric field we created with the Derived Column. Rows that are numeric have a 1. This way it is easy for us to determine which rows of a certain field is numeric and which are not numeric by checking for NULL.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Wednesday, October 8, 2014

My First Report in SSRS

In this article we will be creating our first SSRS report. I am using SSRS 2012 environment to walk you through the SSRS functionalities.

I will start from the very basics. Once we are comfortable with the tool we can skip these steps in coming articles.

Once you install SQL Server 2012 successfully, you get Microsoft SQL Server 2012 folder in your programs. You can find the snapshot below, Click on SQL Server Data Tools.



We will create a Solution and under that Solution we will create SQL Server Reporting Services Project. A Solution can have multiple different types of projects i.e. SSRS, SSIS and SSAS etc. Our Project will contain all the reports we create.

Please follow the below steps to create a solution and project.

 

Once you click on Project or you press Ctrl+Shift+N another window will open. In this new window you have to select your project type. As I am going to demonstrate SQL Server Reporting Services, so I will choose Reporting Services Template and then Report Server Project Business Intelligence. You have to give a proper Solution name with Project Name. You can find below the screen shot.



Once you successfully create your Solution and Project, by default it will be saved under C:\Users\YourUsername\Documents\Visual Studio 2010\Projects. In my case myusername is GG.

Solution file will have .sln file extension and project file will have .rptproj file extension.
Once you create a solution and project, you have to open Solution Explorer to create Report. If it is not open then you can open it either using ctrl+alt+L shortcut or go to View in the Menu bar and then click on Solution Explorer as shown below.


Once you follow any of the method, you will get Solution Explorer. As you can see from the below image our new SSRS project has three folders inside it.


Shared Data Sources –This folder contains list of all Shared Data Source Connections.
Shared Datasets – This folder contains list of all Shared Datasets.
Reports – This folder contains all SSRS reports.

In this article we will be creating a Report without Data source and Data sets. I will be creating another article in which we will first discuss about Data Sources, Data Sets and then create a report.


To add or create a new report into this project, please right click on Reports folder and then click on Add and then choose new item as shown below. You can also add a new item using Ctrl+Shift+A.


Once you follow any of the above method, you get a new window which gives you option to choose your new item. As we are creating a Report so we will choose Report from the available options. You can refer the below image. You have to give a proper Report name and click on add button present at the bottom.


Once you click on the add button your new report will be added to the project under Reports folder.
As you can see from the below image, a new report name as Report.rdl has been added into the SSRS Project (RHS). LHS we have Report Data and in the middle we have Report Body. Report body has two tabs Design and Preview. In the Design tab we design our report and preview is used to execute the report. 


To add an element in the report please right click on report body and then choose insert. Once your do mouse over on Insert, it gives you all available elements for reporting. You can refer below image for list of all available reporting elements.


Please Text Box from available reporting elements. Once you add Text Box it will be available in the report. You can drag & Drop, Increase and Decrease the Text Box size as per your need. This is the element which is used for labeling purpose. You can double click the Text Box and write whatever you want to present.


 To execute or preview the report please click on the Preview button.


You can export your report to any of the available option as shown below.

Congratulations you have successfully created and executed your first report in SSRS.

Monday, September 22, 2014

User Defined Functions in SQL Server

In this article, I am giving a quick overview about user defined functions in SQL Server. After completing this article you will understand:

What is User Defined Functions (UDF) in SQL Server?
What are the types of User Defined Function in SQL Server?
How to create User Defined Function in SQL Server?
How to ALTER and DROP user defined functions in SQL Server?
What are the advantages of User Defined Functions in SQL Server?

Please give your valuable suggestions and feedback to improve this article.

I have already discussed about TSQL programming basics, I will recommend you to visit the article if you are not aware of TSQL.

I hope you have gone through the basics of TSL Programming basics, now we can start.

As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.

Sub Program blocks are of two types in SQL Server.

1. Procedures
2. Functions.

In this article I will be focusing on Functions, I have already discussed about Stored Procedure in my previous article.

There are two types of Functions in SQL Server
  1. Built In Function: These are the inbuilt functions present in SQL Server. User can’t make any change in these functions i.e Min(), Max(), UPPER()
  2. User Defined Functions: SQL Server allows user to create their own functions. These functions are known as User Defined Functions.

What is User Defined Functions (UDF) in SQL Server?

A user defined functions are also a Stored Block of code similar to Stored Procedure. It always returns at least a single value or a table. Function doesn't support output parameters but it can have maximum 1024 input parameters. We can’t perform DDL, DML operations in functions. There are many limitations in functions i.e we can’t use Try-catch blocks in functions. For more about stored procedure and function refer the article Difference between Stored Procedure and Function

What are the types of User Defined Function in SQL Server?

Functions are of 3 types in SQL Server.
  1. Scalar Functions
  2. Inline Table Valued Functions
  3. Multi-Statement Table Valued Functions.
How to create User Defined Function in SQL Server?

Let’s understand each type of User Defined Functions with one simple example.

Scalar Functions

A Scalar user-defined function returns one of the scalar data types referenced in the RETURNS clause in CREATE FUNCTION statement. Text, ntext, image, Cursor and timestamp data types are not supported in Scalar User Defined. Scalar Function can have 0 to 1024 input parameters.

Below is the syntax to create Scalar Type User Defined Functions

Syntax

CREATE FUNCTION Function-Name
(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)
RETURNS Data-Type
WITH Function-Options
AS
BEGIN
Function-Body
RETURN Scalar-Expression
END

Function-Name– This is the function name, you have to remember this name.
@Paramter-Name Data-Type = Default – This is the input parameter name with its data type for the function.
Function-Options – Functions options can be any of these two
  1. Encryption – Indicates that the Database Engine encrypts the catalog view columns that contains the text of the create function statement.
  2. Schemabinding – Indicates that Functions is bound to the database object that it references. Object can’t be dropped until you drop the function or alter the Function without Schemabinding option.
Function-Body – This is the place where we write our logic.

Example

I am creating one EMP table and populating some data in this table. We will be using this table to understand Scalar type user defined functions.

You can use below query to populate dummy data.

USE TEACHMESQLSERVER
GO
CREATE TABLE EMP (EMPID INT, EMPNAME VARCHAR(255), DEPNAME VARCHAR(255), SALARY MONEY, BONUS MONEY)
INSERT INTO EMP VALUES(1,'GHANESH','IT',2000,100)
INSERT INTO EMP VALUES(2,'PRASAD','HR',3000,1100)
INSERT INTO EMP VALUES(3,'GAUTAM','SALES',2500,400)
INSERT INTO EMP VALUES(4,'ANVIE','MARKETING',20000,NULL)
GO
SELECT * FROM EMP


Problem – Create a function which returns Employee’s salary + Bonus from EMP table based on EMPID.
SQL Code –

CREATE FUNCTION MYSCALARFUNCTION (@EMPID INT)
RETURNS MONEY
AS
BEGIN
DECLARE @TOTALSALARY MONEY
SELECT @TOTALSALARY= SALARY+ ISNULL(BONUS,0) FROM EMP WHERE EMPID=@EMPID
RETURN @TOTALSALARY
END

Congratulations you have successfully created your first user defined function which will return (SALARY + BONUS) from EMP Table based on their EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Scalar-Valued Functions. Below Images shows the path. 


Now our task is to call recently created a scalar function.

Syntax for calling a Scalar Function:
SELECT <owner>.<function-name> (Parameters values)

Calling the MYSCALARFUNCTION function:

SELECT DBO.MYSCALARFUNCTION(2) as TOTALSALARY


 As you can see from above result our MYSCALARFUNCTION function is returning only one scalar value which as referenced in the CREATE Function command.

Inline Table Valued Functions

Inline Table Valued Functions return a Table variable as an output. In Inline table valued functions, the Table returns value is defined through a single select statement so there is no need of BEGIN/END blocks in the CREATE FUNCTION statement. There is also no need to specify the table variable name or column definitions for the table variable because the structure of the table variable will be generated from the columns that compose the SELECT statement. In Inline Table Valued Functions there should be no duplicate columns referenced in the SELECT statement, all derived columns must have an associated alias.

Syntax

CREATE FUNCTION Function-Name
(@Parameter-Name Data-Type = Default, @Parameter-Name Data-Type = Default …. n)
RETURNS TABLE
WITH Function-Options
AS
RETURN (SELECT STATEMENT)

Problem – Create a function which returns EMPNAME, DEPNAME and SALARY from EMP table based on SALARY range.
SQL Code –

CREATE FUNCTION MYINLINETABLEVALUEDFUNCTION (@SALARY INT)
RETURNS TABLE
AS
RETURN (SELECT EMPNAME, DEPNAME, SALARY FROM EMP WHERE SALARY>@SALARY)

Congratulations you have successfully created your first Inline Table Valued user defined function which will return EMPNAME, DEPNAME and SALARY from EMP Table based on SALARY range. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path. 

Now our task is to call recently created a scalar function.

Syntax for calling a Table Valued Function:
SELECT *|<Column List> from <function-name> (Parameters values)

Calling the MYINLINETABLEVALUEDFUNCTION function:

SELECT * from MYINLINETABLEVALUEDFUNCTION (2000)


As you can see from the above result set, our table valued function is returning a table which has three columns. Structure of the table variable was generated by the select statement.

Multi-Statement Table Valued Functions

Multistatement Table Valued functions are similar to the Inline Table Valued Function but the body of the body of this function can contain multiple statements and the structure of the table can be defined by the user.

Below is the syntax to create Multistatement Table Valued Type User Defined Functions

Syntax

CREATE FUNCTION Function-Name
(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)
RETURNS @Return_Variable TABLE <Table-Type-Definition>
WITH Function-Options
AS
BEGIN
Function-Body
RETURN 
END

Problem – Create a function which returns SALARY AND ANNUAL SALARY from EMP table for EMPID.
SQL Code –

CREATE FUNCTION MYMSTABLEVALUEDFUNCTION (@EMPID INT )
RETURNS @MYTABLE TABLE(SALARY MONEY, ANNUALSALARY MONEY)
AS
BEGIN
DECLARE @SALARY MONEY,@ANNUALSALARY MONEY
SET @SALARY= (SELECT SALARY FROM EMP WHERE EMPID=@EMPID)
SET @ANNUALSALARY= @SALARY*12
INSERT INTO @MYTABLE VALUES(@SALARY,@ANNUALSALARY)
RETURN
END

Congratulations you have successfully created your first Multistatement Table Valued user defined function which will return SALARY and ANNUAL SALARY from EMP Table for EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path.

Now our task is to call recently created a scalar function.

Syntax for calling a Table Valued Function:
SELECT *|<Column List> from <function-name> (Parameters values)

Calling the MYMSTABLEVALUEDFUNCTION function:

SELECT * FROM MYMSTABLEVALUEDFUNCTION(1)

As you can see from the above result set our recently created function is returning a table variable with two columns.

How to ALTER and DROP user defined functions in SQL Server?

ALTER Function
Once you have created your functions in SQL Server, you might want to make some changes into it.
You can ALTER User Defined Functions using ALTER Statement.  At place of CREATE FUNCTION you have to use ALTER FUNCTION rest everything will remain similar to CREATE FUNCTION syntax.

Drop Function
Once you have created your functions in SQL Server, you might want to remove it from the database. You can drop User Defined Functions using Drop Statement.

Syntax
DROP FUNCTION FUNCTION-NAME

Example

DROP FUNCTION MYSCALARFUNCTION

What are the benefits of User-Defined Functions?

The advantages to SQL Server User-Defined functions are many. First, we can use these functions in so many different places when compared to stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar UDF, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User Defined Function a very powerful tool.

What is difference between Function and Stored Procedure?

I have created a separate article for this topic.  Please click here to understand what are the differences between Stored Procedure and User Defined Functions in SQL Server?

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning!