Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SYSFILES error with dbname

Author  Topic 

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-04 : 10:18:46
I have databse called RM-A1 and a log of RM-A1.log

When i run this statement it errors.

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'
--This works
select * from dbo.sysfiles

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


I cannot determine why log name of '-' are erroring all other databases are working.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 10:30:42
Try enclosing DB name in square brackets:

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'


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-04 : 14:34:06
Hmm no did not work.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-05 : 23:03:53
What was error message?
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-06 : 09:44:28
Invalid object name sysfiles

Msg 208, Level 16, State 1, Line 1
Invalid object name 'RM-A1_log.dbo.sysfiles'.

But i can run the select * form sysfiles and it appears.

Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-06 : 11:10:36
your DBname is [RM-A1]
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-06 : 18:55:12
DB is RM-A1 it was defined by the software installed not something i would have chosen.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-06 : 21:17:56
That's fine, but why you query RM-A1_log.dbo.sysfiles not RM-A1.dbo.sysfiles?
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-07 : 09:37:45

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
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 '-'.

Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-07 : 09:39:07
Use bracket for Database
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-07 : 16:04:03
Tried


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 = '2'

and (RM-A1_log).etc

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-07 : 16:07:47
TRACEYSQL, you said your database is named RM-A1, yet you keep using RM-A1_log in your query, but that's the log file name! Use [RM-A1].

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-07 : 17:32:07
Create any database - i.e TEST-ME


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 = '2'

The fileid 2 is the LOG from sysfiles and fileid 1 = MDF

Even if i do
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].dbo.sysfiles where fileid = '1'

(Notice the 1 and 2)

Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-07 : 17:36:51
Just to make it easy CREATE A DATABASE CALLED TEST-ME

Run this

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'

Errors
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-07 : 17:56:23
You can't use TEST-ME_log as a database name here: [TEST-ME_log].dbo.sysfiles

You must use TEST-ME everywhere:

---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].dbo.sysfiles where fileid = '2'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-07 : 20:42:33
Hmm that interesting been using this script for ever specifying the
log name or mdf name - kind of no point just use databasename
RM-A1 not RM-A1.mdf or RM-A1_log.ldf as the '1' = MDF and '2' = LDF


Cool your right i could not see it for looking.

Thanks

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-07 : 21:11:16
You can't refer to either the MDF or the LDF file in your queries. But it just so happens that your MDF and sometimes your LDF have been equivalent to the database name, hence you not getting an error until now.

The naming convention that you can use is: DatabaseName.ObjectOwner.ObjectName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-08 : 14:06:29
Thanks - thats why i couldn't understand.
Go to Top of Page
   

- Advertisement -