July 16, 2012 at 1:20 pm
Database: timetracking
Column: project varchar(50)
Records are inserted from a SharePoint/InfoPath form
//script 1
select distinct project, len(project) as lproj
from timetracking
group by project
// end script
Script will list projects and the actual length of the string.
If you extract the table using the External Data feature of Excel 2007, the project column will return with some project values space-filled to 50 characters in a (so far apparently) random fashion. What happens is that there will be "project A" and "project A " which, when you try to summarize with a pivot table produces two rows.
//script 2
update timetracking
set project = RTRIM(project)
// end script
After running script 2, script 1 returns the same thing but Excel will have all values at the same (not space filled) length.
Questions:
1. What in the world???
2. How would I craft a trigger to just RTRIM everything on the way in?
Thanks
Donald
July 16, 2012 at 1:39 pm
Without knowing how you are accessing the data via Excel, it is really hard to give any advice or thoughts. Of course, it is very possible that the projects getting inserted into the table are free text in the sense that allows human error, instead of using a drop-down to guarantee consistency.
Jared
CE - Microsoft
July 16, 2012 at 1:56 pm
Donald Bustell (7/16/2012)
Database: timetracking
Column: project varchar(50)
Questions:
1. What in the world???
2. How would I craft a trigger to just RTRIM everything on the way in?
Thanks
Donald
CREATE TRIGGER dbo.project_trg_ins
ON dbo.project
AFTER INSERT
AS
UPDATE p
SET
p.project = RTRIM(p.project)
FROM dbo.project p
INNER JOIN inserted i ON
i.<key_col> = p.<key_col> --<<-- chg <key_col> to actual unique row id
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 16, 2012 at 2:01 pm
ScottPletcher (7/16/2012)
Donald Bustell (7/16/2012)
Database: timetracking
Column: project varchar(50)
Questions:
1. What in the world???
2. How would I craft a trigger to just RTRIM everything on the way in?
Thanks
Donald
CREATE TRIGGER dbo.project_trg_ins
ON dbo.project
AFTER INSERT
AS
UPDATE p
SET
p.project = RTRIM(p.project)
FROM dbo.project p
INNER JOIN inserted i ON
i.<key_col> = p.<key_col> --<<-- chg <key_col> to actual unique row id
Doesn't really get to the source though, does it. The procedure or code that inserts the data should be modified to stop this from happening.
Jared
CE - Microsoft
July 16, 2012 at 2:18 pm
SQLKnowItAll (7/16/2012)
ScottPletcher (7/16/2012)
Donald Bustell (7/16/2012)
Database: timetracking
Column: project varchar(50)
Questions:
1. What in the world???
2. How would I craft a trigger to just RTRIM everything on the way in?
Thanks
Donald
CREATE TRIGGER dbo.project_trg_ins
ON dbo.project
AFTER INSERT
AS
UPDATE p
SET
p.project = RTRIM(p.project)
FROM dbo.project p
INNER JOIN inserted i ON
i.<key_col> = p.<key_col> --<<-- chg <key_col> to actual unique row id
Doesn't really get to the source though, does it. The procedure or code that inserts the data should be modified to stop this from happening.
Yes, that would be ideal. Sometimes though that can take a long time.
Even when changing the original INSERT code, you would likely want the trigger as a fail-safe anyway, so might as well create it now.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 16, 2012 at 2:50 pm
ScottPletcher (7/16/2012)
SQLKnowItAll (7/16/2012)
ScottPletcher (7/16/2012)
Donald Bustell (7/16/2012)
Database: timetracking
Column: project varchar(50)
Questions:
1. What in the world???
2. How would I craft a trigger to just RTRIM everything on the way in?
Thanks
Donald
CREATE TRIGGER dbo.project_trg_ins
ON dbo.project
AFTER INSERT
AS
UPDATE p
SET
p.project = RTRIM(p.project)
FROM dbo.project p
INNER JOIN inserted i ON
i.<key_col> = p.<key_col> --<<-- chg <key_col> to actual unique row id
Doesn't really get to the source though, does it. The procedure or code that inserts the data should be modified to stop this from happening.
Yes, that would be ideal. Sometimes though that can take a long time.
Even when changing the original INSERT code, you would likely want the trigger as a fail-safe anyway, so might as well create it now.
The trigger probably would not fix the problem if it is being inserted into the table with a bulk method, like BCP or BULK INSERT.
July 16, 2012 at 3:02 pm
Michael Valentine Jones (7/16/2012)
ScottPletcher (7/16/2012)
SQLKnowItAll (7/16/2012)
ScottPletcher (7/16/2012)
Donald Bustell (7/16/2012)
Database: timetracking
Column: project varchar(50)
Questions:
1. What in the world???
2. How would I craft a trigger to just RTRIM everything on the way in?
Thanks
Donald
CREATE TRIGGER dbo.project_trg_ins
ON dbo.project
AFTER INSERT
AS
UPDATE p
SET
p.project = RTRIM(p.project)
FROM dbo.project p
INNER JOIN inserted i ON
i.<key_col> = p.<key_col> --<<-- chg <key_col> to actual unique row id
Doesn't really get to the source though, does it. The procedure or code that inserts the data should be modified to stop this from happening.
Yes, that would be ideal. Sometimes though that can take a long time.
Even when changing the original INSERT code, you would likely want the trigger as a fail-safe anyway, so might as well create it now.
The trigger probably would not fix the problem if it is being inserted into the table with a bulk method, like BCP or BULK INSERT.
If that's the case, they should explicitly force the trigger to fire on the BCP or BULK INSERT command.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 16, 2012 at 3:24 pm
I didn't build the InfoPath front end and having never run it I have no idea if it is putting the rows in one at a time or what; I suspect it might be a "bulk type" insert because you build your timecard and then hit "submit".
The project fields are populated with a drop-down which is pulled from another SQL table (in Great Plains accounting) so there is no way a user can mistype.
The weird part is that the space filling does not have a particular pattern: not every user, not every week, not particular projects - just every now and then.
I've burned enough time trying to spot patterns let alone trying to figure out what is going on in InfoPath -> SQL Server -> Excel.
The Excel extraction is just done using the 'External Data' button/wizard on the Data ribbon.
I'll give these triggers a try and see what happens.
Thanks for your thoughts and suggestions.
Donald
July 16, 2012 at 3:25 pm
ScottPletcher (7/16/2012)
Michael Valentine Jones (7/16/2012)
ScottPletcher (7/16/2012)
SQLKnowItAll (7/16/2012)
ScottPletcher (7/16/2012)
Donald Bustell (7/16/2012)
Database: timetracking
Column: project varchar(50)
Questions:
1. What in the world???
2. How would I craft a trigger to just RTRIM everything on the way in?
Thanks
Donald
CREATE TRIGGER dbo.project_trg_ins
ON dbo.project
AFTER INSERT
AS
UPDATE p
SET
p.project = RTRIM(p.project)
FROM dbo.project p
INNER JOIN inserted i ON
i.<key_col> = p.<key_col> --<<-- chg <key_col> to actual unique row id
Doesn't really get to the source though, does it. The procedure or code that inserts the data should be modified to stop this from happening.
Yes, that would be ideal. Sometimes though that can take a long time.
Even when changing the original INSERT code, you would likely want the trigger as a fail-safe anyway, so might as well create it now.
The trigger probably would not fix the problem if it is being inserted into the table with a bulk method, like BCP or BULK INSERT.
If that's the case, they should explicitly force the trigger to fire on the BCP or BULK INSERT command.
Yes, but if they have the option of changing the load procedure, then they might as well fix that to trim the trailing spaces, instead of creating a trigger to fix what the load procedure should be doing.
July 16, 2012 at 3:42 pm
Again, in theory yes.
But in the real world that's often difficult to do and takes a lot of time. And often a change later to the code by someone not familiar with the issue recreates the problem.
Besides, I would think you would *still* want the fail-safe code in place just in case the load had that error again.
All in all, I say it's safer to rely on the 100% trigger than on the iffy idea of correcting *all* data loads *all* the time.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 16, 2012 at 4:36 pm
Donald Bustell (7/16/2012)
I didn't build the InfoPath front end and having never run it I have no idea if it is putting the rows in one at a time or what; I suspect it might be a "bulk type" insert because you build your timecard and then hit "submit".The project fields are populated with a drop-down which is pulled from another SQL table (in Great Plains accounting) so there is no way a user can mistype.
The weird part is that the space filling does not have a particular pattern: not every user, not every week, not particular projects - just every now and then.
I've burned enough time trying to spot patterns let alone trying to figure out what is going on in InfoPath -> SQL Server -> Excel.
The Excel extraction is just done using the 'External Data' button/wizard on the Data ribbon.
I'll give these triggers a try and see what happens.
Thanks for your thoughts and suggestions.
Donald
The wizard has several options, and these can change how the data gets to Excel. However, it seems that an update fixes it, so I believe that the issue is with the data itself and not the retrieval method. I believe there is a function in Excel to fix this as well, but it is best to change your data so that it is correct. Personally, I would run a trace and see if I could find a procedure inserting this data and do the RTRIM(LTRIM()) there. I'm not a fan of triggers of the problem can be fixed easier somewhere else. However, if it is too difficult to change at the source, a trigger may be the better option.
Jared
CE - Microsoft
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply