32-bit Excel and 64-bit SQL Server

32-bit Excel and 64-bit SQL Server
Rate this post

What could be simpler than to get data from an Excel table in SQL Server?

There are many ways to accomplish this task. You can utilize Integration Services (former DTS) or import and export wizard, which is the same under the hood. Another way is to create a simple ADO.NET app. You can use the Linked Server mechanism, which allows you to see any ODBC / OLE DB-reachable object in the form of a table (a collection of tables) or the result of an ad hoc query.

Read More

Understanding Transactions in SQL

Understanding Transactions in SQL
Rate this post

A transaction in SQL is a unit of execution that groups one or more tasks together. A transaction is considered successful if all the tasks within it are executed without error.

However, if any of the tasks within a transaction fails to execute, the whole transaction fails. A transaction has only two results: successful or failed. Read More

Don’t like database triggers? You just don’t know how to work with them!

Don’t like database triggers? You just don’t know how to work with them!
Rate this post

When designing large relational databases, we often make a decision to diverge from a normal form, i.e. denormalization.

The reasons for this can be different, such as an attempt to speed up access to the specified data, constraints of the used platform/framework/development tools, and lack of skills of a database developer/designer.

Read More

Analysis of MS SQL Server for those who see it for the first time – Part 1

Analysis of MS SQL Server for those who see it for the first time – Part 1
Rate this post

Ive recently encountered a problem – SVN went down on ubuntu server. I develop for Windows and I do not have much experience with Linux. I googled the errorwithout success. The error turned out to be the most typical one (the server unexpectedly closed the connection) and does not indicate anything. Therefore, it was necessary to go deeper and analyze logs/settings/rights/etc.

Finally, I figured out the mistake and found everything I needed, but I spent a lot of time. After solving this problem, I thought about how to reduce the uselessly spent hours and decided to write an article that will help people quickly get the understanding of the unfamiliar software.

Read More

The Lost Update Problem in Concurrent Transactions

The Lost Update Problem in Concurrent Transactions
Rate this post

The lost update problem occurs when 2 concurrent transactions try to read and update the same data. Let’s understand this with the help of an example.

Suppose we have a table named “Product” that stores id, name, and ItemsinStock for a product.

It is used as part of an online system that displays the number of items in stock for a particular product and so needs to be updated each time a sale of that product is made.

Read More

Counting references to a record in a table via Foreign Keys

Counting references to a record in a table via Foreign Keys
Rate this post

I have recently needed to solve the task for my own purpose: to calculate the number of external records linked by a foreign key for each record in a table (File). The task was solved for the specific structure of the File table, but if necessary, the solution can be reworked to a universal one.

I’ll clarify that the solution was developed for an unloaded database, without millions of records and an every minute update, so there was not much concern about the performance.

Read More

Configuring Database Mail Notifications in MS SQL Server

Configuring Database Mail Notifications in MS SQL Server
Rate this post

Introduction

Often, there is a need to somehow inform administrators about the problems with a server. Notifications are generally divided into 2 types:

1) real-time notifications, i.e. those that must come immediately when a problem occurs

2) delayed notifications, i.e. those that come after a fairly long time (more than 1 hour) after a problem occurs. Read More

Understanding Dirty Read Problem with SQL Server

Understanding Dirty Read Problem with SQL Server
4.3 (86.67%) 3 votes

One of the most common problems that occur while running concurrent transactions is the Dirty Read problem. A dirty read occurs when one transaction is permitted to read data that is being modified by another transaction which is running concurrently but which has not yet committed itself.

If the transaction that modifies the data commits itself, the dirty read problem doesn’t occur. However if the transaction that modifies the data is rolled back after the other transaction has read the data, the latter transaction has dirty data that doesn’t actually exist. Read More