Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Logging with SQL Server Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 1:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:30 AM
Points: 982, Visits: 758
Looks like you're catching the most likely causes but ignoring them. Take out the try/catch blocks and look in the application log.
Post #843174
Posted Wednesday, January 6, 2010 2:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:36 AM
Points: 19, Visits: 86
Phillip - Texas (1/6/2010)
Can anyone post a working code example of a Windows Service? I tried using the code in the article and had to play with it a bit to get it to compile. I then added an installer project and eventually installed the service in Windows. My service starts then stops and I get the message that "your service started then stopped..."

Here is what I have:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
using System.Xml;
using System.IO;

namespace svcbroker
{
public partial class Service1 : ServiceBase
{
private bool m_Terminate = false;
private string LogFolder = "c:\\svcbroker\\LogFolder\\";
private string ArchiveFolder = "c:\\svcbroker\\ArchiveFolder\\";


public Service1()
{
InitializeComponent();
}

protected override void OnStart(string[] args)
{
using (SqlConnection Conn = new SqlConnection("Data Source=(local);Initial Catalog=ServiceBroker;Integrated Security=SSPI"))
{
Conn.Open();
using (SqlCommand Cmd = Conn.CreateCommand())
{
Cmd.CommandText = "ProcessLogQueue";
Cmd.CommandType = System.Data.CommandType.StoredProcedure;
Cmd.CommandTimeout = 0; // no timeout

while (!this.m_Terminate) // looping until the service is stopped
{
string Response = Cmd.ExecuteScalar().ToString(); // execute the command
if (Response.Length > 0)
{
XmlDocument Doc = new XmlDocument();
Doc.LoadXml(Response);
XmlNode RootNode = Doc.SelectSingleNode("LOG");
XmlNode RowNode = RootNode.SelectSingleNode("Row");
string ProcessName = RowNode.SelectSingleNode("ProcessName").InnerText;
string MachineName = RowNode.SelectSingleNode("MachineName").InnerText;
string MachineFolder = Path.Combine(this.LogFolder, MachineName);

if (!Directory.Exists(MachineFolder))
{
try
{
Directory.CreateDirectory(MachineFolder);
}
catch (Exception Ex)
{
// log the failure to a logfile for the Windows Service
return;
}
}

// create the name of the log file

string FileName = Path.Combine(MachineFolder, ProcessName + ".log");

try
{
this.CheckLog(MachineName, FileName); // does the log file nee to be archived?
}
catch (Exception Ex)
{
// log the failure to a logfile for the Windows Service
return;
}
try
{
using (StreamWriter SW = new StreamWriter(FileName, true))
{
SW.WriteLine(string.Format("{0} {1}", RowNode.SelectSingleNode("LogTime").InnerText,
RowNode.SelectSingleNode("ProcessMessage").InnerText));
}
}
catch (SqlException Ex)
{
// log the failure to a logfile for the Windows Service and quit
}
catch (System.Threading.ThreadAbortException)
{
// we have been ordered to quit
}
catch (Exception Ex)
{
// log the failure to a logfile for the Windows Service and quit
}
}
}
}
}
}

private void CheckLog ( string MachineName, string FileName )
{

FileInfo FI = new FileInfo ( FileName );

if ( FI.Exists )
{
DateTime Today = Convert.ToDateTime ( DateTime.Now.ToShortDateString () );

if ( FI.LastWriteTime < Today )
{
string ArchiveFileName = Path.GetFileName ( FileName ).Replace ( ".log", FI.LastWriteTime.ToString ( "yyyyMMdd" ) + ".log" );
string MachineFolder = Path.Combine ( this.ArchiveFolder, MachineName );

if ( !Directory.Exists ( MachineFolder ) )
{
Directory.CreateDirectory ( MachineFolder );
}

FI.MoveTo ( Path.Combine ( MachineFolder, ArchiveFileName ) );
}
}
}

protected override void OnStop()
{
}
}
}



I will send you a copy in the morning of what I use.
Post #843198
Posted Thursday, January 7, 2010 5:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:36 AM
Points: 19, Visits: 86
Regarding the user of a Windows Service, the following is the code that we use:

using System;
using System.ServiceProcess;
using System.Threading;


namespace ROAMLogService
{
public delegate void ThreadCallback ( string Message );

public partial class ROAMLogService : ServiceBase
{
private ServiceThread ST;
private Thread m_ServiceThread;

public ROAMLogService ()
{
InitializeComponent ();
}

public void StartService ()
{
this.ST = new ServiceThread ( new ThreadCallback ( ServiceEnded ) );

this.m_ServiceThread = new Thread ( new ThreadStart ( ST.Run ) );
this.m_ServiceThread.Start ();
}

private void ServiceEnded ( string Message )
{
Thread.Sleep ( 3000 );
this.StartService ();
}

protected override void OnStart ( string[] args )
{
this.StartService ();
}

protected override void OnStop ()
{
this.ST.Terminate = true;
Thread.Sleep ( 3000 );

if ( this.m_ServiceThread != null )
{
this.m_ServiceThread.Abort ();
this.m_ServiceThread.Join ();
}
}
}
}

