SQL job, Windows Service, or timered thread? RSS

• • Reply
  

  cklein

  Participant

  1561 Points

  363 Posts

  SQL job, Windows Service, or timered thread?

  Aug 05, 2003 10:07 PM|LINK

  I need to connect to another database (also a SQL server, which I can connect by SP_AddLinked_Server) to retrieve some data from that database, and update my current database accordingly, finally sending out an email regarding what has been done. My boss want the items embeded in the email, i.e., a HTML table inside the email will be perfect. This has to be done at a daily basis. My boss said a SQL job could do the job, after I write the scripts, very easy to set up. but apparently, it's going to be hard to for me to send that email from inside SQL server, hard to construct that long email body. He even want the emailbody to be templated. and emailto list configurable. I've already have the templted email done in VB.NET. I was thinking maybe a windows service or a timered thread will be better. Who can give me some advice, as to: 1. Make my boss easy to set up, maintain, understand 2. Make my job easier.
  Live by a Better Code
  
  http://www.raincoder.com
  
  Equal parts art and science
  
  Email: cguo@raincoder.com
  
  
• • Reply
  

  russnem

  Contributor

  7001 Points

  1389 Posts

  ASPInsiders

  MVP

  Re: SQL job, Windows Service, or timered thread?

  Aug 05, 2003 11:07 PM|LINK

  SQL can go ahead and do the moving of the data and log the results in a log table that you set up, so the actual work would be done. It sounds like your problems come in to play when it is time to REPORT on what happened. A Windows service could definitely be written to do your emails. You could even use the windows scheduler to run a VBS file (VBScript) or a console application. Its really up to you. I think it is important to recognize the separation between the work that needs to be done and the OTHER things that need to happen as a result of that work. Does this help?
• • Reply
  

  cklein

  Participant

  1561 Points

  363 Posts

  Re: SQL job, Windows Service, or timered thread?

  Aug 06, 2003 04:00 AM|LINK

  If I told my boss to use Windows Service and SQL job at the same time, I am gonna lose my job. It has to be simple for him to understand, maintain. No matter how hard it's for me. Calvin
  Live by a Better Code
  
  http://www.raincoder.com
  
  Equal parts art and science
  
  Email: cguo@raincoder.com
  
  
• • Reply
  

  daibatron

  Participant

  1090 Points

  218 Posts

  Re: SQL job, Windows Service, or timered thread?

  Aug 08, 2003 10:03 PM|LINK

  Have you looked into xp_sendmail? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp
  MCSD, MCDBA, MCAD, MCSD .NET, MSF Practitioner
• • Reply
  

  Stephen Vaki...

  Contributor

  2540 Points

  508 Posts

  Re: SQL job, Windows Service, or timered thread?

  Aug 08, 2003 10:47 PM|LINK

  So what about just using the windows scheduler to run a .net console app which calls an sp that generates the data, returning a result set, which is then transformed into html via whatever mechanism desired??
• • Reply
  

  mstruck

  Member

  15 Points

  3 Posts

  Re: SQL job, Windows Service, or timered thread?

  Aug 09, 2003 05:23 PM|LINK

  Look into SQL Server notification services, may be what you are looking for. http://www.microsoft.com/sql/ns/default.asp Mark