Showing posts with label views. Show all posts
Showing posts with label views. Show all posts

Wednesday, March 28, 2012

Replication subscriber views, locks and blocks.

Quick question, do views on the subscription server create locking and
blocking issues?
I have 2 servers set-up with transactional replication. We have scheduled
reports running on the subscribing server, most of those reports using views.
The views seem to create performance, locking and blocking issues.
Thank you very much in advance.
Message posted via http://www.droptable.com
Yes they can. You might want to consider using indexed views depending on
your version and edition of SQL Server.
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
"Frank N via droptable.com" <u10790@.uwe> wrote in message
news:5a40019273826@.uwe...
> Quick question, do views on the subscription server create locking and
> blocking issues?
> I have 2 servers set-up with transactional replication. We have scheduled
> reports running on the subscribing server, most of those reports using
> views.
> The views seem to create performance, locking and blocking issues.
> Thank you very much in advance.
> --
> Message posted via http://www.droptable.com
|||A (non-dirty) read of data takes out a shared lock and this is not peculiar
to views. If these views are used for reporting purposes, you might want to
have a replica to be used for reporting eg using transactional replication,
or database mirroring with database snapshots.
Alternatively you could allow dirty reads (NOLOCK) on the tables in
question - depends on the business constraints.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||So if we place (nolock) hints on all the tables in all the views that should
eliminate the locking?
Paul Ibison wrote:
>A (non-dirty) read of data takes out a shared lock and this is not peculiar
>to views. If these views are used for reporting purposes, you might want to
>have a replica to be used for reporting eg using transactional replication,
>or database mirroring with database snapshots.
>Alternatively you could allow dirty reads (NOLOCK) on the tables in
>question - depends on the business constraints.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Message posted via http://www.droptable.com
|||Yes - or more easily set the transaction isolation-level to read
uncommitted.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Friday, March 9, 2012

replication of views that reference other views

hi,
in a merge replication I use a view (V1) that references another view (V2)
in its select statement.
when running the merge agent to initialize the subscriber I get this error:
The schema script '\\SSS-SERVER\ReplData\unc\SSS-
SERVER_E5K_714_FS_E5K_714_FS_Replikat\200404072145
58\V1_667.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: SSS1 (Agent); Error number: 0)
Invalid object name 'dbo.V2'.
(Source: SSS1 (Data source); Error number: 208)
I think the error occurs, because V2 is not yet present on the subscriber
when the agent tries to create V1. (when i check the views created on the
subscriber after the agent fails, V2 is missing)
so how can I tell the agent the order of creating objects?
or is there any other solution?
thanks for your help
thomas
Thomas,
your dependencies are out of sync. Run sp_refreshview on the 2 views then
they'll be replicates in the correct order.
Regards,
Paul Ibison
|||thanks paul,
i tried sp_refreshview on all my views, but i still have the same problem.
thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> your dependencies are out of sync. Run sp_refreshview on the 2 views then
> they'll be replicates in the correct order.
> Regards,
> Paul Ibison
>
|||use a presnapshot script that precreates the views on the subscriber.
"Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
> thanks paul,
> i tried sp_refreshview on all my views, but i still have the same problem.
> thomas
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
then
>
|||Thomas,
Although my reply may not apply to you, I am posting some documentation that
may help others.
Contrary to table articles, order of views in publication is seen to be
controlled differently. Maybe this behaviour has been fixed in a later
service pack or hotfix that I am not aware of, so sorry if that is the case.
Unless it is too late or at all feasible, try this workaround:
1. Create a publication for the views.
2. Add one view and generate a snapshot (this step is key to successful
implementation of this workaround).
3. Continue step 2 until all the views have been added. The order in which
you add your views will determine the order in which the snapshot will apply
them to the subscribers.
4. Apply the snapshot. This should work fine now.
Now, the tricky part here is that if you have to change your views and
dependencies change, you might be in trouble again. For example, View2
depends on View1. You add View1, gen snapshot, add View2, gen snapshot and
finally apply snapshot. Later you create View3 and change View2 to include
reference to View3. If you now add View3 to the publication and reinitialise
your subscriptions, the snapshot will try to apply View1, View2 and View3 in
that order instead of the order View1, View3, View2 as required. Obviously,
the agent will fail with the same message you have posted because View3 does
not exist at the subscriber yet and View2 is being applied.
So, the bottom line is: do not use publications with views as articles if
you can have the above scenario. But if your design is not going to change
at all (very unlikely?), then go ahead with the above workaround.
Alternatively, use pre-snapshot scripts, as also suggested by Hilary. Of
course, the scripts should contain the views in the proper order.
Hope the above helps,
Raj Moloye
|||Thomas,
the order of running it is crucial - run sp_refreshview on the simple view
then run it on the view that depends on it. This dependency should show up
using sp_depends. Provided the dependency is recognised, then the order of
application in the snapshot should be correct. I have run this successfully
before. However, if it doesn't work then Hilary's advice (postscripts) or
sp_addscriptexec will do the same thing.
Regards,
Paul Ibison
|||views can also be replicated to all subscribers using sp_addscriptexec
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eMlshkkHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> use a presnapshot script that precreates the views on the subscriber.
> "Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
> news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
problem.
> then
>
|||Paul,
you were right. I re-ordered my script so that the simple views were
refreshed first - now replication works fine.
thanks to all of you for your support
Thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OH$2dhlHEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> the order of running it is crucial - run sp_refreshview on the simple view
> then run it on the view that depends on it. This dependency should show up
> using sp_depends. Provided the dependency is recognised, then the order of
> application in the snapshot should be correct. I have run this
successfully
> before. However, if it doesn't work then Hilary's advice (postscripts) or
> sp_addscriptexec will do the same thing.
> Regards,
> Paul Ibison
>

Replication of SPs/Views/UDFs - Third party tool?

Hi,
I am using merge replication with anonymous pull subscriber. My problem is
getting a way to consistenly replicate database objects (SPs/Views/UDFs).
Replication kind of support it, but then you are not able to modify
(drop/alter) the objects and it doesn't work anyways because it depends on
the sysdependencies information (which is totally messed up).
So, I cannot use replication for that. I have tested DTS and it fails
because sysdependencies doesn't hold dependencies accurately enough.
I am starting to think on getting a third party tool or program my own...
I know I can include before snapshot scripts to create my objects; the
problem is that my database is highly dynamic, ie: objects are
created/modified/dropped too frequently.
Any advice?
Thanks, Jos Araujo.
use sp_addscriptexec to deploy your changes in your procs, views, and other
objects. This will only work for non ftp deployed subscriptions.
Getting dependencies correct is a problem no matter what RDBMS you use.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am using merge replication with anonymous pull subscriber. My problem is
> getting a way to consistenly replicate database objects (SPs/Views/UDFs).
> Replication kind of support it, but then you are not able to modify
> (drop/alter) the objects and it doesn't work anyways because it depends on
> the sysdependencies information (which is totally messed up).
> So, I cannot use replication for that. I have tested DTS and it fails
> because sysdependencies doesn't hold dependencies accurately enough.
> I am starting to think on getting a third party tool or program my own...
> I know I can include before snapshot scripts to create my objects; the
> problem is that my database is highly dynamic, ie: objects are
> created/modified/dropped too frequently.
> Any advice?
> Thanks, Jos Araujo.
>
|||I would do that (use sp_addscriptexec); however there is SQL code being
autocreated by my application.
For instance, there are "rules" that the user defines, that are supposed to
affect the records, those rules are "translated" to stored procedures that
the application creates.
Of course, i could change a lot of things to get it working with
sp_addscriptexec, however, it would really easier to just have an
application to "synchronize" these objects.
Thanks, Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> use sp_addscriptexec to deploy your changes in your procs, views, and
other[vbcol=seagreen]
> objects. This will only work for non ftp deployed subscriptions.
> Getting dependencies correct is a problem no matter what RDBMS you use.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
is[vbcol=seagreen]
(SPs/Views/UDFs).[vbcol=seagreen]
on[vbcol=seagreen]
own...
>
|||This is not an automated approach, but you can put your objects in a
different publication, manually create the snapshot when needed and then
reinit the subscribers to push your changes.
Scott
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
>I would do that (use sp_addscriptexec); however there is SQL code being
> autocreated by my application.
> For instance, there are "rules" that the user defines, that are supposed
> to
> affect the records, those rules are "translated" to stored procedures that
> the application creates.
> Of course, i could change a lot of things to get it working with
> sp_addscriptexec, however, it would really easier to just have an
> application to "synchronize" these objects.
> Thanks, Jos.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> other
> is
> (SPs/Views/UDFs).
> on
> own...
>
|||Thanks...
"Scott Wallace" <scott.wallace@.astyles.com> wrote in message
news:eb6eajlqEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> This is not an automated approach, but you can put your objects in a
> different publication, manually create the snapshot when needed and then
> reinit the subscribers to push your changes.
> Scott
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
that[vbcol=seagreen]
problem[vbcol=seagreen]
depends[vbcol=seagreen]
the
>

Replication of SP's mistake

When setting up replication of Stored Procedures & Views I made a mistake, I did not change the properties to tell replication to replicate the execution of the stored procedures.
How can I change this?
Originally, I just selected publish all & now when I look at Articles on the publication this is not checked, once the snapshot ran. I'm a bit confused.....
I want to the stored procedures to execute on my reporting/replicated db.
Thanx!
That's really not a very good option for me, is there any other way?
If I drop this publication, then I will need to reinitialize for >1000
tables only to get the stored procedures & views replicated? Not really
what I want to do, any other options available?
JUDE
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:023b01c4abcd$4d80f980$a301280a@.phx.gbl...
> The easiest way is to script out the publication then
> drop it.
> Change the scripts so
> @.type = N'proc exec'
> @.status = 0
> Then recreate the publication and initialize.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
I replicate my views and stored procedures in a separate publication to
avoid snapshotting the tables when my views change - perhaps this would be
an easier option for you?
Alternatively you could drop the subscription to each article and remove the
article eg:
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
The readd the article, run the snapshot agent and synchronize.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||A separate publication sounds good, these views & SP's seem to be ever
changing at times.
I think I will script the publication, pull out the views & sp's, drop
subscriptions & articles, then setup a separate publication for the views &
sp's.
Another question...
What's the benefit of replicating the view execution (Actually, is there
such a thing?)
When I replicate views & SP's, can it be both schema & execution being
replicated to the reporting server, or an either or situation?
Thanx!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORXYiI%23qEHA.896@.TK2MSFTNGP12.phx.gbl...
> JLS,
> I replicate my views and stored procedures in a separate publication to
> avoid snapshotting the tables when my views change - perhaps this would be
> an easier option for you?
> Alternatively you could drop the subscription to each article and remove
the
> article eg:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> The readd the article, run the snapshot agent and synchronize.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
for views you don't get the same option.
For each stored proc article you get the options listed as the @.type options
in sp_addarticle. So, the 'structure' or 'structure and execution' are the
main options. If you select the latter, the schema will go down as well as
the execution, and it is not possible to select just the latter.
BTW,
I also
(a) apply a post-snapshot script to apply permissions.
(b) have a separate publication for udfs to force them to go first
(c) sometimes resort to sp_addscriptexec when the dependencies get mixed up.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication of dependent objects...

Hi,
I have been trying to create a publication of all my views in my database.
The problem that i have, when the subscriber tries to apply the snapshot is
that some views are dependent to others views... so, the subscriber fails
(because the dependency has not been created yet).
This is happening in snapshot and merge replication... I know i can
workaround it, by just creating multiple publications (one per each level of
dependencies that i have), however, this adds more tasks to my already long
maintance list of TO-DOs...
Anyone has one idea? Should i just learn to live with this problem?
Thanks, Jos.
replication depends on sysdepends which is not always accurate. You can try
to fix sysdepends, or replication your stored procedures/functions/views
using a post snapshot command.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have been trying to create a publication of all my views in my database.
> The problem that i have, when the subscriber tries to apply the snapshot
is
> that some views are dependent to others views... so, the subscriber fails
> (because the dependency has not been created yet).
> This is happening in snapshot and merge replication... I know i can
> workaround it, by just creating multiple publications (one per each level
of
> dependencies that i have), however, this adds more tasks to my already
long
> maintance list of TO-DOs...
> Anyone has one idea? Should i just learn to live with this problem?
> Thanks, Jos.
>
|||Jos,
I have had the same issue and as Hilary says, the main option is to fix the
dependencies. Correcting the order even applies to a script run after the
snapshot, in the case of views, which (unlike stored procecures) don't use
deferred name resolution.
You might find these articles helpful:
BUG: Recreating a Table Causes sysdepends to Become Invalid
http://support.microsoft.com/?id=115333
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
Sp_depends
http://support.microsoft.com/?id=201846
Displaying Dependencies
http://www.microsoft.com/sql/techinf...pendencies.asp
HTH,
Paul Ibison
|||Thanks for your answer...
However, what is a "post snapshot command"?
Do you mean to not include my sp/functions/views in the original snapshot,
and then running the scripts to create the objects?
Thanks again... Jos
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> replication depends on sysdepends which is not always accurate. You can
try[vbcol=seagreen]
> to fix sysdepends, or replication your stored procedures/functions/views
> using a post snapshot command.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
database.[vbcol=seagreen]
> is
fails[vbcol=seagreen]
level
> of
> long
>
|||I'll sure check out the link you've provided...
Thanks a lot... Jos.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||If your post snapshot script is jumbled you will get warning messages while
running the script referring to the sysdepends problem, but your
distribution agent won't fail. So it will work.
How do you fix the dependencies on the publisher so the snapshot script is
generated correctly in the first place? AFAIK - there is no way to fix
sysdepends.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||"AFAIK - there is no way to fix sysdepends."
Yeah... i have been looking for a way to recreate the dependencies, but the
only one that I have found is to recreate all objects (which is crazy).
It seems so logical that there should be a SP that recreate the dependencies
information for a given object (the same SP that MSSQL should use to create
that information in the first place)... but either it doesn't exist, or
nobody knows it exists ...
Jos.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If your post snapshot script is jumbled you will get warning messages
while[vbcol=seagreen]
> running the script referring to the sysdepends problem, but your
> distribution agent won't fail. So it will work.
> How do you fix the dependencies on the publisher so the snapshot script is
> generated correctly in the first place? AFAIK - there is no way to fix
> sysdepends.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> the
the[vbcol=seagreen]
use
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||evidently this problem plagues other RDBMS's.
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:O2PhDYtYEHA.4068@.TK2MSFTNGP10.phx.gbl...
> "AFAIK - there is no way to fix sysdepends."
> Yeah... i have been looking for a way to recreate the dependencies, but
the
> only one that I have found is to recreate all objects (which is crazy).
> It seems so logical that there should be a SP that recreate the
dependencies
> information for a given object (the same SP that MSSQL should use to
create[vbcol=seagreen]
> that information in the first place)... but either it doesn't exist, or
> nobody knows it exists ...
> Jos.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> while
is[vbcol=seagreen]
fix[vbcol=seagreen]
> the
> use
in
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||right click on your publication, select publication properties, in the
snapshot tab there is an option for pre and post snapshot scripts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for your answer...
> However, what is a "post snapshot command"?
> Do you mean to not include my sp/functions/views in the original snapshot,
> and then running the scripts to create the objects?
> Thanks again... Jos
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> try
> database.
snapshot
> fails
> level
>
|||thanks
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eyBcx1vYEHA.3112@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> right click on your publication, select publication properties, in the
> snapshot tab there is an option for pre and post snapshot scripts.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...
snapshot,[vbcol=seagreen]
can[vbcol=seagreen]
procedures/functions/views[vbcol=seagreen]
> snapshot
already
>