Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.
In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Showing posts with label reports. Show all posts
Showing posts with label reports. Show all posts
Tuesday, March 20, 2012
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Friday, March 9, 2012
Replication & Report execution
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
8.4 s to be completely accurate
To get a consistent database state you should schedule your distribution agent. This way it will not run continuously but rather in a batch.
You might also want to split the tables into their own publication and then use the independent agent option. This should solve the synchronization problems.
The most significant thing you can do to improve your transactional performance is to try to minimize the indexes, triggers and indexed views hanging off the subscriber tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:e7ivuSSlEHA.3988@.TK2MSFTNGP14.phx.gbl...
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
8.4 s to be completely accurate
To get a consistent database state you should schedule your distribution agent. This way it will not run continuously but rather in a batch.
You might also want to split the tables into their own publication and then use the independent agent option. This should solve the synchronization problems.
The most significant thing you can do to improve your transactional performance is to try to minimize the indexes, triggers and indexed views hanging off the subscriber tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:e7ivuSSlEHA.3988@.TK2MSFTNGP14.phx.gbl...
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
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...
>
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...
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...
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...
Monday, February 20, 2012
Replicating Permissions
My reports are currently configured so that only certain users on the
domain can access certain reports. I have installed another server at
a remote location that will need to have the same permissions as the
first one. Is there a way to replicate the security settings without
having to set the permissions manually each time?
Is there a security configuration file somewhere on the first server
that i could move over to the second, since they are pretty much the
same?
Thank you!You may want to check the following tool courtesy of Jasper Smith:
http://www.sqldbatips.com/showarticle.asp?ID=62
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"muris" <rmuris@.hotmail.com> wrote in message
news:1113533808.430998.255460@.f14g2000cwb.googlegroups.com...
> My reports are currently configured so that only certain users on the
> domain can access certain reports. I have installed another server at
> a remote location that will need to have the same permissions as the
> first one. Is there a way to replicate the security settings without
> having to set the permissions manually each time?
> Is there a security configuration file somewhere on the first server
> that i could move over to the second, since they are pretty much the
> same?
> Thank you!
>|||Thanks for your Reply. The app you refered me to helps me replicate
the reports and user roles to multiple servers, but it does not
replicate the security permissions for each folder nor the report. Is
there a script that can accomplish this task?
Thanks!|||Jasper Smith may add this kind of functionality to the tool in a future
release.
On RS 2000 you would have to do this through the SOAP API (ListChildren,
GetProperties, GetPermissions, etc.)
The Management Studio for the upcoming SQL Server 2005 release of Reporting
Services will allow you to generate a script to capture that state and then
apply it to another server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"muris" <rmuris@.hotmail.com> wrote in message
news:1113572435.510884.55430@.z14g2000cwz.googlegroups.com...
> Thanks for your Reply. The app you refered me to helps me replicate
> the reports and user roles to multiple servers, but it does not
> replicate the security permissions for each folder nor the report. Is
> there a script that can accomplish this task?
> Thanks!
>
domain can access certain reports. I have installed another server at
a remote location that will need to have the same permissions as the
first one. Is there a way to replicate the security settings without
having to set the permissions manually each time?
Is there a security configuration file somewhere on the first server
that i could move over to the second, since they are pretty much the
same?
Thank you!You may want to check the following tool courtesy of Jasper Smith:
http://www.sqldbatips.com/showarticle.asp?ID=62
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"muris" <rmuris@.hotmail.com> wrote in message
news:1113533808.430998.255460@.f14g2000cwb.googlegroups.com...
> My reports are currently configured so that only certain users on the
> domain can access certain reports. I have installed another server at
> a remote location that will need to have the same permissions as the
> first one. Is there a way to replicate the security settings without
> having to set the permissions manually each time?
> Is there a security configuration file somewhere on the first server
> that i could move over to the second, since they are pretty much the
> same?
> Thank you!
>|||Thanks for your Reply. The app you refered me to helps me replicate
the reports and user roles to multiple servers, but it does not
replicate the security permissions for each folder nor the report. Is
there a script that can accomplish this task?
Thanks!|||Jasper Smith may add this kind of functionality to the tool in a future
release.
On RS 2000 you would have to do this through the SOAP API (ListChildren,
GetProperties, GetPermissions, etc.)
The Management Studio for the upcoming SQL Server 2005 release of Reporting
Services will allow you to generate a script to capture that state and then
apply it to another server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"muris" <rmuris@.hotmail.com> wrote in message
news:1113572435.510884.55430@.z14g2000cwz.googlegroups.com...
> Thanks for your Reply. The app you refered me to helps me replicate
> the reports and user roles to multiple servers, but it does not
> replicate the security permissions for each folder nor the report. Is
> there a script that can accomplish this task?
> Thanks!
>
Subscribe to:
Posts (Atom)