How to use Database mail feature in SQL Server 2000

We are all talking about the latest versions and features available in SQL Server but most of us are still working on older, SQL Server 2000 servers. It might take a few more years before all of the existing SQL Server 2000 applications are upgraded to newer versions of SQL Server or to other database products. This means we are still stuck with using the old, outdated features available in SQL Server 2000.
One such feature is SQL Mail. I thought that in order to configure email notifications it would be better and easier to use database mail (introduced in SQL Server 2005) rather than SQL Mail where I can. In this article, I will run you through an easier way of using database mail in SQL Server 2000 itself. That is, in your SQL Server 2000, you would be in a position to use database mail instead of SQL Mail for sending job failure notification. This is my procedure:
1) As a first step, you need to configure database mail on your SQL Server 2005 instance if you have not already configured. I haven't covered this in this article, but you can find more information from numerous articles on the internet. You could also go to SQLServer Books Online and read about the wizard. If you are new to SQL Server, don't panic, this takes around 5 to10 minutes to configure.
2) Once database mail is configured or if it is already configured in your SQL Server 2005 server, create a login 'test' on this 2005 instance and make sure you make this login a member of the db_owner role in the msdb database.
3) Once this is done, go to your SQL Server 2000 instance with Query Analyzer and run the sample script below. This will create a linked server on your SQL Server 2000 instance that will help you connect to your SQL Server 2005 instance where you have configured database mail
Please enter the appropriate details relevant to your SQL Server 2005 in the @server and @datasrc parameters.EXEC sp_addlinkedserver @server='SQL_Server_2005 ', @srvproduct='', @provider='SQLOLEDB', @datasrc='SQL Server 2005 instance name'
4) Now Go to SQL Server 2000 instance-> security-> Linked Servers and you will see the new linked server you created. Right click on this new linked server and go to the properties->security tab
Please enter credentials as shown in screenshot, refer to step (2) above where you created a login in SQL Server 2005 (please use the same login details).
And on 'Server Options' tab, we have to enable RPC Out

5) Go to your SQL Server 2000 server and create a test job. Just make sure that this job will fail (eg. Include script for taking backup of non-existent database on your server) and add a job failure notification step on failure of first step. A sample script of the job failure notification step belowexec SQL_Server_2005.msdb.dbo.sp_send_dbmail @profile_name = 'SampleSQLServer2005dbmailProfile', @recipients='yourname@email.com', --@copy_recipients = 'yourgroup@email.com', @subject='Job test_db_mail failed on SQL Server 2000 server', @importance='HIGH', @body ='Job test_db_mail failed on SQL Server 2000 server'
We are referencing the linked server 'SQL_Server_2005' (please refer step 3) that we created on SQL Server 2000. Please enter the appropriate details in @profile_name (this is the database profile you configured in SQL Server 2005) and @recipients parameters. Also, please refer to the screenshot below for your reference.

6) Execute this test job on SQL Server 2000 and in a few minutes you will get a notification to your email address
7) If you did not get the email notification, just make sure that the SQL Server 2005 instance where you are connecting to using linked server is running fine. You could try some test queries from SQL Server 2000 Query analyser to retrieve data from SQL Server 2005.
Example: Select * from openquery(SQL Server 2005 linked server name ,'select * from msdb.dbo.sysjobs')
If the linked server set up is fine, above query should return results.
Conclusion
I always found configuring email notifications a pain in SQL Server 2000. I am sure many of you would have faced difficulties doing the same on SQL Server 2000. I hope by using above method, you don't have to tweak errors you may face while using SQL mail, instead you could start utilizing database mail of SQL Server 2005 and above. Good luck.

Remove empty lines in text using Visual Studio.

Visual Studio has ability to delete empty lines in replace operation using regular expressions
.1.Click Ctrl-H
2. Tick "Use Regular Expressions"
3. In Find specify ^$\n
4. In Replace box delete everything.
5 Click "Replace All"
All empty lines will be deleted.

Regular expression for empty line consist of
Beginning of line ^
End of line $
Line break \n
Note that normally in Windows an end of line indicated by 2 characters CRLF - Carriage Return (CR, ASCII 13, \r) Line Feed (LF, ASCII 10, \n).

Macro to Convert Public Members to Propeties

//*Public Module Utilities
//Declare these two variables under module
Dim repPattern As String
Dim repPatternReadOnly As String
*//Sub ConvertVariables()' Determine current language by looking at the extension of the current document.Dim doc As Document = DTE.ActiveDocumentIf doc Is Nothing Then Exit SubDim docName As String = doc.Name.ToLower()' Read all the text lines touched by the selection.Dim sel As TextSelection = CType(DTE.ActiveDocument.Selection, TextSelection)Dim ed1 As EditPoint = sel.AnchorPoint.CreateEditPoint()ed1.EndOfLine() : ed1.StartOfLine() : ed1.StartOfLine()Dim ed2 As EditPoint = sel.BottomPoint.CreateEditPoint()ed2.EndOfLine()Dim text As String = ed1.GetText(ed2)' The find and replacement pattern depend on the current language.Dim findPattern As StringIf docName.EndsWith(".vb") ThenfindPattern = "(?[\t ]+)Public\s+(?Shared\s+)?(?ReadOnly\s+)?" _& "(?\w+)\s+As\s+(?\S+)(?.*?)\n"' {0}=property name, {1}=property type, {2}=static keyword, {3} initvalue, ' {4}=CR-LF, {5}=Tab, {6}=indentrepPattern = "{6}Private {2}m_{0} As {1}{3}{4}" _& "{6}Public {2}Property {0}() As {1}{4}" _& "{6}{5}Get{4}" _& "{6}{5}{5}Return m_{0}{4}" _& "{6}{5}End Get{4}" _& "{6}{5}Set(ByVal Value As {1}){4}" _& "{6}{5}{5}m_{0} = Value{4}" _& "{6}{5}End Set{4}" _& "{6}End Property{4}{4}"repPatternReadOnly = "{6}Private {2}ReadOnly m_{0} As {1}{3}{4}" _& "{6}Public ReadOnly {2}Property {0}() As {1}{4}" _& "{6}{5}Get{4}" _& "{6}{5}{5}Return m_{0}{4}" _& "{6}{5}End Get{4}" _& "{6}End Property{4}{4}"ElseIf docName.EndsWith(".cs") Then' Notice the (?.*;) element is needed to ensure that public fields are matched, ' but public properties aren'tfindPattern = "(?[\t ]+)public\s+(?static\s+)?(?readonly\s+)?" _& "(?\S+)\s+(?\w+)(?=.*;)(?.*?)\n"' {0}=property name, {1}=property type, {2}=static keyword, {3} initvalue, ' {4}=CR-LF, {5}=Tab, {6}=indentrepPattern = "{6}private {2}{1} _{0}{3}{4}" _& "{6}public {2}{1} {0}{4}" _& "{6}{{{4}" _& "{6}{5}get {{ return _{0}; }}{4}" _& "{6}{5}set {{ _{0} = value; }}{4}" _& "{6}}}{4}{4}"repPatternReadOnly = "{6}private {2}readonly {1} _{0}{3}{4}" _& "{6}public {2}{1} {0}{4}" _& "{6}{{{4}" _& "{6}{5}get {{ return _{0}; }}{4}" _& "{6}}}{4}{4}"End If' Replace the text. Add a trailing CR-LF but remove it later.Dim replaceText As String = Regex.Replace(text + ControlChars.CrLf, findPattern, _AddressOf ReplaceWithProperty)ed1.ReplaceText(ed2, replaceText.Substring(0, replaceText.Length - 2), 0)End Sub' Private callback function for the Replace methodPrivate Function ReplaceWithProperty(ByVal m As Match) As StringDim pattern As String = repPatternIf m.Groups("readonly").Length > 0 Then pattern = repPatternReadOnlyReturn String.Format(pattern, m.Groups("name").Value, m.Groups("type").Value, _m.Groups("static").Value, m.Groups("init").Value, ControlChars.CrLf, _ControlChars.Tab, m.Groups("indent").Value)
End Function

SQL Function to Accept CSV's and to return table

CREATE FUNCTION [dbo].[ConvertListToColumn] (@list nvarchar(MAX))RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,@nextpos int,@valuelen intSELECT @pos = 0, @nextpos = 1WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

Finding Child tables

We can find child tables of a table from the management studio as well as by the following query
select * from sys.foreign_keysselect object_name(parent_object_id), object_name(referenced_object_id)
from sys.foreign_keys

Finding the reference of tables in other tables and Stored procedures

some times we need to update a table/name and we want to know where this table is reffered in other database objects
so we can use following options to find
Option 1
SELECT DISTINCT so.nameFROM syscomments scINNER JOIN sysobjects so ON sc.id=so.idWHERE sc.TEXT LIKE '%tblsysacommunity%'
Option 2
SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE c.TEXT LIKE '%tblsysacommunity%'

Getting Changes in DatabBase with in a specified time

SELECT name, TYPE, type_desc, create_date, modify_date FROM sys.objects WHERE TYPE IN ('U','V','PK','F','D','P') AND modify_date >= Dateadd(HOUR,08,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME)) ORDER BY modify_date
--U - Table
--V - View
--PK - Primary Key
--F - Foreign Key
--D - Default Constraint
--P - Procedure

Recursive CTE to split CSV

Declare @array varchar(max)Declare @del varchar(1)select @array='5,6,7,8,9'
select @del=',';
WITH rep (item,list) AS ( SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item, SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list
UNION ALL
SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item, SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list FROM rep WHERE LEN(rep.list) > 0 )
INSERT INTO @anyTable
SELECT * FROM rep

joining queries on multiple servers

In Sql server we can create Linked servers to execute/join queries on multiple servers/databases.
we can creae linked server by using Management studio option or by using the script like
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerName', @srvproduct=N'DB', @provider=N'SQLNCLI', @datasrc=N'RemoteServerName', @provstr=N'Data Source=RemoteServerName;Initial Catalog=DB_NAME;Persist Security Info=True;User ID=sa;Password=password', @catalog=N'MY_DB' /* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RemoteServerName',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'collation compatible', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'data access', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'dist', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'pub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'rpc', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'rpc out', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'sub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'connect timeout', @optvalue=N'0'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'collation name', @optvalue=nullGOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'lazy schema validation', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'query timeout', @optvalue=N'0'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'use remote collation', @optvalue=N'true'
When we are finished with the creation of Linked Server
we can write any query like
Insert Into RemoteServerName.MY_DB.dbo.MY_TABLE
select columns from current server database table.

Getting Duplicate Records in a table

Declare @temp table(id int,name varchar(30),age int)INSERT INTO @temp values(1,'faheem',12)INSERT INTO @temp values(2,'ahmad',12)INSERT INTO @temp values(1,'faheem',12)INSERT INTO @temp values(3,'faheem',14)
select id,name,age,count(*) from @tempgroup by id,name,agehaving count(*)>1

Cross Apply very good feature in sqlserver 2005

Consider a problem
We have to write a query to return the top 3 orders for each customer in adventure works database.
We can do so by the following 3 ways
1-
SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
CROSS APPLY
(SELECT TOP(3) *
FROM Sales.SalesOrderHeader
WHERE CustomerID = C.CustomerID
ORDER BY TotalDue DESC) AS O
ORDER BY
CustomerID ASC, TotalDue DESC
2-
SELECT CustomerID, SalesOrderID, TotalDueFROM ( SELECT O.CustomerID, O.SalesOrderID, O.TotalDue, ROW_NUMBER = ROW_NUMBER() OVER ( PARTITION BY O.CustomerID ORDER BY O.TotalDue DESC) FROM Sales.SalesOrderHeader AS O ) AS dWHERE d.ROW_NUMBER <= 3
We can change above query for better understanding as

with O AS
(
SELECT ROW_NUMBER()
OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC)
AS rn ,Sales.SalesOrderHeader.* FROM Sales.SalesOrderHeader
)
SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
Inner Join O on C.CustomerID=O.CustomerID
Where O.rn<=3

Find a string value in all database tables

DECLARE @TEXT VARCHAR(500)SET @TEXT = 'Faheem'
DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500), ColumnName VARCHAR(500))INSERT INTO @TABLES(TableName, ColumnName)SELECT O.[NAME], C.[NAME]--SELECT *FROM SYSOBJECTS OJOIN SYSCOLUMNS C ON C.ID = O.IDWHERE O.XTYPE = 'U' AND C.XTYPE NOT IN ( 127 --bigint , 173 --binary , 104 --bit , 61 --datetime , 106 --decimal , 62 --float , 34 --image , 56 --int , 60 --money , 108 --numeric , 59 --real , 58 --smalldatetime , 52 --smallint , 122 --smallmoney , 189 --timestamp , 48 --tinyint , 36 --uniqueidentifier , 165 --varbinary )ORDER BY O.[NAME], C.[NAME]
IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TMPREPORT%')BEGIN DROP TABLE #TMPREPORTENDCREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500), COLUMNNAME VARCHAR(500))
DECLARE @CNTR INT, @POS INT, @TableName VARCHAR(500), @ColumnName VARCHAR(500), @SQL VARCHAR(8000)SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''FROM @TABLES
--SELECT @POS, @CNTR, * FROM @TABLES
WHILE @POS <= @CNTRBEGIN SELECT @TableName = TableName, @ColumnName = ColumnName FROM @TABLES WHERE [ID] = @POS
SELECT @SQL = 'SELECT COUNT(*), ''' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @TableName + ' WHERE CAST(' + @ColumnName + ' AS VARCHAR) LIKE ''%' + @TEXT + '%''' --PRINT @SQL BEGIN TRY INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME) EXEC(@SQL) END TRY BEGIN CATCH PRINT @@ERROR PRINT @SQL END CATCH SELECT @POS = @POS + 1END
SELECT * FROM #TMPREPORT WHERE COUNTER > 0
DROP TABLE #TMPREPORT

Effective Use of Group By

Some times we have to group our data on a specific condition for example in the following table
Create table scenario (studentID smallint, certID smallint, issuedate datetime)GO
populated with data asinsert into scenario --values (studentID, certID, issueDate)select 1,12,'2008-10-13' union all select 1,13,'2009-06-11' union all select 2,16,'2009-08-10' union all select 2,21,'2007-07-23' union all select 3,23,'2009-11-25' GO
I can have a requriement like
"to get records grouped on studentID with last certificate issued according to issue date."
So we can use group by and get required result using the following query
select a.studentID,a.certID,a.issuedatefrom scenario a,(select studentID,max(issuedate)issuedatefrom scenario group by studentID)as bwhere a.studentID = b.studentIDand a.issuedate = b.issuedateorder by a.studentID

Get Table and their total row count

SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME

An Alternate could be


select [Owner] = left(user_name(objectproperty(id, 'OwnerId')), 35)
, [Table] = left(object_name(id), 65)
, [Count] = rows
from dbo.sysindexes
where indid < 2
and objectproperty(id, 'IsUserTable') = 1

Create Data Base Files on Remote Machine

DBCC TRACEON ( 1807 )DBCC TRACEstatus ( 1807 )
USE [master]GO/****** Object: Database [Test4] Script Date: 10/07/2009 18:22:48 ******/CREATE DATABASE [Test4] ON PRIMARY ( NAME = N'Test4', FILENAME = N'\\dnet01\share\Test4.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Test4_log', FILENAME = N'\\dnet01\share\Test4_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_ASGODBCC TRACEOFF (1807)

Enable / Disable All Databbase Triggers

In Sql Server 2005 we can use
sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
To enable all triggers, you can use following statement
sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

Returning Data into a Table Variable

CREATE TABLE dbo.UDFTestTable (Col1 int, Col2 nvarchar(10))GOINSERT dbo.UDFTestTable(Col1,Col2) VALUES (1, N'One')INSERT dbo.UDFTestTable(Col1,Col2) VALUES (2, N'Two')INSERT dbo.UDFTestTable(Col1,Col2) VALUES (3, N'Three')INSERT dbo.UDFTestTable(Col1,Col2) VALUES (4, N'Four')GO
CREATE FUNCTION dbo.UDFReturnsTable (@MinRange int = 1, @MaxRange int = 4)-- Procedure BRETURNS TABLEAS RETURN(SELECT A.Col2 AS N'TextNumber' FROM dbo.UDFTestTable AS A WHERE A.Col1 BETWEEN @MinRange AND @MaxRange)GO
CREATE PROCEDURE dbo.MySP_CallsUDF-- Procedure AAS DECLARE @MyTableVar TABLE (MyTextName NVARCHAR(10)) -- Call the UDF. Note the use of the explicit default value. INSERT @MyTableVar SELECT A.TextNumber FROM dbo.UDFReturnsTable(2,default) AS A SELECT * FROM @MyTableVarGO
-- And see what happens.EXEC dbo.MySP_CallsUDF