Database Names With Hyphens Issue

Question

Post reply

Database Names With Hyphens Issue

JC-3113

SSCrazy Eights

Points: 8371
More actions
August 27, 2009 at 12:27 pm

#225540

Hi Folks
can someone tell me why I am getting an error on this sql when i try it with a database name with "-" in it ?
use MASTER;
go
select
CAST (a.name as varchar(66)) as database_name,
a.dbid
from
sys.sysdatabases a
where
a.name like 'QA_WSS%'
order by
a.name;
go
Output:
database_name dbid
------------------------------------------------------------------ ------
QA_WSS_Content_Mysite 19
QA_WSS_Content_SSP 18
QA_WSS_Search_NSAB-SS79-SPC-N 16
QA_WSS_Search_NSAB-SS82-SPF-N 17
select name from QA_WSS_Content_Mysite.dbo.sysfiles;
go
Output:
name
--------------------------
QA_WSS_Content_Mysite
QA_WSS_Content_Mysite_log
select name from QA_WSS_Search_NSAB-SS82-SPF-N.dbo.sysfiles;
go
Output:
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Thanks
Jim
I

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLDCH SSChampion Points: 11630 More actions · Answer 1

Try putting brackets around the database name:

select name from [QA_WSS_Search_NSAB-SS82-SPF-N].dbo.sysfiles;

go

SQL no likey the dash, probably sees it as a minus sign.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Thanks Grasshopper

that worked 🙂

wil i also have to use brackets in any alter database commands or any other commands for that matter ?

jim

GSquared SSC Guru Points: 260824 More actions · Answer 3

JC (8/27/2009)
Thanks Grasshopper
that worked 🙂
wil i also have to use brackets in any alter database commands or any other commands for that matter ?
jim

Yes.

This article on MSDN has the rules on table names and how you have to use them:

http://msdn.microsoft.com/en-us/library/ms175874.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon