Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-07-18
6
Medium Priority
?
227 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 66

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 66

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 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 70

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Screencast - Getting to Know the Pipeline
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 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