DB name seems to not work

Last Post 07 Apr 2008 05:31 AM by TRACEYSQL. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
TRACEYSQL
New Member
New Member

--
06 Apr 2008 02:59 PM
I have a report that capture my space consumption daily.

Some software was installed and the databasename became RM-A1 not something i would have chosen but it was the software choice

So when i run below


My script is generated and executes for each database but this one it fails on

Im using a dynamic statement

set @cmd = N'select @ceil = ceiling((size * 8192.0)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
''MB '' + convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) '
+ 'else ''PER '' + convert (varchar, growth) '
+ 'end' + char(10)+char(13)
+ 'from ' + @l_db_name + '.dbo.sysfiles where fileid = ''1'''

exec sp_executesql @cmd, @retType, @cnt OUTPUT, @ceil OUTPUT


Witout the dynamic --

select ceiling((size * 8192.0)/(1024.0 * 1024.0)),
case when status & 0x100000 = 0 then
convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0)))
else convert (varchar, growth)
end + char(10)+char(13)
from RM-A1_log.dbo.sysfiles where fileid = '1'

It errors can't find in sysfiles

--This works
select * from dbo.sysfiles

But i tried [RM-AL_log].dbo.sysfiles but still not work.

Any suggestions


SwePeso
New Member
New Member

--
07 Apr 2008 03:51 AM
And it's a good practice to provide a PRINT statement before a dynamic EXEC.

PRINT @cmd
exec sp_executesql @cmd, @retType, @cnt OUTPUT, @ceil OUTPUT

to be sure what exactly is being executed.
TRACEYSQL
New Member
New Member

--
07 Apr 2008 05:31 AM
Databasename is RM-A1



print Error @cmd

--First it gets the MDF size

select @ceil = ceiling((size * 8192.0)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
'MB ' + convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) else 'PER ' + convert (varchar, growth) end
from RM-A1.dbo.sysfiles where fileid = '1'
Msg 102, Level 15, State 1, Line 3

---Then log size
Incorrect syntax near '-'.
select @ceil = ceiling((size * 8192.0)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
'MB ' + convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) else 'PER ' + convert (varchar, growth) end
from RM-A1_log.dbo.sysfiles where fileid = '2'
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '-'.


(All databases work but it is only when there is a '-' in the database name

select name from sysfiles

RM-A1
RM-A1_log (Log is here)
SwePeso
New Member
New Member

--
07 Apr 2008 05:53 AM
And putting brackets around the dbname doesn't help?

from [RM-A1].dbo.sysfiles where fileid = '1'
from [RM-A1_log].dbo.sysfiles where fileid = '2'
TRACEYSQL
New Member
New Member

--
07 Apr 2008 01:29 PM
Does not work with the brackets - bizarre.

You can created any database with TEST-ME and its the '-' im just wondering if this is reserved.
But the software created it with the '-' in it.

Kind of strange to me.
TRACEYSQL
New Member
New Member

--
07 Apr 2008 01:39 PM
just to make it easy

if you create new database and call this TEST-ME

Then

USE TEST-ME
select * from sysfiles will give you
TEST-ME
TEST-ME_log


Now run this
---MDF
select ceiling((size * 8192.0)/(1024.0 * 1024.0)),
case when status & 0x100000 = 0 then
convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0)))
else convert (varchar, growth)
end + char(10)+char(13)
from [TEST-ME].dbo.sysfiles where fileid = '1'

--LDF
select ceiling((size * 8192.0)/(1024.0 * 1024.0)),
case when status & 0x100000 = 0 then
convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0)))
else convert (varchar, growth)
end + char(10)+char(13)
from [TEST-ME_log].dbo.sysfiles where fileid = '2'


It is the LDF that errors and i cannot see why?
You are not authorized to post a reply.

Acceptable Use Policy