Feeds:
Posts
Comments

Archive for March, 2013

Statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the statement, then rebuilding the columnstore index after S_MSG is complete is one of the new error messages shipped with SQL Server 2012 and related to columnstore index.

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

Severity : 15

Error Message:  %S_MSG statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the %S_MSG statement, then rebuilding the columnstore index after %S_MSG is complete.

Error Generation:
Let’s generate this error step by step.

Step 1:
Create a table to demonstrate this error.

Use AdventureWorks2012
Go
Create Table tbl_Product_Master
(
 [Product ID] int,
 [Product Name] nvarchar(100),
 [Model] int
)
Go

Step 2:
Insert a record in the table.

Use AdventureWorks2012
Go
insert into tbl_Product_Master values (1,'Car',2011)

Step 3:
Browse the table to check that the record has been updated properly.

Use AdventureWorks2012
Go
Select * from tbl_Product_Master

Step 4:
Create a columnstore index on this table.

CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] ([Product Name], [Model],[Product ID])

Step 5:
Try to execute insert, delete and update statements to perform transactions in the table.

insert into tbl_Product_Master values (2,'Car',2012)

Ooopps…… I am unable to execute it.

Msg 35330, Level 15, State 1, Line 1

INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

delete from tbl_Product_Master where [Product ID]=1

Ooopps…… I am still unable to execute it.

Msg 35330, Level 15, State 1, Line 1
DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete.

Update tbl_Product_Master set [Model]=2013 where [Product ID]=2

Ooopps…… Yet again, I am unable to execute it.

Msg 35330, Level 15, State 1, Line 1
UPDATE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.

The reason behind this error is (as per MSDN) : A table with a columnstore index cannot be updated.

Resolution:
There are some best practices, by which we can post transactions or update in a columnstore index table.

Lets resolve it step by step.

Step 1 :
First of all you need find the name of the columnstore index of the particular table. (Here is the tool that will help you to find the columnstore index). Once you find the name, you need to either disable it or drop it (but in case of drop, you must keep the columnstore index creation script as well).

USE AdventureWorks2012;
GO
--To disable columnstore index
ALTER INDEX [IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] DISABLE ;

--OR
USE AdventureWorks2012;
GO
--To drop columnstore index
DROP INDEX [IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master]

Step 2 :

Now, try to post any transaction in the table.

USE AdventureWorks2012;
 GO
insert into tbl_Product_Master values (2,'Car',2012)
--OUTPUT

(1 row(s) affected)

delete from tbl_Product_Master where [Product ID]=1
--OUTPUT

(1 row(s) affected)

Update tbl_Product_Master set [Model]=2013 where [Product ID]=2
--OUTPUT

(1 row(s) affected)

You can see that the above statements executed successfully.

Step 3 :
After posting the transactions then you need to have a look at Step 1. If you have disabled the columnstore index in Step 1 then you need to enable (rebuild) it in Step3 and if you dropped it, you need to create it.

--To enable / rebuild columnstore index
ALTER INDEX [IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] REBUILD;
GO

--OR
--To create columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_tbl_Product_Master_ColumnStore]
ON [tbl_Product_Master] ([Product Name]
, [Model],[Product ID])

Conclusion :
Whenever you come across this issue, simply you need to disable/drop the columnstore index, post the transaction and enable/create it respectively.
Remember, you cannot post any transaction in columnstore index table without disabling and dropping columnstore index.

Read Full Post »

In my previous article, I wrote a script to disable all index of any particular database or you can disable any particular category (NONCLUSTERED COLUMNSTORE INDEXES ,HEAP, CLUSTERED INDEX, NONCLUSTERED INDEX, XML INDEX, SPATIAL INDEX) index as well. Given below is the script that can rebuild (enable) all indexes of any particular database or can rebuild (enable) any particular category index as well.

CREATE PROCEDURE dbo.[REBUILD_ALL_INDEXES_PROC]
@Type int
AS
BEGIN
DECLARE @Schema_TableName nvarchar(max)
DECLARE @Index_Type int
DECLARE @String varchar(max)
DECLARE @Fillfactor VARCHAR(3)
SET @Fillfactor=80

DECLARE IndexCursor CURSOR FOR

