Feeds:
Posts
Comments

Archive for June, 2013

SQL SERVER 2014 – Community Technology Preview (CTP) 1, now available

Posted in Raresql News, SQL SERVER, tagged , , , on June 30, 2013| Leave a Comment »

In my earlier article, I had discussed about new features of SQL Server 2014. Those features are very exciting and I was waiting for the CTP. Now, CTP 1 is available for download.

Given below are the few instructions that you should follow to install CTP 1.

  • The machine should not have any earlier versions of SQL Server.
  • There is no upgrade / side by side installation available in this CTP.
  • This CTP is only available for X64 architecture.

System requirement for SQL Server 2014 is available here.

Let me know if you face any issues with the installation.

Read Full Post »

SQL SERVER – Omitted leading zeros in excel using text import wizard

Posted in Solutions, SQL SERVER, tagged , , , , on June 29, 2013| Leave a Comment »

In my earlier article, I had discussed about the omitted leading zeros in excel and I received a very nice solution from Mr. Harsh, one of my blog readers. (This solution works in Excel 2010 and above).

Let me explain his solution step by step.

Step A :
Let me create a sample to demonstrate the error.

USE tempdb
GO
CREATE TABLE [dbo].[tbl_Employee](
[Employee ID] [varchar](5) NULL,
[Employee Name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00001', N'A. Scott')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00002', N'Alan')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00003', N'Alejandro')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00004', N'Alex')

INSERT [dbo].[tbl_Employee] ([Employee ID], [Employee Name])
VALUES (N'00005', N'Alice')
GO
SELECT [Employee ID], [Employee Name] FROM [tbl_Employee]
GO
--OUTPUT

Remove_zeros_in_excel.1.1

Step B :
Copy the data from SQL Server, open an excel sheet, click on Paste Button and select Use Text Import Wizard (as shown in the picture below).

omittingleadingzeros1.1

Step C :
It then takes you to 3 steps of Text Import Wizard. Under Text Import Wizard – Step 1 of 3, you need to determine the data type. Select Delimited and press Next (as shown in the picture below).

omittingleadingzeros1.2

Step D :
Text Import Wizard – Step 2 of 3 lets you to set the delimiters your data contains. Select Tab and press Next (as shown in the picture below).
omittingleadingzeros1.3

Step E :
Text Import Wizard – Step 3 of 3 lets you set the column data format. Select Text and press Finish (as shown in the picture below).

omittingleadingzeros1.6

Step F :
Once finished, the data would be pasted in the required format, meaning along with the zeros as shown in the picture below.

omittingleadingzeros1.5

Let me know if you know any better solution.

Read Full Post »

SQL SERVER – How to use ‘if… else’ in ‘where’ clause

Posted in Errors, SQL SERVER, tagged , , , , , on June 28, 2013| 4 Comments »

IF… ELSE clause is very handy and whenever you need to perform any conditional operation, you can achieve your results using it. But there are some limitations in IF… ELSE,  and one of the limitations is that you cannot use it in WHERE clause.

Let me demonstrate the limitations.

USE AdventureWorks2012
GO
DECLARE @City AS VARCHAR(50)
SELECT
BusinessEntityID
, FirstName
, LastName
, City
FROM [HumanResources].[vEmployee]
WHERE
IF @City IS NULL
City='Renton'
ELSE
City=@City
--OUTPUT

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword ‘IF’.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘City’.

Nevertheless, there are solutions which are given below:

SOLUTION 1 :
You can use CASE statement instead of IF..ELSE clause to do conditional where clause.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
DECLARE @City AS VARCHAR(50)
SELECT
BusinessEntityID
, FirstName
, LastName
, City
FROM [HumanResources].[vEmployee]
WHERE City =
(CASE WHEN @City IS NULL THEN 'Renton' ELSE @City END)
ORDER BY BusinessEntityID
--OUTPUT

ifelse1.1

SOLUTION 2 :
In this solution, you can use IIF clause instead of IF..ELSE  to do conditional where clause.
Given below is the script.

--This script is compatible with SQL Server 2012 and above
USE AdventureWorks2012
GO
DECLARE @City AS VARCHAR(50)
SELECT
BusinessEntityID
, FirstName
, LastName
, City
FROM [HumanResources].[vEmployee]
WHERE City = IIF (@City IS NULL, 'Renton', @City)
ORDER BY BusinessEntityID
--OUTPUT

ifelse1.1

Read Full Post »

SQL SERVER – How to sort month names in month order instead of alphabetical order – Part II

Posted in Solutions, SQL SERVER, tagged , , , on June 27, 2013| 2 Comments »

In my earlier article, I had discussed about how to sort month names in month order instead of alphabetical order . In that article, we had given the date and we had to convert into month and sort it. But, in today’s case we have month & year in the table and we need to sort it in month & year order instead of alphabetical order. To achieve this, we usually use lots of case statements and sort it.

Let me create an example to demonstrate it.

USE tempdb
GO
CREATE TABLE tbl_MonthName
(
[ID] INT,
[MONTH_NAME] VARCHAR(50),
[YEAR] VARCHAR(4)
)
GO

INSERT INTO tbl_MonthName
SELECT 1, N'January',2014 UNION ALL
SELECT 2, N'July',2013 UNION ALL
SELECT 3, N'February',2014 UNION ALL
SELECT 4, N'September',2013 UNION ALL
SELECT 5, N'March',2014 UNION ALL
SELECT 6, N'April',2014 UNION ALL
SELECT 7, N'November',2013 UNION ALL
SELECT 8, N'May',2014 UNION ALL
SELECT 9, N'June',2014 UNION ALL
SELECT 10, N'August',2013 UNION ALL
SELECT 11, N'October',2013 UNION ALL
SELECT 12, N'December',2013
GO

SELECT * FROM tbl_MonthName
GO
--OUTPUT

Monthnameandyear1.1

As you can see, the above result set is not sorted properly.

Given below is a simple solution to sort data in month and year order.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT * FROM tbl_MonthName
ORDER BY [YEAR],MONTH('1' + [MONTH_NAME] +'00')
--OUTPUT

Monthnameandyear1.2

Read Full Post »

SQL SERVER – How can I select few columns from the result set of a stored procedure

Posted in Solutions, SQL SERVER, tagged , , , on June 25, 2013| 2 Comments »

In most of the applications, we create some multipurpose stored procedure that we use for multiple forms, reports, exports, etc. The challenge we usually face with this kind of stored procedure is that whatever columns are available in the result set, stored procedure will return the same number of columns as an output and not the selected/required information.

Let me take a sample stored procedure from AdventureWorks2012 database and view its result set.

USE AdventureWorks2012
GO
EXEC [dbo].[uspGetManagerEmployees] 16
GO
--OUTPUT

selectcolumn1.1

As you can see that a number of columns are available in the above stored procedure result set but I need to select [BusinessEntityID], [FirstName], [LastName] ONLY.

Given below are the two methods to achieve it.

Method 1 :
In this method, we need to follow three steps :

Step 1:
Create a temporary table according to the number of columns in the result set of the stored procedure.

USE AdventureWorks2012
GO
CREATE TABLE #TEMP
(
[RecursionLevel] INT
, [OrganizationNode] VARCHAR(50)
, [ManagerFirstName] NVARCHAR(50)
, [ManagerLastName]  NVARCHAR(50)
, [BusinessEntityID] INT
, [FirstName] NVARCHAR(50)
, [LastName] NVARCHAR(50)
)
GO

Step 2:
Insert the complete result set of the stored procedure in the table.

USE AdventureWorks2012
GO
INSERT INTO #TEMP EXEC [dbo].[uspGetManagerEmployees] 16
GO

Step 3:
Select the required columns from the temporary table.

USE AdventureWorks2012
GO
SELECT
[BusinessEntityID]
, [FirstName]
, [LastName]
FROM #TEMP
GO
--OUTPUT

selectcolumn1.2

Method 2 :
In this method, we need to use OPENROWSET and can select the column of the stored procedure result set directly.

SELECT
[BusinessEntityID]
, [FirstName]
, [LastName]
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=test;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16') ;
GO
--OUTPUT

selectcolumn1.3

Read Full Post »

SQL SERVER – Reuse current document window

Posted in SQL SERVER, Tips, tagged , , , , on June 24, 2013| Leave a Comment »

Problem :
Most of the time, I find developers surrounded by hundreds of scripts and frantically looking for the required file amongst the open documents.

Given blew is the screen shots :

reusedoc1.1

And also, it is near impossible that you are using all the documents at once. So, how to avoid this issue ?

Solution :
You just need to enable a very nice feature in SQL Server “Reuse current document window, if saved”. You can find this option in
Tools >> Options >> Environment >> Documents, as shown in the given below screen shot.

reusedoc1.2

reusedoc1.3

How it works :
You just need to enable this option and once it is enabled, just save the script which you have finished editing, and click on new query. So it will close the saved script (document) and open a new query in the same place.

Note : All the above screen shots are taken from SQL Server 2012.

Read Full Post »

SQL SERVER – How to sort month names in month order instead of alphabetical order

Posted in Solutions, SQL SERVER, tagged , , , on June 23, 2013| 3 Comments »

How to sort month names in month order instead of alphabetical order is a common problem and has been discussed on multiple forums. But what encourages me to write this post is, I found new methods to do it.

Let me create a sample to explain multiple methods to achieve it.

USE tempdb
GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-04-04')
INSERT INTO tbl_Sample VALUES (2,'2013-07-07')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-01-01')
INSERT INTO tbl_Sample VALUES (5,'2013-02-02')
INSERT INTO tbl_Sample VALUES (6,'2013-03-03')
INSERT INTO tbl_Sample VALUES (7,'2013-05-05')
INSERT INTO tbl_Sample VALUES (8,'2013-06-06')
INSERT INTO tbl_Sample VALUES (9,'2013-08-08')
INSERT INTO tbl_Sample VALUES (10,'2013-09-09')
INSERT INTO tbl_Sample VALUES (11,'2013-11-11')
INSERT INTO tbl_Sample VALUES (12,'2013-12-12')
GO

Method 1 :
This is the simplest method, wherein you just need to sort on the basis of column [date] instead of DATENAME(month,Date).
Given below is the script.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT
DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
ORDER BY [Date]
--OUTPUT

monthnumberorder1.1

Method 2 :
In this method, you need to get the month number using Month function and sort it on month number.
Given below is the script.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT
DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
ORDER BY Month(Date)
--OUTPUT

monthnumberorder1.1

Method 3 :
In this method, you need to get the month number using DatePart function and sort it on month number.
Given below is the script.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT
DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
ORDER BY DATEPART(m,Date)
--OUTPUT

monthnumberorder1.1

Method 4 :
In this method, you need to get the month number using Format function and sort it on month number.
Given below is the script.

--This script is compatible with SQL Server 2012 and above
USE tempdb
GO
SELECT
DATENAME(month,Date) AS [Month Name]
, [Date]
FROM tbl_Sample
Order By FORMAT([Date],'MM')
--OUTPUT

monthnumberorder1.1

Read Full Post »

SQL SERVER – Fix – Error – 11537 – EXECUTE statement failed because its WITH RESULT SETS clause specified column(s) for result set number, but the statement sent column(s) at run time.

Posted in Errors, SQL SERVER, tagged , , , , , on June 22, 2013| 1 Comment »

EXECUTE statement failed because its WITH RESULT SETS clause specified %d column(s) for result set number %d, but the statement sent %d column(s) at run time is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS.

Let’s discuss this in detail:
Message Number: 11537

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified %d column(s) for result set number %d, but the statement sent %d column(s) at run time.

Error Generation:
Let me create a sample stored procedure to generate this error.

CREATE PROCEDURE usp_Resultset
AS
SELECT 'A' AS [First Letter],
'B' as [Second Letter]
GO
EXEC usp_Resultset

errormessage11537.1.2

You can see that the above stored procedure is executed successfully and it has ONE result sets with TWO columns.
Lets try to execute it using WITH RESULT SETS clause.

USE tempdb
GO
EXEC usp_Resultset
WITH RESULT SETS
(([1st Letter] VARCHAR(1)
));
--OUTPUT
Msg 11537, Level 16, State 1, Procedure usp_Resultset, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 2 column(s) at run time.

Ooops…… I am unable to execute it properly. It returned the error message.

errormessage11537.1.3

Resolution:
Why this error ? Because, the above stored procedure returns ONE result sets with TWO columns but we defined WITH RESULT SETS clause for ONE column ONLY. Lets rewrite the script and define TWO columns in the result sets (the same number of columns in the result sets returned by stored procedure) to avoid this error.

USE tempdb
GO
EXEC usp_Resultset
WITH RESULT SETS
(([1st Letter] VARCHAR(1)
, [2nd Letter] VARCHAR(1)
));
--OUTPUT

errormessage11537.1.4

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, make sure you use exactly the same number of columns in result set returned by stored procedure to avoid such errors.

Read Full Post »

SQL SERVER 2012 – How to generate a varchar Sequence Number – Using Sequence Object

Posted in Solutions, SQL SERVER, tagged , , , , on June 21, 2013| 1 Comment »

Sequence object is one of the new additions in SQL Server 2012. It has lot of functionality plus identity column functionality as well. However, there are certain limitations and one of the limitations is, you can only create sequence object in number datatype. Given below is the screen image.

varcharsequence1.1

So, how can we create a sequence object with varchar datatype ?
Lets do it step by step.

Step 1 :
In this step, we need to create a sample table and a sequence to demonstrate it.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE TABLE
USE tempdb
GO
CREATE TABLE dbo.tbl_sample
( [ID] VARCHAR(8) ,
[Name] varchar(50)
CONSTRAINT PK_Employee_EmployeeID
PRIMARY KEY CLUSTERED ([ID] ASC) )
GO

--CREATE SEQUENCE
USE tempdb
GO
CREATE SEQUENCE dbo.Sample_Seq AS
INT START WITH 1
INCREMENT BY 1 ;
GO

Step 2 :
In this step, we need to create a default value for the [ID] column of the above table, and the default value of the [ID] column should be SEQUENCE and add a custom script to make it varchar.

Given below is the script.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE DEFAULT VALUE OF SEQUENCE
USE tempdb
GO
ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_Sample_Seq
DEFAULT FORMAT((NEXT VALUE FOR dbo.Sample_Seq),'CUS0000#') FOR [ID];
GO

Step 3 :
Lets insert few records into the table.

-- This script is compatibile with SQL Server 2012 and above.
-- Insert records into the table
USE tempdb
GO
INSERT INTO dbo.tbl_sample ([Name])
VALUES ('Imran'),('Bob'),('Sandra')
GO

Step 4 :
Once the data has been inserted, you can browse the table and view the [ID] column data that it is either a number or varchar only.

--Browse Table
USE tempdb
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

varcharsequence1.2

Conclusion :
As you can see, the [ID] column has varchar data instead of number. Remember, whenever you need to do generate sequence in varchar format, you must format the sequence object.

Read Full Post »

SQL Server 2012 – Audit Enhancements – Part II

Posted in Audit, SQL SERVER, tagged , , , on June 20, 2013| Leave a Comment »

In my earlier article, I discussed a lot of different enhancements of Audit features in SQL Server 2012. Today we are going to implement these features step by step.

Step 1 :
In this step, we need to create an audit at server level and set the options as per the requirement.
Given below are the two different methods to create Audit.

(1) Using SQL Server Management Studio :
To create Audit you need to go to Object Explorer, expand the security node (folder) after that right click the Audits folder and select New Audit as shown in the picture below.

Audit1.1

Audit1.2

(2) Using Transact-SQL :
Given below is the script to create an audit on server.

USE [master]
GO

CREATE SERVER AUDIT [Audit_Sample]
TO FILE
( FILEPATH = N'C:\Audit\'
)

ALTER SERVER AUDIT [Audit_Sample] WITH (STATE = ON)
GO

Step 2 :
In this step, we need to create an audit specification on the specific database. Also, we can setup the criteria for audit as well.
Given below are the two methods to achieve it.

(1) Using SQL Server Management Studio :
To create Audit specification, you need to go to Object Explorer, expand the security node (folder) of that particular database and then right click the Database Audits Specifications folder and select New Database Audit Specification… as shown in the picture below.

Audit1.3

Audit1.4

(2) Using Transact-SQL :
Given below is the script to create an Audit Specification on Adventure database.

USE [AdventureWorks2012]
GO

CREATE DATABASE AUDIT SPECIFICATION [FilterFor_HumanResource_Data]
FOR SERVER AUDIT [Audit_Sample]
ADD (SELECT ON SCHEMA::[HumanResources] BY [public])
WITH (STATE = ON)
GO

Step 3 :
Once Audit and Audit Specifications are done, lets browse any table within the [HumanResources] Schema to show its reflection in audit report.

USE [AdventureWorks2012]
GO
SELECT * FROM HumanResources.Department
GO

Step 4 :

Lets view the audit report using fn_get_audit_file

USE [AdventureWorks2012]
GO
SELECT * FROM fn_get_audit_file
('C:\Audit\Audit_Sample_*.sqlaudit',default,default);
GO

Audit1.5

Conclusion :
I captured only few areas of Audit, but there is much more to do in it. Also, you can easily configure it in such a way that it can reduce your troubleshoot time.

Let me know if you used this feature in real world example along with its benefits.

Read Full Post »

Older Posts »