November 27, 2007 at 5:28 am
Hello all, I have this script and I can't see for looking where the fault its.
its in the "if exists" statement.. i just can't see why its saying invalid object Sys.Databases
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.databases'.
Any help would be greatly apprecieated!
Dave
use master
go
declare
@UseDate as varchar(50),
@dbname as varchar(50),
@mycommand as varchar(2000)
set @usedate = replace(LEFT(CONVERT(VARCHAR(19), getdate()-3, 120),10),'-','')
set @dbname = 'NAVTEMP'+@usedate
--print @dbname
set @mycommand = 'if exists(select * from sys.databases where name = '''+@dbname+''') drop database ' + @dbname
--print @mycommand
Exec(@mycommand)
go
November 27, 2007 at 5:33 am
Are you on SQL 2005
November 27, 2007 at 5:37 am
bah.. sorry, i've stuck this into the wrong group..
Dear admin, please could you move this post into backup / scripting section.. ta
November 27, 2007 at 5:37 am
yeah sorry..
windows 2003 std
sql 2000 std
cheers
Dave
November 27, 2007 at 5:42 am
Use sysdatabases table
--Ramesh
November 27, 2007 at 5:55 am
your an absolute star Ramesh..
many thanks.
Dave
November 28, 2007 at 3:54 am
Hey.... guess I hit someone's "sweet" spot.:D:D
--Ramesh
November 28, 2007 at 4:30 am
The Script is working without any error for me.
use master
go
declare
@UseDate as varchar(50),
@dbname as varchar(50),
@mycommand as varchar(2000)
set @usedate = replace(LEFT(CONVERT(VARCHAR(19), getdate()-3, 120),10),'-','')
set @dbname = 'NAVTEMP'+@usedate
print @dbname
set @mycommand = 'if exists(select * from sys.databases where name = '''+@dbname+''') drop database ' + @dbname
Exec(@mycommand)
go
November 28, 2007 at 4:34 am
In SQL 2005 you can use sys.databases or sysdatabases
On 2000 you only have sysdatabases
I think.
November 28, 2007 at 4:35 am
Hey, you are still using sys.databases...
--Ramesh
November 28, 2007 at 4:41 am
Yes I think sysdatabases in 2005 is implemented as a view on sys.databases
from 2005 BOL
This Microsoft SQL Server 2000 system table is included as a view for backward compatibility.
November 28, 2007 at 7:46 pm
[nitpick] Why do 'select *'? Given you are just dropping the databases, why not do 'select name' instead? Adhere to principal of 'only get what you really need' [/nitpick]
😛
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply