replication error? (somewhat urgent)

Sorry, this is a long winded post I know. But all help is appreciated as me
and my managers need to explain all this to our CEO on Monday.
sql2k sp3
Transactional, non immediate Updating, continuous, no Transformations
Replication.
This last Tuesday morning a report against was run against the same
Replicated db that it is every morning. It is run automatically at the same
time Monday thru Friday. With the exception of errors on my end, Replication
has been purring along just fine with absolutely no problems for the 3
months its been up and running. However, this last Tuesday the report was
run and the numbers generated didn't match the numbers the end user was
expecting to see. They ran it again 10 minutes later and the numbers were
more to they're liking. As a group, our IT dept has ruled out pretty much
everything we can think of. So our only theory is that Replication was
having problems. We have narrowed down all possibilities to 1 table. This
table is imported into the Publisher from a file using BCP every morning
before this report is run. This table has a column with a GETDATE() function
on it so we know what time the data made it into the Publisher for sure. The
time it says is 45 minutes before the report was run. So the theory is that:
(finally)
There was about a 50 minute lag in Replication.
That the first time the report was run, not all of the data was in the
Subscriber.
That the second time it was run (10 minutes later) all of the data made it
in and life was good.
There is nothing supporting anything like this in the error log. Nor is
there anything supporting this theory in either the Log/ Distribution Agents
history. (although on Thursday I did stop/ restart both Agents for unrelated
reasons and I dont know if that clears them.)
So, does anyone have any way I can prove/ disprove these theories? It seems
a bit far stretched to me since Ive got a fair amount of Replication
experience under my belt and the only time I got anything like a "lag" was
along the lines of "cant exec sp_repldone" and then it blew up totally
untill fixed manually.
All ideas are greatly appreciated.
TIA, ChrisR
You might want to query msrepl_errors to find out if there was any errors in
this time period, like a general network error, or query the
msdistribution_history table to see how many commands were replicated in
this time period. Unfortunately these tables are pruned by default every 2
days so you might not see anything there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:O6qegci2EHA.3244@.TK2MSFTNGP11.phx.gbl...
> Sorry, this is a long winded post I know. But all help is appreciated as
> me
> and my managers need to explain all this to our CEO on Monday.
> sql2k sp3
> Transactional, non immediate Updating, continuous, no Transformations
> Replication.
> This last Tuesday morning a report against was run against the same
> Replicated db that it is every morning. It is run automatically at the
> same
> time Monday thru Friday. With the exception of errors on my end,
> Replication
> has been purring along just fine with absolutely no problems for the 3
> months its been up and running. However, this last Tuesday the report was
> run and the numbers generated didn't match the numbers the end user was
> expecting to see. They ran it again 10 minutes later and the numbers were
> more to they're liking. As a group, our IT dept has ruled out pretty much
> everything we can think of. So our only theory is that Replication was
> having problems. We have narrowed down all possibilities to 1 table. This
> table is imported into the Publisher from a file using BCP every morning
> before this report is run. This table has a column with a GETDATE()
> function
> on it so we know what time the data made it into the Publisher for sure.
> The
> time it says is 45 minutes before the report was run. So the theory is
> that:
> (finally)
> There was about a 50 minute lag in Replication.
> That the first time the report was run, not all of the data was in the
> Subscriber.
> That the second time it was run (10 minutes later) all of the data made it
> in and life was good.
> There is nothing supporting anything like this in the error log. Nor is
> there anything supporting this theory in either the Log/ Distribution
> Agents
> history. (although on Thursday I did stop/ restart both Agents for
> unrelated
> reasons and I dont know if that clears them.)
> So, does anyone have any way I can prove/ disprove these theories? It
> seems
> a bit far stretched to me since Ive got a fair amount of Replication
> experience under my belt and the only time I got anything like a "lag" was
> along the lines of "cant exec sp_repldone" and then it blew up totally
> untill fixed manually.
> All ideas are greatly appreciated.
> TIA, ChrisR
>
|||Hillary do you know if theres adjust these settings to keep the history for
a longer time period? Im guessing the proc in the "dist cleanup" job?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#Rigw1i2EHA.3468@.TK2MSFTNGP14.phx.gbl...
> You might want to query msrepl_errors to find out if there was any errors
in[vbcol=seagreen]
> this time period, like a general network error, or query the
> msdistribution_history table to see how many commands were replicated in
> this time period. Unfortunately these tables are pruned by default every 2
> days so you might not see anything there.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:O6qegci2EHA.3244@.TK2MSFTNGP11.phx.gbl...
as[vbcol=seagreen]
was[vbcol=seagreen]
were[vbcol=seagreen]
much[vbcol=seagreen]
This[vbcol=seagreen]
it[vbcol=seagreen]
was
>
|||Right click on Replication Monitor, select distributor properties. I believe
you set it there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <bla@.noemail.com> wrote in message
news:e0vQb3j2EHA.304@.TK2MSFTNGP11.phx.gbl...
> Hillary do you know if theres adjust these settings to keep the history
> for
> a longer time period? Im guessing the proc in the "dist cleanup" job?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#Rigw1i2EHA.3468@.TK2MSFTNGP14.phx.gbl...
> in
> as
> was
> were
> much
> This
> it
> was
>

