Solved

IIS 6 - logging to SQL Server - trailing spaces in fields

Posted on 2014-07-18
6
188 Views
Last Modified: 2014-07-22
I have followed the instructions at the folllowing link to enable IIS's logging to be recorded on our SQL server:
http://support.microsoft.com/kb/245243

After a few false starts, messing about with logins and a restart of the www service, its finally working.

However, many of the field values are stored with loads of trailing spaces.  Many of the fields are Varchar(255) but the values are padded out with spaces.
Is there any way of either avoiding the trailing spaces in the first place, or failing that, a clean get around?
0
Comment
Question by:Beamson
  • 2
  • 2
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40204340
The T-SQL answer would be to use RTRIM() for every field that you suspect has trailing spaces:
Declare @value varchar(255) = 'banana                        '
SELECT RTRIM(@value) + 'with no trailing spaces'

Open in new window

A case can be made that whatever table has fields with trailing spaces has some quality issues, and a one-time update should be done to remove them.
0
 
LVL 1

Author Comment

by:Beamson
ID: 40204392
Thanks for that.

I'm aware that I could periodically run an UPDATE routine which does this but a lot of the queries I'd want to make on the data are for events which may have happened only in the last few minutes.  The requirement to seperately UPDATE the data before querying it for my purposes is not ideal.

Is there a way that the data can be RTRIM'ed at the moment that IIS does the table insert?
I don't know much about TRIGGER operations but this sort of seems to be the right area.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40204400
SSIS - Yes, IIS - Don't know.  

One possibility, not abundantly ideal but it would work, would be to add an insert trigger into this table to remove trailing spaces.

I'll back out of the question to see if any IIS experts can respond.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40205092
I strongly suggest using a trigger.  You want to get that extra space removed from the page before the INSERT is "hardened" (committed).  

You need to add an identity column to the table to use a key, and a clustered index on that column (or nonclustered index if you really prefer).  Note that adding a clustered index will require the entire table to be re-written once.

Correct the table name as required in the code below:

ALTER TABLE dbo.tablename ADD ident int IDENTITY(1, 1) NOT NULL
CREATE UNIQUE /*NON*/CLUSTERED INDEX tablename__CL ON dbo.tablename ( ident ) WITH ( FILLFACTOR = 100 )


CREATE TRIGGER tablename__TRG_Insert
ON dbo.tablename
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE tn
SET
    ClientHost = RTRIM(ClientHost),
    Username= RTRIM(Username),
    [Service] = RTRIM([Service]),
    ...
FROM dbo.tablename tn
INNER JOIN inserted I ON
    i.ident = tn.ident
GO --end of trigger
0
 
LVL 1

Author Closing Comment

by:Beamson
ID: 40211216
I went for a similar triggger solution using INSTEAD OF INSERT, rather than AFTER INSERT and then just a simple INSERT INTO myTable Rtrim(field1), Rtrim(field2)... etc.  Seems to work but don;t know if its more/less/equally efficient as the proposed one.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40211994
INSTEAD OF INSERT should be more efficient and is a great choice if you can use it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Prologue It is often required to host multiple websites on a single instance of IIS, mostly in development environments instead of on production servers. I am sure it is not much a preferred solution on production servers but this is at least a pos…
Lync server 2013 or Skype for business Backup Service Error ID 4049 – After File Share Migration
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question