Shimon Krokhmal's blog

For those who didn't read the previews post about Sequence Implementation In SQL Server 2000 , please read it before continuing.

basically the idea is quite simple but i tough to add this post to complete the sequence framework.
adding some handling functions to your DAL object :

        /// <summary>
        /// this procedure retrieves from the database the value of the desired sequence.
        /// this is a generic function that gets the stored procedure to get the data from and the sequence name itself
        /// </summary>
        /// <param name="seqName">the name of the desired sequence</param>
        /// <param name="storedProcedure">the stored prosedure to call</param>
        /// <param name="connection">the connection to the database</param>
        /// <returns>an integer that represent the value of the sequence</returns>
        internal virtual int GetSequenceID(string seqName,string storedProcedure, SqlConnection connection)
        {
            int ret = int.MinValue;
            
            // Create a command
            SqlCommand command = new SqlCommand(storedProcedure, connection);    
            command.CommandType = CommandType.StoredProcedure;

            // pass the parameter of the sequence name for the series sequence
            SqlParameter p1 = new SqlParameter("@seqName",SqlDbType.VarChar);
            p1.Value = seqName;
            p1.Direction = ParameterDirection.Input;
            command.Parameters.Add(p1);        

            // open connection or using it if already opened
            bool OpenedConnection = false;
            try
            {
                if(connection.State!=System.Data.ConnectionState.Open)
                {
                    OpenedConnection = true;
                    connection.Open();
                }            
                
                // Execute the command
                ret = Convert.ToInt32(command.ExecuteScalar());
            }
            finally
            {
                if ((OpenedConnection) && (connection.State != System.Data.ConnectionState.Closed))
                    connection.Close();
            }
            
            return ret;
        }

        /// <summary>
        /// this is an internal function that retrieves the sequence value
        /// generally, it wraps the other internal method that does the actual work
        /// </summary>
        /// <param name="seqName">the name of the sequence that we want</param>
        /// <param name="storedProcedure">the stored procedure that we address for</param>
        /// <returns>the value of the sequence</returns>
        internal int GetSequenceID(string seqName,string storedProcedure)
        {
            int ret = int.MinValue;

            // Use the connection
            using(SqlConnection connection = DalServices.Connection())
            {
                try
                {
                    // Open the connection
                    connection.Open();
                    
                    // Get the object throught the internal method
                    ret = GetSequenceID(seqName,storedProcedure,connection);
                }
                
                finally
                {
                    // Close the connection
                    if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
                }
            }
            
            return ret;
        }

and add some wrapping functions to extern the methods:

        /// <summary>
        /// The Next value of the series sequence
        /// </summary>
        /// <returns>the value of the sequence</returns>
        public int GetNextSeries()
        {
            // Calling the internall function to get the value
            return GetSequenceID("sq_series","sp_SequenceNextVal");
        }

        /// <summary>
        /// The current value of the series sequence
        /// </summary>
        /// <returns>the value of the sequence</returns>
        public int GetCurrentSeries()
        {
            // Calling the internall function to get the value
            return GetSequenceID("sq_series","sp_SequenceCurrVal");
        }

once again i'm a happy kamper.

for those folks who worked with oracle, know that sequence is an internal database object which has it's own methods and properties.
hey, why shouldn't i use this wonderful auto number feature that sql server has ?
well, auto number will give you a good solution to most of your needs that a sequence provided in oracle.
but, here is some conditions where auto number just won't suffice :

• Managing more than one "auto number" in one table - auto number can be defined only at one field.
• Managing the same auto number across multiple tables - the auto number can be asigned only to a specified table.

the solution is simple:
we will create some framework that will allow us to manage sequences and work with them.

first we will create one table to contain the sequences.

CREATE TABLE [dbo].[sysSequences] (
    [SequenceName] [char] (30) COLLATE Hebrew_CI_AS NOT NULL ,
    [Incrasement] [int] NOT NULL ,
    [StartValue] [int] NOT NULL ,
    [CurrentValue] [int] NOT NULL
) ON [PRIMARY]
GO

next thing we need to do is create some stored procedures that will handle these sequences.

the create sequence procedure :

CREATE procedure dbo.sp_Create_Sequence
(
    @seqName     varchar(30),
    @Increasement     int,
    @startValue    int
)
as
begin

declare @r int
begin transaction

    -- Check if the sequence exists
    set @r =(    select count(*)
    from sysSequences where SequenceName = @SeqName)
    
    if @r <> 0 --if error is raised
    begin
        goto LogError
    end

