CODEWITHSUNDEEP
sqlc#-3.0
NoviceMe
NoviceMe

Reputation: 3256

create auto sql script that runs in every hour - in c# or any other easy way

I have simple sql script:

Select * from student where score > 60

What i am trying to do is run this above script every 1 hour and getting notified on my computer in any way possibe that above condition was met. So basically i dont want to go in there and hit F5 every hour on the above statement and see if i get any result. I am hoping someone out here has something exactly for this, if you do please share the code.

Upvotes: 0

Views: 1204

Answers (1)

Charleh
Charleh

Reputation: 14002

You can use Sql Agent to create a job, Sql server 2008 also has mail functionality

Open SQL Management Studio and connect to your SQL Server

Expand the SQL Server Agent node (if you don't see it, use SQL configuration manager or check services and ensure that SQL Server Agent (SQLINSTANCENAME) is started)

Right click on Jobs and choose 'New Job'

You can run a SQL statement in a job. I'll let you figure out the rest of that part (it's pretty intuitive)

You may want to send your mail using xp_sendmail

Check out the SQL documentation for xp_sendmail

http://msdn.microsoft.com/en-us/library/ms189505(v=sql.105).aspx

You might need to turn the feature on (afaik it's off by default) and you need some server/machine to deliver the mail (so you might need IIS and SMTP installed if on a local machine)

Edit:

Assuming you can't access the server and want to do this on the client side, you can create a .NET framework app or windows service to do the work for you using a schedule or a timer approach:

Schedule approach:

Create a simple command line application which does the query and mails the results, and use the windows scheduler to invoke it every hour (or whatever your interval may be)

Timer approach:

Create a simple application or windows service that will run a timer thread which does the work every x number of minutes

I'd probably just go for the former. The code would be quite simple - new console app:

static void Main(string args[])
{
   // No arguments needed so just do the work
   using(SqlConnection conn = new SqlConnection("ConnectionString")) 
   {
       using(SqlCommand cmd = new SqlCommand("sql query text", conn))
       {
           var dr = cmd.ExecuteReader();
           List<myClass> results = new List<myClass>();

           // Read the rows
           while(dr.Read()) 
           {
               var someValue = dr.GetString(dr.GetOrdinal("ColumnName"));
               // etc
               // stuff these values into myClass and add to the list
               results.Add(new myClass(someValue));
           }
       }
   }

   if(results.Count > 0) // Send mail
   {
      //Send the message. 
      SmtpClient client = new SmtpClient(server);
      // Add credentials if the SMTP server requires them.
      client.Credentials = CredentialCache.DefaultNetworkCredentials;


      MailMessage message = new MailMessage(
           "[email protected]",
           "[email protected]",
           "Subject",
           "Body");
     // Obviously you'd have to read the rows from your list, maybe override ToString() on 
     // myClass and call that using a StringBuilder to build the email body and append the rows


     // This may throw exceptions - maybe some error handling (in any of this code) is advisable
     client.Send(message);

    }
}

Disclaimer: probably none of this will compile :D

Edit 2: I'd go this way as it's much easier to debug than a windows service as you can just run it from the command line. You can also pass command line arguments so you don't need an application configuration file

Upvotes: 1

Related Questions