Feeds:
Posts
Comments

Posts Tagged ‘Msg 10770 Level 16 State 102’

SQL SERVER HEKATON – Fix – Error – 10770 – The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

Posted in Errors, SQL SERVER, tagged , , , , , on September 3, 2013| 2 Comments »

The operation ‘ALTER TABLE’ is not supported with memory optimized tables is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server Hekaton.

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

Severity : 16

Error Message: The %S_MSG ‘%ls’ is not supported with memory optimized tables.

Error Generation:
Let me create a sample memory optimized table to demonstrate this error.

USE Sample_DB
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Given below is the script that I tried to execute but it resulted with the following error :

ALTER TABLE tbl_sample ADD [CreationDateTime] datetime NULL
--OUTPUT

Msg 10770, Level 16, State 102, Line 17
The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

Ooopps…… I am unable to execute it.

Resolution:
Memory optimized tables do not support alter table statement. So, instead of alter, you need to drop and create the memory optimized tables.

Lets rewrite the above statement using drop and create. Given below is the script.

USE Sample_DB
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL,
[CreationDateTime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Read Full Post »

SQL SERVER HEKATON – Fix – Error – 10770 – The %S_MSG ‘%ls’ is not supported with memory optimized tables.

Posted in Errors, SQL SERVER, tagged , , , , on July 16, 2013| Leave a Comment »

The %S_MSG ‘%ls’ is not supported with memory optimized tables. is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server Hekaton.

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

Severity : 16

Error Message: The %S_MSG ‘%ls’ is not supported with memory optimized tables.

Error Generation:
Let me create a sample memory optimized table and insert few records in it to demonstrate this error.

USE Sample_DB
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

INSERT INTO tbl_sample ([ID],[Name]) VALUES (1,'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name]) VALUES (2,'raresql-2')
--OUTPUT

Given below is the script that I tried to execute but it resulted with the following error :

TRUNCATE TABLE tbl_sample
--OUTPUT

Msg 10770, Level 16, State 102, Line 17
The statement ‘TRUNCATE TABLE’ is not supported with memory optimized tables.

Ooopps…… I am unable to execute it.

Resolution:
Memory optimized tables does not support truncate statement, So, instead of truncate you can use delete statement for memory optimized tables.

Lets rewrite the above statement using delete. Given below is the script.

USE Sample_DB
GO
DELETE FROM tbl_sample
--OUTPUT

(2 row(s) affected)

Lets browse the table and view either the records are deleted or not.

USE Sample_DB
GO
SELECT * FROM tbl_sample
--OUTPUT

truncate memory optimized table 1.1

Read Full Post »