Solved

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

Posted on 2014-07-18
6
183 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
ID: 40211994
INSTEAD OF INSERT should be more efficient and is a great choice if you can use it.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now