Select scma.[name] + '.'
+ Quotename(tbl.name) as [Schema_TableName]
,i.type
from sys.tables AS tbl
INNER JOIN sys.indexes AS i
ON (i.index_id > 0 AND i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.schemas AS scma
ON tbl.schema_id=scma.schema_id
Where tbl.type='U' AND ((1=(CASE
WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type))
Order By scma.[name] + '.' + tbl.name

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Schema_TableName,@Index_Type

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Index_Type=6
	BEGIN
                --To rebuild columnstore index only
		SET @String = 'ALTER INDEX ALL ON ' + @Schema_TableName +
                ' REBUILD;'
    END
	ELSE
	BEGIN
                --To rebuild other indexes
		SET @String = 'ALTER INDEX ALL ON ' + @Schema_TableName +
                ' REBUILD WITH (FILLFACTOR = ' + @Fillfactor + '
                , SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
    END
	Print @String
	EXEC (@String)
	FETCH NEXT FROM IndexCursor INTO @Schema_TableName,@Index_Type
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
END
GO
--SYNTAX
--EXEC dbo.[REBUILD_ALL_INDEXES_PROC] Index type

--TO REBUILD NONCLUSTERED COLUMNSTORE INDEXES ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 6

-- TO REBUILD HEAP ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 0
GO
-- TO REBUILD CLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 1
GO
-- TO REBUILD NONCLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 2
GO
-- TO REBUILD XML INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 3
GO
-- TO REBUILD SPATIAL INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] 4
GO
--TO REBUILD ALL INDEXES
USE AdventureWorks2012
GO
EXEC dbo.[REBUILD_ALL_INDEXES_PROC] NULL
GO

Read Full Post »

If you would like to post any most frequent DML (Data manipulation language) transaction except Select statement in any Columnstore Index owned table, it will generate an error. (I shall post the error details in my upcoming post). You must therefore disable/ drop columnstore index before posting any DML (Data manipulation language). Given below is the script that disables all indexes with respect to category (NONCLUSTERED COLUMNSTORE, HEAP, CLUSTERED INDEX, NONCLUSTERED INDEX, XML INDEX, SPATIAL INDEX). Also, it can disable ALL indexes of any particular databases.

CREATE PROCEDURE dbo.[Disable_ALL_INDEXES_PROC]
@Type int
AS
BEGIN
DECLARE @Schema_TableName nvarchar(max)
DECLARE @String varchar(max)
DECLARE IndexCursor CURSOR FOR

Select scma.[name] + '.'
+ Quotename(tbl.name) as [Schema_TableName]
from sys.tables AS tbl
INNER JOIN sys.indexes AS i
ON (i.index_id > 0 AND i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.schemas AS scma
ON tbl.schema_id=scma.schema_id
Where tbl.type='U' AND ((1=(CASE
WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type))
Order By scma.[name] + '.' + tbl.name

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Schema_TableName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @String = 'ALTER INDEX ALL ON ' + @Schema_TableName + ' DISABLE;'
Print @String
EXEC (@String)
FETCH NEXT FROM IndexCursor INTO @Schema_TableName
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
END
GO
--SYNTAX
--EXEC dbo.[Disable_ALL_INDEXES_PROC] Index type

--TO DISABLE NONCLUSTERED COLUMNSTORE INDEXES ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 6

-- TO DISABLE HEAP ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 0
GO
-- TO DISABLE CLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 1
GO
-- TO DISABLE NONCLUSTERED INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 2
GO
-- TO DISABLE XML INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 3
GO
-- TO DISABLE SPATIAL INDEX ONLY
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] 4
GO
--TO DISABLE ALL INDEXES
USE AdventureWorks2012
GO
EXEC dbo.[Disable_ALL_INDEXES_PROC] NULL
GO

Read Full Post »

Few days ago, I was working on a client database and faced some issues with ColumnStore index and to fix those issue I had to develop certain tools. Given below is the list of tools I developed :

  1. How to find all columnstore indexes with column name and datatype in a particular database ?
  2. How to disable all columnstore indexes in a particular database ?
  3. How to rebuild all columnstore indexes in a particular database ?
  4. How to drop all columnstore indexes in a particular database ?

Given below is the script of  “How to find all columnstore indexes with column name and datatype in a particular database? ”  This script not only works for columnstore indexes but also gives the list of all indexes of any particular database as well.

CREATE PROCEDURE dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC]
@Type int
AS
BEGIN
;WITH CTE AS (
SELECT TBL.object_id AS [Object ID]
, schema_name(schema_id) AS [Schema Name]
, tbl.name AS [Table Name]
, i.name AS [Index Name]
, i.type AS [Index type]
, i.type_desc AS [Index Desc]
, clmns.name AS [Column Name]
, styps.name AS [Type Name]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i
ON (i.index_id > 0 and i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic
ON (ic.column_id > 0 and (ic.key_ordinal > 0
OR ic.partition_ordinal = 0
OR ic.is_included_column != 0))
AND (ic.index_id=CAST(i.index_id AS int)
AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns
ON clmns.object_id = ic.object_id
AND clmns.column_id = ic.column_id
INNER JOIN sys.systypes AS styps
ON clmns.system_type_id=styps.type
WHERE ((1=(CASE WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type)))
--To convert all datatypes into one row
SELECT [Object ID]
, [Schema Name]
, [Table Name]
, [Index Name]
, [Index Desc]
, STUFF((SELECT ', ' + [Column Name] + '(' + [Type Name] + ')'
FROM CTE AS CTE1
WHERE CTE1.[Object ID]= CTE2. [Object ID]
FOR XML PATH('')),1,1,'') as [Column Name with datatype]
FROM CTE AS CTE2
GROUP BY
 [Object ID]
,[Schema Name]
,[Table Name]
,[Index Name]
,[Index Desc]
ORDER BY [Object ID],[Schema Name],[Table Name]
END
GO
-- LIST OF NONCLUSTERED COLUMNSTORE INDEX
USE AdventureWorks2012
GO
EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 6
GO
-- LIST OF HEAP
USE AdventureWorks2012
GO
EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 0
GO
-- LIST OF CLUSTERED INDEX
USE AdventureWorks2012
GO
EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 1
GO
-- LIST OF NONCLUSTERED INDEX
USE AdventureWorks2012
GO
EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 2
GO
-- LIST OF XML INDEX
USE AdventureWorks2012
GO
EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 3
GO
-- LIST OF SPATIAL INDEX
USE AdventureWorks2012
GO
EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 4
GO
-- LIST OF ALL INDEX
USE AdventureWorks2012
GO
EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] NULL
GO

Result : List of all index
listofallindex1.2

Result : List of all NON-CLUSTERED COLUMNSTORE INDEX
listofallindex1.3

Shall post other related tools (how to disable, rebuild and drop columnstore indexes) in my upcoming posts.

Read Full Post »

EXECUTE statement failed because its WITH RESULT SETS clause specified %d result set(s), but the statement only sent %d result set(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: 11536

Severity : 16

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

Error Generation:

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

Create Procedure UDP_Resultset
As
Select 'A' as [First Letter]
Select 'B' as [Second Letter]
GO
EXEC UDP_Resultset

exec resultset1.1

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

 EXEC UDP_Resultset
WITH RESULT SETS
(([1st Letter] varchar(50)
)
,([2nd Letter] varchar(50)
)
,([3rd Letter] varchar(50)
)
);
--OUTPUT
Msg 11536, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 3 result set(s), but the statement only sent 2 result set(s) at run time.

Ooops…… I am unable to execute it properly. It returned the two result sets and the error message.

errormsg11536.1.1

Resolution:
Why this error ? Because, the above stored procedure returns two result sets but we defined WITH RESULT SETS clause for three result sets. Lets rewrite the script and define two result sets (The same number of result sets returned by stored procedure) to avoid this error.

EXEC UDP_Resultset
WITH RESULT SETS
(([1st Letter] varchar(50)
)
,([2nd Letter] varchar(50)
));
--OUTPUT

exec resultset1.3

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

Read Full Post »

How to calculate the number of days in a year ? Was a question I came across and began with my usual research for the solution. Given below are few methods.

Method 1 :
In this method, I calculated the no of days in a year using the days difference between 1 Jan of the current year and the next year.
Given below are the scripts.

--This method will work on SQL Server 2005 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEDIFF(DAY,DATEADD(YEAR,@year-1900,0)
,DATEADD(YEAR,@year-1900+1,0)) AS [TOTAL NO OF DAYS]
GO
--This technique is same as above but using new datefromparts
--function introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.

DECLARE @Year INT =2012
SELECT DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1)
, DATEFROMPARTS(@year+1,1,1)) AS [TOTAL NO OF DAYS]
GO

Method 2 :
In this method, I checked whether February had 28 days or 29. Obviously, if 29 days it means total no of days in a year is 366 else 365.
Given below are the scripts.

--This method will work on SQL Server 2005 and above.
DECLARE @year AS INT
SET @year=2012
SELECT (Case When Day(DATEADD(YEAR,@year-1900,0)+59)=29
Then 366 else 365 end) AS [TOTAL NO OF DAYS]
GO
--This technique is same as above but using new datefromparts and
--EOMONTH functions introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year=2012
SELECT (Case When  DAY(EOMONTH(DATEFROMPARTS(@Year,2,1)))=29
Then 366 else 365 end) AS [TOTAL NO OF DAYS]

Method 3 :
In this method, I checked the day of last date of the year e.g 31st Dec 2012.

--This method will work on SQL server 2005 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEPART(dy,(DATEADD(YEAR,@year-1899,0)-1))
AS [TOTAL NO OF DAYS]

--This technique is same as above but using new datefromparts
--functions introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEPART(dy,DATEFROMPARTS(@Year,12,31))
AS [TOTAL NO OF DAYS]

Conclusion :
We have multiple ways to calculate the number of days but I recommend to use the Method 3, as it has shortest way to calculate the number of days in a year.

Let me know if you have a better way to achieve it.

Read Full Post »

I have been using sys.dm_exec_requests for quite a long time and I have developed some tools to store certain request information using this dynamic management view. In the SQL Server 2012, sys.dm_exec_requests has a breaking change, which I realized once I deployed my tools in SQL Server 2012 and I was unsuccessful. Let me explain why and how did I fixed it.
Given below is the script taken from one of the tools that is developed using sys.dm_exec_requests.

Let me explain it step by step.
Step 1 :
Lets create a table (On SQL Server 2008 and 2012) to demonstrate it.

use tempdb
GO
CREATE TABLE [dbo].[dm_exec_requests_temp](
	[session_id] [smallint] NOT NULL,
	[status] [nvarchar](30) NOT NULL,
	[blocking_session_id] [smallint] NULL,
	[command] [nvarchar](16) NOT NULL,
	[wait_type] [nvarchar](60) NULL,
	[wait_time] [int] NOT NULL,
	[wait_resource] [nvarchar](256) NOT NULL,
	[transaction_id] [bigint] NOT NULL
) ON [PRIMARY]
GO
--OUTPUT

Command(s) completed successfully.

Step 2 :
Insert / Select records in the above temporary table in SQL Server 2005/2008.

Insert into dm_exec_requests_temp
SELECT session_id ,status ,blocking_session_id, command
    ,wait_type ,wait_time ,wait_resource
    ,transaction_id
FROM sys.dm_exec_requests
GO
Select * from dm_exec_requests_temp
--OUTPUT

sys.dm_exec_requests1.1

Step 3 :
Insert / Select records in the above temporary table in SQL Server 2012.

Insert into dm_exec_requests_temp
SELECT session_id ,status ,blocking_session_id, command
    ,wait_type ,wait_time ,wait_resource
    ,transaction_id
FROM sys.dm_exec_requests
GO
Select * from dm_exec_requests_temp
--OUTPUT

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

Oooops……………
It generated an error.

Step 4:
The above script executed successfully in SQL Server 2005/2008 but failed in SQL Server 2012. The reason behind above error is sys.dm_exec_requests has a column namely command. In SQL Server 2005/2008 the data type of command column was nvarchar(16) but it has been changed to nvarchar(32) in SQL Server 2012. As you can see, I created the column command in temporary table with nvarchar(16) datatype.

Step 5:
To resolve above error, you need to change the data type of command column in temporary table (Step 1) from nvarchar(16) to nvarchar(32) and insert records again.

Alter table dbo.dm_exec_requests_temp Alter column [command] nvarchar(32)

Conclusion :
In SQL Server 2012, sys.dm_exec_requests has changed the data type of column “Command” from nvarchar(16) to nvarchar(32). Remember to implement this data type change effects to avoid such errors.

Reference : MSDN

Read Full Post »

Older Posts »