Returning from single user mode

I'm using a modified backup plan to perform nightly backups. In the
integrity check I've checked the Attempt to repair (I know some don't
recommend it). To prepare for that stage of the backup I kill the user
processes that are still active when the backup begins. However, when the
backup finishes the databases are being left in single user mode. How can I
return the databases to multi user mode? I've tried a procedure like so:
create procedure setmultiuser
as
declare @.dbname varchar(128),
@.cmd varchar(128)
declare dbname_crsr cursor for
select name from sysdatabases
where sid <> 0x01
open dbname_crsr
fetch dbname_crsr into @.dbname
while (@.@.fetch_status <> -1)
begin
set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
EXEC ('sp_dboption ' + @.cmd)
fetch dbname_crsr into @.dbname
end
close dbname_crsr
deallocate dbname_crsr
But, the accursed EXEC command refuses to allow me to understand it's
syntax.
Any help would be greatly appreciated.I get:
Line 16: Incorrect syntax near 'single'.
The actual line reads:
set @.cmd = @.dbname + ', 'single user', 'FALSE''
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
Throw a print in there to see where the wheels may be coming off...
PRINT ('sp_dboption ' + @.cmd)
How many databases exist on your server? Would it be easier to simply
hardcode the appropriate call within a separate step of the job?
--
Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> I'm using a modified backup plan to perform nightly backups. In the
> integrity check I've checked the Attempt to repair (I know some don't
> recommend it). To prepare for that stage of the backup I kill the user
> processes that are still active when the backup begins. However, when the
> backup finishes the databases are being left in single user mode. How can
I
> return the databases to multi user mode? I've tried a procedure like so:
> create procedure setmultiuser
> as
> declare @.dbname varchar(128),
> @.cmd varchar(128)
> declare dbname_crsr cursor for
> select name from sysdatabases
> where sid <> 0x01
> open dbname_crsr
> fetch dbname_crsr into @.dbname
> while (@.@.fetch_status <> -1)
> begin
> set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
> EXEC ('sp_dboption ' + @.cmd)
> fetch dbname_crsr into @.dbname
> end
> close dbname_crsr
> deallocate dbname_crsr
> But, the accursed EXEC command refuses to allow me to understand it's
> syntax.
> Any help would be greatly appreciated.
>|||Does this help?
declare @.cmd varchar(100), @.dbname char(5)
set @.cmd =3D 'thecmd ' set @.dbname =3D 'thedb' set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
print @.cmd
-- Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message =news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> I get:
> > Line 16: Incorrect syntax near 'single'.
> > The actual line reads:
> set @.cmd =3D @.dbname + ', 'single user', 'FALSE''
> > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote =in
> message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Throw a print in there to see where the wheels may be coming off...
> > PRINT ('sp_dboption ' + @.cmd)
> > How many databases exist on your server? Would it be easier to simply
> hardcode the appropriate call within a separate step of the job?
> > -- > Keith
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I'm using a modified backup plan to perform nightly backups. In the
> > integrity check I've checked the Attempt to repair (I know some =don't
> > recommend it). To prepare for that stage of the backup I kill the =user
> > processes that are still active when the backup begins. However, =when the
> > backup finishes the databases are being left in single user mode. =How can
> I
> > return the databases to multi user mode? I've tried a procedure =like so:
> >
> > create procedure setmultiuser
> > as
> >
> > declare @.dbname varchar(128),
> > @.cmd varchar(128)
> >
> > declare dbname_crsr cursor for
> > select name from sysdatabases
> > where sid <> 0x01
> >
> > open dbname_crsr
> > fetch dbname_crsr into @.dbname
> >
> > while (@.@.fetch_status <> -1)
> > begin
> > set @.cmd =3D @.dbname + ', ' + ''single user', 'FALSE''
> > EXEC ('sp_dboption ' + @.cmd)
> >
> > fetch dbname_crsr into @.dbname
> > end
> >
> > close dbname_crsr
> > deallocate dbname_crsr
> >
> > But, the accursed EXEC command refuses to allow me to understand =it's
> > syntax.
> >
> > Any help would be greatly appreciated.
> >
> >
> >|||That was real close. This is what finally worked:
set @.cmd = @.dbname + ''', ''single user'', ''FALSE'''
exec ('sp_dboption ''' + @.cmd)
But you put me on the right track. Thanks a billion!!!
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:ug4IM1bZDHA.2236@.TK2MSFTNGP10.phx.gbl...
Does this help?
declare @.cmd varchar(100), @.dbname char(5)
set @.cmd = 'thecmd '
set @.dbname = 'thedb'
set @.cmd = @.dbname + ''', ''single user'', ''FALSE'''
print @.cmd
--
Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> I get:
> Line 16: Incorrect syntax near 'single'.
> The actual line reads:
> set @.cmd = @.dbname + ', 'single user', 'FALSE''
> "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
> message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Throw a print in there to see where the wheels may be coming off...
> PRINT ('sp_dboption ' + @.cmd)
> How many databases exist on your server? Would it be easier to simply
> hardcode the appropriate call within a separate step of the job?
> --
> Keith
> "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I'm using a modified backup plan to perform nightly backups. In the
> > integrity check I've checked the Attempt to repair (I know some don't
> > recommend it). To prepare for that stage of the backup I kill the user
> > processes that are still active when the backup begins. However, when
the
> > backup finishes the databases are being left in single user mode. How
can
> I
> > return the databases to multi user mode? I've tried a procedure like
so:
> >
> > create procedure setmultiuser
> > as
> >
> > declare @.dbname varchar(128),
> > @.cmd varchar(128)
> >
> > declare dbname_crsr cursor for
> > select name from sysdatabases
> > where sid <> 0x01
> >
> > open dbname_crsr
> > fetch dbname_crsr into @.dbname
> >
> > while (@.@.fetch_status <> -1)
> > begin
> > set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
> > EXEC ('sp_dboption ' + @.cmd)
> >
> > fetch dbname_crsr into @.dbname
> > end
> >
> > close dbname_crsr
> > deallocate dbname_crsr
> >
> > But, the accursed EXEC command refuses to allow me to understand it's
> > syntax.
> >
> > Any help would be greatly appreciated.
> >
> >
>|||Learning is half the fun. Applying what you learn is the other half.
-- Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message =news:Opv#VOcZDHA.2020@.TK2MSFTNGP10.phx.gbl...
> That was real close. This is what finally worked:
> > set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
> exec ('sp_dboption ''' + @.cmd)
> > But you put me on the right track. Thanks a billion!!!
> > > > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote =in
> message news:ug4IM1bZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Does this help?
> > > declare @.cmd varchar(100), @.dbname char(5)
> set @.cmd =3D 'thecmd '
> set @.dbname =3D 'thedb'
> set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
> print @.cmd
> > -- > Keith
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> > I get:
> >
> > Line 16: Incorrect syntax near 'single'.
> >
> > The actual line reads:
> > set @.cmd =3D @.dbname + ', 'single user', 'FALSE''
> >
> > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> =wrote in
> > message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> > Throw a print in there to see where the wheels may be coming off...
> >
> > PRINT ('sp_dboption ' + @.cmd)
> >
> > How many databases exist on your server? Would it be easier to =simply
> > hardcode the appropriate call within a separate step of the job?
> >
> > -- > > Keith
> >
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> > news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > > I'm using a modified backup plan to perform nightly backups. In =the
> > > integrity check I've checked the Attempt to repair (I know some =don't
> > > recommend it). To prepare for that stage of the backup I kill the =user
> > > processes that are still active when the backup begins. However, =when
> the
> > > backup finishes the databases are being left in single user mode. =How
> can
> > I
> > > return the databases to multi user mode? I've tried a procedure =like
> so:
> > >
> > > create procedure setmultiuser
> > > as
> > >
> > > declare @.dbname varchar(128),
> > > @.cmd varchar(128)
> > >
> > > declare dbname_crsr cursor for
> > > select name from sysdatabases
> > > where sid <> 0x01
> > >
> > > open dbname_crsr
> > > fetch dbname_crsr into @.dbname
> > >
> > > while (@.@.fetch_status <> -1)
> > > begin
> > > set @.cmd =3D @.dbname + ', ' + ''single user', 'FALSE''
> > > EXEC ('sp_dboption ' + @.cmd)
> > >
> > > fetch dbname_crsr into @.dbname
> > > end
> > >
> > > close dbname_crsr
> > > deallocate dbname_crsr
> > >
> > > But, the accursed EXEC command refuses to allow me to understand =it's
> > > syntax.
> > >
> > > Any help would be greatly appreciated.
> > >
> > >
> >
> >
> >