Feeds:
Posts
Comments

Archive for June, 2013

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.

Advertisements

Read Full Post »

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 »

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 »

SQL Server 2012 came up with lots of new exciting features along with great enhancements as well. One of the enhancements available in SQL SERVER 2012 is its audit features.

Given below are the details of the enhancements:

  • Database level auditing is available in Developer, Enterprise & Evaluation editions but its support is available in all editions of SQL Server. So, now you can take the benefit of database audit in every edition.
  • Another major issue is resolved using this new enhancement. Lets say if you setup a target directory over the network and due to any reason (e.g network failure) the target is not available, you usually lose the data in the earlier version. But in SQL Server 2012, it will automatically recover once the connection is restored.
  • Now, you can restrict the number of files to be created for audit. This will stop creating unnecessary files in the audit folder. But there’s one more option to make it unlimited. (Note: be careful when you use this option, it requires proper maintenance).
  • This feature is basically helpful for the database developers because now it gives the additional information of audit log and you can even trace whether the SQL Query is passed through application directly or via stored procedures.
  • Now you can also write audited events in the audit log using a new stored procedure (sp_audit_write) introduced in SQL Server 2012.
  •  Using user-defined audit event, now applications can write custom audit information in the log as well.
  • To keep a track of user defined events, SQL Server has provided the new catalog views (sys.server_audits, sys.server_file_audits) .
  • It is also very easy to filter the audit events before it is written into the audit files. So you can customize the type of events you would like to capture. Otherwise you need to capture unnecessary events.
  • As we discussed contained database features in my earlier article, now you can monitor contained database users & its activity as well.
  • And the last but not the least, the dialogue boxes are now available in SQL Server management studio. So not only from script but also you can get much more control than earlier version through SSMS as well.

Read Full Post »

Datetime conversion from one format to another format is sometimes very critical. You should be very careful while doing it, since even a single digit can mess up everything. Recently, I was working on a Report and I needed to convert datetime to a short date format in SQL Server. Given below are three different methods we can use to achieve it.

Method 1 :
In this method, we will convert the datetime value to short date format, using CONVERT function. Here goes the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CONVERT(VARCHAR(11), @datetime, 100) AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Method 2 :
In this method, we will convert the datetime to short date format using CAST function. Script given below.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CAST(@datetime AS VARCHAR(11)) AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Method 3 :
In this method, we will convert the datetime to short date format using FORMAT function. Script as follows:

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
FORMAT(@datetime, 'MMM dd yyyy') AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Conclusion :
As you can see, we used three different methods (CONVERT, CAST & FORMAT functions)  to convert datetime to short date format and all of them gave the same result set. Nonetheless, I usually use Method 1 (CONVERT function) because it is compatible with different versions of SQL SERVER and also the performance is better than other two.

What do you prefer? Do write to me.

Read Full Post »

SQL Server 2014 was announced a couple of weeks ago during TechEd North America Conference 2013. So, I started research about its features and I found few features very exciting to me. Given below are the details of the few features available in SQL Server 2014.

  • In-Memory OLTP : It improves the performance of database applications about 50x as compared to the normal database application. It can achieve significant performance. 
  • Enhanced In-Memory ColumnStore : Now, you can update the columnstore index (woww) , also you can apply a new option called COLUMNSTORE_ARCHIVE for higher compression and storage space savings of as much as 90 per cent in columnstore index.
  • Buffer pool extension to SDD (solid-state drives)SQL Server improved the query performance by allowing the use of non-volatile devices (e.g solid-state drives (SSDs)) to reduce usage of SQL Server memory with no risk of data loss.
  • Enhanced Query Processing : SQL queries became much faster due to the enhancement of query processing and one of the reasons why querying process became faster is, it separates querying into two steps, decision-making and execution. 
  • Enhanced Always On : Now it can support up to 8 secondaries.
  • Backup to Azure : SQL Server can take backup and restore directly to the Windows Azure Blob service.

  • Resource Governor Enhancements

    Resource Governor can provide better control and performance for different types of workloads in SQL Server applications.

  • SQL Server Audit Enhancements : Auditing became more easier because it is enabled by default and it will record each and every database activities, including database reads, with minimal impact on performance.

Let me know if I missed out any important features.

For further details, you can download given below sources:

Read Full Post »

« Newer Posts - Older Posts »