another simple snapshot replication question

i have a publisher agent that generates 61 articles for publication
without a problem.
however i'm having trouble understanding exactly how the distribution
agent executes. when executed manually once, it ran successfully, and
the subscriber got the data.
is it possible to run the distribution agent again to re-push/pull data
for the subscribers without running the publisher agent? when i try to
re-run the distributor agent, i get the success message "No replicated
transactions are available." have the snapshot files become
unavailable, or is it just something where the distribution agent knows
that they are older than the subscriber last obtained?
if it's possible to have a distribution agent push/pull data from a
single collection of publisher snapshot articles multiple times, i
would greatly appreciate knowing how that's done.
thanks in advance for any help,
jason
Jason,
to have the distribution agent pick up any new snapshot files, you need to
reinitialize this particular subscriber (sp_reinitsubscription). However if
this is a regular process, perhaps you should just use snapshot replication
instead? Or are you trying to avoid table locking?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||paul,
sorry, i should have specified that this is a snapshot replication. but
because of the very reason you mentioned, the table locking, i was
hoping that i could tweak the distribution agent steps without having
to republish the data (avoiding further table locks).
does this information suggest any particular solutions as possible /
superior?
thanks for the response,
jason
|||Sounds like transactional would be more beneficial. Unless there are loads
of updates to the same rows, you'll notice an improvement in performance
versus regualr snapshots. Also you'll avoid locking issues by using the
concurrent snapshot generation option when initializing/reinitializing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i thought that might be the case. but doesn't transactional replication
come with a performance hit to the updates in the publisher database?
this replication is just to get "production-like" data from the
production database into the development and staging databases. we
don't want to perpetually diminish the performance of the production
database for a process we probably only need to run every couple of
weeks/months. but at the same time, the database is accessed 24-7, so
if we can do the replication without record locking, that's highly
preferable.
another option i'm looking into is a DTS task, which also seems like it
could be done without locking the tables. but i'm still sorting out the
details on DTS with regard to object copies vs. data transformations,
and how to handle identity columns, etc.
sorry for that long contextual tangent. so would you still recommend
transactional replication for a task that really only needs to
replicate data once or twice a month?
thanks,
jason
|||With no downtime of the production site allowed and representative data
required at fortnightly intervals, I'd restore full database backups - much
easier solution with no hit on the production database.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||that's an awesome idea. we use some sql-aware third party backup
software, so if i can figure out how to restore a single database file
from there in a way that sql can use, that's definitely the solution
i'll choose.
thanks for the suggestion!

Another " Error: 4813 Expected the text length in data stream - "

After working for over a year, one of our snapshot replication
distributions now fails with the following error:
The process could not bulk copy into table '"texttab"'.
Expected the text length in data stream for bulk copy of text, ntext,
or image data.
(Source: DESTINATION_SERVER (Data source); Error number: 4813)
Function sequence error
(Source: ODBC Driver Manager (ODBC); Error number: S1010)
The last column of the table in error is type "text". If we lower the
number of the "BCPBatchSize" in the distribution agent profile, more
rows get copied before the error occurs - but there are too many other
very large tables in the snapshot to use that as a go-around.
As suggested in a previous post, we cannot change the table structure
because it is a third-party developed product.
When we use the -UseInProcLoader option in the dist agent, it creates
another error.
We're running SQL Server 2000 8.00.760
Thanks for any ideas.
remove the text column and replicate the remaining table. Use another method
to synchronize the text column, such as using a trigger to write to an audit
table. Have DTS read this audit table at regular intervals and then update
the text column between the publisher and subscriber. In many topologies you
will find that the text/image column is highly static.
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
"Dean" <dpichotto@.cse-insurance.com> wrote in message
news:1120071435.407646.302670@.g49g2000cwa.googlegr oups.com...
> After working for over a year, one of our snapshot replication
> distributions now fails with the following error:
> The process could not bulk copy into table '"texttab"'.
> Expected the text length in data stream for bulk copy of text, ntext,
> or image data.
> (Source: DESTINATION_SERVER (Data source); Error number: 4813)
> Function sequence error
> (Source: ODBC Driver Manager (ODBC); Error number: S1010)
> The last column of the table in error is type "text". If we lower the
> number of the "BCPBatchSize" in the distribution agent profile, more
> rows get copied before the error occurs - but there are too many other
> very large tables in the snapshot to use that as a go-around.
> As suggested in a previous post, we cannot change the table structure
> because it is a third-party developed product.
> When we use the -UseInProcLoader option in the dist agent, it creates
> another error.
> We're running SQL Server 2000 8.00.760
> Thanks for any ideas.
>
|||Thanks very much for your reply.
Wow - how twisted! I wonder if using ftp for the distribution would get
around this...