insert into sysSequences (SequenceName,Incrasement,StartValue,CurrentValue)
values (@seqName,@Increasement,@startValue,@startValue)    

commit transaction
goto ProcEnd

LogError:
rollback transaction
RAISERROR ('Can not create sequence with name %s, sequence exists already.',16,1,@seqName)

ProcEnd:
end

once we created the "object" (not really an object, more as a row in the sequence table), lets get his current value

create procedure dbo.sp_SequenceCurrVal
(
    @seqName     varchar(30)

)
as
begin

declare @rows int

begin transaction
    -- Check if the sequence exists
    set @rows =(    select count(*)
    from sysSequences where SequenceName = @SeqName)
    
    -- if no such row
    if @rows = 0
    begin
        goto LogError
    end

    -- Get the new value
    select CurrentValue from sysSequences
    where SequenceName = @SeqName
    
commit transaction
goto ProcEnd

LogError:
rollback transaction
RAISERROR ('sequence named %s, does not exists.',16,1,@seqName)

ProcEnd:
end

and the "get next value" method (very similar to the get current procedure.)

create procedure dbo.sp_SequenceNextVal
(
    @seqName     varchar(30)

)
as
begin

declare @rows int

begin transaction
    -- Check if the sequence exists
    set @rows =(    select count(*)
    from sysSequences where SequenceName = @SeqName)
    
    -- if no such row
    if @rows = 0
    begin
        goto LogError
    end


    -- Update the sequence increasement
    update sysSequences
    SET CurrentValue = CurrentValue + Incrasement
    where SequenceName = @SeqName

    -- Get the new value
    select CurrentValue from sysSequences
    where SequenceName = @SeqName
    
commit transaction
goto ProcEnd

LogError:
rollback transaction
RAISERROR ('sequence named %s, does not exists.',16,1,@seqName)

ProcEnd:
end

thats it, done.
now we have some sql server sequences

 

or, What have the latest windows 2003 service pack 2 has done to my sql server ?

Today i scheduled for my self some working time on the NightDuck project (Coming Soon),
but suddenly something went wrong, the sql server, which installed on a physically different machine than the development rig, gave me the following message:

"Sql server does not exist or access denied ConnectionOpen(Connect())"

what could have go wrong ?

the interesting part is when i tried to connect to the server remotely (remote desktop) and connecting to the Sql server from the localhost, it had no problems what so ever.
Since the sql server machine is not a dependency of a production application or something like that, i went wild on the experiments to try and fix the problem

• Lets restart the sql server service - no good.
• Router configurations and port forwarding - nothing has changed since the last time i worked on the project, thats not the problem either
• SQL SERVER user - defining a new user did not solved the problem either

so i started googling on the subject, and found a few things, like this one for example at the Microsoft site :
Potential causes of the "SQL Server does not exist or access denied" error message

