question regarding referenced views and replication order

You can update the view definition and hope that this updates the
dependencies correctly. If it doesn't you can use sp_addscriptexec to add
all the views in the correct order. Note that in SQL Server 2005 you can
specify the article order (@.processing_order) to get round this type of
issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Stefan,
replication of views and procs doesn't follow the usual paradigm in
replication. Changes to these programming objects aren't picked up by the
log-reader, so we have to resort to some sort of manual solution to pick up
changes to replicated objects and apply them which ever way we go about it.
The script to generate the view creation scripts could be based on the view
definition - in which case it'll always be upto date if you even need to run
the whole thing again. Ad-hoc changes can meanwhile be done using individual
views.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.
Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>
|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>
|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is s
o
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:

> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups
?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

Question on SQL Update behaviour

I have a question on how SQL Server updates records. Say assuming if I have
a table with 50000 records.. and i need to update a particular field C in
this table, based on field A and field B on the same table. I can either
write a set based SP or a cursor based SP to do that.
If I update it using cursors, I can see the records being updated 1 by 1. If
I update the whole thing using a set based SP, am I right to say that I will
not see any records updated till the process is over? Does SQL Server does
the transaction commitment itself if I write it using the set-based
approach?
Thanks.Hi,
Update with set will do a batch updat based on the condition you are gving
in the where clause. So until the transaction is commited
you will not be able to see change. Where as Cursor does a row by row
operation. So after each record update you could see the
change.
Have a look into ISOLATION LEVEL topic in books online.
Thanks
Hari
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>I have a question on how SQL Server updates records. Say assuming if I have
>a table with 50000 records.. and i need to update a particular field C in
>this table, based on field A and field B on the same table. I can either
>write a set based SP or a cursor based SP to do that.
> If I update it using cursors, I can see the records being updated 1 by 1.
> If I update the whole thing using a set based SP, am I right to say that I
> will not see any records updated till the process is over? Does SQL Server
> does the transaction commitment itself if I write it using the set-based
> approach?
> Thanks.
>|||thanks for the prompt response... i'm really curious about how the table
will be locked up since i'm updating the table based on conditions from
fields that are in the same table as well...
will cursors work better in this case than batch insert due to the lockups?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Update with set will do a batch updat based on the condition you are gving
> in the where clause. So until the transaction is commited
> you will not be able to see change. Where as Cursor does a row by row
> operation. So after each record update you could see the
> change.
> Have a look into ISOLATION LEVEL topic in books online.
> Thanks
> Hari
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
>>I have a question on how SQL Server updates records. Say assuming if I
>>have a table with 50000 records.. and i need to update a particular field
>>C in this table, based on field A and field B on the same table. I can
>>either write a set based SP or a cursor based SP to do that.
>> If I update it using cursors, I can see the records being updated 1 by 1.
>> If I update the whole thing using a set based SP, am I right to say that
>> I will not see any records updated till the process is over? Does SQL
>> Server does the transaction commitment itself if I write it using the
>> set-based approach?
>> Thanks.
>|||you can actually see the changes taking place by running a query with the
table hint nolock as in: select count(*) from mytable with (nolock) where...
I use this approach at times to see how far in a large insert or update is so
I can calculate how much longer the statement will take.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Nestor" wrote:
> thanks for the prompt response... i'm really curious about how the table
> will be locked up since i'm updating the table based on conditions from
> fields that are in the same table as well...
> will cursors work better in this case than batch insert due to the lockups?
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uGQThcfgFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > Update with set will do a batch updat based on the condition you are gving
> > in the where clause. So until the transaction is commited
> > you will not be able to see change. Where as Cursor does a row by row
> > operation. So after each record update you could see the
> > change.
> >
> > Have a look into ISOLATION LEVEL topic in books online.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Nestor" <n3570r@.yahoo.com> wrote in message
> > news:uncViVfgFHA.2904@.tk2msftngp13.phx.gbl...
> >>I have a question on how SQL Server updates records. Say assuming if I
> >>have a table with 50000 records.. and i need to update a particular field
> >>C in this table, based on field A and field B on the same table. I can
> >>either write a set based SP or a cursor based SP to do that.
> >>
> >> If I update it using cursors, I can see the records being updated 1 by 1.
> >> If I update the whole thing using a set based SP, am I right to say that
> >> I will not see any records updated till the process is over? Does SQL
> >> Server does the transaction commitment itself if I write it using the
> >> set-based approach?
> >>
> >> Thanks.
> >>
> >
> >
>
>

Question on OUTPUT feature of DML

I need to audit inserts/updates/deletes on active tables to audit tables on a set of tables that have foreign key constaints with the update cascade and delete cascade defined. I can explicitly code the delete/update on the parent table to perform an OUTPUT to an audit table, but how do I OUTPUT the cascaded delete/update that happens on the child table because of the FK constraint with delete cascade defined without having to resort to triggers.

Thanks,

-chiraj

You might want to check into the use of DML triggers for this kind of issue, but this is not something that I have used much.

Dave

|||

Thanks for the response.

Using DML triggers is a no-brainer. I have used them all my life. I was wondering if it could be accomplished with the OUTPUT clause. I believe it is a limitation of the OUTPUT clause unless someone can show me otherwise.

Thanks,

-chiraj.