Troubleshooting intermitten connectivity issues can be difficult. One approach is to repeatedly test connecting to SQL Server outside of an existing application in order to verify if a server-wide intermitten issue is occurring. This script is used to test remote connectivity to a SQL Server. The script loops in one minute intervals (adjust as needed). […]
2006-09-04 (first published: 2006-08-08)
1,427 reads
This is a replacement for SP_who and modification of sp_who2. It will always list only active, nonsystem processes, and will list the number of seconds a transaction has been running for. Some transactions do not report a last batch time, so I forced a large value to display so you can see any issue with […]
2006-10-02 (first published: 2006-04-28)
2,284 reads
Our business accounting software is Microsoft Business Solutions Solomon IV.which is now being called Dynamics Solomon. You will see specific refernces to this software in this code. I originally developed this to gain faster view of issues we were having with Solomon. This procedure analyzes system tables and looks for blocks. This isfaster than using […]
2005-11-03 (first published: 2005-09-20)
404 reads
Examining the output of DBCC SQLPERF(UMSSTATS) helps in determining a CPU bottleneck. The output of the command is not handy for further investigation (from a table).This procedure performs a transformation of the results, so it is easy to query and store in a database.
2005-11-04 (first published: 2005-09-20)
1,950 reads
This script was created to find an application problem that was causing SQL Server blocked processes. The BlockedProcess table is created to log both the blocker and blocked processes. The sp_BlockedProcess stored procedure is created to handle the situation by: inserting all processes affected by the blocking, notifying the blocking application user, emailing the admins […]
2005-12-01 (first published: 2005-06-30)
4,181 reads
sp_who (and sp_who2) work great for a quick view of what's going on in the system, and can be filtered by user, but there's no way to filter by database. This quick stored procedure provides data on which users are accessing a specific database.
2004-09-13
973 reads
A kind of sp_lock with an ORDER BY possibility. It gives also the name of the locked object (table) if executed in the same database.
2004-10-22 (first published: 2004-07-26)
684 reads
This script is used to disconnect all connections by a particular individual to a server. I use it when I have to disconnect somebody and they have too many processes to do it by hand. Normally, you can just connect the offending process, but if there are multiple processes that are causing a problem , […]
2004-03-08
2,650 reads
2 steps:step 1 => create a table in master database called BlockCheckstep 2 => create the stored procedure in master called BLOCK_CHECK @database_name varchar(20)What SP does:- it gets the spid that is blocked + info about it (like what it does at the moment it is blocked)- it gets the spid that acts as a […]
2003-06-05
722 reads
This script identifies the blocking locks at the top of the blocking chain and kills them. Specifically, it reports what the top blocking spids are doing, kills them, waits three seconds and then reports on current blocking status. If you have a situation where single connections are causing a huge blocking chain and you want […]
2003-05-12
4,348 reads
By Steve Jones
I had been meaning to post this, so as I finished a piece that...
By Steve Jones
fardle-din – n. a long-overdue argument that shakes up a relationship, burning wildly through...
The post Lukáš Karlovský: I got the green light from management and built Fabric...
Hi, I would like to contribute to SQLServerCentral in peer reviews. As there are...
Comments posted to this topic are about the item Getting ANY of the data
Comments posted to this topic are about the item Why Would You Do That?
I have this data in two tables:
-- Beer table BeerIDBeerNamebrewer 5Becks Interbrew 6Fat Tire New Belgium 7Mac n Jacks Mac & Jack's Brewery 8Alaskan AmberAlaskan Brewing 9Kirin Kirin Brewing -- Beercount table BeerName BottleCount Becks 5 Fat Tire 1 Mac n Jacks 2 Alaskan Amber 4 NULL 7 Corona 2 Tsing Tao 4 Kirin 12What is returned from this query?
SELECT * FROM dbo.BeerCount AS bc WHERE bc.BeerName=ANY (SELECT b2.BeerName FROM dbo.Beer AS b2);See possible answers