I work with a WebApi project in Net.Core and MS SQL Database. MS SQL server is executed in Docker container. Suddenly I found out, that ID field value with autoincrement has increased from 2 to 1001.

Create a console app with .Net Framework 4.7.2 with Dapper and datasource in MSSQL Server or PostgreSQL.

If you create this ancient app, you should only add Dapper and NPGSQL packages from Nuget. The support of MS SQL Server is already built in. The scenario for .Net Framework is from real life, several applications still require this framework.

The task is to build a tree in a table with parentID from bottom to top. It's useful for generating hierarchy strings, lists or any other types. You can also create a storage procedure of this and it will make it easy to use in queries.

After installation of MS SQL Server 2012 on Windows Server 2012, I had gotten these errors in Event Viewer on execution server:
The server was unable to initialize encryption because of a problem with a security library. The security library may be missing. Verify that security.dll exists on the system.
TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support. The group or resource is not in the correct state to perform the requested operation.
TDSSNIClient initialization failed with error 0x139f, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The group or resource is not in the correct state to perform the requested operation.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I must notice that I had an old distributive of MS SQL Server 2012.

There's a column Photo with type "Image" in MS SQL table (database – MS SQL Server 2008 R2). The task is to get all the records where Photo is empty. Values of empty images is "0x00000000".

(read more...)

To receive quantity of rows of the returned records of SQL query, you should use function @@ROWCOUNT. Function @@ROWCOUNT returns the quantity of rows, which were used in a query.

(read more...)

How to get as SQL query result the list of databases in SQL server.

When you modify columns of a table in MS SQL management studio, you got the message "Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created … " (Figure 1). The reason of this is re-creating of table. It's really important for productive environment but not very for test ones.

(read more...)

Rename table in MS SQL Server:

In this post I will write about SQL conditions. It seems to me that construction "IF ... ELSE" you can find in any language. Transact SQL is not exclusion.

Let’s see IF ... ELSE in action on the sample below. (read more...)