SQL Server – Vincy Boy!

Microsoft.ApplicationBlocks. SqlHelper.ExecuteReader issue.

Yes, I had issues with the said title. This is specifically when a reader is declared under a SqlTransaction. In the end when the transaction also includes insert/delete and updates, the changes will not reflect.

Please look at this wrong code.

        public ReturnedCheckEntity SelectByID(int id)
        {
        
            if(WithTransaction)
            {
                using(SqlDataReader reader = SqlHelper.ExecuteReader(
                    Transaction,
                    _commandSelectByID,
                    id))
                {
                    return ProcessRow(reader);
                }
 
            }
            else
            {
                using(SqlDataReader reader = SqlHelper.ExecuteReader(
                    ConnectionString,
                    _commandSelectByID,
                    id))
                {
                    return ProcessRow(reader);
                }
 
            }
        }

This now is the right code.

public ReturnedCheckEntity SelectByID(int id)
        {
            if (WithTransaction)
            {
 
                using (SqlCommand cmd = new SqlCommand(_commandSelectByID, Connection, Transaction))
                {
 
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@ID", id));
 
                    using
                    (
                        SqlDataReader r =
                            cmd.ExecuteReader
                                ()
                    )
                    {
 
                        return ProcessRow(r);
 
                    }
                }
 
            }
            else
            {
                using
                    (
                        SqlDataReader r =
                            SqlHelper.ExecuteReader
                                (
                                    ConnectionString
                                    , System.Data.CommandType.StoredProcedure
                                    , _commandSelectByID
                                    , new SqlParameter("@ID", id)
                                )
                    )
                {
 
                    return ProcessRow(r);
 
                }
            }
        }

You must not use SqlHelper’s Execute reader instead use the native SqlCommand object for creating a SqlDataReader.

Happy Coding!

DBCC CHECKIDENT RESEED Scenarios

I wrote this post to help SQL lovers get informed on how to use.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15

This post will primarily inform the exact behavior when we RESEED.

When you reseed upon

a. Right after CREATE TABLE and no records have been inserted

b. Right after TRUNCATE TABLE.

c. Right after DELETE TABLE but no records have been inserted(Yes, why would someone do this?)

THE NEW IDENTITY VALUE WILL FOLLOW THE RESEED VALUE.

But, when you reseed after records have been inserted. The followup new identity value will be an increment of 1 of the reseed value.

Code below show the different scenarios:

DECLARE @ReseedValue INT = 100;

IF OBJECT_ID(N’dbo.SimpleTable’, N’U’) IS NOT NULL
DROP TABLE dbo.SimpleTable;

CREATE TABLE dbo.SimpleTable
(
ID INT NOT NULL IDENTITY(1, 1),
[TestCol] CHAR NULL
);

DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);

SELECT * FROM dbo.SimpleTable;
/*
Result
ID TestCol
100 A
101 B
102 C
*/

TRUNCATE TABLE dbo.SimpleTable;

DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);

SELECT * FROM dbo.SimpleTable;

/*
Result
ID TestCol
100 A
101 B
102 C
*/

DELETE dbo.SimpleTable;

DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);

SELECT * FROM dbo.SimpleTable;

/*
Result
ID TestCol
101 A
102 B
103 C
*/
SET @ReseedValue = 200;
DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);

INSERT INTO dbo.SimpleTable VALUES
(‘D’);

SELECT * FROM dbo.SimpleTable;

/*
Result
ID TestCol
101 A
102 B
103 C
201 D
*/

Happy coding!

Promoted to MCSA! Passed 70-762 Microsoft Certification Exam

When studying for this particular exam, a lot of built in SQL Objects like views and functions are mentioned throughout the reference book.

The supposed to be easiest preparation turned out to be most stressful since I had to memorize a lot of those objects.

For passing this exam. I bought an e-book by Microsoft. This is the official exam reference book for this exam by Louis Davidson and Stacia Varga. I have read the book 3 times and I practice on-hand when in the mood.

Also I needed to read from these following urls:

https://www.sqlshack.com/locking-sql-server/

My preparation lasted for 7 months. On average I dedicated about 2.5 hours a day everyday reading and practicing! It was a stressful 7 months, I had a lot of momentary pain in the head as if my skull is trying to compress. Saturdays/Sundays and holidays were dedicated to preparation. I still had the time to enjoy beer and get wasted.

This was also one out of the two exams you need to pass to attain MCSA. I finally am an MCSA. Thanks for Intelligent Touch Corporation, my employer who paid for this exam. Thanks for the continued patronage.

Posted below is the new badges I acquired. Also below I posted my score.

My stored procedure parameter is DateTime2 but I keep on getting SqlDateTime overflow. Microsoft.ApplicationBlocks.Data.SqlHelper

If you keep on getting this even if your stored procedure parameter is DateTime2. The error is:

Error – SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

The solution is to pass an SqlParameter object with the DateTime2 SqlDBType specified. Before I used to pass parameters of System.Object type and let the CLR and SQL Server figure out the proper type between DateTime and DateTime2.

Please look at the code for before and after. Also focus on the DocumentDate, StartDate and EndDate parameters.

BEFORE – used parameters as System.Object

object retVal = SqlHelper.ExecuteScalar(
                    Transaction,
                    _commandInsert,
                    data.ItemID,
                    data.SequenceNumber,
                    data.ReferenceNumber,
                    data.DocumentDate,
                    data.StartDate,
                    data.EndDate,
                    data.Spoilage,
                    data.Remarks,
                    data.NotedByUserID,
                    data.QualityCheckUserID,
                    data.ReturnedByUserID,
                    data.CreatedBy,
                    data.UpdatedBy,
                    data.EstimatedQuantity,
                    data.ActualQuantity,
                    data.RawMaterial,
                    data.DirectLabor,
                    data.MachineOverhead,
                    data.ApproveMaterialsByUserID,
                    (byte)data.Status,
                    data.WIPItemID,
                    data.WIPReportID);

AFTER and the CORRECT way – passed parameters as SqlParameter, also specifying explicitly SqlDBType DateTime2

SqlParameter paramDocumentDate = new SqlParameter("@DocumentDate", SqlDbType.DateTime2); paramDocumentDate.Direction = ParameterDirection.Input;
            SqlParameter paramStartDate = new SqlParameter("@StartDate", SqlDbType.DateTime2); paramStartDate.Direction = ParameterDirection.Input;
            SqlParameter paramEndDate = new SqlParameter("@EndDate", SqlDbType.DateTime2); paramEndDate.Direction = ParameterDirection.Input;
 
            paramDocumentDate.Value = data.DocumentDate;
            paramStartDate.Value = data.StartDate;
            paramEndDate.Value = data.EndDate;
 
            if(WithTransaction)
            {
                SqlHelper.ExecuteNonQuery
                (
                    Transaction,
                    CommandType.StoredProcedure,
                    _commandInsert,
                    paramID,
                    new SqlParameter("@ItemID", data.ItemID),
                    new SqlParameter("@SequenceNumber", data.SequenceNumber),
                    new SqlParameter("@ReferenceNumber", data.ReferenceNumber),
                    paramDocumentDate,
                    paramStartDate,
                    paramEndDate,
                    new SqlParameter("@Spoilage", data.Spoilage),
                    new SqlParameter("@Remarks", data.Remarks),
                    new SqlParameter("@NotedByUserID", data.NotedByUserID),
                    new SqlParameter("@QualityCheckUserID", data.QualityCheckUserID),
                    new SqlParameter("@ReturnedByUserID", data.ReturnedByUserID),
                    new SqlParameter("@CreatedBy", data.CreatedBy),
                    new SqlParameter("@UpdatedBy", data.UpdatedBy),
                    new SqlParameter("@EstimatedWeight", data.EstimatedWeight),
                    new SqlParameter("@ActualWeight", data.ActualWeight),
                    new SqlParameter("@ActualQuantity", data.ActualQuantity),
                    new SqlParameter("@RawMaterial", data.RawMaterial),
                    new SqlParameter("@DirectLabor", data.DirectLabor),
                    new SqlParameter("@ManufacturingOverhead", data.ManufacturingOverhead),
                    new SqlParameter("@ApproveMaterialsByUserID", data.ApproveMaterialsByUserID),
                    new SqlParameter("@Status", (byte)data.Status),
                    new SqlParameter("@WIPItemID", data.WIPItemID),
                    new SqlParameter("@WIPReportID", data.WIPReportID),
                    paramRV
                );
            }

HAPPY CODING!!!

Play safe. Neutral literal date format for multi language property of SQL Server Login

Lesson learned. My usual date time literal:

YYYY-MM-DD HH:MM:SS.nnn

happens to be a bad practice since it is interpreted differently by the language property of the current login.

This sample code shows how it interprets a date with poor format by different languages.

DECLARE @datePoorFormat AS VARCHAR(23);
SET @datePoorFormat = ‘2017-02-12 23:12:12.123’;

DECLARE @DateTable AS TABLE (temp1 DATETIME)

SET LANGUAGE us_english;
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);

SET LANGUAGE british
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);

SELECT temp1 FROM @DateTable

The result of the query is

2017-02-12 23:12:12.123 — Feb 12
2017-12-02 23:12:12.123 — Dec 02

Please use the neutral formats. For DATETIME type these are:

‘YYYYMMDDHH:MM:SS.nnn’

‘YYYY-MM-DDTHH:MM:SS.nnn’

‘YYYYMMDD’

I like the second one because hyphens emphasizes date parts better in your code.

BTW language config is set by default at your current login. The picture below shows where you can set it via GUI.

SQL Server – Applying business logic using CHECK type constraint

Our client is in the business of fabricating soap. One feature of our accounting suite is keeping track of all their cooking sessions (they have a production line dedicated for that and it is called kettle).

A database table called ProductLineForm handles immediate data for each session.

A session can be NOT STARTED, STARTED and ENDED. Two columns StartDate and EndDate of type DATETIME must always be of valid values all the time.

NOT STARTED should have both start and end dates to have the SQL minimum date value.

STARTED should have its end date still using the minimum value while the start date will get the started date.

ENDED must be that the start date is always equal or earlier than the end date.

I need to implement this rule to the table. Here is the check constraint applied.

ALTER TABLE ProductLineForm
ADD CONSTRAINT ProductLineForm__Date__Rule
CHECK
(
(DATEDIFF(d,StartDate,’1753-01-01′) = 0 AND DATEDIFF(d,EndDate,’1753-01-01′)=0)
OR (DATEDIFF(d,’1753-01-01′, StartDate) > 0 AND DATEDIFF(d,EndDate,’1753-01-01′)=0)
OR DATEDIFF(d, StartDate, EndDate) >= 0
)