SQL Server Specialist site contains useful information about using and supporting SQL Server as a DBA
SQL Server Specialists
Home
Syndicate
Search
This Month
October 2007
Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
Recent Articles
Find servers with fixed Ports on SQL Server
Script to Run DBCC CheckDB against all databases on an instance.
Maintenance Plan Fails with the following - Failed:(0) Alter failed for Server
SQLServerAgent cannot start because the instance of the server (MSSQLSERVER) is not the expected instance
Shrinking log issue - Unable to shrink
Error Handling in SQL 2005
Monitor for long running agent jobs
House Keeping - Delete old files using SSIS
Server Info
File Locations
List Databases on Server
Database Properties
SQL Server 2005 DDL Triggers
SQL Agent Error
Restore the Master Database
SQL Server Community Blogs
MS SQL Tips
Month Archive
December 2007
November 2007
October 2007
Year Archive
2009
2008
2007
Login
User name:
Password:
Remember me 
Main Page
Next 30 days »
Wednesday, October 31
View Article  Alter the collation to database default
by Administrator on Wed 31 Oct 2007 22:34 GMT

/*---------------------------------------------------------------------------------------------------

Purpose: This script generate scripts to alter the collation

Author: Carolyn Richardson

Date: 05/10/2007

---------------------------------------------------------------------------------------------------*/

USE{databasename}

GO...   more »

Leave Comment  |  Permanent Link  |  Cosmos
View Article  Identify tables on Server with collation issue
by Administrator on Wed 31 Oct 2007 22:26 GMT
This script will identify tables on all databases on a server that have differing collations, collation differences have probably occured between upgrades to SQL Server versions or where the server default differs   more »
Leave Comment  |  Permanent Link  |  Cosmos
View Article  Find tables with large logical fragmentation
by Administrator on Wed 31 Oct 2007 22:12 GMT
--Quick script to find tables with large logical fragmentation and generate a script to alter the fill factor

CREATE TABLE...   more »

Leave Comment  |  Permanent Link  |  Cosmos
Monday, October 22
View Article  Upgrading to SQL Server 2005
by Administrator on Mon 22 Oct 2007 13:55 BST
Upgrading to SQL Server 2005   more »
Leave Comment  |  Permanent Link  |  Cosmos
Friday, October 19
View Article  How to transfer logins and passwords between instances of SQL Server
by Administrator on Fri 19 Oct 2007 14:04 BST
How to transfer logins and passwords between instances of SQL Server    more »
Leave Comment  |  Permanent Link  |  Cosmos
Thursday, October 18
View Article  Alerts for SQL Server 2000
by Administrator on Thu 18 Oct 2007 22:37 BST

-- These are our preferred Alerts for SQL Server 2000/2005

-- Author: SQL Server Specialists (www.SQLServerSpecialists.co.uk)

USE MSDB

GO

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 19 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 19 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 20 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 20 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Sev. 21 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 21 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 22 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 22 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 23 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 23 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 24 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 24 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 25 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 25 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Full msdb log'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full msdb log'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full tempdb'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full tempdb'

 

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Backup Failure'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Backup Failure', @message_id = 3201, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Deadlock Event'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Deadlock Event'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Deadlock Event', @message_id = 1205, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Driver or Hardware problem with the I/O system'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Driver or Hardware problem with the I/O system'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Driver or Hardware problem with the I/O system', @message_id = 845, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error in current Proccess - 020'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in current Proccess - 020'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error in current Proccess - 020', @message_id = 0, @severity = 20, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error in Database Processes - 021'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in Database Processes - 021'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error in Database Processes - 021', @message_id = 0, @severity = 21, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error In Resource - 019'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error In Resource - 019'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error In Resource - 019', @message_id = 0, @severity = 19, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error: Database Integrity Suspect - 023'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Database Integrity Suspect - 023'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Database Integrity Suspect - 023', @message_id = 0, @severity = 23, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error: Hardware Error - 024'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Hardware Error - 024'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Hardware Error - 024', @message_id = 0, @severity = 24, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error: Table Integrity Suspect - 022'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Table Integrity Suspect - 022'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Table Integrity Suspect - 022', @message_id = 0, @severity = 22, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Errror - 025'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Errror - 025'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Errror - 025', @message_id = 0, @severity = 25, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full Log - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full Log - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full Log - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full msdb log - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full msdb log - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full msdb log - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'msdb', @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full tempdb - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full tempdb - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full tempdb - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Lock Allocation'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Lock Allocation'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Lock Allocation', @message_id = 17125, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Lock request timeout period exceeded'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Lock request timeout period exceeded'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Lock request timeout period exceeded', @message_id = 1222, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Misc Table Error'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Misc Table Error'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Misc Table Error', @message_id = 2511, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Non Logged SQL Server Shutdown'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Non Logged SQL Server Shutdown'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Non Logged SQL Server Shutdown', @message_id = 20531, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF (EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Potentially Fatal Error Insufficient Resources'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Potentially Fatal Error Insufficient Resources'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Potentially Fatal Error Insufficient Resources', @message_id = 0, @severity = 17, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 4, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Unexpected failure acquiring application lock.'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Unexpected failure acquiring application lock.'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Unexpected failure acquiring application lock.', @message_id = 21414, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Unexpected failure releasing application lock'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Unexpected failure releasing application lock'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Unexpected failure releasing application lock', @message_id = 21415, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

 

 
Leave Comment  |  Permanent Link  |  Cosmos
View Article  Save DTS packages as Structured Storage Files
by Administrator on Thu 18 Oct 2007 22:25 BST

