January 13, 2012 at 9:19 am
Hello all,
My error message is "Incorrect syntax near 'sp_dboption'"
I am trying to build a script that will backup a series of databases and take them offline but only take them offline when my @offline parameter = N
DECLARE @offline char(1)
SET @offline = 'N'
--Test script
--Test
BACKUP DATABASE [Test] TO DISK = N'D:\Test.bak' WITH NOFORMAT, INIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
IF @offline = 'Y'
BEGIN
sp_dboption 'Test','offline','true'
END
--Test2
BACKUP DATABASE [Test] TO DISK = N'D:\Test2.bak' WITH NOFORMAT, INIT, NAME = N'Test2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
IF @offline = 'Y'
BEGIN
sp_dboption 'Test2','offline','true'
END
January 13, 2012 at 9:31 am
You need an EXEC before the procedure call - EXEC sp_dboption..., however sp_dboption is deprecated and shouldn't be used.
DECLARE @offline char(1)
SET @offline = 'N'
--Test script
--Test
BACKUP DATABASE [Test] TO DISK = N'D:\Test.bak' WITH NOFORMAT, INIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
IF @offline = 'Y'
BEGIN
ALTER DATABASE Test SET OFFLINE
END
--Test2
BACKUP DATABASE [Test2] TO DISK = N'D:\Test2.bak' WITH NOFORMAT, INIT, NAME = N'Test2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
IF @offline = 'Y'
BEGIN
ALTER DATABASE Test2 SET OFFLINE
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2012 at 9:32 am
IF @offline = 'Y'
BEGIN
EXECUTE sp_dboption 'Test','offline','true'
END
January 13, 2012 at 9:32 am
It should work if you add exec in front of the sp_dboption.
At least it did in my test.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2012 at 9:43 am
Thanks you all once again. This is resolved.
January 13, 2012 at 9:46 am
Good to hear...
So did you add the exec, or did you decide to use the method outlined by Gail?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2012 at 9:53 am
I added the exec since I am actually working in a 2000 environment. I posted in this forum since I was also able to generate my error in a 2008 R2 environment. Thanks again...
January 14, 2012 at 3:58 am
Per BOL EXEC or EXECUTE to run a stored procedure is optional.
--Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
{
[ @return_status = ]
{ module_name [ ;number ] | @module_name_var }
[ [ @parameter = ] { value
| @variable [ OUTPUT ]
| [ DEFAULT ]
}
]
[ ,...n ]
[ WITH RECOMPILE ]
}
[;]
{ } (braces): Required syntax items. Do not type the braces.
[ ] (brackets): Optional syntax items. Do not type the brackets.
For More:
January 14, 2012 at 4:16 am
Dev (1/14/2012)
Per BOL EXEC or EXECUTE to run a stored procedure is optional.
No, it's not. From the link you posted:
You do not have to specify the EXECUTE keyword when you execute stored procedures when the statement is the first one in a batch.
So this works fine:
SELECT name FROM sys.tables;
GO
sp_help 'sys.tables';
but this does not:
SELECT name FROM sys.tables;
sp_help 'sys.tables';
That said, it's good practice to always use exec in any code other than quick throw-away scripts, that way you don't have to worry about adding code at the beginning and having stuff break
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply