Click here to Skip to main content
13,596,708 members
Click here to Skip to main content
Add your own
alternative version


22 bookmarked
Posted 3 Feb 2004

Complete Remote (Intra/Inter) SQL Server Manager

, 3 Feb 2004
Rate this:
Please Sign up or sign in to vote.
SQL Server manager for remote monitering and managing.


Database 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.


  1. SQLMail is configured and up and running on a box.
  2. Your 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?

Step 1: "Create Procedures"

Execute the below script on the SQL box where SQL-Mail is setup:

use master


CREATE procedure usp_osqlexecute 

@servername varchar(128) =@@servername,

@Databasename varchar(128)='Master',

@authentication varchar(128)=' -E ',

@Query varchar(2000) = 'sp_readerrorlog'


declare @formsql varchar(3000)

set @formsql = 'osql -S'+@servername +' 

 -d'+@Databasename+' -Q"'+@Query+ '"

' +@authentication + ' >c:\output.txt'

set @formsql ='master.dbo.xp_cmdshell '''+ @formsql+''''

print @formsql

exec (@formsql)


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


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)-@start

set @message= substring(@message,@start,@len)

exec (@message)

set @start =charindex('<email>',@message2)+7

set @len = charindex('</email>',@message2)-@start

set @sendmailto= substring(@message2,@start,@len)

exec master.dbo.xp_sendmail @recipients=@sendmailto,@attachments= 'c:\output.txt'


Step2: "Change Mail Client Options"

In your mail client, change the mail options to the settings displayed below:

Mail Settings

Step3: "Create Job"

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

Job Properties

Job Properties

Job Properties

How to query the server?

Let's assume you work for XYZcompany and your SQLMail email account is Use any of your email accounts, for example, Yahoo!, Hotmail, your local broadband email, or your company's web-mail, and send email to in the following format:

Mail query

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

Query Resultin Mail

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.


  2. Want to send the results to many people?


  3. Simple Queries


    <query>usp_osqlexecute @servername="SQL2k",
    @Query="Select name from sysobjects", @Databasename=Payroll 
  4. Need to read the error log of a different server?


  5. Want to run DBCC on a database?


    <query>usp_osqlexecute @Query = "DBCC Checkdb", @Databasename ="PAY" , 
    @Servername ="sql2k\instance1"</query><email></email>
  6. Need to use SQL authentication instead of Windows authentication?


    <query>usp_osqlexecute @servername="SQL2k\instance1", @authentication=" -Usa 
    -Pyeahright "</query><email></email>
  7. Need to delete a huge log table that you forgot to delete?


    @Query = "Delete from Logtable where date>=getdate()-10", 
    @Databasename ="MAK" , @Servername

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 additional query tag (such as <user>JAK</User>) can be used to verify authentication.


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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Web Developer
United States United States
Chaudhary is currently working as software engineer; His strong points are his passion and Hardworking, and commitment. He is even an Electronics and telecomm Engineer; Apart from Software his interests are Photography, Gardening, Electronics, he is hobbyist. Currently in his time he is working on some telecommunication communication system from past couple of years.

You may also be interested in...


Comments and Discussions

GeneralRe: security... Pin
Chaudhary5-Feb-04 5:03
memberChaudhary5-Feb-04 5:03 
Firstly Always the server need not be available to Internet

1. The mail can be routed by the local mail server to which the user will have the access.
2. The server though has the access to Internet, cannot be connected by the user from remote machine through Internet unless the server is assigned a public IP (The server can be a part of a local network not directly connected to internet and can have the internet access through proxy.).

In order to connect to he server from remote machine (cyber cafe/Public Computers, home pc's), which don’t have the MS SQL installed, this would work fine from any mail account HTTP (hotmail/Yahoo)/POP.... with out any additional installation/software.

There are couple other issues under which this method can be used to manage the SQL server but it is the absolute to go for this option Wink | ;) Rose | [Rose]

Rose | [Rose]


Generalsecurity... Pin
l a u r e n4-Feb-04 10:01
memberl a u r e n4-Feb-04 10:01 
GeneralRe: security... Pin
dog_spawn4-Feb-04 11:02
memberdog_spawn4-Feb-04 11:02 
GeneralRe: security... Pin
Chaudhary4-Feb-04 20:33
memberChaudhary4-Feb-04 20:33 
GeneralRe: security... Pin
dog_spawn5-Feb-04 2:53
memberdog_spawn5-Feb-04 2:53 
GeneralRe: security... Pin
Daniel Turini5-Feb-04 0:49
memberDaniel Turini5-Feb-04 0:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.180621.3 | Last Updated 4 Feb 2004
Article Copyright 2004 by Chaudhary
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid