changing autocommit mode

Hi:

The first stmts in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_35bq.asp are

"Autocommit mode is the default transaction management mode of Microsoft? SQL Server?. Every Transact-SQL statement is committed or rolled back when it completes."

"if a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions."

My question is, how do we change autocommit mode. I don't want the dml to be commited until i explicitly do commit. This should be default setting.

I want this change either to be made at database level, maybe by doing some setting or through some T-SQL stmt.

I DO NOT want to use anything that has to be done in query windows like set ... off | on in each and every query window i use.

The change has to be done only once and should persist through-out.

Any solution will be of help to me.

Thanking you,

Regards,

kumar

Hi,

did you have a look on the server properties in Management Studio under the Connection Setting menu, there is a settin under the default connecting settings with the name: "implicit transactionS" which can be turned on at server level.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||This can also be enabled at the server level by using sp_configure 'user options'. See Books Online for more details on which bit to set in the user options value.|||

Hi:

Thank you for the reply.

But setting the property in studio. To my understanding, it is going to effect all the databases.

I want to set implict transaction to only my database and not for all databases. It should be global to only my database.

regards,

kumar

sql

changing autocommit mode

Hi:

The first stmts in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_35bq.asp are

"Autocommit mode is the default transaction management mode of Microsoft? SQL Server?. Every Transact-SQL statement is committed or rolled back when it completes."

"if a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions."

My question is, how do we change autocommit mode. I don't want the dml to be commited until i explicitly do commit. This should be default setting.

I want this change either to be made at database level, maybe by doing some setting or through some T-SQL stmt.

I DO NOT want to use anything that has to be done in query windows like set ... off | on in each and every query window i use.

The change has to be done only once and should persist through-out.

Any solution will be of help to me.

Thanking you,

Regards,

kumar

Hi,

did you have a look on the server properties in Management Studio under the Connection Setting menu, there is a settin under the default connecting settings with the name: "implicit transactionS" which can be turned on at server level.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||This can also be enabled at the server level by using sp_configure 'user options'. See Books Online for more details on which bit to set in the user options value.|||

Hi:

Thank you for the reply.

But setting the property in studio. To my understanding, it is going to effect all the databases.

I want to set implict transaction to only my database and not for all databases. It should be global to only my database.

regards,

kumar

Changing authentication mode in TSQL?

Is there a TSQL statement that will allow you to change
the authentication mode from 'Windows Authentication'
to 'Mixed Mode'?
Thanks in advance!You can use the undocumented xp_regwrite/xp_instance_regwrite to change the
registry key that contols this (which is what EM does anyway) or use SQLDMO
but it needs a SQL Service restart to take effect e.g.
exec master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'LoginMode', N'REG_DWORD', 2
'Set security mode to mixed where oSQL is a SQLServer object
Set oSEC = oSQL.IntegratedSecurity
oSEC.SecurityMode = 2
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Fee" <william.fee@.compuware.com> wrote in message
news:132701c37266$f9ad2310$a601280a@.phx.gbl...
Is there a TSQL statement that will allow you to change
the authentication mode from 'Windows Authentication'
to 'Mixed Mode'?
Thanks in advance!

Changing Authentication Mode

How can I change the authentication mode for sqlexpress without going through the UI? I looked at the docs for sp_configure, dmo and smo but I may have missed it. I'd like to be able to run a script that changes the authentication mode of an installed sqlexpress instance from windows only to moxed mode.

ThanksYOU can either use the SMO classes for this (tell me if you need an additional smaple for this) or change the appropiate value in the registry. See my Screencast for that on my site in the Screencast section.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

This should work in SMO. You will need to restart the server after doing this.

Server s = new Server(); // default instance
s.Settings.LoginMode = ServerLoginMode.Integrated;
s.Settings.Alter();

Mike

|||...and restarting the service: http://msdn2.microsoft.com/en-us/library/ms162139.aspx

:-)

-Jens.|||Apparently I missed it. Thanks!

Changing Authentication Mode

