|
How can I import the Message Manager Deluxe log into Microsoft SQL 2005 or 2008?
This tutorial will walk you through the steps to create a nightly automated task within Microsoft SQL 2005 or 2008 to import the Message Manager Deluxe log file into a database table.
This tutorial makes several assumptions:
- You have a general knowledge of the SQL Server Management Studio
- You have administrator rights to the SQL Server
- SQL Server Agent service is started and running
Open the Message Manager Deluxe message editor and review the settings for your message. Click on the Logging tab and choose to Create a Log file. This log file created will be what is used to import into your SQL database. You must select a shared server location that each user has read/write access to, as well as the SQL Server machine has read/write access to. As seen in the screenshot, you must select the option to use the same file name for everyone. The server path and file name you specify here will be used later in this tutorial.
Click on the Log Contents tab and select what information you want stored in the log file. This information must match the fields in the SQL Table that you are going to create later.
This is an example of the log output to a comma-delimited log file, similar to the file your users will automatically create when running Message Manager Deluxe.
Next you will need to open the SQL Server Management Studio for the SQL Server you want to import the Message Manager Deluxe log into. You need to create a new SQL Database to contain the message log information. You can do this many ways, we are not going to go into details here about how to create a SQL Database. In our tutorial here we have called our database "message_log".
Next you need to create a SQL Table that contains all of the fields that you are logging to your Message Manager Deluxe log file from the Log Contents tab. Below is an example SQL Query that can create this table for you.
USE [message_log]
GO
CREATE TABLE [dbo].[messages](
[TheDate] [nvarchar](15) NULL,
[TheTime] [nvarchar](15) NULL,
[Username] [nvarchar](50) NULL,
[MachineName] [nvarchar](50) NULL,
[MessageName] [nvarchar](255) NULL,
[ButtonClicked] [nvarchar](50) NULL,
[IPAddress] [nvarchar](50) NULL
) ON [PRIMARY]
GO
You can create indexes on this data to speed up the queries, but we are not going to describe that in this basic tutorial. Just understand that retrieving the data can be optimized if need be.
For our tutorial we are going to use the SQL Server Agent's Job functionality to schedule the automatic nightly import of the Message Manager Deluxe log file into SQL Server. Under the SQL Server Agent we are going to create a New Job, this job is going to handle the import and renaming of our file.
Right-click on on the Jobs folder under the SQL Server Agent and choose New Job. You will be presented with a screen similar to this.
Give this job a descriptive name and select the Owner of this job. Note: The owner is a SQL Server us that must have rights to execute this job. In our example 'administrator' is the 'sa' user.
On the left pane you can click on Steps. This allows you to view the steps of the job you are scheduling. Click on the New button to create a new step.
For the step name, give it something that describes what the step is going to perform. In this case, we are going to do a Bulk Import of the Message Manager Deluxe log file into the SQL Server table we created above. Select the database you want to issue the command on, in this case we are using our message_log database we created above
The following SQL will do the import into the messages table, from the log file. Note: you must modify the FROM command to be the UNC path of your Message Manager Deluxe log file.
BULK
INSERT messages
FROM '\\servername\message.log' ** CHANGE THIS PATH
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2 --asuming there is a header, if not use 1
)
GO
UPDATE messages
SET thedate = REPLACE(thedate, '"', '),
thetime = REPLACE(thetime, '"', '),
username = REPLACE(username, '"', '),
machinename = REPLACE(machinename, '"', '),
messagename = REPLACE(messagename, '"', '),
buttonclicked = REPLACE(buttonclicked, '"', '),
ipaddress = REPLACE(ipaddress, '"', ')
GO
Click OK to save this job step.
Next we need to create a step that will rename the message.log file created to include the date. Click New to create a new job. Again, give it a descriptive job name. Select the database you want to issue the command on, in this case we are using our message_log database we created above.
The command we are running is a master.xp_cmdShell command that will shell out to DOS and execute the rename command. Here is the example SQL:
declare @winCmd varchar(400)
set @winCmd = 'rename c:\message.log message.' + convert(varchar, getdate()-1, 2) + '.log'
exec master..xp_cmdShell @winCmd
You might get an error like this:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server.
If you do, you need to turn on permissions to execute the cmd shell. You can do this by running this ONCE:
Exec Master.dbo.Sp_Configure 'Show Advanced Options', 1
Reconfigure
Exec Master.dbo.Sp_Configure 'XP_CmdShell', 1
Reconfigure
Click OK to save this step. Next we want to schedule this job to run nightly. Click on Schedules to configure this. Click on New to create a new schedule.
Give this schedule a descriptive name. Set the frequency to occur Daily. For our example it will run at 1am and import all of the data in the log and rename it. You might have to change this time based on your needs. Click OK to save this schedule.
There are other options for notifications and alerts for when this scheduled job runs, but we are not going into those in detail for this tutorial.
Click OK to save this Job. It should show up in your list of Jobs under the SQL Server Agent.
We need to test this job to ensure that it is going to work. A simple test would be to create a message.log file and store it on the server where the job is looking for the file (or your can use a production file).
To Test this job we are going to manually kick off the job from the SQL Server Agent. Right-click and choose Start Job at Step. This allows you to select which Step to start. In our tutorial we want to start on Step 1 to ensure the data is imported into the table for step 1 and step 2 also renames the file.
Once you click Start on this it will run the import and the rename. You can see a status of the job run in the SQL Starts Jobs window.
When this is successful you will need to verify it did what we've told it to do. Verify the message.log file is renamed to message.[date].log
Verify the messages table has the data you are looking for. You can execute this query:
SELECT * FROM messages
If it was successful you will see your Message Manager Deluxe log file in the columns in SQL Server.
|
Additional Information