Replication between SQL2k and Oracle

From the looks of other messages, I'm in the right place. I have a question
about the replication services in SQK2k. I work in a Realty Estate
management company where we use a management tool connected to a SQL2k DB.
I'm in the process of planning a move of our data to a third party management
company. We will still need access to the database but they're going to do
the work for us. I know that SQL2k has the ability to replicate a database
to another such as MS SQL2k or Oracle(which is what I'll be replicating too).
I need to know if it's possible to limit the individual tables that are
replicated. When I publish my db, will all the tables replicate to the new
server or just a group that I specify.
TIA,
Chris
You will be able to specify which tables you want to replicate.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:7990EECE-3C1E-45A4-AE77-4FF9E8A59142@.microsoft.com...
From the looks of other messages, I'm in the right place. I have a question
about the replication services in SQK2k. I work in a Realty Estate
management company where we use a management tool connected to a SQL2k DB.
I'm in the process of planning a move of our data to a third party
management
company. We will still need access to the database but they're going to do
the work for us. I know that SQL2k has the ability to replicate a database
to another such as MS SQL2k or Oracle(which is what I'll be replicating
too).
I need to know if it's possible to limit the individual tables that are
replicated. When I publish my db, will all the tables replicate to the new
server or just a group that I specify.
TIA,
Chris

replication between different sites

Can standard SQL2k replicate with enterprise edition within different sites?
*example - Central server(enterprise) in LA want to replicate with
branch(standard) in NY.
Imran,
these editions of sql server are completely compatible. As for different
sites, you'll need to follow the guidelines for replication across the
internet or non-trusted domains: http://support.microsoft.com/?id=321822.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Replicating to MYSQL

I have been trying to replicate from SQL2K to MySQL and I'm getting an
'Invalid Cursor State' error. After looking around for a solution I
found this old message (http://tinyurl.com/2mb5ln) saying it was a bug
in the distribution agent code and it would be fixed in future service
packs.
Was this ever solved?
My understanding is that they did not.
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
"Fawzib Rojas" <f_rojas@.nospam.spectron.msim.com> wrote in message
news:OHG1g0hdHHA.4468@.TK2MSFTNGP03.phx.gbl...
>I have been trying to replicate from SQL2K to MySQL and I'm getting an
>'Invalid Cursor State' error. After looking around for a solution I found
>this old message (http://tinyurl.com/2mb5ln) saying it was a bug in the
>distribution agent code and it would be fixed in future service packs.
> Was this ever solved?

replicating to denormalized db

SQL2K sp3
Howdy all ...its been some time since Ive been around these parts.
I may need to start replicating to a denormalized DB. I tested this a while
back and it was grueling. After spinning my wheels for a couple of weeks my
then emplyer decided he wanted to keep the db as is so I just did normal
replication. Now if memory servers correctly, the only method I found for
successful replication to a denormalized db was to create Clustered Indexed
Views on the Publisher, and then replicate those, not the tables. I recall
trying another method (dont remember what it was), that was totally
unsuccessful. I need to do 1 way, continuous, Transactional replication. So
my questions are:
1; Are there other methods?
2; If so, what are they?
3; Are they in Hillary's book?
4; I may have the luxury of making the Subscriber SQL2K5, will that help
accomplish my goials.
TIA, ChrisR
Chris,
as an alternative to indexed views, you could use transformable
subscriptions, which is a mix of replication and DTS. This applies to SQL
Server 2000 rather than 2005 AFAIR.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)