Solved

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

Posted on 2014-07-18
6
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HTTP to HTTPS but have 2 sub sites 8 47
Help Required 2 56
PHP 5.6 and 7.x 4 51
Substring works but need to tweak it 14 35
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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