Follow-up from a previous SQL question - Invalid SQL query/view issue.
When last we met our intrepid hero on his journey to the mythical kingdom of "Stable App", he was deep in the untamed jungle running from invalid CF queries... now he faces his biggest challenge yet - the hall of invalid table names!
I've imported a bunch (few hundred tables, each with 1 to hundreds of columns) of data into SQL 2008. Apparently because of the horrid way we had to migrate data, some of the column names may now have trailing spaces. This was causing my view to fail. Removing the space fixed the issue.
Yes, I'm aware SQL 2008 says it ignores trailing spaces in column names. It might, but coldfusion flips its shit and errors out.
Oh lords of the Query - Is there a way to check for trailing spaces in tables names without manually checking each one?
Posts
This isn't directed at you per se, but to the person that designed your database
UR DOIN IT WRONG
In Oracle I'd use the DBA_TAB_COLUMNS view to do this. For MS SQL Server 2008, a combination of the syscolumns and sysobjects views should provide comparable info. You can run a query against a join of those views to see if any columns have trailing spaces in the name: That should list all columns that have trailing spaces in the column name, as well as the table each one is in. Those views may exist in some other schema than SYS, I don't know if the name of the user that owns those views can vary in SQL Server 2008.
If you need to check for leading and trailing spaces in column names, you can use LTRIM(RTRIM(c.name)).
Correct.
I'm going to play with that code. Thanks a ton.
Yeah, this is the second "How the hell did this happen?" question from you.
Either you need to fire the DBA you have, or hire one because you don't have one. Either way, these problems should not be happening. Like, ever.
That works except for the RTRIM. I can take out that last line and get enough to work with, but it seems very close. I've read SQL 2k8 doesn't care about trailing spaces, so I might have to do this manually with the results I get (which are super-fucking helpful, thank you. I owe you a beer).
Dude if I could, I so would. Hiring takes ~6 months to a year. This systems goes live inside two weeks. o_O
"other duties as assigned", you know the drill. Shit I'm a networking guy anyway. Always hated doing server admin. This is why. :x
I know little of ye colde fusione
If that still doesn't work, you can try replacing the RTRIM bit with a LIKE, as in JoeUser's query for tables:
You may have to use CAST for either method, I don't have access to a SQL Server 2008 system so I can't say for sure.
I'm thinking that if SQL 2k8 ignores the trailing spaces in column names like they say, then maybe both fields would parse the same. Thus no rows... but that makes no sense to me. Maybe there's a checkbox for "fuck with the server admin lol" in here somewhere.
Anyway the "AND c.name LIKE '% ': worked like a champ. Got a nice short list with all the bad column names. Thank you guys!