Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET

Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET
Rate this post

Often, it is necessary to send a query to all databases of all specified servers. Many DML-queries can be created with built-in tools. However, what about DDL-queries?

In this article, we are going to explore an example of implementing the application that sends a query to all databases of all specified servers, using MS SQL Server and C#.NET.

Read More

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

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

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

We continue to analyze what is happening on our MS SQL Server. In this article, we are going to explore how to retrieve information about user performance: who makes what, and how much resources are consumed.

I think the second part will be interesting for both database administrators and developers who need to understand what is wrong with the requests on the production server that used to work fine on the test server.

Read More

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

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