Saving change is not permitted

Saving change is not permitted

Some time if your setting changed by some one then you can not update table in MSSQL 2008.
you will see the below error:

“A table that can not be re-created or enabled the options prevent saving changes.
Sql 2008 Management Tools: Can’t save changes that require Recreation of Database”

That time you need to update your setting.

The fix is:

Go to Tools => Options => Designers =>Tables and Designers and uncheck the Prevent Saving Changes that require table re-creation option:

As it is, reverting back to the ‘old’ behavior now doesn’t let you know that a table recreate is required either, so the behavior now is the same as was with the old tools. Here Microsoft added some useful functionality and then UI fails to expose it intelligently.

CREATE TYPE TABLE (Transact-SQL 2008)

CREATE TYPE TABLE (Transact-SQL 2008)

Create TYPE  Table is new opotion in SQL2008. It is very easy to handle and good to use in TSQ.
This is good as compare to TEMP Table.

Here is the Example:
Create Type MyTable as TABLE
(
ID INT IDENTITY (1,1),
NAME varchar(20),
ADRES VARCHAR(200)
)

DECLARE @T AS MyTable;

INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )
INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )
INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )
INSERT INTO @T( NAME, ADRES) VALUES( 'RAGHU', 'KTM' )


SELECT * FROM @T ;
DROP TYPE MyTable;

Many a times, we need to utilize a single Stored Procedure to update multiple database tables with one-to-many relationships. In such occasions, we end up concatenating large strings, and inside the procedure, end up parsing the string to get records. With SQL Server 2008, it is very simple, and we no more need to write tons of lines of code to implement such requirements.

CREATE PROC dbo.sp_GetOrders
(@T AS dbo.tbl_Order READONLY)
AS
SELECT O.OrderID, O.OrderDate, O.CustomerID
FROM dbo.tbl_Order AS O JOIN @T AS T ON
O.OrderID = T. OrderID
ORDER BY T. RecordID;
GO


DECLARE @MyOrderIDs AS dbo.tbl_Order;
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(1, 10248)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(2, 10250)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(3, 10249);


EXEC dbo.sp_GetOrders @T = @MyOrderIDs;

This example shows  how to use the Table UDT as a parameter in SQL Stored Procedure.
Hope you enjoyed this feature. Happy Programming.