Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Red face Unanswered: spaces in DB names

    I created the stored procedure shown below with some help from a website over a year ago.
    -------------------------------------------------------------------------
    CREATE PROCEDURE CP_LCSQL_FileSizing AS
    DECLARE @dbid INT
    DECLARE @MaxId INT
    DECLARE @dbName SYSNAME
    SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)
    SET @dbid = 1
    WHILE @dbid <= @MaxId
    BEGIN
    SET @dbName = (SELECT name FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)
    IF (@dbname IS NOT NULL)
    BEGIN
    EXEC ('SET QUOTED_IDENTIFIER OFF
    insert into LCSQL_Statistics SELECT "' + @dbname +'" AS DB_Name,
    RTRIM(name) AS DB_DevName,
    RTRIM(filename) AS DB_Filename,
    RTRIM(size) AS DBSize, CURRENT_TIMESTAMP as Stat_Time FROM ' + @dbname + '.dbo.sysfiles')
    SET @dbid = @dbid + 1
    END
    ELSE
    SET @dbid = @dbid + 1
    END
    ---------------------------------------------------------------------------

    What this sp does is go through the the list of databases on the server, gets the name and size and stores the data in a table in the master database. It fires once a month so we can track data growth. Well we had a !@#$%^%&*(*())*&^%$%# consultant come in. He threw up a database on my server without my knowledge or consent that is named "LCI CTX Farm 1" (yes spaces in the name). He has his head so far up MS's A** that he can't understand why my sp blows up when it gets to his database name. Luckily at the moment it is the highest DBID in the list so I'm still getting data on all but his, but...

    I believe the problem is with the quoted identifier parameter, but I don't know how to beat this sp back into shape. Any suggestions? He is saying it will take hours to change everything to a new name.

    Jim P.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    try

    SET @dbName = (SELECT '[' + name + ']' FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Talking

    Originally posted by nigelrivett
    try

    SET @dbName = (SELECT '[' + name + ']' FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)

    That worked. Thanks for the assist.

    I'm now watching this consultant for anymore bonehead moves he makes. I shouldn't have to be cleaning up after this <individual> but I am.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •