replication error with oracle

Hi,
I have a transactional replication with Oracle Server, and when it generate the initial snapshot, I got an error, saying ORA-00906, missing left parenthesis. It's actually just because when the snapshot agent tried to genenrate a primary key index, it has
a word of "CLUSTERED" in the script. Is there a way that I can modify the script or at least get rid of the "CLUSTERED' word?
Thanks
Hong Wang,
have a look at sp_addarticle. It has a @.creation_script parameter that
perhaps you could use to apply your own script.
HTH,
Paul Ibison
|||right click on your publication, select properties, go to the articles tab,
click on the browse button to the right of your table. in the snapshot tab,
uncheck include DRI.
Regenerate your snapshot and try again.
Thanks to Allan Mitchell (http://www.sqldts.com,
http://www.intmalemodel.com) for this solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eghZmwnXEHA.3944@.tk2msftngp13.phx.gbl...
> Hong Wang,
> have a look at sp_addarticle. It has a @.creation_script parameter that
> perhaps you could use to apply your own script.
> HTH,
> Paul Ibison
>

Replication error in Snapshot publication

Hi,
I built a database replication between 2 SQL 2000 servers in our domain
(both with SP3). I used snapshot publication and it worked for a few days.
But one day the Distribution Agent task failed with the following,
Error message:
Line 95: Incorrect syntax near 'DROP TABLE '.
Error details:
Line 95: Incorrect syntax near 'DROP TABLE '.
(Source: CAWLKW05 (Data source); Error number: 170)
------
Line 102: Incorrect syntax near 'SELECT * INTO '.
(Source: CAWLKW05 (Data source); Error number: 170)
------
Please suggest
Thanks
Johnny H.
can you script out the schema of the articles you are replicating and post
them here along with your publication script?
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
"Johnny Hu" <Johnny Hu@.discussions.microsoft.com> wrote in message
news:A5490840-428B-4025-B3DD-80536F81DE5F@.microsoft.com...
> Hi,
> I built a database replication between 2 SQL 2000 servers in our domain
> (both with SP3). I used snapshot publication and it worked for a few
days.
> But one day the Distribution Agent task failed with the following,
> Error message:
> Line 95: Incorrect syntax near 'DROP TABLE '.
> Error details:
> Line 95: Incorrect syntax near 'DROP TABLE '.
> (Source: CAWLKW05 (Data source); Error number: 170)
> ----
--
> Line 102: Incorrect syntax near 'SELECT * INTO '.
> (Source: CAWLKW05 (Data source); Error number: 170)
> ----
--
> Please suggest
> Thanks
> Johnny H.

Replication error (old Snapshotfiles)

Basically your snapshot does not contain enough information in it to deploy
a new subscriber and then backfill it with changes which have occurred on
the publisher so the subscriber can merge with the publisher.
To do this you need a new snapshot. You might need to change your retention
period to something larger.
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
"Jonas Jegler" <jonas.jegler@.siegel-edv.de> wrote in message
news:OSTIybWNHHA.3944@.TK2MSFTNGP06.phx.gbl...
> We have a SQL Server 2005 Merge Replication to an SQL Server 2005 EXPRESS
> EDITION.
> After indefinable time intervals the error message:
> "They must again implement the Snapshot, since the current Snapshotfiles
> became outdated"
> comes.
> Can someone say something to me about the error message?
>
how frequently is your snapshot generated?
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
"Jonas Jegler" <jonas.jegler@.siegel-edv.de> wrote in message
news:Oc$SQejNHHA.4912@.TK2MSFTNGP02.phx.gbl...
> thankyou for your answer.
> we can do the Replication in each direction. but after indefinable time we
> becomes the error message:
> "They must again implement the Snapshot, since the current Snapshotfiles
> became outdated"
> where can i change the retention period?
>

Replication error --> General Network Error with 11 & 08S01 errors

HI ,
I found that the replication keeps failing and have logged the replication -
-> scalaDB_MLD (not the Snapshot or logreader agent of that publication) and
got the followings details :
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++
Adding alert to msdb..sysreplicationalerts: ErrorId = 48,
Transaction Seqno = 000005ff00005ce7000400000001, Command ID = 5
Message: Replication-Replication Distribution Subsystem: agent MLDDC1-
scalaDB_MLD-SQL1-24 scheduled for retry. General network error. Check your
network documentation.[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call
sp_MSadd_repl_alert(3, 24, 48, 14152, ?, 5, N'MLDDC1', N'scalaDB_MLD',
N'SQL1', N'scalaDB_MLD', ?)}
ErrorId = 48, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET
CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET
ANSI_PADDING ON SET ANSI_WARNINGS ON '
[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET
CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET
ANSI_PADDING ON SET ANSI_WARNINGS ON
ErrorId = 48, SourceTypeId = 5
ErrorCode = '11'
ErrorText = 'General network error. Check your network documentation.'
[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
5, ?, N'11', ?)}
Category:SQLSERVER
Source: SQL1
Number: 11
Message: General network error. Check your network documentation.
ErrorId = 48, SourceTypeId = 4
ErrorCode = '08S01'
ErrorText = 'Communication link failure'
[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
4, ?, N'08S01', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: 08S01
Message: Communication link failure
ErrorId = 48, SourceTypeId = 4
ErrorCode = '08S01'
ErrorText = 'Communication link failure'
[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
4, ?, N'08S01', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: 08S01
Message: Communication link failure
[11/24/2005 3:52:08 PM]SQL1.scalaDB_MLD: exec dbo.sp_MSupdatelastsyncinfo
N'MLDDC1',N'scalaDB_MLD', N'', 0, 5, N'General network error. Check your
network documentation.'
Disconnecting from Subscriber 'SQL1'
Disconnecting from Distributor 'MLDDC1'
Disconnecting from Distributor History 'MLDDC1'
The agent failed with a 'Retry' status. Try to run the agent at a later time.
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++
i could see there is ODBC SQL Server Driver that caused the communication
link failure, so i have manually created a SQL SERVER odbc link from the data
sources and it seems that i could connect
i have checked the event viewer but could not see any particular details
where it can show connectivity issue
in fact the error happens intermittenly , but previously there's no change of
the settings and it could work
Wherelse can i check to be able to resolve this error once & for all ?
I am using SQL Server 2000 , SP3a , TCP/IP protocol using the default port#
1433
appreciate ur advise
tks & rdgs
Message posted via http://www.droptable.com
Hi,
Refering to the same issue below : i could see the agent profile details
(default) as below
BcpBatchSize - 100000
CommitBatchSize - 100
CommmitBatchThreshold - 1000
Are those in bytes and shld any of the values be decreased/increased ?
tks & rdgs
maxzsim wrote:
>HI ,
> I found that the replication keeps failing and have logged the replication -
>-> scalaDB_MLD (not the Snapshot or logreader agent of that publication) and
>got the followings details :
>+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++
> Adding alert to msdb..sysreplicationalerts: ErrorId = 48,
>Transaction Seqno = 000005ff00005ce7000400000001, Command ID = 5
>Message: Replication-Replication Distribution Subsystem: agent MLDDC1-
>scalaDB_MLD-SQL1-24 scheduled for retry. General network error. Check your
>network documentation.[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call
>sp_MSadd_repl_alert(3, 24, 48, 14152, ?, 5, N'MLDDC1', N'scalaDB_MLD',
>N'SQL1', N'scalaDB_MLD', ?)}
>ErrorId = 48, SourceTypeId = 1
>ErrorCode = ''
>ErrorText = 'SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET
>CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET
>ANSI_PADDING ON SET ANSI_WARNINGS ON '
>[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
>1, ?, N'', ?)}
>Category:COMMAND
>Source: Failed Command
>Number:
>Message: SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET
>CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET
>ANSI_PADDING ON SET ANSI_WARNINGS ON
>ErrorId = 48, SourceTypeId = 5
>ErrorCode = '11'
>ErrorText = 'General network error. Check your network documentation.'
>[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
>5, ?, N'11', ?)}
>Category:SQLSERVER
>Source: SQL1
>Number: 11
>Message: General network error. Check your network documentation.
>ErrorId = 48, SourceTypeId = 4
>ErrorCode = '08S01'
>ErrorText = 'Communication link failure'
>[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
>4, ?, N'08S01', ?)}
>Category:ODBC
>Source: ODBC SQL Server Driver
>Number: 08S01
>Message: Communication link failure
>ErrorId = 48, SourceTypeId = 4
>ErrorCode = '08S01'
>ErrorText = 'Communication link failure'
>[11/24/2005 3:52:08 PM]MLDDC1.distribution: {call sp_MSadd_repl_error(48, 0,
>4, ?, N'08S01', ?)}
>Category:ODBC
>Source: ODBC SQL Server Driver
>Number: 08S01
>Message: Communication link failure
>[11/24/2005 3:52:08 PM]SQL1.scalaDB_MLD: exec dbo.sp_MSupdatelastsyncinfo
>N'MLDDC1',N'scalaDB_MLD', N'', 0, 5, N'General network error. Check your
>network documentation.'
>Disconnecting from Subscriber 'SQL1'
>Disconnecting from Distributor 'MLDDC1'
>Disconnecting from Distributor History 'MLDDC1'
>The agent failed with a 'Retry' status. Try to run the agent at a later time.
>+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++
>i could see there is ODBC SQL Server Driver that caused the communication
>link failure, so i have manually created a SQL SERVER odbc link from the data
>sources and it seems that i could connect
>i have checked the event viewer but could not see any particular details
>where it can show connectivity issue
>in fact the error happens intermittenly , but previously there's no change of
>the settings and it could work
>Wherelse can i check to be able to resolve this error once & for all ?
>I am using SQL Server 2000 , SP3a , TCP/IP protocol using the default port#
>1433
>appreciate ur advise
>tks & rdgs
Message posted via http://www.droptable.com
|||Hi ,
Futher info is the script in that agent : -Subscriber [SQL1] -SubscriberDB
[scalaDB_MLD] -Publisher [MLDDC1] -Distributor [MLDDC1] -
DistributorSecurityMode 1 -PublisherDB [scalaDB_MLD] -Continuous
appreciate any advise
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi,
> Refering to the same issue below : i could see the agent profile details
>(default) as below
> BcpBatchSize - 100000
> CommitBatchSize - 100
> CommmitBatchThreshold - 1000
> Are those in bytes and shld any of the values be decreased/increased ?
>tks & rdgs
>[quoted text clipped - 80 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200511/1
|||Hi ,
I have checked thru the error details , it was able to applied some of
the schema files xxx.sch before getting the general network error msg
The last command was :
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++
SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET
ANSI_WARNINGS ON
Transaction sequence number and command ID of last execution batch are
0x000005FF00005CE7000400000001 and 20.
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++
does this mean anything ?
appreciate if anyone could point out where it might have gone wrong
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi ,
>Futher info is the script in that agent : -Subscriber [SQL1] -SubscriberDB
>[scalaDB_MLD] -Publisher [MLDDC1] -Distributor [MLDDC1] -
>DistributorSecurityMode 1 -PublisherDB [scalaDB_MLD] -Continuous
>appreciate any advise
>tks & rdgs
>[quoted text clipped - 13 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200511/1

Replication error

Hi

I am trying to replicate a database using the snapshot method. The publication worked fine, when the second server tried to pull the replication, it started, but then come up with the following error whilst creating the tables.

Insert Error: Column name or number of supplied values does not match table definition.

Any ideas?

cheers

SimonThe error usually occurs in transactional replication when someone accidentally changed the DDL in subscriber. You can reinitialize the subscription from the publication database and refresh your subscription.|||still no luck. Didn't help. Stil had the same error, any other ideas?

cheers

Replication doesnt replicate

Ok, I'm knee-deep in replication-hell.

I'm trying to set up snapshot replication between two SQL Server 2000 databases over the internet. Both servers run Windows 2000 server edition and SQL server 2000. The idea is to send certain local information, stored in database tables to a database from which the information is published on a website.

I've set up the distribution database (with the original name: distribution) and connected a publisher to it (let's call that one "source_database") using the sp_adddistpublisher stored procedure. No problems there...
Next step, I added a publication, the tables in source_database, and a subscriber, the website database. I've opened up port 1433 on both servers, but still it won't send anything from the source_database to the website database...

It gives no errors, but just doesn't do anything...
Now, I've looked for possible problems and I think these problems might be able to cause problems:
- The initial snapshot is not reaching the subscriber
- I've made a push subscription which may has to become a pull subscription
- The RPC's aren't available, because both servers are highly secured
- The wrong serverpack is installed (this one is a wild guess)

Does anybody have any idea on how I can get my distribution working or solve any of the problems above, cause I'm running out of idea's...
(P.S. I'm a bit of a noobie to replication, I've set it up in a test environment before and it just worked perfectly, but the real thing isn't)You may have already done it, but just to make sure that you have define the publisher and the subscriber in the source server, right? Does your source server know the network id of the target server? Simply try to register the target server's name on EM of the source to see.|||Service pack will get you bad. I experimented with 2 servers running 194 and 818 builds, and was getting nothing, no matter what I tried. And when you apply SP3 you'd notice that there are scripts that directly affect replication (repl*.sql)|||I solved the problem...
It did turn out to be the service pack.
Sp3a was installed by a sys-admin, but not correctly.
After re-installing Sp3a everything just started working.

Man, I'm glad that one's solved, I had like 5 minutes left till insanity... :cool:

Anyhow, thanx for the help...|||Man, I'm glad that one's solved, I had like 5 minutes left till insanity... :cool: Oh, so you aren't really a dba yet? Actually, once I gave up on the sanity part, the rest seemed a lot easier!

-PatP

Replication does not start

We would like to make snapshot replicaiton. We use SQL Server 2000
Enterprise Edion with sp3 (over windows 2k advanced server).
We configured the server distributor and publisher correctly. After we
create publication and Publication properties- Run agent Now- we take the
following error message.
" Non-SysAdmins have been denied to permission to run replication jobsteps
without a proxy account.The step failed."
SQL Server and SQL Server agent services are running a domain administrator
account.
Both services use the same startup account and sql server agent's account
has sysadmin role.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4b4701c490c5$e6de1e50$a301280a@.phx.gbl...
> Can you make the sql server service and the sql server
> agent both use the same startup account. Alternatively
> you could add the sql server agent's service login
> account to the sysadmin role on sql server.
> HTH,
> Paul Ibison
|||Right-click the replication moniotor and select
distributor properties. On the publisher's tab click the
relevant elipsis and check that the agent uses
impersonation to connect to the publisher, then do the
same thing on the subscriber's tab.
HTH,
Paul Ibison
|||i checked it. it same as you said. but still it isnt working.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4bc701c490cf$0a167350$a301280a@.phx.gbl...
> Right-click the replication moniotor and select
> distributor properties. On the publisher's tab click the
> relevant elipsis and check that the agent uses
> impersonation to connect to the publisher, then do the
> same thing on the subscriber's tab.
> HTH,
> Paul Ibison
>
|||Is the SQL account you are using part of the local admin group on the
Publisher machine?
Can you run this from Query Analyzer on the Subscriber?
master.dbo.xp_cmdshell 'dir \\publisherServerName\c$'
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Digikoray" <kkayisli@.hotmail.com> wrote in message
news:eLu2cJNkEHA.644@.tk2msftngp13.phx.gbl...
> i checked it. it same as you said. but still it isnt working.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4bc701c490cf$0a167350$a301280a@.phx.gbl...
>
|||- The SQL account is part of the domain admins group( It's already part
of the Local administrator group.)
- I don't know how I can use Query Analyzer .
- What do you mean with master.dbo.xp_cmdshell.
-
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uxntDZOkEHA.592@.TK2MSFTNGP11.phx.gbl...
> Is the SQL account you are using part of the local admin group on the
> Publisher machine?
> Can you run this from Query Analyzer on the Subscriber?
> master.dbo.xp_cmdshell 'dir \\publisherServerName\c$'
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Digikoray" <kkayisli@.hotmail.com> wrote in message
> news:eLu2cJNkEHA.644@.tk2msftngp13.phx.gbl...
>
|||go to start, run, and type isqlw - that's query analyzer
the extended stored procedure xp_cmdshell will allow you to run command
shell (dos) arguments within query analyzer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Digikoray" <kkayisli@.hotmail.com> wrote in message
news:%237f3b%23OkEHA.712@.TK2MSFTNGP09.phx.gbl...
> - The SQL account is part of the domain admins group( It's already part
> of the Local administrator group.)
> - I don't know how I can use Query Analyzer .
> - What do you mean with master.dbo.xp_cmdshell.
> -
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uxntDZOkEHA.592@.TK2MSFTNGP11.phx.gbl...
>

Replication as a job

For a pull snapshot replication: I want to run it as a job ie fully automated. Rite now I have start the 'Snapshot' under the publisher->Replication Monitor>Publishers>ServerName>Name.. and then manually start the Pull replication agent.

Is there a way to do this automatically ie in a job?You should be able to simply go to Agent Properties and set up a schedule for it.

Replication and triggers

Does anyone know if when applying the initial snapshot for merge replication if it fires the insert or update user written triggers?Does anyone know Yep

if it fires the insert or update user written triggers? No it does'nt|||Since I could not get an answer I went ahead and applied the snapshot. When it had completed there were no records that my triggers would have generated so I guess the answer is NO IT DOES NOT FIRE THE TRIGGERS.

replication and log shrinking

Since I created a replication (snapshot basic - push) when I'm running the backup log ... with truncate_ only....the size of the transaction log don't lessens. why? (sql2005 without sp)I set to simple the recovery mode and back to full....nothing
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||

check of any open transaction . DBCC Opentran

Madhu

|||thnx
I run and and this is the result:

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4990:4944:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What can i do with this?|||

From BOL

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft? SQL Server?, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

you need to termintate this open transaction

http://msdn2.microsoft.com/en-us/library/ms182792.aspx

http://www.sql-server-performance.com/dbcc_commands.asp

google it you will fine many

Madhu

|||I stopped the replication (snapshot) and after I could shrink the log....
So this is a bug? ( the db running without any sp)

I tried....I re-set the replication and the symptom is occurred again....
so when I set a replication on this db, I can't shrink the transaction log anymore...

Replication 2000 - What if Publisher dead

What if a Publication and Distribution server machine fail to work. Do I need to reinitiliaze the whole snapshot on a new machine and retransmit the data to the subscriber. Or can I make use of replication script to recreate the publication and distribution and then replicate to existing subscriber using same machine name?

ThanksHow bad did it fail?|||Let say, if unable to load the SCSI hardisk, or the hardware failured.
Thanks

Originally posted by joejcheng
How bad did it fail?

replication "Invalid syntax error": Table has computed primary key

Hi,

I have set up a publisher using transactional replication. ( all seems ok). The initial snapshot has been generated.

The replication share on the distributor has all the generated DDL in it.

I add a subscriber. The tables are generated up to tblCountry then I get an incorrect syntax near ')' error

The Replication Monitor shows the following code as the cause. ( bold indicates incorrect sql)

Is this a bug in Replication (as this is an autogenerated sp)or have I configured something incorrectly?

The ddl for the table index is as follows ( from the replication folder)

/-

CREATE TABLE [APP].[tblCountry](
[CountryId] AS ([ISO 3166-1 NUMERIC-3]) PERSISTED NOT NULL,
[CountryCode] AS ([ISO 3166-1 ALPHA-2]) PERSISTED NOT NULL,
[CountryName] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-2] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-3] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 NUMERIC-3] [int] NOT NULL
)

GO

/

/ Keys ddl (.dx)

ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [PK_TBLCOUNTRY] PRIMARY KEY CLUSTERED ([CountryId])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA2] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-2])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA3] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-3])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_COUNTRYNAME] UNIQUE NONCLUSTERED ([CountryName])
go

--/

/

Command attempted:

create procedure "sp_MSins_APPtblCountry_msrepl_ccs"
@.c1 int,@.c2 varchar(80),@.c3 char(2),@.c4 char(3),@.c5 int
as
begin
if exists ( select * from "APP"."tblCountry"
where
)
begin
update "APP"."tblCountry" set
"CountryName" = @.c2
,"ISO 3166-1 ALPHA-2" = @.c3
,"ISO 3166-1 ALPHA-3" = @.c4
,"ISO 3166-1 NUMERIC-3" = @.c5
where
end
else
begin
insert into "APP"."tblCountry"(
"CountryName"
,"ISO 3166-1 ALPHA-2"
,"ISO 3166-1 ALPHA-3"
,"ISO 3166-1 NUMERIC-3"
)
values (
@.c2
,@.c3
,@.c4

(Transaction sequence number: 0x00000016000004F2014500000000, Command ID: 213)

/

There is a known snapshot scripting bug that has symptoms like you have above, but I need to know the original table schema and PK columns that exist on the publisher side, can you cut/paste it below so we can verify?

In the meantime, you can try disabling concurrent snapshot to get around this scripting issue.

|||

Hi Gareth, I have verified that this issue still exists on our latest internal build of SQL2005 SP2, and I will try to get the fix in for the final SP2 release. As for working around the problem, in addition to not using concurrent snapshot (sp_changepublication <PublicationName>, 'sync_method', 'native') as Greg had suggested, you can also modify the .sch script to include "CountryId" = @.c1 as the where clause of the broken update statement. This should save you from having to regenerate the snapshot again. And if you are running the Enterprise edition of SQL2005, you can also use the undocumented 'database snapshot' sync_method (to be made official in SP2) which will prevent locks from being held for an extended period of time on the published tables when the snapshot is being generated.

-Raymond

|||

Greg , Raymond

I altered the script as suggested and this solved my problem

thanks for the help

Replication

when using snapshots in replication, these files get copied to the hard drive. Can they be automatically deleted after the snapshot is posted?
Thanks
Niles,
if you have anonymous subscribers then they'll stay around until the publication's subscriber's timeout value. If you have standard subscribers, these files will be removed once they have all initialized. For transactional, this is when the distribution a
gent of each subscriber has run. The agent which removes the snapshot files is the distribution cleanup agent, which runs every 10 mins. You could run this immediately (manually) after your last initialization or you could schedule it to run more frequent
ly.
HTH,
Paul Ibison
|||What is the difference between anonymous subscriptions and standard and can it be switched easily
Thanks
|||An anonymous subscription is a type of pull subscription for which detailed
information about the subscription and the Subscriber is not stored. The
Subscriber is responsible for keeping an anonymous subscription
synchronized, and they are particularly used if Subscribers use the Internet
to access publications.
sp_helpsubscription will tell you if the subscription is anonymous or
http://msdn.microsoft.com/library/de...repl3_4dym.asp
Typically, the Subscriber is not explicitly named at the Publisher when
using anonymous subscriptions. So to change from a named subscription to an
anonymous one is not so simple and you'd drop and recreate the subscription.
To see if any of this is relevant to your case have a look at the
allow_anonymous value of sp_helppublication.
HTH,
Paul Ibison

replication

wot should be the ans of the below scenario..
You are the administrator of a SQL Server 2000 computer. You want to set up snapshot replication on the server. The server will serve as Publisher and Distributor for a minimum of 40/50 Subscribers. Currently, you want to publish 3GB/4GB of data, but the data is expected to grow over time. Subscribers will receive a new snapshot each month. You want to minimize the workload on the Publisher/Distributor. Which two actions should you take to configure snapshot replication? (Each correct answer presents part of the solution. Choose two)
a. Store the snapshot in the default folder on the Publisher/Distributor.
b. Store the snapshot in an alternative folder on the Publisher/Distributor.
c. Store the snapshot in a shared folder on a file server.
d. Create pull subscriptions.
e. Create push subscriptions.I would store the snapshot on a file server
and create pull subscriptions
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...
> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set
up snapshot replication on the server. The server will serve as Publisher
and Distributor for a minimum of 40/50 Subscribers. Currently, you want to
publish 3GB/4GB of data, but the data is expected to grow over time.
Subscribers will receive a new snapshot each month. You want to minimize the
workload on the Publisher/Distributor. Which two actions should you take to
configure snapshot replication? (Each correct answer presents part of the
solution. Choose two)
>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the
Publisher/Distributor.
> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>|||Although I am not a replication expert, I would choose c and d.
Pull subscriptions ( by default) cause the distribution process to run at
the subscriber ( and not the publisher) which offloads work from the
publisher... ( As a side note, SQL 2000 allows a push subscription to have
the subscriber do his own work as well, it is called remote activation..)
Storing the snapshot files somewhere OTHER than the publisher/distributor
simply means that the publisher does NOT have to do IO during the initial
sync with the subscriber..
Hope this help.s
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...
> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set
up snapshot replication on the server. The server will serve as Publisher
and Distributor for a minimum of 40/50 Subscribers. Currently, you want to
publish 3GB/4GB of data, but the data is expected to grow over time.
Subscribers will receive a new snapshot each month. You want to minimize the
workload on the Publisher/Distributor. Which two actions should you take to
configure snapshot replication? (Each correct answer presents part of the
solution. Choose two)
>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the
Publisher/Distributor.
> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>

Replication

when using snapshots in replication, these files get copied to the hard drive. Can they be automatically deleted after the snapshot is posted
ThanksAn anonymous subscription is a type of pull subscription for which detailed
information about the subscription and the Subscriber is not stored. The
Subscriber is responsible for keeping an anonymous subscription
synchronized, and they are particularly used if Subscribers use the Internet
to access publications.
sp_helpsubscription will tell you if the subscription is anonymous or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl3_4dym.asp
Typically, the Subscriber is not explicitly named at the Publisher when
using anonymous subscriptions. So to change from a named subscription to an
anonymous one is not so simple and you'd drop and recreate the subscription.
To see if any of this is relevant to your case have a look at the
allow_anonymous value of sp_helppublication.
HTH,
Paul Ibison

replication

wot should be the ans of the below scenario...
You are the administrator of a SQL Server 2000 computer. You want to set up
snapshot replication on the server. The server will serve as Publisher and D
istributor for a minimum of 40/50 Subscribers. Currently, you want to publis
h 3GB/4GB of data, but the
data is expected to grow over time. Subscribers will receive a new snapshot
each month. You want to minimize the workload on the Publisher/Distributor.
Which two actions should you take to configure snapshot replication? (Each c
orrect answer presents part
of the solution. Choose two)
a. Store the snapshot in the default folder on the Publisher/Distributor.
b. Store the snapshot in an alternative folder on the Publisher/Distributor.
c. Store the snapshot in a shared folder on a file server.
d. Create pull subscriptions.
e. Create push subscriptions.I would store the snapshot on a file server
and create pull subscriptions
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...

quote:

---

> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set

---

up snapshot replication on the server. The server will serve as Publisher
and Distributor for a minimum of 40/50 Subscribers. Currently, you want to
publish 3GB/4GB of data, but the data is expected to grow over time.
Subscribers will receive a new snapshot each month. You want to minimize the
workload on the Publisher/Distributor. Which two actions should you take to
configure snapshot replication? (Each correct answer presents part of the
solution. Choose two)

quote:

---

>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the

---

Publisher/Distributor.

quote:

---

> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>

---

|||Although I am not a replication expert, I would choose c and d.
Pull subscriptions ( by default) cause the distribution process to run at
the subscriber ( and not the publisher) which offloads work from the
publisher... ( As a side note, SQL 2000 allows a push subscription to have
the subscriber do his own work as well, it is called remote activation..)
Storing the snapshot files somewhere OTHER than the publisher/distributor
simply means that the publisher does NOT have to do IO during the initial
sync with the subscriber..
Hope this help.s
"rahul" <anonymous@.discussions.microsoft.com> wrote in message
news:A26AAAD3-6275-4C3D-90D9-F97CEB0FE429@.microsoft.com...

quote:

---

> wot should be the ans of the below scenario...
> You are the administrator of a SQL Server 2000 computer. You want to set

---

up snapshot replication on the server. The server will serve as Publisher
and Distributor for a minimum of 40/50 Subscribers. Currently, you want to
publish 3GB/4GB of data, but the data is expected to grow over time.
Subscribers will receive a new snapshot each month. You want to minimize the
workload on the Publisher/Distributor. Which two actions should you take to
configure snapshot replication? (Each correct answer presents part of the
solution. Choose two)

quote:

---

>
> a. Store the snapshot in the default folder on the Publisher/Distributor.
> b. Store the snapshot in an alternative folder on the

---

Publisher/Distributor.

quote:

---

> c. Store the snapshot in a shared folder on a file server.
> d. Create pull subscriptions.
> e. Create push subscriptions.
>

---

Replication

when using snapshots in replication, these files get copied to the hard driv
e. Can they be automatically deleted after the snapshot is posted?
ThanksNiles,
if you have anonymous subscribers then they'll stay around until the publica
tion's subscriber's timeout value. If you have standard subscribers, these f
iles will be removed once they have all initialized. For transactional, this
is when the distribution a
gent of each subscriber has run. The agent which removes the snapshot files
is the distribution cleanup agent, which runs every 10 mins. You could run t
his immediately (manually) after your last initialization or you could sched
ule it to run more frequent
ly.
HTH,
Paul Ibison|||What is the difference between anonymous subscriptions and standard and can
it be switched easily
Thanks|||An anonymous subscription is a type of pull subscription for which detailed
information about the subscription and the Subscriber is not stored. The
Subscriber is responsible for keeping an anonymous subscription
synchronized, and they are particularly used if Subscribers use the Internet
to access publications.
sp_helpsubscription will tell you if the subscription is anonymous or
l3_4dym.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
l3_4dym.asp
Typically, the Subscriber is not explicitly named at the Publisher when
using anonymous subscriptions. So to change from a named subscription to an
anonymous one is not so simple and you'd drop and recreate the subscription.
To see if any of this is relevant to your case have a look at the
allow_anonymous value of sp_helppublication.
HTH,
Paul Ibison

Replicating Stored Procedure

Hi:
I made a change to the stored proc in the publisher database but the change
did not get replicated. I should not have to run the snapshot agent again to
do that. Please let me know how can this be easily done.
Thanks
Mike,
this is better handled in SQL 2005, but in 2000 we can use sp_addscriptexec,
linked servers or manual addition of the new code. For this reason I
generally add the code parts of my publications (sps, views, functions) to a
separate publication, so if I decide to use replication to send them over, it
doesn't disturb my main publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use sp_addscriptexec to send the commands if your subscribers were deployed
through UNC(file copies). If there were deployed through ftp you will have
to connect to them individual and run the commands individually.
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
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:15028C08-3FA7-4647-9C23-7A2698AF8C41@.microsoft.com...
> Hi:
> I made a change to the stored proc in the publisher database but the
> change
> did not get replicated. I should not have to run the snapshot agent again
> to
> do that. Please let me know how can this be easily done.
> Thanks
>

Replicating Multiple Databases

I am currently using snapshot replication to backup a single SQL database to
a server at home. What technique(s) would be best if I wanted to backup say
6 databases? These would all be one-way as no changes are made to the data
from the backup server. Also, the data is not SO critical so as not to be
able to afford the loss of up to 24 hours worth of changes from one to the
other. I've considered doing 6 Publications-Subscriptions etc, but I
thought there must be a better way. Is there?
Thanks
Carlos, the publications are on a per-database basis, so there isn't a
simpler method than the one you're considering. Potential alternatives (not
simpler) include log shipping and transactional replication. If you're
concerned about bandwidth issues then these would be more suitable.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If you have big pipes between your publisher and subscribers I would
recommend log shipping. Otherwise I would recommend transactional
replication as only the changes will be replicated.
For transactional replication you will need a pk on each table; although
there are ways around this.
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
"Carlos Y. Maldonado, Ph.D." <cmaldonado@.earthlink.net> wrote in message
news:U5i4f.14372$vw6.9184@.newsread1.news.atl.earth link.net...
>I am currently using snapshot replication to backup a single SQL database
>to a server at home. What technique(s) would be best if I wanted to backup
>say 6 databases? These would all be one-way as no changes are made to the
>data from the backup server. Also, the data is not SO critical so as not
>to be able to afford the loss of up to 24 hours worth of changes from one
>to the other. I've considered doing 6 Publications-Subscriptions etc, but
>I thought there must be a better way. Is there?
> Thanks
>