12/3/2012 5:36:04 AM
Amit Bansal -
Apologies for getting back late on this, I was buried under some assignments clubbed with hectic travel schedule. So, sometime back I had posted this to be solved by you…
Many of you commented on the original post which is here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/685/sql-server-strange-things-do-happen-but-why-are-they-allowed-in-the-first-place
And you all gave me interesting tips including Refreshing SSMS, checking for preceding space in from the db name, not to use sp_renamedb, checking the connection, checking permissions, etc. And trust me, even I had looked into all of them and many more when I encountered this for the first time.
But what helped me was to check sys.databases system catalog and PM Shawn (one of the readers) suggested that. But still it was tricky. This is why…
So when I executed select * from sys.databases, I did get HOCBASE listed as follows:
And you can observe that there is a space but SSMS object explorer does not show that.
As I said before things were trickier than it appeared. Knowing that there is a space, I executed sp_renamedb again and explicitly put a space.
Things didn’t work. It was a bit of brain pain before I tried this:
And it worked :)
Yes, there was a CRLF (ENTER) between HOC & BASE. But how did that happen? A Junior DBA who believes he is the fastest on keyboard did this :) – and I call this pace without accuracy ;) – so while renaming the DB, he mistakenly pressed ENTER and executed it. He was fast enough not to notice what he was doing.
There could be other ways of identifying that there was a CRLF in the database name but I did not know of any and sys.databases showed a space. If there is a way you know, do let the readers know by commenting.
Hope you enjoyed this :)
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Have a SQL Server question? Join the fastest growing SQL Server group on FaceBook - http://www.facebook.com/groups/458103987564477/Regards,
Visit my FaceBook page at http://www.facebook.com/AmitRSBansalContribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
Amit Bansal (Member since: 3/12/2011 4:59:54 PM)
Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions.
FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346
LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
It's pretty easy to parse a column of strings, and determine the ASCII value of each character. The characters for CRLF are 13 and 10. Here's an example using [sys].[databases]
;WITH -- generate a quick tally table. Better to use one that already exists, but this will do for demo purposes
T0 AS (SELECT 0 as [n] UNION ALL SELECT 0 ), --2
T1 AS (SELECT .[n] FROM T0 , T0 ), --4
T2 AS (SELECT .[n] FROM T1 , T1 ), --16
T3 AS (SELECT .[n] FROM T2 , T2 ), -- 256
T AS (SELECT ROW_NUMBER() OVER (ORDER BY .[n]) [n] FROM T3 )
[db].[name] as [database_name],
SUBSTRING([db].[name], [t].[n], 1) as [letter],
ASCII(SUBSTRING([db].[name], [t].[n], 1)) as [ascii]
[sys].[databases] [db], [t]
LEN(SUBSTRING([db].[name], [t].[n], 1)) > 0
Perhaps in the future you should deploy a DDL trigger that prevents database names from using unprintable characters. Just listen for the CREATE_DATABASE and ALTER_DATABASE events, parse out the name from EVENTDATA() and if the name has an unprintable character, just rollback. This will prevent database names from having unprintable characters. Permit only ASCII character values 48-57 (characters 0-9), 65-90 (characters A-Z) and 97-122 (a-z).
Then it doesn't matter how fast your DBA types.
You don't have to go to such extremes as listing all the ASCII values of all the letters. When using SSMS to display results in a grid, all special ascii characters are converted to a space when you display as a grid. Display as text will give you the exact result. Even a carriage return without a linefeed would show up, but you'd have to use special character techniques to copy it. This would have solved the original problem by using a straight copy/paste:
SELECT '''' + name + '''' FROM sys.databases WHERE name LIKE 'HOC%BASE'
That's assuming you didn't put a "'" in the name.
Ken & Mark, thanks for the responses. Good inputs !
Ken, i tried your solution. works good. nice learning here. Thanks again !
Great Sir, i like it :)
changing the SSMS grid mode to text also will help you to find out the new line breakers
Strange things happen for certain. I had just read this article yesterday and was creating a linked server connection today. I tested the connection using the 4 part name to the database that would be queried as standard due diligence before giving the green light to the end user (even though the test connection succeeded and the catalog was there). It failed. After re-checking everything required, this article recalled to mind. Sure enough, a previous DBA had added a space at the end of the database name. A simple [dbname ] change to my query later and success.
Ken's suggested format of SELECT '''' + name + '''' FROM sys.databases WHERE name LIKE 'HOC%BASE' (using a truncated 'dbname%') helped to confirm that a space existed easily.
if i understand well youre case why you dont try simply to rename your database by right clik and rename its easy fast and effective :)
Leave a comment