relationship between set ansi_defaults and set implicit_transactio

BOL States the following in the SET ANSI_DEFAULTS section:
SQL Server ODBC driver automatically set ANSI_DEFAULTS to ON when
connecting. The driver then set CURSOR_CLOSE_ON_COMMIT and
IMPLICIT_TRANSACTIONS to OFF.
BOL States the following in the SET IMPLICIT_TRANSACTIONS:
When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is enabled.
What does SET IMPLICIT_TRANSACTIONS is enabled mean? If it means SET
IMPLICIT_TRANSACTIONS is ON, is this conflicting with the statment in the SE
T
ANSI_DEFAULTS section. Or the setting ANSI_DEFAULTS affects the setting
IMPLICIT_TRANSACTIONS differently based on the source (driver or sql
statement).ANSI_DEFAULT is just a grouping of other SET options, a way to turn on a num
ber of SET options with
one command. Seems like ODBC first turn on ANSI_DEFAULT and then turn off IM
PLICIT_TRANSACTIONS.
This is doable, "turn on all which are in ANSI_DEFAULT but I don't want IMPL
ICIT_TRANSACTIOPNS so I
turn that off explicitly":
DBCC USEROPTIONS
SET ANSI_DEFAULTS ON
GO
DBCC USEROPTIONS
SET IMPLICIT_TRANSACTIONS OFF
GO
DBCC USEROPTIONS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:AF6843DA-18C9-4980-A25E-55D9FAE003E6@.microsoft.com...
> BOL States the following in the SET ANSI_DEFAULTS section:
> SQL Server ODBC driver automatically set ANSI_DEFAULTS to ON when
> connecting. The driver then set CURSOR_CLOSE_ON_COMMIT and
> IMPLICIT_TRANSACTIONS to OFF.
>
> BOL States the following in the SET IMPLICIT_TRANSACTIONS:
> When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is enabled.
>
> What does SET IMPLICIT_TRANSACTIONS is enabled mean? If it means SET
> IMPLICIT_TRANSACTIONS is ON, is this conflicting with the statment in the
SET
> ANSI_DEFAULTS section. Or the setting ANSI_DEFAULTS affects the setting
> IMPLICIT_TRANSACTIONS differently based on the source (driver or sql
> statement).

Reinstating Replication on a subscriber automatically

Hi all

I am setting up a system using SQL Server 2005 replicating to both SQL Express and MSDE clients. My question is this - if a client PC has been rebuilt - i.e. new hard disk etc, how can I automatically make that machine realise that it is already a subscriber of a SQL Server database and for it to automatically get a snapshot?

I hope this makes sense, thanks for your help.

D

If the subscriber has the same name as the old one, the SQL Server version of the subscriber is the same as the old one, and the history or distribution retention period has not passed you merely need to restore the subscriber database and the distributor will backfill the missing commands.

If this is not the case you should try to do a no-sync and then a validation to determine what is missing, or how out of sync you are. At that point you might want to evaluate re-initializing.sql

Reindex systems tables

Hi All
Just want to know, How can we reindex systems table? i.e
syscolumns, sysdepends, sysusers etc.
In case it can be done automatically, How can we let it
reindex by itself?
The problem is sp_help_fulltext_columns issue in nearly
the same database but generate totally different execution
plan. One execute for 1.23 minutes and another for 0.01
minutes.
I still think about the systems table because it use
only systems table concern, may be I wrong. Any recommend
will be very appreciate.
Thanks you in advance.
James Jarupan
How do the execution plans differ?
Do the system tables have the same number of rows in? Differening numbers of
rows can generate different execution plans.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"James Jarupan" <anonymous@.discussions.microsoft.com> wrote in message
news:9b7001c478cb$82022790$a301280a@.phx.gbl...
> Hi All
> Just want to know, How can we reindex systems table? i.e
> syscolumns, sysdepends, sysusers etc.
> In case it can be done automatically, How can we let it
> reindex by itself?
> The problem is sp_help_fulltext_columns issue in nearly
> the same database but generate totally different execution
> plan. One execute for 1.23 minutes and another for 0.01
> minutes.
> I still think about the systems table because it use
> only systems table concern, may be I wrong. Any recommend
> will be very appreciate.
> Thanks you in advance.
> James Jarupan
|||Something strange with this Stored Procedure,
sp_help_fulltext_columns. I test with different databases
in different computers it show the same execution plan.
The reason may be this stored procedure is in Master
Database.
So the rows in system tables should not have the effect
but I will check all again.
Can you provide the second point that I should pay
attention.
Thanks you.
James Jarupan

