Control your SQL Server 7.0 and 2000 via E-Mail

by MAK [Muthusamy Anantha Kumar]

Data
Base Administrators are often exposed to a situation where they want to query a
table, check on the status of a server, check an error log or run a DBCC
command on a SQL Server box when they do not have access to the box directly.
This often happens when they are off site or on vacation or they do not have
remote clients etc., but have access to the Internet.

This article describes how you
can query SQL Server using your email and get the results back as E-Mail
attachments.

Pre-requisite

1. SQLMail is configured
and up and running on a box.

2. You SQLMail
account can receive emails.

3. Mail Client (MS-Outlook) is open
and running on the box where we are going to create all the procedures and
jobs.

How to Configure?

Step1: “Create Procedures” Execute
the below script on the SQL box where SQL-Mail is setup and the Mail Client is
running.


use master
go

CREATE procedure usp_osqlexecute
@servername varchar(128) [email protected]@servername,
@Databasename varchar(128)=’Master’,
@authentication varchar(128)=’ -E ‘,
@Query varchar(2000) = ‘sp_readerrorlog’
as
declare @formsql varchar(3000)
set @formsql = ‘osql -S’[email protected] +’
-d’[email protected]+’ -Q”‘[email protected]+ ‘”
[email protected] + ‘ >c:\output.txt’
set @formsql =’master.dbo.xp_cmdshell ”’+ @formsql+””
print @formsql
exec (@formsql)

go

CREATE Procedure usp_readmail as
declare @message varchar(1000)
declare @message2 varchar(1000)
declare @start int
declare @len int
declare @sendmailto varchar(128)
declare @count int
create table #messages (Originator varchar(1000)
,date datetime,recipients varchar(1000),cclist varchar(300),
bcclist varchar(300),subject varchar(300),message ntext,
unread varchar(10),attachments varchar(1000),messageid varchar(1000),
type varchar(1000))

set @sendmailto=’ ‘
insert #messages exec “xp_readmail”
set @count = (select count(*) from #messages where unread=’true’)
if @count>0
begin

set @message=(select convert(varchar(1000),message) as message from #messages
where date = (select max(date) from #messages) and unread=’true’)

set @message2 = @message
set @start =charindex(‘<query>’,@message)+7
set @len = charindex(‘</query>’,@message)[email protected]
set @message= substring(@message,@start,@len)
exec (@message)

set @start =charindex(‘<email>’,@message2)+7
set @len = charindex(‘</email>’,@message2)[email protected]
set @sendmailto= substring(@message2,@start,@len)

exec master.dbo.xp_sendmail @[email protected],@attachments= ‘c:\output.txt’

end

Step2: “Change Mail Client Options”

In your Mail Client, change the Mail options to the settings
displayed below:

Step3: “Create Job”

Create a job that executes the above-created procedure (usp_readmail)
every 1 minute.

How to query the server?

Let’s assume you work for XYZcompany
and your SQLMail email account is [email protected].
Use any of your email account, for example, Yahoo, Hotmail, your local
broadband Email or your companys web-mail and send email to [email protected] in the
following format.

In few minutes, you will have results, similar as those
displayed below, as an attachment in your email.

Query format

I use this query format because whenever you use any free
email accounts, such as Yahoo or Hotmail, there are additional text
advertisements and signature attached to the email. By using this format, we
can filter out the query and email accounts from those advertisements.

1. By default, the procedure usp_osqlexecute will read the
error log and send back the results as an attachment.

Example:

<query>usp_osqlexecute </query><email>[email protected]</email>

2. Want to send the results to many people?

Example:

<query>usp_osqlexecute
</query><email>[email protected];[email protected]</email>

3. Simple Queries

Example:

<query>usp_osqlexecute @servername="SQL2k",
@Query="Select name from sysobjects", @Databasename=Payroll </query><email>[email protected]</email>

4. Need to read the Errorlog of a different server?

Example:

<query>usp_osqlexecute
@servername="SQL2k\instance1"</query><email>[email protected]</email>

5. Want to run DBCC on a database?

Example:

<query>usp_osqlexecute @Query = "DBCC Checkdb", @Databasename ="PAY" , 
@Servername ="sql2k\instance1"</query><email>[email protected]</email>

6. Need to use SQL authentication instead of Windows
authentication?

Example:

<query>usp_osqlexecute @servername="SQL2k\instance1", @authentication=" -Usa 
-Pyeahright "</query><email>[email protected]</email>

7. Need to delete a huge log table that you forgot to
delete?

Example:

<query>usp_osqlexecute
@Query = "Delete from Logtable where date>=getdate()-10", 
@Databasename ="MAK" , @Servername
="sql2k\instance1"</query><email>[email protected]</email>

Note:

You can limit the users accessing this feature by adding
security such as RC4 encryption to the query format and/or evaluate where the
email is coming from and/or an addition query tag (such as <user>MAK
</User>) can be used to verify authentication.

Conclusion:

With this method, wherever you have access to the Internet,
you can be in touch with your SQL Server boxes. Using the methods described in this
article you can do all of the work that you can to do on a query analyzer from
a remote location with a little latency.

Latest Articles