SQL Server - Strange things do happen, but why are they allowed in the first place - THE ANSWER

Who is online?  216 guests and 0 members
home  »  blogs  »  Amit Bansal  »  SQL Server - Strange things do happen, but why are they allowed in the first place - THE ANSWER
  Rate This Blog Entry:  register  or  login


AmitBansal 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

Comments (11)

12/3/2012 10:14:07 AM Vipul said:


Marc Jellinek
12/3/2012 2:26:01 PM Marc Jellinek said:

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 [1].[n] FROM T0 [1], T0 [2]),		--4
	T2	AS	(SELECT [1].[n] FROM T1 [1], T1 [2]),		--16
	T3	AS	(SELECT [1].[n] FROM T2 [1], T2 [2]),		-- 256
	[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

Marc Jellinek
12/3/2012 2:33:27 PM Marc Jellinek said:

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.

12/4/2012 8:48:58 PM Ken said:

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.

12/4/2012 9:12:28 PM Ken said:
Just goes to show first responses aren't best. This would work if only one DB would be caught and can be executed as many times as you like with any kind of special characters used:
DECLARE @db sysname
SELECT @db=name from sys.databases WHERE name LIKE N'HOC%BASE'
IF (@@ROWCOUNT > 0) EXEC sp_renamedb @db, 'PRODHOCBASE'
12/5/2012 4:40:16 AM Amit Bansal said:

Ken & Mark, thanks for the responses. Good inputs !

12/5/2012 4:45:30 AM Amit Bansal said:

Ken, i tried your solution. works good. nice learning here. Thanks again !

12/7/2012 7:36:49 PM Piyush Bajaj said:

Great Sir, i like it :)

1/2/2013 10:09:51 PM Karthick said:

changing the SSMS grid mode to text also will help you to find out the new line breakers

select 'hoc

1/3/2013 6:16:12 PM George said:

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.

4/25/2013 11:06:11 AM Moel said:

Hello world,

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

Email:  (your email is kept secure)

example: "http://www.SQLServerGeeks.com"

Type the characters:

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Amit Bansal's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Hi Friends, In this blog post, let’s have an insight on how Auditing can be done in SSIS packages? The basic auditing in package includes measures like How many rows were inserted, updated or de...
  • Hi SQL Geeks, Here are the blog posts by Piyush Bajaj for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at ht...
  • Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of May. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at htt...
  • Hi Friends, Are you facing any issue on SQL Server 2014 while running the same query which is running fine on previous version of SQL Server? If answer is yes then this may be due to the change in SQL...
  • I have written a SQL Server Change Data Capture GUI interface to facilitate for CDC operations. The project is hosted here https://sqlcdcapp.codeplex.com/ . Feel free to download, review and suggest c...
  • Recently I came across an interesting deadlock scenario I wasn’t aware off. I didn’t have idea that foreign key constraint can also result in deadlocks. The detailed steps to replicate the...
  • Hi Friends, In my earlier blog, I just explain about the importance of filtered indexes. Link for that blog is mention below: http://sqlservergeeks.com/blogs/princerastogi/personal/599/filtered-indexe...
  • A few words about the technology of our site. It's easy to take technology for granted, to focus on content and pay no attention to how it is being delivered. Typically we would encourage this focus. ...