The other blog of Miguel Pereira

…or the newly Microsoft SQL DBA converted to Oracle by proxy. I think I may be posting quite a few of these in the upcoming days while I slowly move deeper into Oracle DBA territory. The learning curve will be steep, but hope it will be a rewarding trip. Enough nonsense though. Here we go.
Comparing [...]

Share on Facebook

This little query can be very handy sometimes.
1234567891011121314DECLARE @INSTANCENAME AS VARCHAR(20)
DECLARE @KeyString as VARCHAR(500)
SELECT @INSTANCENAME = RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX(‘\’,@@SERVERNAME))

SET @keystring = ‘SOFTWARE\Microsoft\Microsoft SQL Server\’ + @INSTANCENAME + ‘\MSSQLServer\SuperSocketNetLib\Tcp’

DECLARE @port varchar(5)

EXEC xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=@keystring,
@value_name=’TcpPort’,
@value=@port OUTPUT

PRINT @port + ‘/’ + @instancename
Share on Facebook

Share on Facebook

This little script’s helped me numerous times when refreshing environments manually from backups and had to ensure there were no left-over connections left which would’ve made the restores fail. Without further a due:
1234567891011121314151617181920212223242526DECLARE @dbspid AS int
DECLARE @killcommand AS varchar(50)
DECLARE ConnectionKiller CURSOR FOR
SELECT sysproc.spid FROM sysprocesses AS sysproc
INNER JOIN sysdatabases AS sysdb ON
sysdb.dbid = sysproc.dbid
WHERE sysdb.NAME IN [...]

Share on Facebook

I’ve seen quite a bit of confusion as to the purpose and application of the four basic JOIN operations available for transact SQL. Even I dread coming out of my main comfort zone (my beloved INNER JOIN) sometimes. Nonetheless, JOINS are one of the most essential building blocks to some of the more complex data [...]

Share on Facebook