IIS 6 - logging to SQL Server - trailing spaces in fields
I have followed the instructions at the folllowing link to enable IIS's logging to be recorded on our SQL server:
http://support.microsoft.c
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?
http://support.microsoft.c
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?
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
INSTEAD OF INSERT should be more efficient and is a great choice if you can use it.
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.