syntax error in 2008 not in 2000

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 2008 Forums

Transact-SQL (2008)

syntax error in 2008 not in 2000

Author

Topic

Chap
Starting Member

30 Posts

Posted - 2010-08-02 : 09:58:35

When running the following query in 2008 it returns a syntax error but in 2000 it runs fine:

declare d cursor for select name
from sysdatabases
where name not in ('Northwind', 'pubs', 'tempdb')
order by name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to MonthlyBackups with init, noskip, description = 'Monthly Database Backup', Name ='Monthly Database Backup'
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to MonthlyBackups with noinit, noskip, description = 'Monthly Database Backup', Name ='Monthly Database Backup'
select @db=@next_db
end
dump database @db to MonthlyBackups with noinit, noskip, description = 'Monthly Database Backup', Name ='Monthly Database Backup'
deallocate d

The reuturned error is as follows:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'dump'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

We have recently upgraded this server to 2008 and I am wondering did I miss something in the upgarde? Any help is appreciated.

Thanks

George

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 10:04:13

See Documentation for "Discontinued Database Engine Functionality in SQL Server 2008"

DUMP was deprecated and is no longer supported