Month: June 2014

Is that database used?

Before an upgrade I wanted to remove any unused databases. To help find them I created a generic sql-job that every 10 mins would log connections.

I say ‘generic’ as the job needed to work on SQL2000, SQL2005, and SQL2012. So I created it using SQL-2000 enterprise-manager and scripted it out as ‘createJob_DbaAudit.sql’.

The script in job-step-1 (‘Build Table – if needed’) was …

select	@@ServerName AS Server, 
	db.[name] [Database],
	getdate() [TimeStamp], 
	Hostname, 
	Program_name, 
	Net_library, 
	Loginame
into master.dbo.tbl_connections
from master.dbo.sysdatabases db
join master.dbo.sysprocesses pr on db.dbid = pr.dbid
where db.dbid > 4

… this step was set to continue on to the next-step regardless of whether it succeded or failed.

Step-2 (‘Record Connections’) was similar …

insert into master.dbo.tbl_connections
select	@@ServerName AS Server, 
	db.[name] [Database],
	getdate() [TimeStamp], 
	Hostname, 
	Program_name, 
	Net_library, 
	Loginame
from master.dbo.sysdatabases db
join master.dbo.sysprocesses pr on db.dbid = pr.dbid
where db.dbid > 4

Final notes: To be robust and generic, I did not set-up Notifications. Also, I added a few lines to the top of the ‘CreateJob_DbaAudit.sql’ script to delete the tbl_connections table from master if it already existed.

Uninstall SQL Server without using Add/Remove

The Add/Remove option on my work PC (Windows XP) was restricted. So I removed SQL Server 2005 by finding and running ‘ARPWrapper.exe’. This brings up a maintenance-type box which after the first few screens has a screen with 2 big square buttons, the lower one is labled ‘Remove SQL’.

Infact you have to run this executable to completion twice (with a reboot inbetween) for the instance and the shared components.

Once complete I deleted the Microsoft SQL Server folder from c:\program files\

UPDATE: on SQL 2008r2 the executable is ‘SetupARP.exe’

Dropping all Temp tables

I wanted to drop all my temp tables at the top of a large SELECT statement and already had a query to drop a Named temp-table …

IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL DROP TABLE #Temp2

… so just needed to make that dynamic. Looking at the temp tables …

select * from tempdb.sys.objects where name like '#%'

… I noticed all mine had a bunch of underscores and a GUID appended to the name. And the GUID always contained atleast 5 zero’s. So I was able to filter out just mine with

select name from tempdb.sys.objects where name like '#%00000%'

… and paste that into a while loop to drop them …

--first drop all temp tables
IF OBJECT_ID('temptables') IS NOT NULL DROP TABLE temptables
select name into temptables from tempdb.sys.objects where name like '#%00000%'

declare @table varchar(max), @cmd varchar(max)
while (select count(*) from temptables) > 0
begin
	set @table = (select top 1 name from temptables)
	set @cmd = 'IF OBJECT_ID(''tempdb..' + @table + ''') IS NOT NULL DROP TABLE ' + @table
	exec(@cmd)
	delete from temptables where name = @table
end

DROP TABLE temptables

By initially creating a list and then working through it deleting the temp-table and its entry in the list, I ensured there would be no endless looping should it try (and fail) to drop a temp-table I don’t have permissions on.

The power of SSIS

When you start SSIS development it seems rather easy to paste some tested t-sql into a box, QED! job done!

However, the real power of SSIS lies in the 2 areas where it out-performs raw t-sql.

1) Server linking: Because it uses a native format, grabbing data from a distant source and applying it somewhere else is quick and robust.

2) There are blinding-fast (in memory) replacements for the t-sql operations … GROUP BY (called the ‘Aggregate’ transformation), CASE (called ‘Conditional Split’), CAST (‘Data Conversion’), UPDATE (Derived Column), SOUNDEX (‘Fuzzy Lookup’), SORT (er called ‘sort’).