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...

Replication

I'm trying to update a row in a subscriber, and I get an error like this:
"Another user has modified this table or view content; the row you are trying to modifiy does not longer exists in the database".
Can anybody help me to solve this problem? Thanks...Originally posted by berto00
I'm trying to update a row in a subscriber, and I get an error like this:
"Another user has modified this table or view content; the row you are trying to modifiy does not longer exists in the database".
Can anybody help me to solve this problem? Thanks...

try restarting the snapshot agent again, you have a row that perhaps exists in the subscriber and not in the publisher

regards,
-blackpearl|||Originally posted by blackpearl
try restarting the snapshot agent again, you have a row that perhaps exists in the subscriber and not in the publisher

regards,
-blackpearl

I have checked all rows (they're only 10 rows...) and they're all the same. I try to update the value of a column of this row, but then that error occurs...|||Originally posted by berto00
I have checked all rows (they're only 10 rows...) and they're all the same. I try to update the value of a column of this row, but then that error occurs...

ok

Go to the snapshot agents folder and restart your respective snapshot agent (wait till it ends creating the snapshot), then select the the subscriber in your publications folder and click on "Reinitialize".

regards,
-blackpearl|||Originally posted by blackpearl
ok

Go to the snapshot agents folder and restart your respective snapshot agent (wait till it ends creating the snapshot), then select the the subscriber in your publications folder and click on "Reinitialize".

regards,
-blackpearl

ok, I'll try it out... thanks|||Originally posted by berto00
ok, I'll try it out... thanks

I've tried what you told me, but still the same... I don't know if it's important, but the error message also includes this:
"[Microsoft][ODBC Sql Server driver][Sql Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

And the two servers (publisher and subscriber) are connected through adsl...

replicating the resultset of a view

hello all,

I created a view and I want the result set(records) of the view to be replicated to another server. i've tried replicating the view by checking the view in the replication wizard but it seems that only the definition of the view is being replicated. Is there a way that i can fullfill the desired result through replication. That to store the result of the view to a new table in the replication destination. I'm using snapshot replication

thanks in advance

joey

Hi Joey,

I saw that Paul Ibison has already suggested that you publish your view as an indexed view to table article (or the slightly misnamed 'indexed view logbased' type article) on the microsoft.public.sqlserver.replication newsgroup. This is probably the easiest solution to your problem although creating a unique index on your view may impair update performance at your publisher. If your view is defined to return a subset of columns for one of your tables, you may want to publish the underlying table with horizontal\vertical filters that match your view definition. We will also consider to simply allow the scenario that you described in a future release.

Thanks for your feedback.

-Raymond