Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logging with SQL Server


Logging with SQL Server

Author
Message
david.wright-948385
david.wright-948385
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 967
Looks like you're catching the most likely causes but ignoring them. Take out the try/catch blocks and look in the application log.
EdSwiedler
EdSwiedler
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 94
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.
EdSwiedler
EdSwiedler
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 94
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
Phillip - Texas
Phillip - Texas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 162
Thank you Ed.
EdSwiedler
EdSwiedler
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 94
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.
Phillip - Texas
Phillip - Texas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search