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.logWhen 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 worksselect * from dbo.sysfilesIm 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 OUTPUTI 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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-04 : 14:34:06
|
Hmm no did not work. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-05 : 23:03:53
|
What was error message? |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-06 : 09:44:28
|
Invalid object name sysfilesMsg 208, Level 16, State 1, Line 1Invalid object name 'RM-A1_log.dbo.sysfiles'.But i can run the select * form sysfiles and it appears. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-06 : 11:10:36
|
your DBname is [RM-A1] |
|
|
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. |
|
|
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? |
|
|
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) endfrom RM-A1.dbo.sysfiles where fileid = '1'Msg 102, Level 15, State 1, Line 3Incorrect 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) endfrom RM-A1_log.dbo.sysfiles where fileid = '2'Msg 102, Level 15, State 1, Line 3Incorrect syntax near '-'. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-07 : 09:39:07
|
Use bracket for Database |
|
|
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 |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-07 : 17:32:07
|
Create any database - i.e TEST-MEselect 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 = MDFEven if i doselect 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) |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-07 : 17:36:51
|
Just to make it easy CREATE A DATABASE CALLED TEST-MERun this USE TEST-MEselect * from sysfiles will give youTEST-METEST-ME_logNow run this---MDFselect 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'--LDFselect 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 |
|
|
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.sysfilesYou must use TEST-ME everywhere:---MDFselect 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'--LDFselect 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
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 databasenameRM-A1 not RM-A1.mdf or RM-A1_log.ldf as the '1' = MDF and '2' = LDFCool your right i could not see it for looking.Thanks |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-04-08 : 14:06:29
|
Thanks - thats why i couldn't understand. |
|
|
|