>--Original Message--
>How do the execution plans differ?
>Do the system tables have the same number of rows in?
Differening numbers of
>rows can generate different execution plans.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"James Jarupan" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:9b7001c478cb$82022790$a301280a@.phx.gbl...
i.e[vbcol=seagreen]
it[vbcol=seagreen]
nearly[vbcol=seagreen]
execution[vbcol=seagreen]
recommend
>
>.
>
|||Something strange with this Stored Procedure,
sp_help_fulltext_columns. I test with different databases
in different computers it show the same execution plan.
The reason may be this stored procedure is in Master
Database.
So the rows in system tables should not have the effect
but I will check all again.
Can you provide the second point that I should pay
attention.
Thanks you.
James Jarupan

>--Original Message--
>How do the execution plans differ?
>Do the system tables have the same number of rows in?
Differening numbers of
>rows can generate different execution plans.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"James Jarupan" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:9b7001c478cb$82022790$a301280a@.phx.gbl...
i.e[vbcol=seagreen]
it[vbcol=seagreen]
nearly[vbcol=seagreen]
execution[vbcol=seagreen]
recommend
>
>.
>

Registering Servers

In Enterprise Manager 2000 I was able to register multiple servers and next
time I opened it those servers would automatically connect. With SQL Server
Management Studio 2005 I can register multiple servers, but next time I open
it only the last one I had connected shows. Is there a way to maintain
multiple connections?
Hi Gary
I believe the object browser will show the server that you have logged into,
you will need to log into the other servers to get them in object explorer.
You want to raise this as an request at
http://lab.msdn.microsoft.com/produc...k/default.aspx
John
"Gary L" wrote:

> In Enterprise Manager 2000 I was able to register multiple servers and next
> time I opened it those servers would automatically connect. With SQL Server
> Management Studio 2005 I can register multiple servers, but next time I open
> it only the last one I had connected shows. Is there a way to maintain
> multiple connections?

Registering Servers

In Enterprise Manager 2000 I was able to register multiple servers and next
time I opened it those servers would automatically connect. With SQL Server
Management Studio 2005 I can register multiple servers, but next time I open
it only the last one I had connected shows. Is there a way to maintain
multiple connections?Hi Gary
I believe the object browser will show the server that you have logged into,
you will need to log into the other servers to get them in object explorer.
You want to raise this as an request at
http://lab.msdn.microsoft.com/produ...ck/default.aspx
John
"Gary L" wrote:

> In Enterprise Manager 2000 I was able to register multiple servers and nex
t
> time I opened it those servers would automatically connect. With SQL Serv
er
> Management Studio 2005 I can register multiple servers, but next time I op
en
> it only the last one I had connected shows. Is there a way to maintain
> multiple connections?

Registering Servers

In Enterprise Manager 2000 I was able to register multiple servers and next
time I opened it those servers would automatically connect. With SQL Server
Management Studio 2005 I can register multiple servers, but next time I open
it only the last one I had connected shows. Is there a way to maintain
multiple connections?Hi Gary
I believe the object browser will show the server that you have logged into,
you will need to log into the other servers to get them in object explorer.
You want to raise this as an request at
http://lab.msdn.microsoft.com/productfeedback/default.aspx
John
"Gary L" wrote:
> In Enterprise Manager 2000 I was able to register multiple servers and next
> time I opened it those servers would automatically connect. With SQL Server
> Management Studio 2005 I can register multiple servers, but next time I open
> it only the last one I had connected shows. Is there a way to maintain
> multiple connections?