Database Names With Hyphens Issue

  • 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

  • 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.
  • Thanks Grasshopper

    that worked 🙂

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

    jim

  • 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

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

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