Article in publication not replicating

I have transactional replication set up on SQL Server 2000 SP3a on a Windows
2003 server. The replicated server is also SQL Server 2000 SP3a with Windows
2003. One of the publications contains 6 articles. This publication was
configured and pushed to the subscriber all at the same time. One of the 6
articles was not sending data to the subscriber and there are was no messages
indicating that there was a problem. This is a production critical
application so when I found the error, I dropped the article and recreated
it. It is now working fine.
Has anyone ever run into this type of issue? I am at a loss to explain why
only one article was not replicating.
How was the publication created in the first place? For example did you add
it later using sp_addarticle, or was it added with the other articles in the
beginning?
If you use sp_addarticle you have to issue a sp_refreshpublications after
doing it, for the subscriber to be updated.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gert" <gert@.discussions.microsoft.com> wrote in message
news:D3F7320E-33FB-467D-9058-2FD56D6207B5@.microsoft.com...
>I have transactional replication set up on SQL Server 2000 SP3a on a
>Windows
> 2003 server. The replicated server is also SQL Server 2000 SP3a with
> Windows
> 2003. One of the publications contains 6 articles. This publication was
> configured and pushed to the subscriber all at the same time. One of the
> 6
> articles was not sending data to the subscriber and there are was no
> messages
> indicating that there was a problem. This is a production critical
> application so when I found the error, I dropped the article and recreated
> it. It is now working fine.
> Has anyone ever run into this type of issue? I am at a loss to explain
> why
> only one article was not replicating.
|||All the articles were added at the same time and pushed to the subscriber at
the same time. That is what is so confusing about this incident. I am aware
that when a article is added that it needed to be pushed to the subscriber.
I just cannot determine why only one of the articles disn't appear to be
pushed to the subscriber.
I have a customer looking for an explanation and I am at a loss.
"Hilary Cotter" wrote:

> How was the publication created in the first place? For example did you add
> it later using sp_addarticle, or was it added with the other articles in the
> beginning?
> If you use sp_addarticle you have to issue a sp_refreshpublications after
> doing it, for the subscriber to be updated.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "gert" <gert@.discussions.microsoft.com> wrote in message
> news:D3F7320E-33FB-467D-9058-2FD56D6207B5@.microsoft.com...
>
>
|||can you script out the tables and post them here?
I take it that you are doing plain vanilla transactional replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gert" <gert@.discussions.microsoft.com> wrote in message
news:E52DD2BF-5D3D-40BB-B997-F42CC43ED7B4@.microsoft.com...[vbcol=seagreen]
> All the articles were added at the same time and pushed to the subscriber
> at
> the same time. That is what is so confusing about this incident. I am
> aware
> that when a article is added that it needed to be pushed to the
> subscriber.
> I just cannot determine why only one of the articles disn't appear to be
> pushed to the subscriber.
> I have a customer looking for an explanation and I am at a loss.
>
> "Hilary Cotter" wrote:
|||this is the script. This 1st article is the one that did not replicate.
-- Adding the transactional articles
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_RPO',
@.source_owner = N'dbo',
@.source_object = N'VR_RPO',
@.destination_table = N'VR_RPO',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_SIR',
@.source_owner = N'dbo',
@.source_object = N'VR_SIR',
@.destination_table = N'VR_SIR',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_SP',
@.source_owner = N'dbo',
@.source_object = N'VR_SP',
@.destination_table = N'VR_SP',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_SVI',
@.source_owner = N'dbo',
@.source_object = N'VR_SVI',
@.destination_table = N'VR_SVI',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL', @.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_VEH_COMP',
@.source_owner = N'dbo',
@.source_object = N'VR_VEH_COMP',
@.destination_table = N'VR_VEH_COMP',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_VEH_COMP_HIST',
@.source_owner = N'dbo',
@.source_object = N'VR_VEH_COMP_HIST',
@.destination_table = N'VR_VEH_COMP_HIST',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'NONE',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription
@.publication = N'VEHICLE-Config4',
@.article = N'all',
@.subscriber = N'CAIGSC024',
@.destination_db = N'vehicle',
@.sync_type = N'none',
@.update_mode = N'read only',
@.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
"Hilary Cotter" wrote:

> can you script out the tables and post them here?
> I take it that you are doing plain vanilla transactional replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "gert" <gert@.discussions.microsoft.com> wrote in message
> news:E52DD2BF-5D3D-40BB-B997-F42CC43ED7B4@.microsoft.com...
>
>
|||I was more interested in the schemas of the tables you are replicating!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"gert" <gert@.discussions.microsoft.com> wrote in message
news:48D96A93-6572-4BF7-839C-51437D2ED31D@.microsoft.com...[vbcol=seagreen]
> this is the script. This 1st article is the one that did not replicate.
> -- Adding the transactional articles
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_RPO',
> @.source_owner = N'dbo',
> @.source_object = N'VR_RPO',
> @.destination_table = N'VR_RPO',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_SIR',
> @.source_owner = N'dbo',
> @.source_object = N'VR_SIR',
> @.destination_table = N'VR_SIR',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_SP',
> @.source_owner = N'dbo',
> @.source_object = N'VR_SP',
> @.destination_table = N'VR_SP',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_SVI',
> @.source_owner = N'dbo',
> @.source_object = N'VR_SVI',
> @.destination_table = N'VR_SVI',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL', @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_VEH_COMP',
> @.source_owner = N'dbo',
> @.source_object = N'VR_VEH_COMP',
> @.destination_table = N'VR_VEH_COMP',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_VEH_COMP_HIST',
> @.source_owner = N'dbo',
> @.source_object = N'VR_VEH_COMP_HIST',
> @.destination_table = N'VR_VEH_COMP_HIST',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'NONE',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> -- Adding the transactional subscription
> exec sp_addsubscription
> @.publication = N'VEHICLE-Config4',
> @.article = N'all',
> @.subscriber = N'CAIGSC024',
> @.destination_db = N'vehicle',
> @.sync_type = N'none',
> @.update_mode = N'read only',
> @.offloadagent = 0,
> @.dts_package_location = N'distributor'
> GO
> "Hilary Cotter" wrote:
subscriber[vbcol=seagreen]
be[vbcol=seagreen]
you[vbcol=seagreen]
in[vbcol=seagreen]
after[vbcol=seagreen]
with[vbcol=seagreen]
publication[vbcol=seagreen]
of[vbcol=seagreen]
no[vbcol=seagreen]
explain[vbcol=seagreen]
|||Here is the table schema, and yes I am doing vanilla transaction replication.
We have actually been replicating this data for 3 years, but just recently
moved to a Windows 2003 server.
CREATE TABLE VR_RPO (
RPO_CODE varchar (3) NOT NULL ,
FKVBI_PVI varchar (9) NOT NULL ,
CONSTRAINT PKVRRPO PRIMARY KEY NONCLUSTERED
(
FKVBI_PVI,
RPO_CODE
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_SIR (
ID varchar (3) NOT NULL ,
INSTRUCTION_DATA varchar (56) NULL ,
TYPE varchar (1) NULL ,
SEQUENCE_NUMBER varchar (2) NULL ,
FKVBI_PVI varchar (9) NOT NULL ,
CONSTRAINT PKVRSIR PRIMARY KEY NONCLUSTERED
(
FKVBI_PVI,
ID
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_SP (
KEY1 varchar (8) NOT NULL ,
Key2 varchar (10) NOT NULL ,
PVI char (9) NOT NULL ,
Data varchar (65) NULL ,
Update_Date_Time datetime NULL ,
CONSTRAINT PKVRSP PRIMARY KEY CLUSTERED
(
KEY1,
Key2,
PVI
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_SVI (
SVI_TYPE varchar (10) NOT NULL ,
SVI_VALUE varchar (20) NOT NULL ,
PVI varchar (9) NOT NULL ,
CONSTRAINT PKVRSVI PRIMARY KEY NONCLUSTERED
(
SVI_TYPE,
SVI_VALUE
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_VEH_COMP (
PVI varchar (9) NOT NULL ,
COMPONENT varchar (10) NOT NULL ,
PASSFAIL char (1) NOT NULL ,
COMPDATA varchar (20) NOT NULL ,
CREATE_DATE_TIME datetime NOT NULL ,
UPDATE_DATE_TIME datetime NULL ,
CONSTRAINT PKVRVEHCOMP PRIMARY KEY CLUSTERED
(
PVI,
COMPONENT
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_VEH_COMP_HIST (
PVI varchar (9) NOT NULL ,
COMPONENT varchar (10) NOT NULL ,
PASSFAIL char (1) NOT NULL ,
COMPDATA varchar (20) NOT NULL ,
CREATE_DATE_TIME datetime NOT NULL ,
UPDATE_DATE_TIME datetime NULL ,
CONSTRAINT PKVRVEHCOMHIS PRIMARY KEY CLUSTERED
(
PVI,
COMPONENT
) WITH FILLFACTOR = 90
)
GO
"Hilary Cotter" wrote:

> I was more interested in the schemas of the tables you are replicating!
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "gert" <gert@.discussions.microsoft.com> wrote in message
> news:48D96A93-6572-4BF7-839C-51437D2ED31D@.microsoft.com...
> subscriber
> be
> you
> in
> after
> with
> publication
> of
> no
> explain
>
>

Article Defaults > can't change

Hello,
We're running transactional replication with no trouble...except we can't
seem to change the article defaults. We are running SQL Server 2000, SP3,
Standard edition.
We don't want data deleted on the subscribers when data is deleted on the
publisher. I follow directions in chapter 4 of Hilary Cotter's book...type
the string 'NONE' in the 'Replace DELETE...' box.
I dropped the subscription before doing this. After saving Default Articles
Property, I exit it, go back in, and the 'NONE' string is gone.
I do this with table articles and indexed view articles...same results. I
try to change a property on the 'Snapshot' tab, same thing. The change
doesn't stick.
Any ideas?
Thanks...
Mojo Jojo
If the None is gone it means that nothing will be replicated. This is an
inconsistency in the GUI/Wizard.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mojo" <Mojo@.discussions.microsoft.com> wrote in message
news:F2F8C8CE-AA36-4957-BBA0-0B29F5CE6CEB@.microsoft.com...
> Hello,
> We're running transactional replication with no trouble...except we can't
> seem to change the article defaults. We are running SQL Server 2000, SP3,
> Standard edition.
> We don't want data deleted on the subscribers when data is deleted on the
> publisher. I follow directions in chapter 4 of Hilary Cotter's book...type
> the string 'NONE' in the 'Replace DELETE...' box.
> I dropped the subscription before doing this. After saving Default
Articles
> Property, I exit it, go back in, and the 'NONE' string is gone.
> I do this with table articles and indexed view articles...same results. I
> try to change a property on the 'Snapshot' tab, same thing. The change
> doesn't stick.
> Any ideas?
> Thanks...
> --
> Mojo Jojo
|||It doesn't seem to matter what I do in the GUI/Wizard...no changes I attempt
to make in article defaults are saved. The behavior of the database doesn't
change.
Mojo Jojo
"Hilary Cotter" wrote:

> If the None is gone it means that nothing will be replicated. This is an
> inconsistency in the GUI/Wizard.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Mojo" <Mojo@.discussions.microsoft.com> wrote in message
> news:F2F8C8CE-AA36-4957-BBA0-0B29F5CE6CEB@.microsoft.com...
> Articles
>
>
|||This is the default behavior. Once you have made this change you can't go
back, unless you use sp_changearticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mojo" <Mojo@.discussions.microsoft.com> wrote in message
news:73D45C24-6410-41E0-80FD-6345DE88DA6F@.microsoft.com...
> It doesn't seem to matter what I do in the GUI/Wizard...no changes I
attempt
> to make in article defaults are saved. The behavior of the database
doesn't[vbcol=seagreen]
> change.
> --
> Mojo Jojo
>
> "Hilary Cotter" wrote:
can't[vbcol=seagreen]
SP3,[vbcol=seagreen]
the[vbcol=seagreen]
book...type[vbcol=seagreen]
results. I[vbcol=seagreen]

Are transactional settings used only during updates?

Hi,

In the Web application I am working on, data is read from a SQL Server
database. At any time, there are about 15 people browsing the web.

The SQL Server database is updated with new information once every
hour. The update takes a couple of minutes. The isolation level during
the update is so to Serialazable so that the front-end does not get
any incorrect data.

Now, here is my problem. When the web page is being loaded, the server
side ASP.NET code uses several SELECT statements at multiple places.
For various design reasons, these SELECT statements cannot be combined
into a single statement. As a result, it may happen that during the
page load, we get some data before an update and some data after an
update.

I am wondering if I must used a transactional lock even for the Web
application although technically it is not updating the database.

Also, after playing with various transactional settings, I noticed the
following behavior for the readers when a writer enters a transaction:

1. If the reader app has not yet executed the query, the call to query
execution blocks until the writer has done its job.
2. If the reader app has already begun executing the query, the call
is not blocked and SQL Server provides the needed isolation.

I do not wish to block the readers while the update is going on.
Ideally, I would like it to be such that even if the writer is
updating, the readers must continue to get the old data, that is,
until the writer commits the update. However, I did not find any
isolation settings that would let me achieve this non-blocking
behavior. Am I missing something?

Thank you in advance for enlightening me.

PradeepPradeep (ipradeep@.msn.com) writes:
> The SQL Server database is updated with new information once every
> hour. The update takes a couple of minutes. The isolation level during
> the update is so to Serialazable so that the front-end does not get
> any incorrect data.

The isolation level for the update process has nothing to do with
what happens at the front-end. What matters for the front-end is its
isolation level.

> Now, here is my problem. When the web page is being loaded, the server
> side ASP.NET code uses several SELECT statements at multiple places.
> For various design reasons, these SELECT statements cannot be combined
> into a single statement. As a result, it may happen that during the
> page load, we get some data before an update and some data after an
> update.
> I am wondering if I must used a transactional lock even for the Web
> application although technically it is not updating the database.

Yes, this is the place for serialiable isolation level. You start a
transaction, so if the update process chimes in while you are reading,
it will be blocked until you are completed and commit.

There is certainly all reason to be careful here. For instance, don't wait
for user input before you commit, because then the update process could
be blocked forever.

> I do not wish to block the readers while the update is going on.
> Ideally, I would like it to be such that even if the writer is
> updating, the readers must continue to get the old data, that is,
> until the writer commits the update. However, I did not find any
> isolation settings that would let me achieve this non-blocking
> behavior. Am I missing something?

This would be possible in Oracle today, and a new isolation level called
Snapshot isolation in the upcoming version of SQL Server, SQL 2005, also
makes this possible.

But now we are in SQL2000, and will have to do the best we can. I would
probably look into that update process. I don't know how much data that
is involved, but "a couple of minutes" for the update sounds a tad long
to me. I would not be surprised if that time can be significantly reduced.
Particularly the time for inserting data into the actual target tables.
If you need to clean up data, you can use staging tables, that are only
used by the update process.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Are there any triggers added in transactional replication

As part of transactional replication, are there cases when triggers created
? I know custom sprocs are created in default trans repl and additional
column is created for updatable subscriptions.. but not too sure about
triggers ..
Hassan,
for immediate or queued updating subscribers in transactional replication,
you'll find triggers on the subscriber's replicated tables. These triggers
will either do a distributed transaction with the publisher, or insert into
the queue table.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Are there any MS SQL operations that are not transactional ?

I pretty new to MS SQL.
Long ago I heard that some MS SQL data definition operations are not
transactional.
Executed in the transaction's boundaries their results persists even if
transaction rolls back.
Could anyone ellaborat on that subject ?
Thank you.Table variables are non-transaction, that is to say, they aren't affected by
transaction scope - there is still logging on the transaction log.
declare @.tb table ( mycol int )
insert @.tb values( 1 )
begin tran
update @.tb set mycol = mycol + 1
rollback tran
select * from @.tb
The above returns 2, if you use a temporary table or permanent table you
will get 1...
create table #tb ( mycol int )
insert #tb values( 1 )
begin tran
update #tb set mycol = mycol + 1
rollback tran
select * from #tb
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||TRUNCATE table.
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||That's incorrect. TRUNCATE participates in a transaction like any other
DML operation (table variables excepted).
David Portas
SQL Server MVP
--|||> TRUNCATE table.
?
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
ROLLBACK
SELECT Col1 FROM MyTable
DROP TABLE MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>|||You can have non transaction DDL inside a transaction if you use xp_cmdshell
and osql to execute the DDL. Extended stored procedures are not included in
a transaction. But if you are looking for a bug or "feature" where plain DDL
(table variables excepted as mentioned in the other posts) is not
transactional, there is none I am aware of in SQL Server 2000, although
there might have been in long ago versions like 6.0 or 6.5.
Jacco Schalkwijk
SQL Server MVP
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||I stand corrected.
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>

Are there any limitations on tables to be published for republishi

Hey. I have successfully setup Merge and transactional pubs on a serverA as
publisher and serverB as subscriber. Now, I want to partition the data in
ServerB and send it back to serverA in a different db. This is in testing. In
prod, it'll be going to a different server. The problem is that the tables
from the transactional publication can't be republished. Is this true or am i
doing something wrong? Can you guide me a bit through this? Thank you...
I'm sorry. I found the solution for the problem.