SQL 101 : Email

SQL 2005

SQL Server 2005 replaces SQL Mail with Database Mail which allows you to send to a SMTP Server directly.

Enabling Database Mail on a server (via SAC | via TSQL)

Configuring Database Mail via TSQL


SQL 2000

SQL Server 2000 features two ways to access email functionality, 'SQL Mail' and 'SQLAgentMail'.

Both components are MAPI applications, hence MAPI must be present on the server e.g. through Microsoft Outlook.
Through MAPI, SQL 2000 can talk to Exchange or POP3 mail servers.
Setting this up requires Outlook to be installed on the SQL Server, configured with a mailbox and profile name (log in as the account sql runs under to do so).

How to Configure SQL Mail
http://support.microsoft.com/kb/263556

In practice, MAPI can be a real pain both in terms of setup and reliability (a must if using for system notifications).
Therefore multiple solutions exist on the web to send email via other delivery mechanisms from SQL Stored procedures.

CDONTS (think Windows NT) requires a local 'virtual' SMTP server to be installed which is configured to forward emails to a physical one.

CDOSYS (Windows 2000 onwards) can send to a local or remote SMTP server.
Both methods require elavated privileges or the sysadmin role.

CDOSYS example

How to send e-mail without using SQL Mail in SQL Server
http://support.microsoft.com/kb/312839

NOT IN vs NOT EXISTS vs OUTER JOIN

3 ways Missing Data to locate missing data.
All are executed indentically according to the execution plan.
The third (outer join with null checking) is probably the most accedemically correct, though (IMHO) the least readable..

-- not in
select * from Sales.SalesOrderDetail
where SalesOrderID not in (select SalesOrderID from Sales.SalesOrderHeader)

-- not exists
select * from Sales.SalesOrderDetail SalesOrderDetail
where not exists (select * from Sales.SalesOrderHeader SalesOrderHeader
where SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID)

-- outer join and NULL checking
select * from Sales.SalesOrderDetail SalesOrderDetail
left outer join Sales.SalesOrderHeader SalesOrderHeader
on SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
where SalesOrderHeader.SalesOrderID is null

SQL 2000 > 2005 Linked Server Error

" A severe error occurred on the current command. The results, if any, should be discarded. "

This occurs passing a NVARCHAR to sp_executesql on sql 2000 when linking to a 2005 instance.

Sql 2000 could not cope with the output returned by sql 2005 hence although the steps ran perfectly on their own.

declare @chvDataBaseName varchar(100)
declare @sql nvarchar(2000)
set @chvDataBaseName = 'northwind'

-- backup on local server

set @sql = N' BACKUP DATABASE ' + @chvDataBaseName
set @sql = @sql + ' TO DISK = N''d:\tempmigrationfolder\'
set @sql = @sql + @chvDataBaseName + '.BAK'' WITH NOFORMAT, INIT,'
set @sql = @sql + ' NAME = N''Full Database Backup'','
set @sql = @sql + ' SKIP, NOREWIND, NOUNLOAD, STATS = 10'
print @sql
execute linked2005server.master.sp_executesql @sql

It is confirmed as a bug. I simply reversed my plan and ran the script from sql2005 performing the remote call on the sql 2000 databases.

http://support.microsoft.com/kb/896373

SQL 2005 : Database Mail

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0 "SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online. "

By default, Database Mail is not enabled when SQL 2005 is installed.

You can enable it by the Surface Area Configuration Tool or by TSQL.

Enabling Database Mail via TSQL

-- allow advanced options

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO

-- enable database mail (0 disables , 1 enables)
 
sp_configure 'Database Mail XPs', 1; 
GO 
RECONFIGURE 
GO

-- hide advanced options again

sp_configure 'show advanced options', 0; 
GO 
RECONFIGURE; 
GO 

Enabling Database Mail via Surface Area Configuration

1) Launch Surface Area Configuration >

Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration Tool


2) Open 'Surface Area Configuration for Features' (text link at bottom of page)


Click Database Mail and place an tick in the 'Enable Database Mail Stored Procedures'.
Click Apply then OK.

Float as Varchar (Not Scientific)

Formatting a float in varchar but NOT in scientific notation

SELECT CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2)))

or

SELECT STR(@testFloat, 38, 2)

Database Diagrams (SQL 2005)

A bit primitive at the moment and nowhere near the database diagramming functionality provided in Visio, but its a start. Picking up on defined relationships in a database, here is what it made of a handful of AdventureWorks tables >

Excel VBA - Code for importing a SQL Server table

Excel VBA - Code for importing a SQL Server table

Sub GetTableDataFromSQLServer()

' Declare the QueryTable object
Dim qt As QueryTable

' Set up the SQL Statement
sqlstring = 'select column1, column2, column3 from table'

' Set up the connection string, reference an ODBC connection
' Leave the name and password blank for NT authentication

connstring = 'ODBC;DSN=DataSouirceName;UID=;PWD=;Database=DatabaseName'

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range('A1'), Sql:=sqlstring)

.Refresh

End With
End Sub

Configuring SQL Database Mail via TSQL

I personally find this quicker than using management studio...

USE msdb
GO
DECLARE @mailprofilename VARCHAR(100)
DECLARE @mailaccountname VARCHAR(100)
DECLARE @mailaccountdescription VARCHAR(100)
DECLARE @smtpserver VARCHAR(100)
DECLARE @emailaddress VARCHAR(100)
DECLARE @from VARCHAR(100)
DECLARE @to VARCHAR(100)

/*
Declare everything as variables (makes it nice and easy to reuse script)
*/

SET @mailprofilename = 'DBMail Profile';
SET @mailaccountname = 'SQL Administrator';
SET @mailaccountdescription = 'for sql generated email notifications';
SET @smtpserver = '192.168.0.20';
SET @emailaddress = 'SQLAdmin@mydomain.net';
SET @from = 'SQL Administrator';
SET @to = 'recipient@mydomain.net';

-- add mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @mailaccountname,
@description = @mailaccountdescription,
@email_address = @emailaddress,
@display_name = @from,
@mailserver_name = @smtpserver

-- add mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @mailprofilename 

-- associate mail account & mail profile together
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @mailprofilename,
@account_name = @mailaccountname,
@sequence_number = 1 ;

-- send test email
EXEC msdb.dbo.sp_send_dbmail
@recipients = @to,
@subject = 'rabbit, rabbit, rabbit, rabbit',
@body = 'bunny, bunny, bunny, bunny', 
@profile_name = @mailprofilename

Display ASCII table script

The extended characters ( > 127 ) will differ depending on collation...

if object_id('tempdb..#results') is not null
  drop table #results

create table #results (asciicode smallint,displaychar char)

declare @intCounter smallint
set @intCounter = 1
while @intCounter <= 255  
begin  
insert into #results (asciicode,displaychar)  
select @intCounter, char(@intCounter)  
set @intCounter = @intCounter + 1 
end  
select asciicode, displaychar from #results