SQL server mode - SQL Server Forums

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

SQL Server Administration (2000)

SQL server mode

Author

Topic

SQL360
Starting Member

20 Posts

Posted - 2008-09-25 : 18:43:41

How to change the sql server(not the individual database) from single user to multi user mode? Thank you.

SQL360

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-25 : 19:06:30

By default it is set to Multi-User. If you want to change all of your databases from single-user to Multi-user,you can use sp_Msforeachdb with
ALTER DATABASE '?' SET MULTI_USER WITH NO_WAIT

SQL360
Starting Member

20 Posts

Posted - 2008-10-08 : 17:54:52

Can you please provide me the complete command?

It is giving me some error, Server:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'test'.

Thank you.

SQL360

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-08 : 17:56:51

quote:
Originally posted by SQL360

Can you please provide me the complete command?

It is giving me some error, Server:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'test'.

Can you provide the complete command that you tried?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-08 : 23:24:09

I Used this one to change from Multi-User to Single User and it worked:

Sp_Msforeachdb @command1= 'Exec sp_dboption ''?'',''Single User'',''True'''

But when I used Other way round,it didn't work as only one connection can use database.

sp_Msforeachdb @command1= 'Exec sp_dboption ''?'',''Single User'',''False'''

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-10-09 : 14:19:24

To set SQL Server to single user mode, you need to shut down SQL Server, and restart it with the -m commandline switch. This can be done in Enterprise Manager, or in the Services applet (but it is recommended you use Enterprise Manager).

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-10 : 00:53:19

quote:
Originally posted by mcrowley

To set SQL Server to single user mode, you need to shut down SQL Server, and restart it with the -m commandline switch. This can be done in Enterprise Manager, or in the Services applet (but it is recommended you use Enterprise Manager).

Op is talking about putting all databases to Multi-User Mode from Single-User Mode.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-10-10 : 10:51:55

I am not sure that is so. The original question asks how to set SQL Server and not an individual database. We need some clarification on this point.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources