Hello;
I have worked my way through numerous connection problems and login
issues, but Im left with one problem Im hoping I can get some help
on:
Ive got an SQL 2000 v 8.00.760 SP3a, with a production database Im
trying to replicate from one workgroup to another (yes, workgroups
ack!) Ive got the connections all worked out, it was a few days of
fun...
Ive setup a transactional replication between a production database
and a blank database.
The database is about 1.5Gb so its a hefty size.
I setup the transactional replication to include an initialization of
the Schema and Data, and asked the wizard to start the snapshot agent
and begin the initialization process immediately.
The Subscription creates and the snapshot is successful.
However, the Push fails with the following error: "Unable to
replicate a view or function because the referenced objects or columns
are not present on the subscriber"
Any help would be greatly appreciated!
1st time poster, Im impressed with the knowledge of the boards
members, but Im new, so go easy on me if its obvious to you :D
EDIT: After looking through the replication, I have noticed that there
are 3x as many tables in the Production database as in the replicated
database, is this the snapshot not pulling properly, or implementing
properly?
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict233874.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=810666
"SNA2" wrote:
> Hello;
> I have worked my way through numerous connection problems and
> login issues, but I'm left with one problem I'm hoping I can
> get some help on:
> I've got an SQL 2000 v 8.00.760 SP3a, with a production
> database I'm trying to replicate from one workgroup to another
> (yes, workgroups ack!) I've got the connections all worked
> out, it was a few days of fun...
> I've setup a transactional replication between a production
> database and a blank database.
> The database is about 1.5Gb so it's a hefty size.
> I setup the transactional replication to include an
> initialization of the Schema and Data, and asked the wizard to
> start the snapshot agent and begin the initialization process
> immediately.
> The Subscription creates and the snapshot is successful.
> However, the Push fails with the following error: "Unable to
> replicate a view or function because the referenced objects or
> columns are not present on the subscriber"
> Any help would be greatly appreciated!
>
> 1st time poster, I'm impressed with the knowledge of the
> board's members, but I'm new, so go easy on me if it's obvious
> to you :D
> EDIT: After looking through the replication, I have noticed
> that there are 3x as many tables in the Production database as
> in the replicated database, is this the snapshot not pulling
> properly, or implementing properly?
On a side note, I did try initially to do a snapshot replication of
this database (before I realized how large it was) and was told by the
wizard that it couldnt do more than 255 columns in a replication...
In my humble opinion thats a little limited considering who would be
doing replications, most of our databases are over 255 columns, is
that normal? Or is there any way you can get around that limit in
replication?
For instance this current database is 46,000 Rows, and about 3,500
Columns. The only way I can think of getting the correct schema &
data over (aside from a knowledgeable answer to my above post?) is
doing a backup & restore to the blank database. Would that work?
|||I suspect you are getting your error, because you are replicating some views
and not the base tables for these views. I would script out the view and
then run them in the subscriber to determine which views are missing the
base tables.
I believe you can only replicate tables less than 255 columns. In SQL 2005
the limit is 1000 column tables.
From BOL entitled Publishing data and database objects -
A table used in a snapshot or transactional publication can have a maximum
of 255 columns and a maximum row size of 8,000 bytes. A table used in a
merge publication can have a maximum of 246 columns and should have a
maximum row size of 6,000 bytes, because conflict-tracking columns may
consume up to 2,000 bytes. If row size exceeds 6,000 bytes in a merge
publication, conflict-tracking meta data may be truncated.
Horizontal, vertical, dynamic, and join filters enable you to create
partitions of data to be published. By filtering published
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
"SNA2" <DoNotEmail@.dbForumz.com> wrote in message
news:4_810668_841eddc87755ed7380f614344396c5b6@.dbf orumz.com...
> "SNA2" wrote:
> On a side note, I did try initially to do a snapshot replication of
> this database (before I realized how large it was) and was told by the
> wizard that it couldn't do more than 255 columns in a replication...
> In my humble opinion that's a little limited considering who would be
> doing replications, most of our databases are over 255 columns, is
> that normal? Or is there any way you can get around that limit in
> replication?
> For instance this current database is 46,000 Rows, and about 3,500
> Columns. The only way I can think of getting the correct schema &
> data over (aside from a knowledgeable answer to my above post?) is
> doing a backup & restore to the blank database. Would that work?
|||"Hilary Cotter3" wrote:
> I suspect you are getting your error, because you are
> replicating some views
> and not the base tables for these views. I would script out
> the view and
> then run them in the subscriber to determine which views are
> missing the
> base tables.
> I believe you can only replicate tables less than 255 columns.
> In SQL 2005
> the limit is 1000 column tables.
> From BOL entitled Publishing data and database objects -
> A table used in a snapshot or transactional publication can
> have a maximum
> of 255 columns and a maximum row size of 8,000 bytes. A table
> used in a
> merge publication can have a maximum of 246 columns and should
> have a
> maximum row size of 6,000 bytes, because conflict-tracking
> columns may
> consume up to 2,000 bytes. If row size exceeds 6,000 bytes in
> a merge
> publication, conflict-tracking meta data may be truncated.
> Horizontal, vertical, dynamic, and join filters enable you to
> create
> partitions of data to be published. By filtering published
>
> --
> 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
> "SNA2" <DoNotEmail@.dbForumz.com> wrote in message
> news:4_810668_841eddc87755ed7380f614344396c5b6@.dbf orumz.com...
> > > Hello;
> > >
> > > I have worked my way through numerous connection
> problems and
> > > login issues, but I'm left with one problem I'm
> hoping I can
> > > get some help on:
> > >
> > > I've got an SQL 2000 v 8.00.760 SP3a, with a
> production
> > > database I'm trying to replicate from one workgroup
> to another
> > > (yes, workgroups ack!) I've got the connections all
> worked
> > > out, it was a few days of fun...
> > >
> > > I've setup a transactional replication between a
> production
> > > database and a blank database.
> > > The database is about 1.5Gb so it's a hefty size.
> > > I setup the transactional replication to include an
> > > initialization of the Schema and Data, and asked
> the wizard to
> > > start the snapshot agent and begin the
> initialization process
> > > immediately.
> > > The Subscription creates and the snapshot is
> successful.
> > >
> > > However, the Push fails with the following error:
> "Unable to
> > > replicate a view or function because the referenced
> objects or
> > > columns are not present on the subscriber"
> > >
> > > Any help would be greatly appreciated!
> > >
> > >
> > > 1st time poster, I'm impressed with the knowledge
> of the
> > > board's members, but I'm new, so go easy on me if
> it's obvious
> > > to you :D
> > >
> > > EDIT: After looking through the replication, I have
> noticed
> > > that there are 3x as many tables in the Production
> database as
> > > in the replicated database, is this the snapshot
> not pulling
> > > properly, or implementing properly?
> replication of
> told by the
> replication...
> would be
> columns, is
> limit in
> 3,500
> schema &
> post?) is
> work?
Thank you Hillary! That was exactly the problem. [edit] I thought
Id post the solution I came up with and see if you had any input, see
below:
Im still in a bit of a bind with the Database, I understand that
replication, even with SQL 2005 will be an impossibility with the
database as it exists currently, we are looking at pearing it down and
streamlining it over the next year to bring down the size and the
number of columns, as it is quite a mess. We should be able to bring
it under 1000 columns, so replication can take place.
In the meantime, I have thought up a couple of scenarios to do simple
replication through backup/restore.
The full reason for the replication, is that we have a secure site for
the database, which is the reason for it residing on a workgroup, we
are looking to replicate it over the internet to another secure backup
location, so if (heaven forbid) our main secure site goes down, we can
flip the switch on the secondary location and have a working copy up
to date.
And no, Im not being paranoid, our so-called secure location went
down for a full hour recently... oh joy.
Anyway, the solution we came up with was doing a full backup weekly
and transferring the 1.5gb file over the link on an off-peak time, and
every 2 hours during the week, do a differential backup and send the
smaller differential backup file over as well. (the differential
backup is at the most 10-20mb)
THis gives us the option if something goes wrong at the primary
location to flip the switch and just run the quick differential
restore. It is unfortunately a manual process, so depending on the
timing and how quickly the issue is noticed, sometimes the flip may
take a while.
from SQL BOL I picked up the following example script and have
incorporated it successfully:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH INIT
GO
-- Wait
-- then create differential
BACKUP DATABASE MyNwind
TO MyNwind_2
WITH DIFFERENTIAL
GO
For ease of use, I have it setup through Enterprise Manager to run it
every 2 hours, however Im curious to find out if its possible to
time-stamp the filename within an SQL script like the above to create
a new file every time instead of writing over the old one? ie,
MyNwind_0624050230.bak or something similar?
THis solution seems to work well in place of the problem I had above
of trying to replicate a database that SQL has issues with if the
column count is too large.
Any thoughts? I was quite happy with this approach, as the
differential backup files are relatively small and transfer over the
net quickly enough as to not cause any bandwidth hogging. As I
mentioned, even after a week of changes with our database, the file
was only 10-20mb at the most. But, as pointed out, the way I have it
setup currently, SQL overwrites the file every time the differential
backup is done.
Thanks! Mucho appreciation.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict233874.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815727
Showing posts with label snapshot. Show all posts
Showing posts with label snapshot. Show all posts
Monday, March 26, 2012
push snapshot
Hi I'm trying to setup a push replication between 2 servers on different
networks. both machines can ping eachother using there machine names. I setup
the publication and the distributor on the push server and generate a
snapshot. I then run the distributor but it fails to connect to the
subscriber.
thanks for the help.
Mark
You need to ensure the @.@.servername of the subscriber is how the
publisher/distributor refer to it and vice versa. If you have to use IP
address, you can setup client side alias on the distributor that maps the IP
address to the @.@.servername of the subscriber
“This posting is provided "AS IS" with no warranties, and confers no rights.”
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
|||hi ,
be sure that your sql agent services is
running on a domain account
that has permission to your snapshot folder
and the database
thanks,
joey
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
networks. both machines can ping eachother using there machine names. I setup
the publication and the distributor on the push server and generate a
snapshot. I then run the distributor but it fails to connect to the
subscriber.
thanks for the help.
Mark
You need to ensure the @.@.servername of the subscriber is how the
publisher/distributor refer to it and vice versa. If you have to use IP
address, you can setup client side alias on the distributor that maps the IP
address to the @.@.servername of the subscriber
“This posting is provided "AS IS" with no warranties, and confers no rights.”
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
|||hi ,
be sure that your sql agent services is
running on a domain account
that has permission to your snapshot folder
and the database
thanks,
joey
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
Push Merge snapshot fails with Data is Null message.
Hi,
I'm converting a replication script from SQL 2000 to SQL 2005.
I am getting an error with push merge with no way to figure out what is wrong.
I've configured replication on a single XP server in SQL 2005 RTM version.
I have a push merge set up between A and B and between B and C. All 3 databases are 9.0 compatibility.
The snapshot and merge jobs for the A to B run fine with no errors, and merge replicates ok.
The snapshot for B to C fails with this message:
Message
2006-03-09 17:30:35.94
2006-03-09 17:30:35.94 -BcpBatchSize 100000
2006-03-09 17:30:35.94 -HistoryVerboseLevel 2
2006-03-09 17:30:35.94 -LoginTimeout 15
2006-03-09 17:30:35.94 -QueryTimeout 1800
2006-03-09 17:30:35.94
2006-03-09 17:30:35.95 Connecting to Publisher 'MyInstance'
2006-03-09 17:30:35.97 Publisher database compatibility level is set to 90.
2006-03-09 17:30:35.97 Retrieving publication and article information from the publisher database 'MyInstance.MyDB'
2006-03-09 17:30:36.22 [0%] The replication agent had encountered an exception.
2006-03-09 17:30:36.22 Source: Replication
2006-03-09 17:30:36.22 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-09 17:30:36.22 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-09 17:30:36.22 Message Code: 52006
2006-03-09 17:30:36.22
Love that exception message: "Data is Null" - very helpful to someone who is clairvoyant perhaps.
I checked the snapshot bcp files. The tables being merged all have data.
A sample add article command is:
exec sp_addmergearticle @.publication = N'MyMerge', @.article = N'Phone', @.processing_order = 4, @.source_owner = N'dbo', @.source_object = N'Phone', @.type = N'table', @.description = null, @.column_tracking = N'true', @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option = 0x000000004C42CDDF, @.article_resolver = null, @.subset_filterclause = null, @.vertical_partition = N'false', @.destination_owner = N'dbo', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.identityrangemanagementoption = N'none' ,@.force_invalidate_snapshot = 1,@.force_reinit_subscription = 1
If you have any ideas on how to fix this, I'd be most grateful. As it is after 6pm I probably won't read this again until morning. Thanks for any suggestions.
Can you get the full stack trace from MSrepl_errors in the distribution database and post it here please? Thanks.
-Raymond
|||Here you go:
Message: Data is Null. This method or property cannot be called on Null values.
Command Text: sp_MSestimatemergesnapshotworkload
Parameters: @.publication = StandardMerge Stack:
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InitializeProgressTracker()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run()
Source: System.Data Target Site: Int32 get_Int32()
Message: Data is Null. This method or property cannot be called on Null values. Stack:
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at Microsoft.SqlServer.Replication.Snapshot.ProgressTracker..ctor(SqlDataReader dataReader, SnapshotGenerationAgent snapshotGenerationAgent)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.CreateProgressTracker(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
This looks like a bug, can you execute sp_MSestimatemergesnapshotworkload @.publication = 'StandardMerge' at the publisher database and post any rows with a null 'taskid' or 'taskload' value here. Thanks.
-Raymond
|||Here they are. I've renamed the tables from their real names.
By the way, for the same task id (4), for the A to B merge, these are the values (in case this is useful for comparison).
Can you try the following query at the publisher database and see if it returns any rows?
select rowcnt from sysindexes where id = object_id(N'Table1') and indid in (0,1)
If the above query doesn't return any rows then this is basically the cause of the problem and it would be great if you can retry the query without the "and indid in (0,1)" part and report back whether there are any rows returned in that case. And if the publisher connection of the snapshot agent is not db_owner\sysadmin, you may want to temporarily change that and see if the problem goes away.
-Raymond
|||The result from the original query is rowcnt = 195.
|||Also, this query and the prior queries were run using the login of the original script.|||
More info - in case it is useful
The parms passed for the merge publications:
For the AtoB merge (which works)
exec sp_addmergepublication @.publication = N'AtoB', @.description = N'Corporate Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'AtoB',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.MyName
exec sp_addmergesubscription @.publication = N'AtoB', @.subscriber = @.MyInstance, @.subscriber_db = N'SiteMaster', @.subscription_type = N'push', @.subscriber_type = N'global', @.subscription_priority = 75.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.Myname
For the BtoC merge (which doesn't)
exec sp_addmergepublication @.publication = N'BtoC', @.description = N'Facility Master Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false',@.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'BtoC',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.Myname
exec sp_addmergesubscription @.publication = N'BtoC', @.subscriber = @.MyInstance, @.subscriber_db = @.MyFacility, @.subscription_type = N'push', @.subscriber_type = N'local', @.subscription_priority = 0.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.MyName
|||This looks really weird, can you run sp_helpmergearticle @.publication =N'BtoC' at B and report back whether the source_object and source_owner columns are NULLs? Thanks much.
-Raymond
|||Raymond,
I get nothing back from that at all. When I run it on the AtoB pub,run at A, the result looks normal. But nothing at all comes back for BtoC publication, run at B.
|||This is interesting:
When I run EXEC dbo.sp_helpmergepublication at A, I get priority = 100.
When I run the same thing at B, I also get priority = 100.
You can see in my sp_addmergesubscription for A, I had set priority to 75.0 and that for B I'd set it to 0.0 so SQL is ignoring those parms I guess.
|||Ok, looks like your publication\article metadata is very messed up at B. To confirm my suspicion, can you try the following query at B:
select name, pubid, objid, object_name(objid) from dbo.sysmergeextendedarticlesview
I am interested to know if any values in the last column are null or not. If there are any null values, then your article meta-data is seriously corrupted. You can, of course, recover from this particular problem by recreating the publication at B but given the kind of problems you have experienced in the past, I strongly suspect some other background operations at B is what is causing the problems in the first place. Here are a couple of possibilities that I can think of:
1) Someone tried to renamed the published table at B
2) Someone tried to drop and recreate the published table at B and use sp_MSunmarkreplino to allow the operation to occur.
3) The merge agent has a problem updating the object ids of published tables at B when a snapshot is delivered from A to B.
4) You have included sysmergearticles and\or sysmergepublications as part of the publication from A to B.
HTH
-Raymond
|||I ran it (ordered by 2,1) and all the articles for B publication have NULL in the last column.
As for the possibilities, nobody but me has done anything to this data.
1) haven't done
2) I ran that once a week ago when I was first putting this stuff together and trying to clean stuff up with another problem I have with transactional replication getting a 3724 error ("Cannot drop the table 'dbo.Mytablename' because it is being used for replication.) Would that still be messing me up after many builds since then?
3) At the moment, I believe everything is running under the SQL Agent Svc login.
4) Those are not in my 13 explicitly named sp_addmergearticle articles.
Also, per priority difference mentioned earlier. I ran sp_helpmergesubscription and the priorities were as I expected. So why are the sp_helpmergepublication priorities 100?
Friday, March 9, 2012
Publishing problem ...
Hello,
I have a problem when trying to publish articles for merge
replication. During execution of the snapshot agent I
receive the following error "The process could not bulk
copy out of table '[dbo].[table_name]'. Can anyone help
me with this problem?
Any help at all would be greatly appreciated!
Thanks in advance
Jim S.
Basically it means the snapshot agent locked up, timed out, was blocked, or
somehow deadlocked. What you do is stop and restart the snapshot agent. If
you are dealing with a large table, you may need to manually transfer it to
the subscriber. There are cases where the size of the table becomes nearly
impossible to get a snapshot to complete.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Do I:
1. Just let the snapshot agent run until it fails
during the bcp process
2. Transfer all tables to the subscriber via dts
3. Create subscription using the 'do not create schema
and data option'
>--Original Message--
>Basically it means the snapshot agent locked up, timed
out, was blocked, or
>somehow deadlocked. What you do is stop and restart the
snapshot agent. If
>you are dealing with a large table, you may need to
manually transfer it to
>the subscriber. There are cases where the size of the
table becomes nearly
>impossible to get a snapshot to complete.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||If it continues to fail, what I normally do is to separate out the table
that are failing into a separate publication. The ones separated out I use
the "do not create..." option and use DTS to manually transfer everything
over. It's ugly and full of trial and error, but it works. There aren't
any other good options at this point, because a snapshot has to complete in
order for transactions to start flowing. The interesting thing you will
find is that DTS will generally transfer the data in about 1/10 the amount
of time that the snapshot takes.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
I have a problem when trying to publish articles for merge
replication. During execution of the snapshot agent I
receive the following error "The process could not bulk
copy out of table '[dbo].[table_name]'. Can anyone help
me with this problem?
Any help at all would be greatly appreciated!
Thanks in advance
Jim S.
Basically it means the snapshot agent locked up, timed out, was blocked, or
somehow deadlocked. What you do is stop and restart the snapshot agent. If
you are dealing with a large table, you may need to manually transfer it to
the subscriber. There are cases where the size of the table becomes nearly
impossible to get a snapshot to complete.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Do I:
1. Just let the snapshot agent run until it fails
during the bcp process
2. Transfer all tables to the subscriber via dts
3. Create subscription using the 'do not create schema
and data option'
>--Original Message--
>Basically it means the snapshot agent locked up, timed
out, was blocked, or
>somehow deadlocked. What you do is stop and restart the
snapshot agent. If
>you are dealing with a large table, you may need to
manually transfer it to
>the subscriber. There are cases where the size of the
table becomes nearly
>impossible to get a snapshot to complete.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||If it continues to fail, what I normally do is to separate out the table
that are failing into a separate publication. The ones separated out I use
the "do not create..." option and use DTS to manually transfer everything
over. It's ugly and full of trial and error, but it works. There aren't
any other good options at this point, because a snapshot has to complete in
order for transactions to start flowing. The interesting thing you will
find is that DTS will generally transfer the data in about 1/10 the amount
of time that the snapshot takes.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
Publishing initial snapshot through IIS like SSCE...
Hi,
Just for curiosity, is it possible in SQL Server 2000 to publish the initial
snapshot through IIS the same way that uses SQL Server CE for accessing it?
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
yes, if you configure ftp to listen on port 80. No, if you insist on using
the http protocol.
"faustino Dina" <ffdina@.matusa.com.mx> wrote in message
news:u8C3oNckEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Just for curiosity, is it possible in SQL Server 2000 to publish the
initial
> snapshot through IIS the same way that uses SQL Server CE for accessing
it?
> Thanks in advance
> --
> Faustino Dina
> If my email address starts with two 'f'
> drop the first 'f' when mailing me.
>
Just for curiosity, is it possible in SQL Server 2000 to publish the initial
snapshot through IIS the same way that uses SQL Server CE for accessing it?
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
yes, if you configure ftp to listen on port 80. No, if you insist on using
the http protocol.
"faustino Dina" <ffdina@.matusa.com.mx> wrote in message
news:u8C3oNckEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Just for curiosity, is it possible in SQL Server 2000 to publish the
initial
> snapshot through IIS the same way that uses SQL Server CE for accessing
it?
> Thanks in advance
> --
> Faustino Dina
> If my email address starts with two 'f'
> drop the first 'f' when mailing me.
>
Wednesday, March 7, 2012
Published tables locked
Hi,
I am using SQL 2000 standard edition. When i do a
snapshot replication, all the published tables are locked
while creating the snapshot. Result is i cannot do a
snapshot during work hours.
My understanding was that there is an improvement in SQL
Server 2000 where by tables do not get locked while
creating the snapshot.
Can someone throw some light on this.
thanks
venk
Nonblocking snapshots are only available when you use transactional. Why?
I really don't know, but that is the restriction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
I am using SQL 2000 standard edition. When i do a
snapshot replication, all the published tables are locked
while creating the snapshot. Result is i cannot do a
snapshot during work hours.
My understanding was that there is an improvement in SQL
Server 2000 where by tables do not get locked while
creating the snapshot.
Can someone throw some light on this.
thanks
venk
Nonblocking snapshots are only available when you use transactional. Why?
I really don't know, but that is the restriction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
Saturday, February 25, 2012
Publication Problem
Hello,
I want to Create Publication with the Wizard but i always have a great
problem: After i click on okay on the Specify Snapshot Folder i always get
the following error:
"SQL Server Enterprise Manager could not retrieve information about the
Distributor of the database. Error 1038: Cannot use empty object or column
names. Use a single space if necessary."
And when i click on okay and the "Choose Publication Database" screen
appears i cannnot choose any database because in there do not appear any
databases.
What went wrong here?
Can anybody please help me here?
regards
patrick
PS: When i want to create a new Table for a Database a similar error occurs:
An unexpected error happened during this operation.
[Microsoft SQL-DMO (ODBC SQLState: 42000) - Cannot use empty object or
column names. Use a single space if necessary.
Maybe this could help to explain me what went wrong in my Enterprise Manager
that the Publication does not work correctly?
Hello,
I installed the Microsoft SQL Server 2000 on my machine once more and
fortunately the Publicaten worked. I did it like it is quoted in your posted
doc-file and on this
<a
href=http://msdn.microsoft.com/SQL/SQLCE/default.aspx?pull=/msdnmag/issues/03/09/datapoints/default.aspx> ReplicationExample
site, because i want to go through the sample of this site.
But it were to good to be true if all would be work, when i deploy the
sample application from this site on the Emulator and presses the
Synch-Button of the application, the following error occurs in a message box
on the display:
Error #1 of 1
Error Code: -2147012867
Message: A request to send data to the computer running IIS has failed. For
more information, see HRESULT.[,,,,]
Minor Err.: 28037
Source: Microsoft SQL Server 2000 Windows CE Edition.
Do know what went wrong here or do i have forgotten anything during the
Create Virtual Directory Wizard or the Publication Wizard or anything else?
Hopefully you are willing to help me again, because i do not simply look
through it.
regards
patrick
PS: I did all as i quoted it in the previous posting and once more my IIS,
SQL Server 2000 and VS.NET are all on the same machine.
I want to Create Publication with the Wizard but i always have a great
problem: After i click on okay on the Specify Snapshot Folder i always get
the following error:
"SQL Server Enterprise Manager could not retrieve information about the
Distributor of the database. Error 1038: Cannot use empty object or column
names. Use a single space if necessary."
And when i click on okay and the "Choose Publication Database" screen
appears i cannnot choose any database because in there do not appear any
databases.
What went wrong here?
Can anybody please help me here?
regards
patrick
PS: When i want to create a new Table for a Database a similar error occurs:
An unexpected error happened during this operation.
[Microsoft SQL-DMO (ODBC SQLState: 42000) - Cannot use empty object or
column names. Use a single space if necessary.
Maybe this could help to explain me what went wrong in my Enterprise Manager
that the Publication does not work correctly?
Hello,
I installed the Microsoft SQL Server 2000 on my machine once more and
fortunately the Publicaten worked. I did it like it is quoted in your posted
doc-file and on this
<a
href=http://msdn.microsoft.com/SQL/SQLCE/default.aspx?pull=/msdnmag/issues/03/09/datapoints/default.aspx> ReplicationExample
site, because i want to go through the sample of this site.
But it were to good to be true if all would be work, when i deploy the
sample application from this site on the Emulator and presses the
Synch-Button of the application, the following error occurs in a message box
on the display:
Error #1 of 1
Error Code: -2147012867
Message: A request to send data to the computer running IIS has failed. For
more information, see HRESULT.[,,,,]
Minor Err.: 28037
Source: Microsoft SQL Server 2000 Windows CE Edition.
Do know what went wrong here or do i have forgotten anything during the
Create Virtual Directory Wizard or the Publication Wizard or anything else?
Hopefully you are willing to help me again, because i do not simply look
through it.
regards
patrick
PS: I did all as i quoted it in the previous posting and once more my IIS,
SQL Server 2000 and VS.NET are all on the same machine.
Publication not enabled to use an independent agent
I've created a snapshot replication between 2 databases on 2 different
servers to run once a day. The snapshot has worked but when the
distribution job runs it fails within a few seconds. I've looked at the
job history and the following error message is recorded:
Publication 'EmailDB' is not enabled to use an independent agent. The
step failed.
I think this may be because I've defined the publication so that
anonymous pull subscriptions are not allowed. If not does anyone know
what the problem could be. If it is how do I alter the publication so
that they are allowed - the option doesn't appear to be changeable with
the publication properties window in EM.
TIA
Laurence Breeze
drop your subscriptions and try this
sp_changepublication 'pubs4','independent_agent','true'
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
"Laurence Breeze" <laurence_breeze@.yahoo.co.uk> wrote in message
news:4289AD77.5070305@.yahoo.co.uk...
> I've created a snapshot replication between 2 databases on 2 different
> servers to run once a day. The snapshot has worked but when the
> distribution job runs it fails within a few seconds. I've looked at the
> job history and the following error message is recorded:
> Publication 'EmailDB' is not enabled to use an independent agent. The
> step failed.
> I think this may be because I've defined the publication so that
> anonymous pull subscriptions are not allowed. If not does anyone know
> what the problem could be. If it is how do I alter the publication so
> that they are allowed - the option doesn't appear to be changeable with
> the publication properties window in EM.
> TIA
> Laurence Breeze
>
servers to run once a day. The snapshot has worked but when the
distribution job runs it fails within a few seconds. I've looked at the
job history and the following error message is recorded:
Publication 'EmailDB' is not enabled to use an independent agent. The
step failed.
I think this may be because I've defined the publication so that
anonymous pull subscriptions are not allowed. If not does anyone know
what the problem could be. If it is how do I alter the publication so
that they are allowed - the option doesn't appear to be changeable with
the publication properties window in EM.
TIA
Laurence Breeze
drop your subscriptions and try this
sp_changepublication 'pubs4','independent_agent','true'
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
"Laurence Breeze" <laurence_breeze@.yahoo.co.uk> wrote in message
news:4289AD77.5070305@.yahoo.co.uk...
> I've created a snapshot replication between 2 databases on 2 different
> servers to run once a day. The snapshot has worked but when the
> distribution job runs it fails within a few seconds. I've looked at the
> job history and the following error message is recorded:
> Publication 'EmailDB' is not enabled to use an independent agent. The
> step failed.
> I think this may be because I've defined the publication so that
> anonymous pull subscriptions are not allowed. If not does anyone know
> what the problem could be. If it is how do I alter the publication so
> that they are allowed - the option doesn't appear to be changeable with
> the publication properties window in EM.
> TIA
> Laurence Breeze
>
Subscribe to:
Posts (Atom)