Saving change is not permitted MS SQL 2008/ SQL 2008 R2

By default saving changes on a table is is blocked. When we want to change or alter any Table Then it shows following message

Saving change is not permitted. the changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that cant be re-created or enabled the option Prevent Saving changes that require the table to be re-created.

We can fix it following way

Continue reading

Cursor in MS SQL

Using Cursor in MS SQL Server 2005 or 2008
Consider following code for creating cursor

declare db_cursor CURSOR for
SELECT ItemSLNO FROM P_Return_ItemSLNOTraking where Com_PReturnId_ItemId = @Com_ReceiveId_ItemId
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ItemSLNO
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SLNO = @SLNO+ ','+ @ItemSLNO
FETCH NEXT FROM db_cursor INTO @ItemSLNO
END
CLOSE db_cursor
DEALLOCATE db_cursor

Split in MS SQL SERVER

Here I shall describe about split in MS SQL.

There is no built in function for splitting your text, that’s why I am describing two customize function for performing split operation in MS SQL.


Split function 1: This function returns table that’s why we can say it table valued function.

--select * from fnSplit('1,22,333,444,,5555,666', ',')
CREATE FUNCTION dbo.fnSplit(
  @sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

 Continue reading