Read more about the article Multi Server Query
Microsoft SQL Server

Multi Server Query

In SQL Server Management Studio, it is possible to execute a multi server query against several servers at the same time. The description from TechNet says most of it:

This topic describes how to query multiple servers at the same time in SQL Server 2012, by creating a local server group, or a Central Management Server and one or more server groups, and one or more registered servers within the groups, and then querying the complete group. The results that are returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login that is used by the query on each server. Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.

Here I’ll show how to create a multi server query from a group under registered servers.

(more…)

Continue ReadingMulti Server Query

Line numbers in t-sql error messages

When you run scripts in SSMS and get an error, the error references a line number in the t-sql statement that caused the error. If you double click the error, SSMS will take you to the error. This works even if you have multiple statements in your query window. So it can be a really helpful feature. But if the error is in code that isn’t in the query window (for instance in a referenced stored procedure), nothing happens when you double click. In that case it can be good to know how SSMS calculates the line number in error messages.

(more…)

Continue ReadingLine numbers in t-sql error messages

Rowlock can block more than a row

To avoid the blocking of other users, rowlocks are sometimes used to create locks on the most granular locking level in a table. In theory, when user A makes changes to a row, user B can read, change or delete any other row than that. But in practice, user B can still become blocked by user A altough they are not accessing the same row. It all depends on how data is accessed.

(more…)

Continue ReadingRowlock can block more than a row

Missing comma between columns in select statement

Hopefully, when you make an typing error while writing a SQL statement, you get the red curly lines under the offending part of the query showing you where the error is. If you execute the following statement, you’ll get an error message:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'FROMTABLE'.
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘FROMTABLE’.

However, some typos doesn’t create an syntax error. Instead it alters the meaning of the t-sql statement in an perhaps unwanted way. Check out the following example.

(more…)

Continue ReadingMissing comma between columns in select statement