Feeds:
Posts
Comments

Archive for the ‘Misc’ Category

In Sql server 2012,  Microsoft has introduced a very nice and interesting feature and that is execute stored procedure with result set.  With the help of feature, now we can change the column name & data type of the stored procedure result set.

Before SQL server 2012, we were doing the same but it was a lengthy procedure. First we had to create a temporary table and then execute the stored procedure and insert the result set in the temporary table and then select it.

Today, we will discuss the different aspects of this new feature.

Let me explain it with simple examples:

Example 1: (Stored procedure with Single Result set)

Use AdventureWorks2012
GO
Create PROCEDURE SP_ResultSet_Example1
as
Select [DepartmentID]
,[Name]
,[ModifiedDate]
from  [HumanResources].[Department]
GO
EXEC SP_ResultSet_Example1
GO

In the above example, we have three columns in the result set
[DeprtmentID],[Name],[ModifiedDate]

But, I would like to rename the column name and change the data type of Modified Date column in the result set like this
[Deprtment ID],[Department Name], [Modified Date]

Use AdventureWorks2012
GO
EXEC SP_ResultSet_Example1
WITH RESULT SETS
(
( [Department ID] int NOT NULL,
[Department name] Name NOT NULL,
[Modified Date] varchar(11) NOT NULL
));
GO
--Result

Example 2: (Stored procedure with Multiple Result set)

The same way we can do it for multiple result sets also. Given below is the example.

--Without resultset
Use AdventureWorks2012
GO
Create PROCEDURE SP_ResultSet_Example2
as
Select [DepartmentID]
,[Name]
,ModifiedDate
from  [HumanResources].[Department]
Select 'Total' as [Total] ,Count(*) as [Count] from [HumanResources].[Department]
GO
EXEC SP_ResultSet_Example2
GO
--With resultset
Use AdventureWorks2012
GO
EXEC SP_ResultSet_Example2
WITH RESULT SETS
(
( [Department ID] int NOT NULL,
[Department name] Name NOT NULL,
[Modified Date] varchar(11) NOT NULL
),
(
[Total] varchar(5) NOT NULL,
[Department Count] Int NOT NULL
));

Limitations :

  1. You cannot change the data type to any incompatible data type.

For example: Cannot change from varchar to int.

  1. You cannot reduce or increase the number of columns in result set.



Summary:

In this article, we discussed how we can rename and change the data type of the stored procedure output. This feature is very handy especially for SQL Server Integration Services (SSIS) developers when they need to rename/ change the column name and data type respectively in the result  set.

Reference : MSDN

Read Full Post »

Adventureworks sample database launched with SQL Server 2012 and you can download it from the codeplex.

Given below are the links :

After downloading the appropriate data files lets proceed with the installation :

Installation via T-SQL :

Step 1:

You should copy the data file to any appropriate location but it should not be in the root directory due to the security issue.

If you do so, it generates error.

Step 2 :

Once you download the file, you will notice that it has only data file and not log file. So, we need to write the script given below to create the database and  in order to build a new log file, we need to use ATTACH_REBUILD_LOG.

Create Database [AdventureWorks2012]
On (FILENAME=  N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')
For ATTACH_REBUILD_LOG
GO

For testing purpose lets put it in the root directory :

Create Database [AdventureWorks2012]
On (FILENAME=  N'C:\AdventureWorks2012_Data.mdf')
For ATTACH_REBUILD_LOG
GO

It generates an error :
File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf” may be incorrect.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\AdventureWorks2012_log.ldf’.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorks2012’. CREATE DATABASE is aborted.

Installation via SSMS :
Step 1:

Should be same as above.

Step 2 :

Right click on the databases and click on Attach. After that click on Add button and browse the file (AdventureWorks2012_Data).

After that, if you press  OK button, it will reflect error as given below.

So, you need to select the file name (AdventureWorks2012_log.ldf) having file type “log”  and message “Not Found”  and press the Remove button.

After that press OK to finish the installation.

Now, you can see the “AdventureWorks2012” database in SSMS.

Read Full Post »