SSMS Tools Pack

I spend a considerable amount of time using SQL Server Management Studio,it's the GUI interface to Microsoft SQL Server (Server and the free Express edition). There's an add-on called SSMS Tools Pack which adds some new much needed features. This tool used to be free (or donation-ware), but since version 2.5.0.0 it's a paid-for licensed tool. I still use version 2.1.0.0 which doesn't require licensing. It has a number of features, though I'll only cover the ones which I use and now find indispensable:

Once installed it adds a new menu item (SSMS Tools) on the top menu and a new context menu or right-clicking of an object (SSMS Tools).

Window Colouring

This colours query windows based on the server name, so you could colour live/development/test servers differently.  I normally switch between servers several times and this gives me the confidence that I'm running the query on the correct server.

It's not perfect as other windows aren't coloured, i.e. the table designer, but hopefully this will come soon.

Generate Insert Statements
This, as it name suggests, generates insert statements for the data in a table.  This allows the query to be copied and run on a different server, or maybe stored in a source control package to keep a history of data.

Query Execution History
This saves all queries run into a local folder, by default c:\SSMSTools\.  This can then be queried by date and the resulting view shows all the SQL commands run on that date - also the time is displayed.  There have been a number of times where I've written an ad-hoc query and not saved it as I thought I'd never need it again, only to find that I needed to perform a similar task at a later date.

Snippets
Similar to snippets in Visual Studio - these are snippets of code/SQL which can be entered using short-cut text entries.  It comes with a number of built-in snippets, for example entering UPD and then pressing Enter adds the following snippet which can then be amended.
UPDATE <>
SET    <>
FROM   <>

I've added a number of my own snippets, a few examples are below.
U
UPDATE 
SET 1=0
WHERE

SSW
SELECT  TOP 1000 *
FROM    
WHERE 1=1

SSIS Import - Primary Key Violation

It took me 4 hours to re-import some tables from an Oracle database using SSIS. It kept coming up with Primary Key Violations at random points during the import.

I finally found this http://support.microsoft.com/?id=972498, and after rebooting the server everything worked fine. It seems that the server was running low on memory - even though it's got 16GB of memory. I suspect that it might have been because it hadn't been rebooted in 6-8 weeks.

SQL Server 2008 Install Error

I was trying to install the Client components of SQL Server 2008 when it failed on "performance counter registry hive consistency" during the install.

After much searching on the web the simple solution is to run this on the command-line and then install:

lodctr /R:PerfStringBackup.INI

SQL Server Online Backup Server

A while ago I was looking into how to have a backup server for our live SQL Server. The backup server had to be able to be accessible while the live server was running, this is so that we could use it for reporting purposes.

These are the different options (and the reasons why they weren't suitable for us) :
1. Mirroring - the database mirror can’t be accessed – it's only available if the live database goes down.
2. Log Shipping - the Backup database disconnects users while new transactions being are being applied.
The only viable option was Continuous Replication where data is replicated in realtime every fixed period of time (normally a few minutes).
For our needs Clustering was too expensive. For the record it's for reliability only and offers no performance gains.

SQL Scripts

Some useful SQL scripts I've picked up over the years:

Find Server NameSELECT @@SERVERNAME


Change Server Name
sp_dropserver ''
go
sp_addserver '', 'local'
go
then Restart SQL Server Service


Cursor LoopsDECLARE curName CURSOR FOR SELECT *
OPEN curName
FETCH NEXT FROM curName INTO @Param
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH
NEXT FROM curName INTO @Param
END
CLOSE curName
DEALLOCATE
curName


Counter Loop DECLARE @CNT INT
SET @CNT = 1
WHILE(@CNT <>

-- LOOP CODE

SET @CNT = @CNT + 1
END


Resetting identity values
Use
TRUNCATE TABLE
ordbcc checkident (YourTable,reseed,1)


Strip time from datetime
CONVERT(DATETIME, CONVERT(CHAR(10), DateTimeField, 101))


Server Version Information
SELECT
SERVERPROPERTY('productversion') Version,
SERVERPROPERTY('productlevel') ServicePack,
SERVERPROPERTY('edition') Edition,
SERVERPROPERTY('MachineName') MachineName,
SERVERPROPERTY('ServerName') ServerName,
SERVERPROPERTY('LicenseType') LicenseType,
SERVERPROPERTY('NumLicenses') NumLicenses

Version numbers are:
9.00.1399.06 - SQL Server 2005
8.00.2039 - SQL 2000 SP4
8.00.760 - SQL 2000 SP3/3a
8.00.534 - SQL 2000 SP2
8.00.760 - SQL 2000 SP1
8.00.194 - SQL 2000
7.00.1063 - SQL 7 SP4
7.00.961 - SQL 7 SP3/3a
7.00.842 - SQL 7 SP2
7.00.699 - SQL 7 SP1
7.00.623 - SQL 7


Table Row Counts
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC

Empty all tables
To use
sp_SYS_EmptyAllTables 'RESET', 1


create Procedure dbo.sp_SYS_EmptyAllTables (@InputString AS CHAR(10), @ResetIdentity Bit)
As
Begin
IF @InputString = 'RESET'
BEGIN
Declare @SQL VarChar(500)
Declare @TableName VarChar(255)
Declare @ConstraintName VarChar(500)
Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type='FOREIGN KEY'
Open curAllForeignKeys
Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
While @@FETCH_STATUS=0
Begin
Set @SQL = 'ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName
Execute(@SQL)
Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
End
Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE='BASE TABLE'
Open curAllTables
Fetch Next From curAllTables INTO @TableName
While @@FETCH_STATUS=0
Begin
Set @SQL = 'DELETE FROM ' + @TableName
If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),'TableHasIdentity')=1
Set @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName + ''',RESEED,0)'
Execute(@SQL)
Fetch Next From curAllTables INTO @TableName
End
Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName
While @@FETCH_STATUS=0
Begin
Set @SQL = 'ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ' + @ConstraintName
Execute(@SQL)
Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
End
Close curAllTables
Deallocate curAllTables
Close curAllForeignKeys
Deallocate curAllForeignKeys
end
else
begin
print 'Not run - RESET not passed in'
end
End