Custom Sequence Numbering 2 –(Row_Number() and Merge Solution)

Goldie Lesser posted a solution with Row_number function on the forum to discuss the article about custom sequencing http://www.sqlservercentral.com/Forums/Topic1059319-2902-4.aspx.
I have adopted the idea and incorporated with my early solution https://jingyangli.wordpress.com/2013/06/14/custom-sequence-numbering-merge-solution/
to come up this newer version:



--DDL for table ToDoList
If Exists (  SELECT *   FROM sys.tables JOIN sys.schemas
      ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'toDoList' )
   Begin
   Drop table ToDoList
   End
 
create table ToDoList (todoID int primary key, personId int, todoText varchar(50), todoSequence  int)
 
insert into ToDoList values (61,1,'colour_thumbnails',1)
,(66,1,'sent_to_print', 2)
,(63,1,'page_approved', 3)
,(60,1,'line_drawing', 4)
,(64,1,'photo_taken',5)
,(65,1,'photo_approved',6)
,(55,1,'line_drawing_photo',7)
 
 
---Test From Here
 
 
select * from ToDoList
order by todoSequence ;
 
 
--Change parameters here!!!!!!!!!!
declare @personID int = 1,
        @todoID int = 55,
        @NewSeq int = 5



		Merge ToDoList TDL
	USING (
	SELECT todoID
		, ROW_NUMBER() OVER(ORDER BY 
			CASE WHEN toDoID = @ToDoID THEN @NewSeq ELSE toDoSequence END                        
		,CASE WHEN toDoID = @ToDoID AND todoSequence < @NewSeq  Then toDoSequence 
		Else toDoSequence*-1 END)		 
		AS RowNum

	FROM ToDoList WHERE PersonID=@PersonID ) as Src (todoID, RowNum)
	On TDL.todoID=Src.todoID
	WHEN Matched Then
	UPDATE SET todoSequence=Src.RowNum;

 
  
select * from ToDoList
order by todoSequence 
 


Monitoring Transactional Replication in SQL Server

Francis Hanlon:

Monitoring Transactional Replication in SQL Server

Kendal Van Dyke’s Troubleshooting Transactional Replication:

http://sqlmag.com/database-administration/troubleshooting-transactional-replication


Custom Sequence Numbering –(Merge Solution)

I have come across one question at ASP.NET forum and I provided one solution to the question related to resorting sequence.

http://forums.asp.net/t/1913208.aspx/2/10?Reorder+the+column+order+while+update+
.

James Skipwith posted one article at sqlservercentral.com http://www.sqlservercentral.com/articles/T-SQL/72068/.

I would like to post my solution here with table DDL modification to work with James’s question.




--Create Stored Procedure

CREATE PROCEDURE [dbo].[setTodoSequence]

(
@personID int,
@todoID as int=63,
@todoSequenceTarget as smallint
)
AS
SET NOCOUNT ON;

declare @todoSequenceExising int

Select @todoSequenceExising=( Select todoSequence  From ToDoList WHERE todoID=@todoid);


begin tran;
BEGIN TRY
--SQL Server 2008 or above

MERGE ToDoList AS t

Using (

SELECT todoID,
CASE WHEN @todoSequenceExising > @todoSequenceTarget

THEN 
 
     CASE
		 WHEN  (todoSequence  >= @todoSequenceTarget AND todoSequence  < @todoSequenceExising) THEN todoSequence +1
		 WHEN  (todoSequence  > @todoSequenceExising  or todoSequence  < @todoSequenceTarget) THEN todoSequence 
		 WHEN todoSequence  = @todoSequenceExising THEN @todoSequenceTarget
     END
 WHEN @todoSequenceExising < @todoSequenceTarget

THEN 
	 CASE
		 WHEN todoSequence  > @todoSequenceExising AND todoSequence  <= @todoSequenceTarget THEN todoSequence -1
		 WHEN   (todoSequence  < @todoSequenceExising  or todoSequence  > @todoSequenceTarget) THEN todoSequence 
		 WHEN  @todoSequenceExising < @todoSequenceTarget AND todoSequence  = @todoSequenceExising THEN @todoSequenceTarget
    END

	ELSE 
	todoSequence  


END
							   AS todoSequence 
       FROM   ToDoList 
	   WHERE personID = @personID) AS src (todoID,  todoSequence )
ON ( src.todoID = t.todoID )
WHEN Matched THEN
  UPDATE SET   t.todoSequence  = src.todoSequence   ;

  
 COMMIT ;

END TRY
BEGIN CATCH
 SELECT @@error;
 ROLLBACK ;
END CATCH

GO

--DDL for table ToDoList
If Exists (  SELECT *	FROM sys.tables	JOIN sys.schemas
	  ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'toDoList' )
   Begin
   Drop table ToDoList
   End

create table ToDoList (todoID int primary key, personId int, todoText varchar(50), todoSequence  int)

insert into ToDoList values (61,1,'colour_thumbnails',1)
,(66,1,'sent_to_print', 2)
,(63,1,'page_approved', 3)
,(60,1,'line_drawing', 4)
,(64,1,'photo_taken',5)
,(65,1,'photo_approved',6)
,(55,1,'line_drawing_photo',7)


---Test From Here

--Before run stored procedure [dbo].[setTodoSequence]
select * from ToDoList
order by todoSequence ;


--Change parameters here!!!!!!!!!!

EXEC	[dbo].[setTodoSequence]
		@personID = 1,
		@todoID = 55,
		@todoSequenceTarget = 6


 
 --After run stored procedure [dbo].[setTodoSequence]
select * from ToDoList
order by todoSequence 


I have read Goldie Lesser’s comments with a Row_number() solution and you can find a newer solution with Row_number and Merge at this follow-up post:
https://jingyangli.wordpress.com/2013/06/17/custom-sequence-numbering-2-row_number-and-merge-solution/


Sql Server replication requires the actual server name to make a connection to the server (Replication to Azure VM Issue)

When I try to create a new subscription for my Azure VM default database server (2008 R2), I have run into this error:
“Sql Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address or any other alternate name are not supported. specify the actual server name”

The connection I used is the DNS name for the server:
myVM.cloudapp.net,57500
It seems the replication does not like this way even though you can connect to the database server with no problem through SSMS.
There is no server name change involved either.
The solution I found from a stackoverflow thread: .

I have included the solution here provided by eLVik:
Steps:

Read the error message and remember SERVERNAME which is in quotes.
Run Sql Server Configuration Manager on publisher/distributor side;
Expand Sql Native 10.0 Configuration\Aliases node;
Create new alias which name exactly SERVERNAME from 1. It is should be a NetBIOS machine name or NetBIOS\instance_name for named instances.
Specify another options for alias (port, server and protocol).
Repeat 4 and 5 for 32bit native client.

Test. Run Management Studio on the same machine and try connect to the server (specify alias as server name).

Repeat 2 – 7 for all client machines where Management Studio will be used for replication setup.

For my 64-bit machine, I need to create new alias for both 32-bit and 64-bit (Sql Native 10.0) as described above.
In my case to connect my database at Azure VM:

Alias Name: myVM
Port No: 57500
Protocol: TCP/IP
Server: myVM.cloudapp.net

Click Apply button when you are ready.


Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

Buck Woody posted this: Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

Also, I found another link is worth reading about peer-to-peer replication from Timothy Khouri:

Another post I read related to Replication:


Comparison Between SQL Server in Windows Azure Virtual Machine and SQL Database

Gregory Leake posted blog at MSDN about comparison between SQL Server in Windows Azure Virtual Machine and SQL Database.
You can read the blog from here:


Transform Matrix-like Data (UNPIVOT Data With T-SQL)

--http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/6217a37e-82b5-40c3-8401-50236a328f26

CREATE TABLE [dbo].[theMatrix](
	[COL0] [varchar](10) NULL,
	[COL1] [varchar](10) NULL,
	[COL2] [varchar](10) NULL,
	[COL3] [varchar](10) NULL,
	[COL4] [varchar](10) NULL,
	[COL5] [varchar](10) NULL,
	[COL6] [varchar](10) NULL,
	[UID] [int] IDENTITY(1,1) NOT NULL,
	[col7] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[theMatrix] ON 

GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (NULL, N'KPI1', N'KPI1', N'KPI1', N'KPI2', N'KPI2', N'KPI2', 9, N'kk')
GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (NULL, N'15-May', N'16-May', N'17-May', N'15-May', N'16-May', N'17-May', 10, NULL)
GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (N'FL', N'3.29', N'2.43', N'3.26', N'4.34', N'7.45', N'3.53', 11, NULL)
GO
INSERT [dbo].[theMatrix] ([COL0], [COL1], [COL2], [COL3], [COL4], [COL5], [COL6], [UID], [col7]) VALUES (N'NY', N'5.43', N'9.43', N'3.24', N'2.34', N'5.43', N'7.34', 12, N'5.55')
GO
SET IDENTITY_INSERT [dbo].[theMatrix] OFF
GO


select * FROM  [dbo].[theMatrix]  

  ;with mycte as
( select * from (select *,row_number() Over(order by col0) rn   from thematrix  ) src
cross apply (
values( col0,'col0'),(col1,'col1'),(col2,'col2'),(col3,'col3')
,(col4,'col4'),(col5,'col5'),(col6,'col6'), (col7,'col7') )   d(vals,cols)
)
 

SELECT m.Col0,a.vals AS c1,b.vals AS c2,m.vals
FROM   mycte m
       LEFT JOIN (SELECT *
                  FROM   mycte
                  WHERE  rn = 1 AND COL0 IS NULL) a
              ON m.cols = a.cols
       LEFT JOIN (SELECT *
                  FROM   mycte
                  WHERE  rn = 2 AND COL0 IS NULL) b
              ON m.cols = b.cols
WHERE  m.col0 IS NOT NULL AND m.col0 <> m.vals