Thursday, September 28, 2017

Revursive SQL

Given a lookup table Mappings such as follows

ID OldValue NewValue
1 A B
2 C D
3 B E
4 E F
5 G H


CREATE TABLE Mappings (id int , OldValue nvarchar, NewValue nvarchar)

INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (1,'A','B')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (2,'C','D')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (3,'B','E')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (4,'E','F')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (5,'G','H')

Given entry A, how do we follow the table all the way to get to the final value of F?

We can obviously do this using a Recursive Function:


CREATE FUNCTION Map(@OldValue nvarchar)
RETURNS nvarchar
AS
BEGIN
DECLARE @RET nvarchar;
SELECT @RET = NewValue FROM Mappings WHERE OldValue = @OldValue
IF (@RET IS NULL) SET @RET = @OldValue
ELSE SET @RET = dbo.Map(@RET)
RETURN @RET
END
GO

SELECT dbo.Map('C') -- returns D
SELECT dbo.Map('D') -- returns D
SELECT dbo.Map('A') -- returns F
SELECT dbo.Map('B') -- returns F


But can we do it just using normal SQL Queries? The answer is yes!

This shows the working


DECLARE @startingPoint NVarchar(max) = 'A';

 
  with working_tree as (
   select [OldValue] , [NewValue]
   from [Mappings]
   where [OldValue] =  @startingPoint-- this is the starting point you want in your recursion
   union all
   select  c.[OldValue], c.[NewValue]
   from [Mappings] c
     join working_tree p on p.[NewValue] = c.[OldValue]  -- this is the recursion
)

select *
from working_tree;

OldValue NewValue
A               B
B               E
E               F


And this is the final form.


  with working_tree as (
   select [OldValue] , [NewValue]
   from [Mappings]
   where [OldValue] =  @startingPoint-- this is the starting point you want in your recursion
   union all
   select  c.[OldValue], c.[NewValue]
   from [Mappings] c
     join working_tree p on p.[NewValue] = c.[OldValue]  -- this is the recursion
)
select t1.NewValue
from working_tree t1
LEFT JOIN [Mappings] t2 ON t2.OldValue = t1.NewValue
where t2.OldValue IS NULL

This returns F, as expected.

Wednesday, September 27, 2017

SQL Pick up Batch in one go, and mark them as picked atomically while returning them

CREATE PROCEDURE [dbo].[sp_ProcessBatch]
@BatchSize int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @ProcessorState bit
set @ProcessorState = 1


UPDATE NotificationMeta
SET NotificationMeta.StateID = 2, -- UPDATE STATE AS PART OF SELECT AND OUTPUT
NotificationMeta.TimePicked = GETUTCDATE(),
RetryCount = RetryCount + 1
OUTPUT NotificationMeta2.ID, NotificationMeta2.EchoToken  -- RETURN PICKED ROWS
FROM NotificationMeta
JOIN
(SELECT TOP (@BatchSize) nd.* FROM NotificationMeta
 WITH (UPDLOCK,READPAST) -- TO ENSURE NO MULTIPLE UPDATES TO SAME ROW
 JOIN NotificationData nd ON ID = NotificationMeta.NotificationID
 WHERE StateID = 1 AND GETUTCDATE() < ExpiryTime
 ORDER BY nd.EntryTime ASC, Priority ASC) AS NotificationMeta2
ON NotificationMeta.NotificationID = NotificationMeta2.ID

END

Sunday, September 24, 2017

Debug Windows Services

Out of the box, one cannot debug a Windows Service easily through Visual Studio.

Making this possible via code is very easy though.

One just needs to change the code in Program.cs to

#if DEBUG
           Service1 debugInstance = new Service1();
           debugInstance.OnDebug();

           Thread.Sleep(Timeout.Infinite);
#else
           ServiceBase[] ServicesToRun;
           ServicesToRun = new ServiceBase[]
           {
               new Service1()
           };
           ServiceBase.Run(ServicesToRun);
#endif


And add an OnDebug method to the partial class that implements ServiceBase, and has the OnStart() and OnStop() methods.

  public void OnDebug()
        {
        }


This will now be your entry point into debugging your Windows Service through Visual Studio.

Wednesday, June 8, 2016

Moving from SVN to GITLAB


  1. Create C:\GITLAB
  2. CD to C:\GITLAB
  3. Create new empty repository in GITLAB (browser)
  4. Create New Project
  5. Give it UniqueName (get newGitLabProjectURL)
  6. Set Visibility Level to Private
  7. Click Create Project
  8. Run command equivalent to git svn clone http://svnServer:port/svn/DeveloperRepo/Project
  9. CD to project folder 
  10. Run command equivalent to git svn rebase
  11. git config --global user.name "YourUsernameHere"
  12. 11. git config --global user.email "yourEmail@Domain.com"
  13. cd existing_folder
  14. git init
  15. git remote add origin http://newGitLabProjectUrl.git
  16. git add .
  17. git commit
  18. git push -u origin master

Wednesday, March 18, 2015

Location of database on hard disk and size by table

Location of database on hard disk



SELECT type_desc, physical_name, size
   FROM sys.database_files


Size by Table

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

Monday, September 1, 2014

Changing logging level for Visual Studio

By default Visual Studio is not very heavy on logging. However if a developer wants to increase logging levels, there is a very easy way.


  • Click on Tools
  • Click on Options
  • Click on Project and Solutions
  • Click on Build and Run
  • Change settings like shown below.


This way when you build a solution, you will be able to see exactly what is happening.





Friday, May 16, 2014

Restore Database from SQL scripts.

I recently had to move a database from one server to another, using SQL Server Management Studio.
I first made use of Generate Scripts to create these SQL files.


  • Right click the database you want to generate scripts for (not the table) and select tasks - generate scripts
  • Next, select the requested table/tables, views, stored procedures, etc (from select specific database objects)
  • Click advanced - select the types of data to script
  • Click Next and finish
http://msdn.microsoft.com/en-us/library/hh245282.aspx


I chose to create the schema on its own, and then the data on its own, one file per table.
Even though the largest SQL files was no larger than 100Mb, SQL Server management Studio was still complaining and running out of memory.

So instead I opted to use the SQLCMD tool, which is leaner, faster, and does not complain of memory issues

This is how I used it.

sqlcmd -S ServerName -i Script.sql -o DbLogFile.log

http://technet.microsoft.com/en-us/library/ms162773.aspx