Replication Error of MS SQL server2k

Distribution Agents come with a error and stop between two SQL2000 server.
The error messages is:

The query processor could not produce a query plan from the optimizer
because a query cannot update a text, ntext, or image column and a
clustering key at the same time. (Source: Some Server (Data source); Error number:
8629)

How can I fix it?Is it transactional replication?
If yes - and you are using Identity fields (with not for replication) then check prsp_MSupd_**** stored procedures on subscriber and remove updating Identity field.

Replication and Log shipping

Sql server 2005 heavily hit database
Plans
1)Create a replicated databases to let users with query only access
2)Log shipping to allow a 4hour lag time with Disaster recovery just in
case
users gaff..
Your opinion
1)Transactional replication
2)Log shipping with a stagger of 4 hours
Not done this and wondering are there things to watch out for?
Are there better ways to go about this?
Is replication and log shipping in 2005 configured as a single setup?
Again your time and ideas are appreciated as alwaysI would create database snapshots on the local server if you have a large
tempdb and lots of memory. Otherwise I would use replication.
With log shipping your exposure to data loss if in excess of 4 hours. When
you apply the log you will have to kick your users off the system as you
apply the log to the database. With replication you can put user indexes on
the replicated subscriber database to offer better querying performance.
With log shipping the database is read only - you can't create indexes
there.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159285642.485892.323850@.b28g2000cwb.googlegroups.com...
> Sql server 2005 heavily hit database
> Plans
> 1)Create a replicated databases to let users with query only access
> 2)Log shipping to allow a 4hour lag time with Disaster recovery just in
> case
> users gaff..
> Your opinion
> 1)Transactional replication
> 2)Log shipping with a stagger of 4 hours
> Not done this and wondering are there things to watch out for?
> Are there better ways to go about this?
> Is replication and log shipping in 2005 configured as a single setup?
> Again your time and ideas are appreciated as always
>|||Massa,
I did an article a while back that will help you decide between the 2
options: http://www.replicationanswers.com/Standby.asp.
As you're using SQL Server 2005, I'd also add a couple more options for you
to consider looking at:
(a) Mirroring and database snapshots
(b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
variant on the standard Transactional Replication
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Folks for your time and great ideas ...............
Paul Ibison wrote:
> Massa,
> I did an article a while back that will help you decide between the 2
> options: http://www.replicationanswers.com/Standby.asp.
> As you're using SQL Server 2005, I'd also add a couple more options for you
> to consider looking at:
> (a) Mirroring and database snapshots
> (b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
> variant on the standard Transactional Replication
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Replication - Replicating the contents from a view to a Table

I have a complex query I use to populate reports. I need to move the
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...You can't use replication in this way. Replication has a very data-centric
view of what it is doing and within a replication set, you have to have all
of the dependencies satisfied. So to replicate your view you would also have
to replicate the tables it depends on and any other tables for which
constraints would need to be satisfied.
By leaving the view on Server A, you also would not be reducing the report
query impact on Server A.
Why not just link the servers and define the query on Server B in terms of
the linked tables to Server A? Then you have your always live data and the
query processing will primarily be done on Server B with out involving a
complicated mechanism like replication?
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668618.880225.309670@.j55g2000cwa.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>

Replication - Replicating the contents from a view to a Table

I have a complex query I use to populate reports. I need to move the
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...

Replication - Replicating the contents from a view to a Table

I have a complex query I use to populate reports. I need to move the
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...

Replicating Stored Proc Execution

Hi Guys:
When we run the proc from Query Analyzer, we can see the replication of
store proc execution on subscriber/distributor. However when we run this
proc from Java application, we don't see the proc being executed on
subscriber even though we see it on distributor. Any reason why execution of
proc is not replicated properly when executing it from Java. Please let me
know.
Thanks
When you say you see it in the distributor do you mean it shows up in the
output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
that it is running there with the correct parameters. Then run it manually
on the subscriber to see if it works there. It is possible that the proc is
referencing non-existent tables or data which is not present on the
subscribers.
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
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
> Hi Guys:
> When we run the proc from Query Analyzer, we can see the replication of
> store proc execution on subscriber/distributor. However when we run this
> proc from Java application, we don't see the proc being executed on
> subscriber even though we see it on distributor. Any reason why execution
> of
> proc is not replicated properly when executing it from Java. Please let
> me
> know.
> Thanks
|||that is what we did we used the profiler on the subscriber but we don't find
that proc when running from Java. You are saying that if the proc is
referencing non-existance table/data, it will not show up in profiler. We
are pretty sure that table is there. Any other thoughts.
Thanks
"Hilary Cotter" wrote:

> When you say you see it in the distributor do you mean it shows up in the
> output of sp_browsereplcmds? If so run profiler on the subscriber to ensure
> that it is running there with the correct parameters. Then run it manually
> on the subscriber to see if it works there. It is possible that the proc is
> referencing non-existent tables or data which is not present on the
> subscribers.
> --
> 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
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:157726CB-E10F-4F1D-991C-C8FCB84A5CE7@.microsoft.com...
>
>
|||This is bewildering. There are some options for stored procedure execution -
like only running within a serializable transaction. Are you using any of
these?
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
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...[vbcol=seagreen]
> that is what we did we used the profiler on the subscriber but we don't
> find
> that proc when running from Java. You are saying that if the proc is
> referencing non-existance table/data, it will not show up in profiler. We
> are pretty sure that table is there. Any other thoughts.
> Thanks
> "Hilary Cotter" wrote:
|||Thanks for your setting. That is what we thought because we were using that
option (serializable). Now we changed it to execute procedure every time but
still having this problem. Not sure whether Java is using some setting
which is causing this.
"Hilary Cotter" wrote:

> This is bewildering. There are some options for stored procedure execution -
> like only running within a serializable transaction. Are you using any of
> these?
> --
> 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
>
> "Sal" <Sal@.discussions.microsoft.com> wrote in message
> news:8DE46216-94FC-4258-9241-083B06C0758B@.microsoft.com...
>
>

replicating MS Great Plains database

Hi, I hope comeone could help with an additional query i have.
I submitted a post just previously asking how I can configure replication in
the situation where I have a database (call it db1) and a copy of db1 (call
it db2). db2 was created via:
1. detach db1
2. make a copy of db1 (call it db2)
3. attach db2 to new server
Replication is set up in such a way so that db1 replicated to db2 (via
transactional - or whatever is best) and after a few days db2 becomes the
new replacement for db1.
My query is, will this work if db1 is a great plains database? I know great
plains uses a number of databases for its configuration.
How should i set this up? Any help most appreciated!
Cheers,
Jen
That's a difficult question to answer. Some application will write server
specific information to the database and it will be difficult to have the
application use the replicated database.
I would test to see if the application is sensitive to the copied database
first.
For disaster recovery solutions similar to what you are describing
replication is not the best option as it does not do automatic failover. Log
Shipping is the best solution as every object is copied, even system
objects.
If you must use replication for this you have to
1) make sure every table has a PK
2) make sure all constraints, triggers and identity columns are marked not
for replication
3) make sure you use different identity seeds on each side (odd on one side
even on the other) and use an increment of 2
4) use DBCC checkident(tablename, reseed, #) on each side on the identity
tables to make sure that the tables with odd seed will all have odd values
for their current or next value, and make sure all tables on the other side
with the even seed will all have even values for their current or next
value.
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
"jen" <jc@.nospam> wrote in message
news:OnlBhSw$FHA.3096@.tk2msftngp13.phx.gbl...
> Hi, I hope comeone could help with an additional query i have.
> I submitted a post just previously asking how I can configure replication
> in the situation where I have a database (call it db1) and a copy of db1
> (call it db2). db2 was created via:
> 1. detach db1
> 2. make a copy of db1 (call it db2)
> 3. attach db2 to new server
> Replication is set up in such a way so that db1 replicated to db2 (via
> transactional - or whatever is best) and after a few days db2 becomes the
> new replacement for db1.
> My query is, will this work if db1 is a great plains database? I know
> great plains uses a number of databases for its configuration.
> How should i set this up? Any help most appreciated!
> Cheers,
> Jen
>