MS SQL Server - Need Help (replication)

Author

Need Help (replication)

Robert Lie

2005-09-29, 3:23 am

Dear All,

I have 2 DB Server, OLTP Server and Report Server.
I want to replicate some tables from OLTP Server to Report Server.
The distributor Database is on Report Server bcos I don't to slow down
the performance of OLTP Server.

But every time the replication is running, the error (see attachement)
always occur on OLTP Server.

Pls somebody give my some suggestion.

Thanks

Robert Lie

Jerry Spivey

2005-09-29, 9:23 am

Robert,

Are there triggers on the tables being replicated? If so, do the triggers
need to be executed again on the subscriber? Not sure if this is the cause
of your error but it might be related.

HTH

Jerry
"Robert Lie" <robert.lie24@gmail.com> wrote in message
news:u2VvQxMxFHA.736@tk2msftngp13.phx.gbl...
> Dear All,
>
> I have 2 DB Server, OLTP Server and Report Server.
> I want to replicate some tables from OLTP Server to Report Server.
> The distributor Database is on Report Server bcos I don't to slow down
> the performance of OLTP Server.
>
> But every time the replication is running, the error (see attachement)
> always occur on OLTP Server.
>
> Pls somebody give my some suggestion.
>
> Thanks
>
> Robert Lie
>

--------------------------------------------------------------------------------

ChrisR

2005-09-29, 1:23 pm

Since I need to use IE to use these forums, I cant see the attached error
file. Please post it.
--
TIA,
ChrisR

"Robert Lie" wrote:

> Dear All,
>
> I have 2 DB Server, OLTP Server and Report Server.
> I want to replicate some tables from OLTP Server to Report Server.
> The distributor Database is on Report Server bcos I don't to slow down
> the performance of OLTP Server.
>
> But every time the replication is running, the error (see attachement)
> always occur on OLTP Server.
>
> Pls somebody give my some suggestion.
>
> Thanks
>
> Robert Lie
>

Robert Lie

2005-09-30, 3:23 am

Here's the error:

Run-time error '-2147217900 (80040e14)'
Maximum soted procedure, function, trigger, or view nesting level
exceeded (limit32)

ChrisR wrote:
> Since I need to use IE to use these forums, I cant see the attached error
> file. Please post it.

Robert Lie

2005-09-30, 3:23 am

Hai Jerry,

You're right, some tables being replicated have triggers on them.
The problem is I need the triggers to be executed on OTLP server since
the triggers are needed to maintain data integrity of applications that
run on OLTP server.

Do you have alternatives solution on top of that?

Thanks

Robert Lie

Jerry Spivey wrote:
> Robert,
>
> Are there triggers on the tables being replicated? If so, do the triggers
> need to be executed again on the subscriber? Not sure if this is the cause
> of your error but it might be related.
>
> HTH
>
> Jerry
> "Robert Lie" <robert.lie24@gmail.com> wrote in message
> news:u2VvQxMxFHA.736@tk2msftngp13.phx.gbl...
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>

Jerry Spivey

2005-09-30, 3:23 am

True but are replicating the triggers as well or creating them on the
subscriber(s)? Something to look into.

HTH

Jerry
"Robert Lie" <robert.lie24@gmail.com> wrote in message
news:eWomXVXxFHA.2620@TK2MSFTNGP09.phx.gbl...[color=darkred]
> Hai Jerry,
>
> You're right, some tables being replicated have triggers on them.
> The problem is I need the triggers to be executed on OTLP server since the
> triggers are needed to maintain data integrity of applications that run on
> OLTP server.
>
> Do you have alternatives solution on top of that?
>
> Thanks
>
> Robert Lie
>
> Jerry Spivey wrote:

Robert Lie

2005-09-30, 3:23 am

Do you mean I'm replicating the triggers to subcriber server (Report
Server) as well or not?
No, I'm not replicating the triggers to subcriber.
Which part do you worry about?

Thanks

Robert Lie

Jerry Spivey wrote:
> True but are replicating the triggers as well or creating them on the
> subscriber(s)? Something to look into.
>
> HTH
>
> Jerry
> "Robert Lie" <robert.lie24@gmail.com> wrote in message
> news:eWomXVXxFHA.2620@TK2MSFTNGP09.phx.gbl...
>
>
>

Jerry Spivey

2005-09-30, 9:23 am

Robert,

I was looking at the possible cause of your issue being related to the fact
that there might be triggers on the subscriber tables that could possibly be
causing this issue. Are there triggers on the subscribing tables
(sp_helptrigger tablename at subscriber)? Are they required? As a test
what if you removed them (one at a time with a retest)? Agan..I am not
saying this is definitely the cause but it might be.

HTH

Jerry
"Robert Lie" <robert.lie24@gmail.com> wrote in message
news:OZjVHYYxFHA.2620@TK2MSFTNGP09.phx.gbl...[color=darkred]
> Do you mean I'm replicating the triggers to subcriber server (Report
> Server) as well or not?
> No, I'm not replicating the triggers to subcriber.
> Which part do you worry about?
>
> Thanks
>
> Robert Lie
>
>
> Jerry Spivey wrote:

Sponsored Links