CREATEPROCEDURE dbo.usp_SaveDTS

@DTSname varchar(256)='',

@Applicationpath varchar(700)='',

@destinationpath varchar(700)='\\Serverpath\Software\SQL 2000\DTS\',

@switches varchar(200)=' -E -!X '

AS

/*=============================================================================================================================

Stored Proc: usp_SaveDTS

Purpose: Saves DTS Packages as Structured Storage File

Author: Carolyn Richardson

Date: 25/09/2007

NOTES: Alter the @destinationpath to point to the correct UNC path to directory

=============================================================================================================================*/

SET  quoted_identifier off

SET nocount on

SET concat_null_yields_null off

DECLARE @Count int

,@folderexist int ,@maxcount int ,@query varchar(1000) ,@date varchar(10) ,@versionid varchar(40) ,@createdate varchar(25)

SET @date =convert(varchar(10),getdate(),112)

SET @Count =1

PRINT  'Saving DTS packages - Started'

PRINT getdate()

SET  @Applicationpath = @Applicationpath +'DTSRUN.exe'

CREATE TABLE  #DTSTABLE

(id intidentity(1,1), DTSname varchar(256),versionid varchar(40), createdate varchar(25))

IF @dtsname =''

BEGIN

INSERT INTO  #DTSTABLE

(dtsname,versionid,createdate)

SELECT name,versionid,replace(replace(convert(varchar(25),createdate,109),':',' '),' ','_')

FROM msdb..sysdtspackages

END

ELSE

BEGIN

INSERTINTO  #DTSTABLE

(dtsname,versionid,createdate)

SELECT name,versionid,replace(replace(convert(varchar (25),createdate,109),':',' '),' ','_')

FROM msdb..sysdtspackages

WHERE name= @DTSname

END

IF(SELECTcount(*)FROM #dTStable)= 0

BEGIN

SET  @date =convert(varchar(100),getdate(),109)

PRINT  'Error: No valid DTS package found for saving'

END

ELSE

BEGIN

-- Check folder for Server Exists

SET  @destinationpath = @destinationpath +@@Servername CREATETABLE #Files (Files int, Folder int, parent int) INSERT  #Files EXEC master.dbo.xp_fileexist @destinationpath

SELECT @folderexist = Folder FROM #Files

IF @folderexist <>1 BEGIN
SET @query ='MKDIR "'+@destinationpath+'"'

PRINT @query EXEC master..xp_cmdshell @query SET @destinationpath = @destinationpath
END ELSE BEGIN
PRINT  'Information:'+ @destinationpath +' already exist. Skipping Folder Creation'

END BEGIN DELETE FROM #Files --Check folder for save date exists SET  @destinationpath = @destinationpath +'\'+@date INSERT  #Files EXEC master.dbo.xp_fileexist @destinationpath SELECT  @folderexist = Folder FROM #Files IF  @folderexist <>1 BEGIN
SET @query ='MKDIR "'+@destinationpath+'"'

PRINT @query EXEC master..xp_cmdshell @query SET @destinationpath = @destinationpath
END ELSE BEGIN
PRINT  'Information:'+ @destinationpath +' already exist. Skipping Folder Creation'

END END SET @maxcount =(SELECTMAX(id)FROM #dTStable)

WHILE @Count <= @maxcount

BEGIN
SELECT @dtsname = dtsname,@versionid=versionid ,@createdate =createdate

FROM #DTSTABLE WHERE id = @Count SET @query =''+@applicationpath +''+' -S"'+@@servername+'" -N"'+@dtsname+'" -V"'+@versionid +'" -F"'+@destinationpath++'\'+@dtsname+'_'+@createdate+'.dts"'+ @switches SET @query = "EXEC master..xp_cmdshell '" + @query + "'"

PRINT @query

EXEC(@query)

IF @@error<> 0

BEGIN

PRINT 'Error'

END

SET @Count = @Count+1

END

END

PRINT getdate()

PRINT 'Save DTS packages - Completed'

DROP TABLE #DTSTABLE

DROP TABLE #Files

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO
Leave Comment  |  Permanent Link  |  Cosmos
View Article  Add database Mail to SQL Server 2005
by Administrator on Thu 18 Oct 2007 21:54 BST
Setting up Database Mail - SQL Server 2005   more »
Leave Comment  |  Permanent Link  |  Cosmos