I am developing a transactional replication plan. I have one table
that most of the time I do not want to replicate deletions. There is
an exception to this rule. If the status_id of another table is false,
then the deletion should be replicated.
I have been having difficulty trying to put this condition in place. I
have tried modifying the sp_MSdel_dboTable stored procedure. I have
removed the if @.@.rowcount check and replication errors with the
messages:
-- Error executing a batch of commands. Retrying individual commands.
Eventually, it will fail with the message: if @.@.trancount > 0 rollback
tran
Any ideas on how to get around this issue?
Publisher: SQL 2000
Distributor: On a separate server
Subscriber: SQL 2005
Thanks for any help I can get.
You need to replicate an indexed view as a table on the subscriber. The
indexed view would only "show" rows which match the status_id in the other
table.
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
"emreeves" <em_reeves@.lycos.com> wrote in message
news:1143754411.192208.291820@.i40g2000cwc.googlegr oups.com...
>I am developing a transactional replication plan. I have one table
> that most of the time I do not want to replicate deletions. There is
> an exception to this rule. If the status_id of another table is false,
> then the deletion should be replicated.
> I have been having difficulty trying to put this condition in place. I
> have tried modifying the sp_MSdel_dboTable stored procedure. I have
> removed the if @.@.rowcount check and replication errors with the
> messages:
> -- Error executing a batch of commands. Retrying individual commands.
> Eventually, it will fail with the message: if @.@.trancount > 0 rollback
> tran
> Any ideas on how to get around this issue?
>
> Publisher: SQL 2000
> Distributor: On a separate server
> Subscriber: SQL 2005
> Thanks for any help I can get.
>
|||Thanks! This sounds better than the next approach I was going to
try... creating a 1st Insert/Update trans replication, and a 2nd Delete
only with row filtering.
BTW - Thanks very much for your replication book. It has helped loads
this past few weeks.
Showing posts with label developing. Show all posts
Showing posts with label developing. Show all posts
Saturday, February 25, 2012
Friday, February 17, 2012
Concurrent access problem...
Hi All
I am developing portfolio management application. In this application i
am fetching data from database and populate to dataset and that dataset
is getting binded to the datagrid... it is multiuse application and one
portfolio is getting accessed by more than one user. somehow i want to
make sure that one transaction can be updated by only one person. no
more than one person should be allowed to update one transaction... can
anyone suggest me good approach to solve this problem
thanks
Deep OceanOne simple approach would be to have a timestamp or datetime column in the
table .
Between the Retrieval and Save of the data - check for the datetime column
to have same value, which mean no other user has updated the record. If the
column values are not same between retrieval and save then return an error
message to the user that the record has already been updated by some other
user and user need to refresh the screen to do a frech update.
"Deep Silent Ocean" wrote:
> Hi All
>
> I am developing portfolio management application. In this application i
> am fetching data from database and populate to dataset and that dataset
> is getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no
> more than one person should be allowed to update one transaction... can
> anyone suggest me good approach to solve this problem
> thanks
> Deep Ocean
>|||Is this a web application using a disconnected ADO.NET dataset or a client
server type application using a connected ADO recordset?
For ADO recordsets bound to a grid (ex: Visual Basic or MS Office), it is a
matter of specifying the LockType property of the recordset to optimistic or
pessimistic.
http://msdn.microsoft.com/library/d.../>
ocktype.asp
http://msdn.microsoft.com/library/d...ocktypeenum.asp
http://msdn.microsoft.com/library/d...
ursortypex.asp
In web application, data changes are made to an ADO.NET database indirectly
via a disconnected dataset, so the issues are different. Here is an
excellent article on handling concurrency issues in a web application.
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
Pessimistic locking can sometimes inadvertently prevent users from
performing otherwise harmless operations, so I favor the method of using a
timestamp column and then giving the user the flexibility of choosing
whether or not to save their changes over another edit. However, this should
be rarely needed. It's also more of a business process issue than a
technical issue. Generally speaking, an account, invoice, portfolio, etc.
should be assigned to a specific account manager. It would seem confusing to
have multiple people working the same customer, but perhaps you mean
something different by "portfolio".
"Deep Silent Ocean" <ocean.indian@.gmail.com> wrote in message
news:O2tvKf9vFHA.908@.tk2msftngp13.phx.gbl...
> Hi All
>
> I am developing portfolio management application. In this application i am
> fetching data from database and populate to dataset and that dataset is
> getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no more
> than one person should be allowed to update one transaction... can anyone
> suggest me good approach to solve this problem
> thanks
> Deep Ocean
I am developing portfolio management application. In this application i
am fetching data from database and populate to dataset and that dataset
is getting binded to the datagrid... it is multiuse application and one
portfolio is getting accessed by more than one user. somehow i want to
make sure that one transaction can be updated by only one person. no
more than one person should be allowed to update one transaction... can
anyone suggest me good approach to solve this problem
thanks
Deep OceanOne simple approach would be to have a timestamp or datetime column in the
table .
Between the Retrieval and Save of the data - check for the datetime column
to have same value, which mean no other user has updated the record. If the
column values are not same between retrieval and save then return an error
message to the user that the record has already been updated by some other
user and user need to refresh the screen to do a frech update.
"Deep Silent Ocean" wrote:
> Hi All
>
> I am developing portfolio management application. In this application i
> am fetching data from database and populate to dataset and that dataset
> is getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no
> more than one person should be allowed to update one transaction... can
> anyone suggest me good approach to solve this problem
> thanks
> Deep Ocean
>|||Is this a web application using a disconnected ADO.NET dataset or a client
server type application using a connected ADO recordset?
For ADO recordsets bound to a grid (ex: Visual Basic or MS Office), it is a
matter of specifying the LockType property of the recordset to optimistic or
pessimistic.
http://msdn.microsoft.com/library/d.../>
ocktype.asp
http://msdn.microsoft.com/library/d...ocktypeenum.asp
http://msdn.microsoft.com/library/d...
ursortypex.asp
In web application, data changes are made to an ADO.NET database indirectly
via a disconnected dataset, so the issues are different. Here is an
excellent article on handling concurrency issues in a web application.
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
Pessimistic locking can sometimes inadvertently prevent users from
performing otherwise harmless operations, so I favor the method of using a
timestamp column and then giving the user the flexibility of choosing
whether or not to save their changes over another edit. However, this should
be rarely needed. It's also more of a business process issue than a
technical issue. Generally speaking, an account, invoice, portfolio, etc.
should be assigned to a specific account manager. It would seem confusing to
have multiple people working the same customer, but perhaps you mean
something different by "portfolio".
"Deep Silent Ocean" <ocean.indian@.gmail.com> wrote in message
news:O2tvKf9vFHA.908@.tk2msftngp13.phx.gbl...
> Hi All
>
> I am developing portfolio management application. In this application i am
> fetching data from database and populate to dataset and that dataset is
> getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no more
> than one person should be allowed to update one transaction... can anyone
> suggest me good approach to solve this problem
> thanks
> Deep Ocean
Labels:
access,
alli,
application,
concurrent,
database,
dataset,
developing,
fetching,
iam,
management,
microsoft,
mysql,
oracle,
populate,
portfolio,
server,
sql
Sunday, February 12, 2012
Concatinating child field
I'm developing an ASP.NET app with SQL back end. I'm trying to develop a use
r
interface that must reference information from two related tables and would
like to return the following (either in table, but preferably in datagrid):
ParentField1 ParentField2 Parent Field3 ChildField1Record1
ChildField1Record2
ChildField1Record3
ParentField1 ParentField2 ParentField3 ChildFeld1Record4
ChildFeld1Record5
ChildField1Record6 etc.
Is there anyway to write a TSQL function to concatinate the child records
into a dynamic field (there are about 400 parent records)
I could create a denormalized table that mimics the child table by has the
ParentID and ChildRecords concatinated that gets updated everytime the child
table changes, but that's just bad programming!
Any suggestions would be greatly appreciated.
Thanks in advance.well the question is not clear to me. but i guess u are trying to concatinat
e
the fields in the query
ucan do it as
select <field1> + <field2> + ..
FROM <Table>
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"AbeR" wrote:
> I'm developing an ASP.NET app with SQL back end. I'm trying to develop a u
ser
> interface that must reference information from two related tables and woul
d
> like to return the following (either in table, but preferably in datagrid)
:
> ParentField1 ParentField2 Parent Field3 ChildField1Record1
> ChildField1Record2
> ChildField1Record3
> ParentField1 ParentField2 ParentField3 ChildFeld1Record4
> ChildFeld1Record5
> ChildField1Record6 etc.
> Is there anyway to write a TSQL function to concatinate the child records
> into a dynamic field (there are about 400 parent records)
> I could create a denormalized table that mimics the child table by has the
> ParentID and ChildRecords concatinated that gets updated everytime the chi
ld
> table changes, but that's just bad programming!
> Any suggestions would be greatly appreciated.
> Thanks in advance.|||Hi Chandra,
Thanks for the response, and sorry for not being clear. Actually I am trying
to return contents of all records with the ParentID from the child table wit
h
the parent record from the parent table, so actually want to select
record1field1 + record2field1 + record3field1 from chldtable joining
parenttable without repeating the parent data for each record.
Regards,
AbeR
"Chandra" wrote:
> well the question is not clear to me. but i guess u are trying to concatin
ate
> the fields in the query
> ucan do it as
> select <field1> + <field2> + ..
> FROM <Table>
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "AbeR" wrote:
>|||>> Is there anyway to write a TSQL function to concatinate the child records
The terminology you used is a bit confusing.
Assuming that there is a one-to-many relationship that exist between the
tables, what you seem to be asking is to return a resultset with the single
value from the referenced table and all the values from the referencing
table as a contenated string. In general, such processing which demand heavy
looping and formatting of values should be done on the client side -- quite
often one should be able to leverage the functionality of a programming
language or a report-writer in such cases.
Doing this on the server often requires procedural logic, often disguised as
a user defined function or perhaps cursors. For some such workarounds, refer
to: http://tinyurl.com/aka4u
Anith|||Exactly. I was hoping not to create another dataset on the client system, bu
t
that's probably the best approach (While loops in C# within the app)...
DataSet1 will be read by the client and produce dataset two with all the
values from the referenced table as one string and then present in a datagri
d.
Many thanks for the advice and reference.
- AbeR
"Anith Sen" wrote:
> The terminology you used is a bit confusing.
> Assuming that there is a one-to-many relationship that exist between the
> tables, what you seem to be asking is to return a resultset with the singl
e
> value from the referenced table and all the values from the referencing
> table as a contenated string. In general, such processing which demand hea
vy
> looping and formatting of values should be done on the client side -- quit
e
> often one should be able to leverage the functionality of a programming
> language or a report-writer in such cases.
> Doing this on the server often requires procedural logic, often disguised
as
> a user defined function or perhaps cursors. For some such workarounds, ref
er
> to: http://tinyurl.com/aka4u
> --
> Anith
>
>
r
interface that must reference information from two related tables and would
like to return the following (either in table, but preferably in datagrid):
ParentField1 ParentField2 Parent Field3 ChildField1Record1
ChildField1Record2
ChildField1Record3
ParentField1 ParentField2 ParentField3 ChildFeld1Record4
ChildFeld1Record5
ChildField1Record6 etc.
Is there anyway to write a TSQL function to concatinate the child records
into a dynamic field (there are about 400 parent records)
I could create a denormalized table that mimics the child table by has the
ParentID and ChildRecords concatinated that gets updated everytime the child
table changes, but that's just bad programming!
Any suggestions would be greatly appreciated.
Thanks in advance.well the question is not clear to me. but i guess u are trying to concatinat
e
the fields in the query
ucan do it as
select <field1> + <field2> + ..
FROM <Table>
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"AbeR" wrote:
> I'm developing an ASP.NET app with SQL back end. I'm trying to develop a u
ser
> interface that must reference information from two related tables and woul
d
> like to return the following (either in table, but preferably in datagrid)
:
> ParentField1 ParentField2 Parent Field3 ChildField1Record1
> ChildField1Record2
> ChildField1Record3
> ParentField1 ParentField2 ParentField3 ChildFeld1Record4
> ChildFeld1Record5
> ChildField1Record6 etc.
> Is there anyway to write a TSQL function to concatinate the child records
> into a dynamic field (there are about 400 parent records)
> I could create a denormalized table that mimics the child table by has the
> ParentID and ChildRecords concatinated that gets updated everytime the chi
ld
> table changes, but that's just bad programming!
> Any suggestions would be greatly appreciated.
> Thanks in advance.|||Hi Chandra,
Thanks for the response, and sorry for not being clear. Actually I am trying
to return contents of all records with the ParentID from the child table wit
h
the parent record from the parent table, so actually want to select
record1field1 + record2field1 + record3field1 from chldtable joining
parenttable without repeating the parent data for each record.
Regards,
AbeR
"Chandra" wrote:
> well the question is not clear to me. but i guess u are trying to concatin
ate
> the fields in the query
> ucan do it as
> select <field1> + <field2> + ..
> FROM <Table>
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "AbeR" wrote:
>|||>> Is there anyway to write a TSQL function to concatinate the child records
The terminology you used is a bit confusing.
Assuming that there is a one-to-many relationship that exist between the
tables, what you seem to be asking is to return a resultset with the single
value from the referenced table and all the values from the referencing
table as a contenated string. In general, such processing which demand heavy
looping and formatting of values should be done on the client side -- quite
often one should be able to leverage the functionality of a programming
language or a report-writer in such cases.
Doing this on the server often requires procedural logic, often disguised as
a user defined function or perhaps cursors. For some such workarounds, refer
to: http://tinyurl.com/aka4u
Anith|||Exactly. I was hoping not to create another dataset on the client system, bu
t
that's probably the best approach (While loops in C# within the app)...
DataSet1 will be read by the client and produce dataset two with all the
values from the referenced table as one string and then present in a datagri
d.
Many thanks for the advice and reference.
- AbeR
"Anith Sen" wrote:
> The terminology you used is a bit confusing.
> Assuming that there is a one-to-many relationship that exist between the
> tables, what you seem to be asking is to return a resultset with the singl
e
> value from the referenced table and all the values from the referencing
> table as a contenated string. In general, such processing which demand hea
vy
> looping and formatting of values should be done on the client side -- quit
e
> often one should be able to leverage the functionality of a programming
> language or a report-writer in such cases.
> Doing this on the server often requires procedural logic, often disguised
as
> a user defined function or perhaps cursors. For some such workarounds, ref
er
> to: http://tinyurl.com/aka4u
> --
> Anith
>
>
Subscribe to:
Posts (Atom)