How can I change the authentication mode of SQl Server from 'windows
authentication' to 'mixed mode'? The SQL Server was installed using
windows authentication but I want to change it to 'mixed mode'.
Thanks
Egbon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi Egbon
The easiest way is through Enterprise Manager. Right click on your server
and choose Properties.
On the Security Tab, in the section called Authentication, select "SQL
Server and Windows".
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Egbon V." <vnjowusi@.gosps.com> wrote in message
news:O$9FAVusDHA.1740@.TK2MSFTNGP12.phx.gbl...
> How can I change the authentication mode of SQl Server from 'windows
> authentication' to 'mixed mode'? The SQL Server was installed using
> windows authentication but I want to change it to 'mixed mode'.
> Thanks
> Egbon
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks a lot!
Egbon
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:em$W3gusDHA.2464@.TK2MSFTNGP12.phx.gbl...
> Hi Egbon
> The easiest way is through Enterprise Manager. Right click on your server
> and choose Properties.
> On the Security Tab, in the section called Authentication, select "SQL
> Server and Windows".
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Egbon V." <vnjowusi@.gosps.com> wrote in message
> news:O$9FAVusDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > How can I change the authentication mode of SQl Server from 'windows
> > authentication' to 'mixed mode'? The SQL Server was installed using
> > windows authentication but I want to change it to 'mixed mode'.
> >
> > Thanks
> >
> > Egbon
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>

changing Authentication mode

Hi,
I am a newbie and i am trying to change authenication mode under enterprise
manager the sql server properties -> security tab to "SQl Server and Windows
NT" but as soon as i click on ok and close the properties it changes bak, to
Windows NT.
I have installed sharepoint services and someday i would configure something
(i dont remember what) i changed authentication to Windows NT , now i want t
o
change it back to mixed mode (Sql server and Windows) but it does want to
obey.
what can i do?From memory, changing the authentication mode only takes affect the
next time you restart SQL Server. Have you stopped & restarted SQL
Server since changing the authentication mode?
*mike hodgson*
http://sqlnerd.blogspot.com
Cyrus wrote:

>Hi,
>I am a newbie and i am trying to change authenication mode under enterprise
>manager the sql server properties -> security tab to "SQl Server and Window
s
>NT" but as soon as i click on ok and close the properties it changes bak, t
o
>Windows NT.
>I have installed sharepoint services and someday i would configure somethin
g
>(i dont remember what) i changed authentication to Windows NT , now i want
to
>change it back to mixed mode (Sql server and Windows) but it does want to
>obey.
>what can i do?
>sql

changing authentication error, Please HELP!

I am running SQL Server 2005 Express. I am trying to change the server
property to the mixed mode authentication (SQL Server and Windows
authentication mode) in SQL Server Management Studio. I loged in to the
server which is local in Managament Studio using windows authentication. When
I right click on the connection I get the following error:
-----
TITLE: Microsoft SQL Server Management Studio Express
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
--
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SqlManagerUI)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
--
Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
(Microsoft.SqlServer.Express.ConnectionInfo)
--
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft
SQL Server, Error: 233)
-----
All the protocols for SQLEXPRESS are enabled. I don't know what's going on.
I also checked the Surface Area Configuration and everything seems to be
turned on there. The log file contains the following.
-----
2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
2006-03-30 16:38:11.98 Logon Could not connect because the maximum
number of '1' user connections has already been reached. The system
administrator can use sp_configure to increase the maximum value. The
connection has been closed. [CLIENT: <local machine>]
-----
But I can't increase the maximum value of connections because when I right
click on connection I get the same error mentioned above. What is going on
here? Do I need to reinstall SQL Express? I can't get rid of this stupid
error. Thank you.
Ilyause command line utility to connect to express and change the setting.
e.g.
sqlcmd -S".\Express" -Q"exec sp_configure 'user connections','32767';
reconfigure with override"
--
-oj
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:D58D1B38-F886-4285-90FB-F1CADFCE9B4A@.microsoft.com...
>I am running SQL Server 2005 Express. I am trying to change the server
> property to the mixed mode authentication (SQL Server and Windows
> authentication mode) in SQL Server Management Studio. I loged in to the
> server which is local in Managament Studio using windows authentication.
> When
> I right click on the connection I get the following error:
> -----
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
> --
> Failed to retrieve data for this request.
> (Microsoft.SqlServer.Express.SqlManagerUI)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> --
> Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> (Microsoft
> SQL Server, Error: 233)
> -----
> All the protocols for SQLEXPRESS are enabled. I don't know what's going
> on.
> I also checked the Surface Area Configuration and everything seems to be
> turned on there. The log file contains the following.
> -----
> 2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
> 2006-03-30 16:38:11.98 Logon Could not connect because the maximum
> number of '1' user connections has already been reached. The system
> administrator can use sp_configure to increase the maximum value. The
> connection has been closed. [CLIENT: <local machine>]
> -----
> But I can't increase the maximum value of connections because when I right
> click on connection I get the same error mentioned above. What is going on
> here? Do I need to reinstall SQL Express? I can't get rid of this stupid
> error. Thank you.
> Ilya
>

changing authentication error, Please HELP!

I am running SQL Server 2005 Express. I am trying to change the server
property to the mixed mode authentication (SQL Server and Windows
authentication mode) in SQL Server Management Studio. I loged in to the
server which is local in Managament Studio using windows authentication. When
I right click on the connection I get the following error:
-----
TITLE: Microsoft SQL Server Management Studio Express
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SqlManagerUI)
For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
(Microsoft.SqlServer.Express.ConnectionInfo)
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft
SQL Server, Error: 233)
-----
All the protocols for SQLEXPRESS are enabled. I don't know what's going on.
I also checked the Surface Area Configuration and everything seems to be
turned on there. The log file contains the following.
-----
2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
2006-03-30 16:38:11.98 Logon Could not connect because the maximum
number of '1' user connections has already been reached. The system
administrator can use sp_configure to increase the maximum value. The
connection has been closed. [CLIENT: <local machine>]
-----
But I can't increase the maximum value of connections because when I right
click on connection I get the same error mentioned above. What is going on
here? Do I need to reinstall SQL Express? I can't get rid of this stupid
error. Thank you.
Ilya
use command line utility to connect to express and change the setting.
e.g.
sqlcmd -S".\Express" -Q"exec sp_configure 'user connections','32767';
reconfigure with override"
-oj
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:D58D1B38-F886-4285-90FB-F1CADFCE9B4A@.microsoft.com...
>I am running SQL Server 2005 Express. I am trying to change the server
> property to the mixed mode authentication (SQL Server and Windows
> authentication mode) in SQL Server Management Studio. I loged in to the
> server which is local in Managament Studio using windows authentication.
> When
> I right click on the connection I get the following error:
> -----
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
> --
> Failed to retrieve data for this request.
> (Microsoft.SqlServer.Express.SqlManagerUI)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
> --
> Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> (Microsoft
> SQL Server, Error: 233)
> -----
> All the protocols for SQLEXPRESS are enabled. I don't know what's going
> on.
> I also checked the Surface Area Configuration and everything seems to be
> turned on there. The log file contains the following.
> -----
> 2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
> 2006-03-30 16:38:11.98 Logon Could not connect because the maximum
> number of '1' user connections has already been reached. The system
> administrator can use sp_configure to increase the maximum value. The
> connection has been closed. [CLIENT: <local machine>]
> -----
> But I can't increase the maximum value of connections because when I right
> click on connection I get the same error mentioned above. What is going on
> here? Do I need to reinstall SQL Express? I can't get rid of this stupid
> error. Thank you.
> Ilya
>

changing authentication error, Please HELP!

I am running SQL Server 2005 Express. I am trying to change the server
property to the mixed mode authentication (SQL Server and Windows
authentication mode) in SQL Server Management Studio. I loged in to the
server which is local in Managament Studio using windows authentication. Whe
n
I right click on the connection I get the following error:
----
--
TITLE: Microsoft SQL Server Management Studio Express
--
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SqlManagerUI)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
(Microsoft.SqlServer.Express.ConnectionInfo)
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft
SQL Server, Error: 233)
----
--
All the protocols for SQLEXPRESS are enabled. I don't know what's going on.
I also checked the Surface Area Configuration and everything seems to be
turned on there. The log file contains the following.
----
--
2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
2006-03-30 16:38:11.98 Logon Could not connect because the maximum
number of '1' user connections has already been reached. The system
administrator can use sp_configure to increase the maximum value. The
connection has been closed. [CLIENT: <local machine>]
----
--
But I can't increase the maximum value of connections because when I right
click on connection I get the same error mentioned above. What is going on
here? Do I need to reinstall SQL Express? I can't get rid of this stupid
error. Thank you.
Ilyause command line utility to connect to express and change the setting.
e.g.
sqlcmd -S".\Express" -Q"exec sp_configure 'user connections','32767';
reconfigure with override"
-oj
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:D58D1B38-F886-4285-90FB-F1CADFCE9B4A@.microsoft.com...
>I am running SQL Server 2005 Express. I am trying to change the server
> property to the mixed mode authentication (SQL Server and Windows
> authentication mode) in SQL Server Management Studio. I loged in to the
> server which is local in Managament Studio using windows authentication.
> When
> I right click on the connection I get the following error:
> ----
--
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
> --
> Failed to retrieve data for this request.
> (Microsoft.SqlServer.Express.SqlManagerUI)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
> --
> Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> (Microsoft
> SQL Server, Error: 233)
> ----
--
> All the protocols for SQLEXPRESS are enabled. I don't know what's going
> on.
> I also checked the Surface Area Configuration and everything seems to be
> turned on there. The log file contains the following.
> ----
--
> 2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
> 2006-03-30 16:38:11.98 Logon Could not connect because the maximum
> number of '1' user connections has already been reached. The system
> administrator can use sp_configure to increase the maximum value. The
> connection has been closed. [CLIENT: <local machine>]
> ----
--
> But I can't increase the maximum value of connections because when I right
> click on connection I get the same error mentioned above. What is going on
> here? Do I need to reinstall SQL Express? I can't get rid of this stupid
> error. Thank you.
> Ilya
>

Changing a 2005 db from 8.0 compatability to 9.0

I've inherited a 2005 database that is in 8.0 compatability mode. I'd like t
o
move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
I think the main thing I need to look for are the old outer join operators
(*= and =*). I've reviewed the list here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-
de1a5f189f25.htm
and I think the join operators are the only thing that would affect me. Does
anyone have any other common issues going from 8.0 to 9.0 that I might be
missing from that list?
Thanks in advance.Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
biting you without knowing it ;-). Other than new keywords and the
deletion of the "evil join" operators, I can't think of anything else
that's common that you'd run into.
sqlboy2000 wrote:
> I've inherited a 2005 database that is in 8.0 compatability mode. I'd like
to
> move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
> won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL a
nd
> I think the main thing I need to look for are the old outer join operators
> (*= and =*). I've reviewed the list here:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec
8-de1a5f189f25.htm
> and I think the join operators are the only thing that would affect me. Do
es
> anyone have any other common issues going from 8.0 to 9.0 that I might be
> missing from that list?
> Thanks in advance.|||We do have a few of those stupid TOP 100 percent views.
Thanks for the heads up.
"David Markle" wrote:

> Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
> biting you without knowing it ;-). Other than new keywords and the
> deletion of the "evil join" operators, I can't think of anything else
> that's common that you'd run into.
> sqlboy2000 wrote:
>

Changing a 2005 db from 8.0 compatability to 9.0

I've inherited a 2005 database that is in 8.0 compatability mode. I'd like to
move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
I think the main thing I need to look for are the old outer join operators
(*= and =*). I've reviewed the list here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-de1a5f189f25.htm
and I think the join operators are the only thing that would affect me. Does
anyone have any other common issues going from 8.0 to 9.0 that I might be
missing from that list?
Thanks in advance.Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
biting you without knowing it ;-). Other than new keywords and the
deletion of the "evil join" operators, I can't think of anything else
that's common that you'd run into.
sqlboy2000 wrote:
> I've inherited a 2005 database that is in 8.0 compatability mode. I'd like to
> move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
> won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
> I think the main thing I need to look for are the old outer join operators
> (*= and =*). I've reviewed the list here:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-de1a5f189f25.htm
> and I think the join operators are the only thing that would affect me. Does
> anyone have any other common issues going from 8.0 to 9.0 that I might be
> missing from that list?
> Thanks in advance.|||We do have a few of those stupid TOP 100 percent views.
Thanks for the heads up.
"David Markle" wrote:
> Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
> biting you without knowing it ;-). Other than new keywords and the
> deletion of the "evil join" operators, I can't think of anything else
> that's common that you'd run into.
> sqlboy2000 wrote:
> > I've inherited a 2005 database that is in 8.0 compatability mode. I'd like to
> > move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
> > won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
> > I think the main thing I need to look for are the old outer join operators
> > (*= and =*). I've reviewed the list here:
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-de1a5f189f25.htm
> > and I think the join operators are the only thing that would affect me. Does
> > anyone have any other common issues going from 8.0 to 9.0 that I might be
> > missing from that list?
> >
> > Thanks in advance.
>

Change Windows Authentication to Mixed Mode

How do I Change Windows Authentication to Mixed Mode on SQL Server 2005
(Express)?
On May 24, 9:09 pm, "Middletree" <middlet...@.hottttttttmail.com>
wrote:
> How do I Change Windows Authentication to Mixed Mode on SQL Server 2005
> (Express)?
I don't know express
In SSMS , go to SERVER , Right Click Properties ,
-> Security-> Server Authentication Select SQL Server and Windows
authentication
OR
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft
\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
|||The first method worked. Thanks.

Change Windows Authentication to Mixed Mode

How do I Change Windows Authentication to Mixed Mode on SQL Server 2005
(Express)?On May 24, 9:09 pm, "Middletree" <middlet...@.hottttttttmail.com>
wrote:
> How do I Change Windows Authentication to Mixed Mode on SQL Server 2005
> (Express)?
I don't know express
In SSMS , go to SERVER , Right Click Properties ,
-> Security-> Server Authentication Select SQL Server and Windows
authentication
OR
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft
\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO|||The first method worked. Thanks.

Change Windows Authentication to Mixed Mode

How do I Change Windows Authentication to Mixed Mode on SQL Server 2005
(Express)?On May 24, 9:09 pm, "Middletree" <middlet...@.hottttttttmail.com>
wrote:
> How do I Change Windows Authentication to Mixed Mode on SQL Server 2005
> (Express)?
I don't know express
In SSMS , go to SERVER , Right Click Properties ,
-> Security-> Server Authentication Select SQL Server and Windows
authentication
OR
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft
\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO|||The first method worked. Thanks.

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>
|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this
> connected
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > I have a db that I changed from multiple user to single user, and want
to
> > change it back now. However, it keeps giving me the message below. I
> have
> > checked with EM and sysprocesses that there is no connection to it. Any
> > suggestion what I can do?
> >
> > Thanks.
> >
> > Quentin
> >
> >
> > message:
> >
> > Server: Msg 5064, Level 16, State 1, Line 1
> > Changes to the state or options of database 'mydb' cannot be made at
this
> > time. The database is in single-user mode, and a user is currently
> connected
> > to it.
> > Server: Msg 5069, Level 16, State 1, Line 1
> > ALTER DATABASE statement failed.
> >
> >
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this[vbcol=seagreen]
> connected
>

change recovery mode

Hi Guys,
If I change recovery mode from full to simple, do I need to reboot computer
or re-start database? Thanks
Hello,
No need to do a SQL Server service restart or machine restart for this.
After changing the recovery model; just disable Transaction log backup, this
is because in SIMPLE rcovery model log
backup is not possible.
But if you do the otherway from SIMPLE to FULL recove then to regroup the
backup chnain you will need to do a full backup and then schedule the
transaction log backup.
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>
|||Thank you guys.
"Hari Prasad" wrote:

> Hello,
> No need to do a SQL Server service restart or machine restart for this.
> After changing the recovery model; just disable Transaction log backup, this
> is because in SIMPLE rcovery model log
> backup is not possible.
> But if you do the otherway from SIMPLE to FULL recove then to regroup the
> backup chnain you will need to do a full backup and then schedule the
> transaction log backup.
> Thanks
> Hari
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
>
>

change recovery mode

Hi Guys,
If I change recovery mode from full to simple, do I need to reboot computer
or re-start database? ThanksNope..
VT
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>|||Hello,
No need to do a SQL Server service restart or machine restart for this.
After changing the recovery model; just disable Transaction log backup, this
is because in SIMPLE rcovery model log
backup is not possible.
But if you do the otherway from SIMPLE to FULL recove then to regroup the
backup chnain you will need to do a full backup and then schedule the
transaction log backup.
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
> Hi Guys,
> If I change recovery mode from full to simple, do I need to reboot
> computer
> or re-start database? Thanks
>|||Thank you guys.
"Hari Prasad" wrote:

> Hello,
> No need to do a SQL Server service restart or machine restart for this.
> After changing the recovery model; just disable Transaction log backup, th
is
> is because in SIMPLE rcovery model log
> backup is not possible.
> But if you do the otherway from SIMPLE to FULL recove then to regroup t
he
> backup chnain you will need to do a full backup and then schedule the
> transaction log backup.
> Thanks
> Hari
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:A24DB4EE-E8E0-4CF9-B651-9BD445A7C7F4@.microsoft.com...
>
>