this article has a list of things that may cause this problem (hard to guess by it's name).
checking the relevant ones, i came across this article about ports needed by the sql server.
i made a little test to check if the ports are ok, i tried to telnet the machine from the dev PC on the 1433 port, but with no luck,
in an effort to isolate the problem i tried that from the localhost of the server, telneting to the localhost on port 1433, which gave me an error "could not connect...".
this is what i was looking for !
At this point the problem is completely isolated, why ?

• the router configuration is not an issue (request does not go out from the machine)
• the development rig is not part of the equation anymore
• firewall is not the problem

based on these symptoms, I've searched once more, and stumbled upon a forum thread about a very similar issue, when installing service pack 2 on windows XP causes the same effect.
Apparently, if you have an out of date version of the file DBNETLIB.dll ,the Service Pack 2 disables TCP/IP access to MS SQL Server.
This is a defense against the Slammer worm. (some ugly work around by Microsoft a have to say...)

the solution is installing the SQL Server 200 security tools , which sets things back to normal.

since the windows 2003 service pack 2 is quite new, i guess this thread will be useful

update :
Just wondering how many others encountered this specific problem,
if you find this info useful, please leave a comment.

remember this neat syntax that exists in oracle database for hierarchy selects ?
actually , its pretty simple :

SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

ever tried to do the same in sql server ?
well, these kind of syntax just does not exist,
we need to work very hard to create such a feature in our database.

so, what do we have:

• a table that contains entities
• each entity connected to some father entity
• each connection describes a "father - son" relation between the two entities

lets not forget the things we need to relay on , when implementing :

• what will happen when we will delete the father of some sub tree ?
• what should we do when we update/add a record ?
• how will we select the data ?
• algorithm efficiency is crucial, if we will need to wait 5 minutes for the data, it's not worth it

the first (but apparently the worst) idea that came to my mind is recursion
lets look at this table :

 

EmployeeID	Name	BossID
1	shimon	NULL
2	yossi	1
3	Gaby	1
4	koby	3
5	jack	3

 

we have we that the employee shimon is the "big boss" (because there is no other boss above him),
under shimon we have the employees Gaby and yossi,
and under Gaby, we have another 2 employees : Koby and jack

the recursive solution is to write some stored procedure that will receive the employeeID and return as a data-Table the results
i will not add the code for this solution and surely will not recommend it because it was many problems :

• for each record we received as a descendant ,
  we need to run with the function and get her descendants,
  and so on, until there are no descendants for the node
• we are limited to 32 levels of hierarchy
• the runtime will depend on the row count that is in the table (we will need to run on each of the rows one at the time)
• the run on the node will look like this :
  
  
  

by the way, the most common way that I've seen to select hierarchical structure,
is simply by setting a join between the levels in the select query.
for example :

SELECT TopBoss.Name TopBoss, Boss.Name Boss, Employees.Name Employee
FROM Employees
INNER JOIN Employees AS Boss ON Employees.BossID=Boss.EmployeeID
INNER JOIN Employees TopBoss ON Boss.BossID=TopBoss.EmployeeID

this apply to the selection of three levels

For each level, you'd need to join the table to itself...not an attractive option if you have 5 or more levels ,
you don't know how many levels you will have to select, there is no way can control it!
It would be great if it could join itself as many times as needed. This is called a recursive join, and though some database products support it (Oracle has the CONNECT BY syntax) SQL Server is not one of them.

the other way is based on a thread that i read here about hierarchies,

lets create a table :

CREATE TABLE Tree (
Node int NOT NULL IDENTITY(100, 1),
ParentNode int,
EmployeeID int NOT NULL,
Depth tinyint,
Lineage varchar(255) )

the extra fields that has been added are the "lineage" and the "depth"

• Depth - for saving the current depth of the record in the hierarchy
• Lineage - for saving all the ancestors of the record as a concatenated string

after filling the needed data for relations, the table looks like this :

Node	ParentNode	EmployeeID	Depth	Lineage
100	NULL	1001	NULL	NULL
101	100	1002	NULL	NULL
102	101	1003	NULL	NULL
103	102	1004	NULL	NULL
104	102	1005	NULL	NULL
105	102	1006	NULL	NULL

The next part is to find the root node of the tree, also known as the top-level, etc.
That's the node that has no parent (Null), so we will start there and set the Lineage column as the root:

UPDATE Tree SET Lineage='/', Depth=0 WHERE ParentNode Is Null

Once we did that,
we can then update the rows who are the descendant of the root node:

WHILE EXISTS (SELECT * FROM Tree WHERE Depth Is Null) 
   UPDATE T SET T.depth = P.Depth + 1, 
   T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
   FROM Tree AS T 
   INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
   WHERE P.Depth>=0 
   AND P.Lineage Is Not Null 
   AND T.Depth Is Null

 

this loop will run once for each level of the hierarchy (not for each node as the recursion method.)
so, with data representation of 10,000 records with 8 levels of hierarchy,
this code will run only 8 times to populate the needed data of the "lineage" field and the "depth" field, and this "heavy" procedure will happen only once at the setup.
the table should look like this after the given operation :

Node	ParentNode	EmployeeID	Depth	Lineage
100	NULL	1001	0	/
101	100	1002	1	/100/
102	101	1003	2	/100/101/
103	102	1004	3	/100/101/102/
104	102	1005	3	/100/101/102/
105	102	1006	3	/100/101/102/

 

You'll notice that for each node, the entire lineage back to the root is stored. This means that finding someone's boss, or their boss' boss, doesn't require any self-joins or recursion to create an indented list. In fact, it can be accomplished with a single SELECT.

SELECT Space(T.Depth*2) + E.Name AS Name
FROM Employees E
INNER JOIN Tree T ON E.EmployeeID=T.EmployeeID
ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

 

maintaining the table is really not a big deal if we will use triggers.
think about the new inserted record as the row that has not been filled in the setup process.
so the insert trigger should be :

UPDATE T SET T.depth = P.Depth + 1,
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/'
FROM Tree AS T
INNER JOIN Tree AS P ON (T.ParentNode=P.Node)
WHERE P.Depth>=0
AND P.Lineage Is Not Null
AND T.Depth Is Null

 

the update trigger should do pretty much the same : building the 2 extra field all over again.

suggestions and request will be repplied :)