Solved

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

Posted on 2014-07-18
6
194 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
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.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

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.
If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 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