The Service Thread is where all of the work is done:

using System;
using System.Data.SqlClient;
using System.IO;
using System.Xml;

namespace ROAMLogService
{
class ServiceThread
{
private bool m_Terminate = false;
public bool Terminate
{
set { this.m_Terminate = value; }
}

private string LogConnectionString;
private string LogFolder = "";
private string ArchiveFolder = "";
private ThreadCallback m_Callback;

public ServiceThread ( ThreadCallback Callback )
{
this.m_Callback = Callback;
this.LogFolder = System.Configuration.ConfigurationManager.AppSettings[ "LogFolder" ].ToString ();
this.ArchiveFolder = this.LogFolder + @"\Old Logs";
this.LogConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ "Logging" ].ToString ();

if ( this.LogConnectionString == null )
{
this.m_Callback ( "Log ConnectionString not found - exiting" );
this.m_Terminate = true;
}
}

public void Run ()
{
try
{
using ( SqlConnection Conn = new SqlConnection ( this.LogConnectionString ) )
{
Conn.Open ();

using ( SqlCommand Cmd = Conn.CreateCommand () )
{
Cmd.CommandText = "ProcessLogQueue";
Cmd.CommandType = System.Data.CommandType.StoredProcedure;
Cmd.CommandTimeout = 0;

while ( !this.m_Terminate )
{
string Response = Cmd.ExecuteScalar ().ToString ();

if ( Response.Length > 0 )
{
XmlDocument Doc = new XmlDocument ();
Doc.LoadXml ( Response );
XmlNode RootNode = Doc.SelectSingleNode ( "LOG" );
XmlNode RowNode = RootNode.SelectSingleNode ( "Row" );
string ProcessName = RowNode.SelectSingleNode ( "ProcessName" ).InnerText;
string MachineName = RowNode.SelectSingleNode ( "MachineName" ).InnerText;

string MachineFolder = Path.Combine ( this.LogFolder, MachineName );

if ( !Directory.Exists ( MachineFolder ) )
{
try
{
Directory.CreateDirectory ( MachineFolder );
}
catch ( Exception Ex )
{
this.Log ( "Run", Ex.ToString () );
return;
}
}

if ( System.Diagnostics.Debugger.IsAttached )
{
Console.WriteLine ( "Processing message from {0}:{1}", MachineName, ProcessName );
}

string FileName = Path.Combine ( MachineFolder, ProcessName + ".log" );

try
{
this.CheckLog ( MachineName, FileName );
}
catch ( Exception Ex )
{
return;
}

using ( StreamWriter SW = new StreamWriter ( FileName, true ) )
{
SW.WriteLine ( string.Format ( "{0} {1}", RowNode.SelectSingleNode ( "LogTime" ).InnerText, RowNode.SelectSingleNode ( "ProcessMessage" ).InnerText ) );
}
}
}
}
}
}
catch ( SqlException Ex )
{
this.m_Callback ( Ex.Message );
}
catch ( Exception Ex )
{
this.m_Callback ( Ex.Message );
}
}

private void CheckLog ( string MachineName, string FileName )
{
FileInfo FI = new FileInfo ( FileName );

if ( FI.Exists )
{
DateTime Today = Convert.ToDateTime ( DateTime.Now.ToShortDateString () );

if ( FI.LastWriteTime < Today )
{
string ArchiveFileName = Path.GetFileName ( FileName ).Replace ( ".log", FI.LastWriteTime.ToString ( "yyyyMMdd" ) + ".log" );
string MachineFolder = Path.Combine ( this.ArchiveFolder, MachineName );

if ( !Directory.Exists ( MachineFolder ) )
{
Directory.CreateDirectory ( MachineFolder );
}

FI.MoveTo ( Path.Combine ( MachineFolder, ArchiveFileName ) );
}
}
}
}
}

The important thing to remember about Windows Services is that they MUST return quickly from the Start command.

Hope this helps.

Ed
Post #843452
Posted Monday, January 11, 2010 8:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:18 AM
Points: 29, Visits: 162
Thank you Ed.
Post #845417
Posted Friday, December 17, 2010 5:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:36 AM
Points: 19, Visits: 86
Sorry, I haven't been paying a lot of attention.

The first thing I notice is that you have all of your code in the OnStart event handler. This won't work as the SCM is expecting a quick response. The best way is to create a thread to handle the work and return immediately.

I had to completely re-write the service I use. I no longer use Service Broker (probably due to a lack of experience, I had some issues). I also had to create a separate thread for each process being logged. I then had memory issues, so I used a series of queues and queue managers, which solved the problem.

If you would like to get a zip of what I ended up doing, please, let me know.
Post #1036809
Posted Monday, December 20, 2010 7:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:18 AM
Points: 29, Visits: 162
Thank you for the follow up. I will have to set aside some time to get this working. Thanks for all your effort.
Post #1037189
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse