How can I set Implicit Transactions ON as Default?
Todd Bailey
I need to have implicit transactions in a SQLServer 7 database for
compatibility with an application that connects via ODBC 3.5. I tried to
use sp_configure 'user options'. In Query Analyzer I issued the command
<sp_configure 'user options'> and it said that the config_value and
run_value were both 0 (zero). I then issued <sp_configure 'user options',
2> and then a < RECONFIGURE WITH OVERRIDE>. Now <sp_configure 'user
options'> says that config_value and run_value are both 2 and Enterprise
Manager shows the Implicit Transaction box checked in the server connection
properties tab, but a <ROLLBACK> still gets an error:
Server: Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
If I issue a <SET IMPLICIT_TRANSACTIONS ON>, the <ROLLBACK> works without an
explicit BEGIN TRANSACTION, but I can not modify the application that
accesses the database to do that. How can I use sp_configure or otherwise
change the default behavior of the SQLServer database?
Thanks in advance
Todd Bailey
todd....@steelox.com
amit...@btinternet.com
stored procedure
You can make this procedure a startup stored procedure and when the
server fires up
It should be chocks away
Allan
BPMargolin
The SQL Server 7.0 Books Online very clearly indicates that the SET IMPLICIT
TRANSACTION ON command is connection specific. So any connection other than the
one running the startup stored procedure will not benefit from the setting.
------------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.
<amit...@btinternet.com> wrote in message
news:38f61c5b...@news.microsoft.com...
a_mit...@bigfoot.com
Allan
Kalen Delaney
server. Other connections, whether from the same user or different users,
are unaffected.
--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
www.TheHungerSite.com
<a_mit...@bigfoot.com> wrote in message
news:38f7a08...@news.microsoft.com...
BPMargolin
Correct.
all...@btinternet.com
I've been mulling this over and I looked at your book (pages
561-562).
if I did this
use master
go
declare dbo.usp_Start
as
SET IMPLICIT TRANSACTIONS ON
go
exec sp_procoption usp_Start, startup, true
go
Surely this would be fired every time a connection to the server was
made for whoever made it. Yes it would fire each time a connection
was made ie first in wouldn't turn it on then no-one else would need
to bother.
Am I still barking up the wrong wooden thing.
Allan
Mitchers
use master
go
declare procedure dbo.usp_Start
as
SET IMPLICIT TRANSACTIONS ON
go
exec sp_procoption dbo.usp_Start, startup, true
go
Mitchers
Mitchers
I have just tried the following
I created a procedure in master which bcp'd the authors table in pubs
out to a text file
I made this a startup procedure
I then stopped and started the SQL server and sure enough a text file
with the authors table sat on my C drive
Is this not what we are trying to do.?
Another thing i thought of was
If you can't set IMPLICIT TRANSACTIONS ON that way then does SQL
server hold a table that when a connection is made we could fire a
trigger to set it on.
Sorry for my ramblings but this one perplexes me
I really must get a hobby <bg>
Allan
On Sat, 15 Apr 2000 07:34:47 GMT, all...@btinternet.com (Mitchers)
wrote:
BPMargolin
If you check out the SQL Server 7.0 Books Online documentation on sp_procoption,
section "sp_procoption (T-SQL)", you will see the following statement:
"A stored procedure that is set to autoexecution runs every time Microsoft® SQL
Server™ is started."
An autoexecution stored procedure runs when SQL Server (the service) is started,
NOT when a connection is made to SQL Server. Admittedly, it might be a nice
option for Microsoft to support, i.e., the ability to execute a stored procedure
whenever a connection is made, but such an option is not yet available.
---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.
<all...@btinternet.com> wrote in message
news:38f816e8...@news.microsoft.com...
BPMargolin
Perhaps posting to the Microsoft SQL Server newsgroups IS your hobby ... it is
certainly mine, although I never quite thought of it that way :-)
I would suggest that your experiment is flawed. Create a startup stored
procedure setting IMPLICIT TRANSACTIONS ON. Start and stop MS SQL Server. Now
establish a **new** connection to SQL Server and see if the IMPLICIT
TRANSACTIONS setting is on or off.
---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.
Mitchers <all...@btinternet.com> wrote in message
news:38f87304...@news.microsoft.com...
> I seem to be having a conversation with myself on this one but
> I have just tried the following
> I created a procedure in master which bcp'd the authors table in pubs
> out to a text file
> I made this a startup procedure
> I then stopped and started the SQL server and sure enough a text file
> with the authors table sat on my C drive
>
> Is this not what we are trying to do.?
>
> Another thing i thought of was
> If you can't set IMPLICIT TRANSACTIONS ON that way then does SQL
> server hold a table that when a connection is made we could fire a
> trigger to set it on.
>
> Sorry for my ramblings but this one perplexes me
> I really must get a hobby <bg>
>
> Allan
>
>
> On Sat, 15 Apr 2000 07:34:47 GMT, all...@btinternet.com (Mitchers)
> wrote:
>
> >SORRY should be
> >
> >use master
> >go
> >declare procedure dbo.usp_Start
> >as
> >SET IMPLICIT TRANSACTIONS ON
> >go
> >exec sp_procoption dbo.usp_Start, startup, true
> >go
Kalen Delaney
1. The syntax is CREATE PROCEDURE, not DECLARE PROCEDURE
2. Startup procs only run when sql server starts, not when every user
starts.
3. Startup procs have their own connection, that is terminated when the
startup proc is finished.
4. Any SET option turned in a procedure is only good for the duration of the
procedure. (So a SET option is only valid for a connection OR for a
procedure.)
HTH
--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
www.TheHungerSite.com
Mitchers <all...@btinternet.com> wrote in message
news:38f81b42...@news.microsoft.com...
Mitchers
Back to 101 for me
Allan
Todd Bailey
options Option" topic in SQLServer books online states:
"Use the user options option to specify global defaults for all users. A
list of default query-processing options is established for the duration of
a user’s work session. user options allows you to change the default values
of the SET options (if the server’s default settings are not appropriate). A
user can override these defaults by using the SET statement. You can
configure user options dynamically for new logins. After you change the
setting of user options, new logins use the new setting; current logins are
not affected."
IMPLICIT_TRANSACTIONS is listed as an option and a procedure for modifying
this option using Enterprise Manager is referenced.
I have used EM to make this change, restarted SQLServer *and* made a new
login but @@OPTIONS *still* shows that IMPLICIT_TRANSACTIONS are turned off
(and they are). Acording to the BOL topic this should work.
Anybody have any thoughts on that?
Todd Bailey
todd....@steelox.com
Kalen Delaney
What does this return?
exec sp_configure 'user options'
Thanks
--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
www.TheHungerSite.com
Todd Bailey <todd....@steelox.com> wrote in message
news:#qi4thJq$GA.320@cppssbbsa04...
Todd Bailey
Here is what I have figured out so far;
There seems to be at least four ways of looking at the IMPLICIT_TRANSACTION
setting;
1) Using @@OPTIONS
2) Using DBCC USEROPTIONS
3) Using sp_configure 'user options'
4) Using the Connection tab of Server Properties in EM.
There seems to be at least three ways of changing the IMPLICIT_TRANSACTION
setting;
1) Using sp_configure 'user options', '#'
2) Using the Connection tab of Server Properties in EM.
3) Using SET IMPLICIT_TRANSACTIONS ON/OFF
@@OPTIONS and DBCC USEROPTIONS show the same value set (which is NOT the
same as what sp_configure 'user options' and the EM…Connection tab shows).
The @@OPTIONS and DBCC USEROPTIONS values change in response to SET
IMPLICIT_TRANSACTIONS ON/OFF. The IMPLICIT_TRANSACTION value (on/off)
reported by @@OPTIONS and DBCC USEROPTIONS is an accurate indication of how
SQLServer will react.
sp_configure 'user options' and the EM…Connection tab show the same value
set (which is not what @@OPTIONS and DBCC USEROPTIONS shows). The
sp_configure 'user options' and the EM…Connection tab values change in
response to a sp_configure 'user options', '#' statement or a change at the
EM…Connection tab. However, even if they show IMPLICIT_TRANSACTION to be
turned on, SQLServer does not use implicit transactions
So, it seems that @@OPTIONS and DBCC USEROPTIONS are looking at a different
set of information than sp_configure 'user options' and the EM…Connection
tab. Also the sp_configure 'user options' and the EM…Connection tab seems
to have no effect on how SQLServer behaves.
Anyone have any idea why an IMPLICIT_TRANSACTION setting from sp_configure
'user options' or the EM…Connection tab doesn't seem to work as advertised?
I'm so confused...
Todd
Kalen Delaney
The complete output from select @@options and exec sp_configure 'user
options'.
These values should be the same at the start of any connection.
--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
www.TheHungerSite.com
Todd Bailey <todd....@steelox.com> wrote in message
news:eNxNxqTq$GA.193@cppssbbsa04...
> Kalen,
>
> Here is what I have figured out so far;
>
> There seems to be at least four ways of looking at the
IMPLICIT_TRANSACTION
> setting;
>
> 1) Using @@OPTIONS
> 2) Using DBCC USEROPTIONS
> 3) Using sp_configure 'user options'
> 4) Using the Connection tab of Server Properties in EM.
>
> There seems to be at least three ways of changing the IMPLICIT_TRANSACTION
> setting;
>
> 1) Using sp_configure 'user options', '#'
> 2) Using the Connection tab of Server Properties in EM.
> 3) Using SET IMPLICIT_TRANSACTIONS ON/OFF
>
> @@OPTIONS and DBCC USEROPTIONS show the same value set (which is NOT the
> same as what sp_configure 'user options' and the EM.Connection tab shows).
> The @@OPTIONS and DBCC USEROPTIONS values change in response to SET
> IMPLICIT_TRANSACTIONS ON/OFF. The IMPLICIT_TRANSACTION value (on/off)
> reported by @@OPTIONS and DBCC USEROPTIONS is an accurate indication of
how
> SQLServer will react.
>
> sp_configure 'user options' and the EM.Connection tab show the same value
> set (which is not what @@OPTIONS and DBCC USEROPTIONS shows). The
> sp_configure 'user options' and the EM.Connection tab values change in
> response to a sp_configure 'user options', '#' statement or a change at
the
> EM.Connection tab. However, even if they show IMPLICIT_TRANSACTION to be
> turned on, SQLServer does not use implicit transactions
>
> So, it seems that @@OPTIONS and DBCC USEROPTIONS are looking at a
different
> set of information than sp_configure 'user options' and the EM.Connection
> tab. Also the sp_configure 'user options' and the EM.Connection tab seems
> to have no effect on how SQLServer behaves.
>
> Anyone have any idea why an IMPLICIT_TRANSACTION setting from sp_configure
> 'user options' or the EM.Connection tab doesn't seem to work as
Todd Bailey
EM Server... Properties... Connections Attribute has only
implicit transactions checked
Print @@options
1080
sp_configure 'user options'
name minimum maximum config_value run_value
--------------- ----------- ----------- ------------
-----------
user options 0 4095 2
2
DBCC USEROPTIONS
Set Option Value
-------------------------- ------------
textsize 64512
language us_english
dateformat mdy
datefirst 7
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET