I get the following error:
Another snapshot is running for the publication.
I just upgraded to Service pack2 and can not get the replication working. I have not clue what is wrong.
Thanks in Advance,
Phillip M. Tricoli
I'm running SQL 7.0 SP3 on two different machines (one with additional hotfixes). I'm taking a nightly snapshot of imported data on Server1 and pushing it out to another SQL 7.0 server on our network, Server2. All but one table is copied successfully. On the final table, I receive the message, "The process could not bulk copy into table '"%"'."
Error Information Category: Data Source, Source: Server2, Number 4813.
Full error message: "Expected the text length in data stream for bulk copy of text, ntext, or image data."
I've looked up 4813, but it's pretty ambiguous/generic. Also, when I SELECT from Server1 and INSERT INTO Server2 in the QA, I receive no errors. Does anyone have any insight?
Hi,
i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.
2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber
2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.784 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.816 Category:AGENT
Source: WMBT-07
Number: 0
Message: The process could not read file '\WMBT-01
epldatauncLTR-IN001_TEST_PUB20060624034804D_NUM_7.sch' due to OS error 1265.
2006-06-24 00:26:02.831 Category:OS
Source:
Number: 1265
Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.
I am using snapshot replication to copy data from one database to another. I truncate the destination table first. The publisher and the subscriber are different databases on the same server. The data turns into 1 unrecognizable row.
Here is what the source table looks like:
displayTypeID, displayType
1, Title List Formats
2, Title Details
3, Title List Export
4, Pricing Notes
5, clone Mask 001
6, clone Mask 002
7, clone Mask 004
8, clone Mask 008
9, hide From Admin
10, not Common Attr
11, Insert Title
12, title Types
The resulting data looks like this:
DisplayType, displayTypeId
, 1660953600
I see no errors in replication and nothing in the source table looks like bad data that could cause such a malady. I see the problem on two different servers now and identified the source of the problem, the column order is reversed in the source and destination tables. The data types do not even match so I would at the very least expect an error from bcp.
I'm converting an existing production replication process to SQL 2005 for testing. My snapshot for a merge replication is failing.
Environment: Single XP server, no filters, @schema_option = 0x000000000C034DD1
I have several snapshot jobs in my process, 2 for transactional replication and 2 for merge replications. In both types, I'm replicating from A to B and then from B to C. In the failing job, I'm merge replicating from B to C. In this failing snapshot job, the error messages are:
2006-03-01 18:42:17.22 [58%] Bulk copying snapshot data for article 'MyName1'
2006-03-01 18:42:17.34 [58%] Bulk copied snapshot data for article 'MyName2' (0 rows).
2006-03-01 18:42:17.41 [58%] Bulk copied snapshot data for article 'MyName1'(195 rows).
2006-03-01 18:42:29.84 [61%] Discovering dependencies among article objects in the specified publication
2006-03-01 18:42:33.66 [61%] The replication agent had encountered an exception.
2006-03-01 18:42:33.66 Source: Unknown
2006-03-01 18:42:33.66 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2006-03-01 18:42:33.66 Exception Message: Discover dependencies failed.
2006-03-01 18:42:33.66 Message Code: Not Applicable
2006-03-01 18:42:33.66
Does anyone have an idea what I could look for to try and fix this?
The A to B snapshot obviously uses the same tables. My snapshot for merge job (which worked) messages for the A to B snapshot are:
Message
2006-03-01 18:40:24.98 Time spent analyzing foreign key references (seconds): 0.00
2006-03-01 18:40:24.98 Time spent analyzing check and default constraint references (seconds): 0.19
2006-03-01 18:40:24.98 Time spent analyzing non-article object dependencies (seconds): 0.42
2006-03-01 18:40:24.98 Time spent preparing snapshot generation (seconds): 35.49
2006-03-01 18:40:24.98 Time spent generating merge replication procedures (seconds): 41.39
2006-03-01 18:40:24.98 Time spent generating merge replication stored procedure scripts for subscribers (seconds): 20.66
2006-03-01 18:40:24.98 Time spent bulk copying data (seconds): 1.25
2006-03-01 18:40:24.98 Time spent copying/compressing generated file (seconds): 0.00
2006-03-01 18:40:25.00 Time spent adding rowguid columns/creating merge triggers (seconds): 1.38
2006-03-01 18:40:25.00 Time spent posting snapshot commands (seconds): 25.97
2006-03-01 18:40:25.00 *******************************************************************************
I kicked off the job again and got a different error message:
Message
2006-03-01 19:17:30.32 ---------------------------------------------
2006-03-01 19:17:30.32 -BcpBatchSize 100000
2006-03-01 19:17:30.32 -HistoryVerboseLevel 2
2006-03-01 19:17:30.32 -LoginTimeout 15
2006-03-01 19:17:30.32 -QueryTimeout 1800
2006-03-01 19:17:30.32 ---------------------------------------------
2006-03-01 19:17:30.32 Connecting to Publisher 'xxx-xxxxxxxx'
2006-03-01 19:17:30.34 Publisher database compatibility level is set to 90.
2006-03-01 19:17:30.36 Retrieving publication and article information from the publisher database 'xxx-xxxxxxxx.xxxxxxxxx'
2006-03-01 19:17:30.54 [0%] The replication agent had encountered an exception.
2006-03-01 19:17:30.54 Source: Replication
2006-03-01 19:17:30.54 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-01 19:17:30.54 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-01 19:17:30.54 Message Code: 52006
2006-03-01 19:17:30.54
Does that help provide a clue?
Hi all.
I get the following error when trying to run a pull subscription from the subscriber:
The schema script '' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
The process could not connect to FTP site 'ftp://ESOFTSERVER.NO-IP.INFO' using port 21.
(Source: ESOFT (Agent); Error number: 0)
---------------------------------------------------------------------------------------------------------------
The connection with the server was reset
(Source: (OS); Error number: 12031)
---------------------------------------------------------------------------------------------------------------
I've tried everything I could to solve this problem, but I can't get it working.
Thanks for any advise.
Fabio Reynoso
Hi there,
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
DDL For this problematic table is as follows
CREATE TABLE [dbo].[AssignedCriteria](
[AssignedCriteria] [uniqueidentifier] NOT NULL,
[CriteriaName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetScore] [numeric](5, 0) NULL,
[HRPlan] [uniqueidentifier] NULL,
[ActualScore] [numeric](18, 0) NULL,
[Criteria] [uniqueidentifier] NULL,
[Employee] [uniqueidentifier] NULL,
[IsActive] [bit] NULL,
[addDate] [datetime] NULL,
[totalscore] [numeric](5, 0) NULL,
[isCalc] [bit] NULL,
[Weight] [decimal](18, 2) NULL,
[ProfileDetail] [uniqueidentifier] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_7FF25DF903B6415FBFF24AC954BC88E4] DEFAULT (newsequentialid()),
CONSTRAINT [PK_AssignedCriteria] PRIMARY KEY CLUSTERED
(
[AssignedCriteria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks.
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedThe database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??
Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM
Create jobs to copy database and restore database in destination servers
------------
Robert at 5/7/2002 11:00:30 AM
Yes and I would rather not use dts to accomplish this task.
------------
Ray Miao at 5/7/2002 10:02:15 AM
Do you have direct network connection to remote server? Did you try dts?
------------
Robert at 5/7/2002 9:08:06 AM
I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.
Thanks
Robert
I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error
message indicating that this object can not be dropped because it is referenced by ....
Do you have any remedy for that? Thanks
2 questions:
1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)
2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?
Thanks so much for any and all help!
Hi Guys,
We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.
If this is possible, will there be any validation problem.
Please help.
Thanks,
Anu
Suppose i want to replicate data from server A to server B
I am using snaphot replication.I did the snapshot replication for the first time
and server B got a snapshot of server A.
Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?
Hi All,I have set up a snapshot replication, and schedule it to run everynight. The snapshot run successfully, and data get replicated to thesubscribed server. However, data do not get transfer as the second dayand there after. I check the job history, the job (distribution) runsuccessfully. I start the snapshot agent again, then data gettransferred. I can schedule the snapshot agent to run every night, butthis is just not the way it's supposed to be. Is there anyone out therecan give me some help. Thanks.
View 1 Replies View RelatedHi,
I am using SQL SERVER 2005 snapshot wizard to create snapshot. But as soon as i create a snapshot it takes away all the indexes and constraint for the tabels on the subscription end although i have this indexes and constraint on Publisher side.
Can someone help me in finding if their is some setting to create a snapshot without losing indexes and constraints or if their is some other way to do this.
Thanks,
Prashant
Hi is that possible to configure replication in the following situation.
server A is built by snap of server B. Because i am able to create publisher on server B but i am unable to create same on server A.
could any one explain the situation, why it is happening this way.
any help is appriciated.
if you are curious what kind of error i got.
TITLE: Publisher Properties
------------------------------
An error occurred connecting to Publisher 'SERVER A'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubshrPropertiesErrorSR&EvtID=CantConnectToPublisher&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER B'. (Replication.Utilities)
------------------------------
BUTTONS:
OK
I am looking forward at performing a SNAPSHOT REPLICATION between a 2000-Publisher and a 2005-Subscriber. But on following the wizard the latter is unable to recognize the publisher.
Could anyone help to let me know the possible reason or if there is some setting which i may have missed out?
Thanks,
Varun
Hi Everybody:
We encounter a difficult problem in our production environment.
I have a merge replicaiton which contains 10 tables from Server s1 and database d1 to Server s2 and database d2. Among those 10 tables 5 of them are true merge tables ( carry data both ways, from publisher to subscriber and from subscriber to publisher). Other 5 tables I would like them to carry data only one way from publisher to subscriber ( never merge data from subscriber). Anyone has any suggestions how I impliment this?
Thanks in advance.
Joan
Hello,
We are using Snapshot replication to move data for our Data Warehouse from a server which performs the loading to the production reporting server. We are currently using Sql Server 2005 in both environments. The loading server is setup as the Distributor and Publisher and the production reporting server is setup as the subscriber. However, the replication is not automated instead we only want it to run if the load process is successful. To do this we did not set a schedule for replication but added the jobs that the Replication Wizard created to our own Sql Agent job which manages the load. I have listed the below commands that the sql agent is running for replication in the order in which they are ran.
1) -Publisher [SRVLOADER] -PublisherDB [PRESENTATION] -Distributor [SRVLOADER] -Publication [SRVREPORTER] -DistributorSecurityMode 1
2)-Subscriber [SRVREPORTER] -SubscriberDB [PRESENTATION] -Publisher [SRVLOADER] -Distributor [SRVLOADER] -DistributorSecurityMode 1 -Publication [SRVREPORTER] -PublisherDB [PRESENTATION]
In our own job we run each of these jobs using the EXEC SP_START_JOB() procedure. Each job listed above runs under their own PROXY account.
Finally, we are experiencing an intermittent problem where the Agent will report it is unable to Copy data into "X" table, with "X" being any one of the tables in the reporting database. This has only ever occurred during the automation process and not from manually running each agent job. This problem also might not occur for a couple of days and then might occur 2 or 3 days in a row, which has led us to question the dependability of this approach.
In closing I would like to ask if there is a problem with the way we have set it up? Something we could do to improve the process so it becomes more reliable? Any advice would be greatly appreciated.
Is there anyway to prevent deadlocks during the snapshot replication?
I understand that you can minimize by maybe creating a couple different snapshots (mixing tables to minimize locking while snapshot is being created), but is there any other way?
Thank you
I configured the transactional replication using push method. I use the option generate snapshot. Until the Snapshot complete, I didn't any tables in the subscriber db.After that also I can see in some tables it has indexes and some tables it doesn't but it has data in that table .
How does the bulk copy works from snapshot?i.e. is it first create the table with columns then insert data later it will create the indexes...How the sequence works in the subscriber.
Hello,
Harry Half wit here!!
I know that snapshot replication is the simplest form of syncing with SQL server and I can't even figure that out today!!.
I keep getting myself confused as to who should be configured to be a publisher, distibutor or subscriber etc etc.
My scenario is simple:
1 server creating a daily snapshot of a table and then 1 remote laptop (msde) pulls the snapshot into it's own database.
Heres what i did so far;
I configured the server to be a publisher and distributor (is that right?) and didn't set up any subscribers because i want to do that from my remote.
From the remote I did nothing but go into EM tools"create new pull subscription" but I cannot see the publication on the server.
SHould I set my remote to be a distributor to do this?
any help very much appreciated!!
Snapshot replication.
View synchronization status.
Last status message:
"The job failed. The Job was invoked by User distributor_admin. The last step to run was step 3 (Detect nonlogged agent shutdown.)."
What should I do to make the synchronization work?
I have two clusters and one publishes snapshots and the other cluster subscribes. I am planning an SP2 installation but I see in the SP2 readme that there are only special considerations for merge and transaction replication.
Will I need to patch in a certain order and will it matter if I patch the publisher who is also the distributor and then a week later patch the subscriber?
Thanks
When configuring snapshot replication of the OnePoint database used by MOM i am having the following error being reported in SQL Replication Monitor:
Command attempted:
CREATE TABLE "dbo"."Computer"(
"idComputer" "uniqueidentifier" ROWGUIDCOL NOT NULL,
"Name" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
"Description" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData1" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData2" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"Domain" "nvarchar"(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"DNSName" "nvarchar"(512) COLLATE SQL_Latin1_General_CP1
(Transaction sequence number: 0x000004670002E32300BF00000002, Command ID: 375)
Error messages:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121)
Get help: http://help/4121
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121)
Get help: http://help/4121
Unfortunately I can't find much help with this error.
Any suggestions?
What I'm trying to do is to create a snapshot replication where it is pushed from the publication server. I have it kinda working but my problem is that the subscription server is not grabbing the tables its asking for. It only grabs some replication tables.
I have noticed two things.
First: When I go to Job syncrinization progress it states that its still in progress even though the replicated database is Very small.
Second: In replication Monitor under the subscription watch list tab it states that the subscription has never been initialized.
Thanks,
Kraig
We have a database which was replicated with merge replication. we added a few table and a field to another. When I try to create the snapshot I get:
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
I have detached and re-attached the db, removed all triggers from the tables, and still I cannot recreate the snapshot the last step before the error as listed in the monitor is generating all procedures for all table articles 49%.
When I try to drop the publication in this order:
exec sp_dropsubscription @publication = @publicName, @subscriber = @servName, @article = N'all'
exec sp_droppublication @publication = @publicName
I am getting the following error after dropping the publication
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Could anyone give me a suggestion about how to avoid this error?
Thanks!
hi all,
I grouped my tables into two publications
To each publication i added my "datamart1" server as a subcriber
using the same database as a destination
I dont have problem when i create snapshot since each of the publication
generates its own snapshot.
The only problem i encounter is that. when i begin to sychronize any one of the two
subscription The two subscription gets synchronized.
I need to isolate each publication's syncronization to know exactly how much time would each publication take to get synchronized in the datamart
thanks,
joey
Hi,
I have got a snapshot replication and have got distributor, publisher and subscriber on different servers all having sql server 2005.
I am looking for a piece of code that i can execute in order to initialize snapshot with new data and push data to subscriber on demand.
I assume there are TSQL calling replication sproc but i m not sure which proc should be used. any help would be highly appriciated.
Thanks,
Furrukh Baig
I've set up Transactional replication Pushed from SQL 7 to SQL 2000. All works fine accept I have noticed that the snapshot agent is running once a week and storing a lot of data I don't really need in c:msql7
epldataunc
eplicationnamedatastamp. This is gradually building up and as C is a smallish partition it'll only survive for another week.
My questions are:
1) Can the Snapshot schedule be disabled? I'm under the impression that it's only required when a new subscriber comes along which is never! If I do need a new subscriber I could just manually produce another snapshot.
2) Can I delete the previous snapshot folders under ReplData? On a development server I renamed one of the folders and the ground didn't open up below my feet.
3) What effect would changing the working folder used by the distribution agen have, the distributer is on the publisher (which as I mentioned is pushing) I've tried changing the working folder, on a development server, I manually ran a snapshot which whose BCP files all ended up in my new location and the Transactional Replication didn't seem to care.
I've looked in a few places for a difinitive answer and I'm not having much luck so any help (preferably before saturday) would be greatly appreciated.
Regards.
Hi:
If I create a merge replication snapshot for database A today and I subscribe to the servers on Monday, will the changes made to database A from the time I created the snapshot be applied to the other servers once i subscribe to them
Thanks, Jay
Hi
I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)
We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)
When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.
If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.
Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?
Or is there a better way of doing this?
I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...
TIA