Peer to Peer difficulty when adding a column

I'm trying to add a column to my peer to peer replication. According to the
help I've found:
Adding Columns
a.. To add a new column to a table and include that column in an existing
publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
column is then replicated to all Subscribers. The column must allow NULL
values or include a default constraint.
b.. To add a new column to a table and not include that column in an
existing publication, disable the replication of schema changes, and then
execute ALTER TABLE <Table> ADD <Column>.
c.. To include an existing column in an existing publication, use
sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
the Publication Properties - <Publication> dialog box.
For more information, see How to: Define and Modify a Column Filter
(Replication Transact-SQL Programming) and How to: Define and Modify a
Column Filter (SQL Server Management Studio). This will require
subscriptions to be reinitialized.
d.. Adding an identity column to a published table is not supported,
because it can result in non-convergence when the column is replicated to
the Subscriber. The values in the identity column at the Publisher depend on
the order in which the rows for the affected table are physically stored.
The rows might be stored differently at the Subscriber; therefore the value
for the identity column can be different for the same rows.
However, while I am able to add the column to the publisher and it is
replicated to the subscriber, I cannot update this column at the subscriber
and have it replicate to the publisher. However when I update at the
publisher it IS pushed to the subscriber.
Outside of the new column, my updates bi-directionally work. Can someone
tell me how I can get updates on newly added columns to be bi-directional?
Thanks,
Adam P. Cassidy
Did you quiesce the system as outlined in this link?
http://msdn2.microsoft.com/en-us/library/ms147385.aspx
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
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
> I'm trying to add a column to my peer to peer replication. According to
> the help I've found:
> Adding Columns
> a.. To add a new column to a table and include that column in an existing
> publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
> column is then replicated to all Subscribers. The column must allow NULL
> values or include a default constraint.
> b.. To add a new column to a table and not include that column in an
> existing publication, disable the replication of schema changes, and then
> execute ALTER TABLE <Table> ADD <Column>.
> c.. To include an existing column in an existing publication, use
> sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
> the Publication Properties - <Publication> dialog box.
> For more information, see How to: Define and Modify a Column Filter
> (Replication Transact-SQL Programming) and How to: Define and Modify a
> Column Filter (SQL Server Management Studio). This will require
> subscriptions to be reinitialized.
> d.. Adding an identity column to a published table is not supported,
> because it can result in non-convergence when the column is replicated to
> the Subscriber. The values in the identity column at the Publisher depend
> on the order in which the rows for the affected table are physically
> stored. The rows might be stored differently at the Subscriber; therefore
> the value for the identity column can be different for the same rows.
> However, while I am able to add the column to the publisher and it is
> replicated to the subscriber, I cannot update this column at the
> subscriber and have it replicate to the publisher. However when I update
> at the publisher it IS pushed to the subscriber.
> Outside of the new column, my updates bi-directionally work. Can someone
> tell me how I can get updates on newly added columns to be bi-directional?
> Thanks,
> Adam P. Cassidy
>
|||Yes. That is the same article I used as a reference originally.
Adam P. Cassidy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%234SYrOq4GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Did you quiesce the system as outlined in this link?
> http://msdn2.microsoft.com/en-us/library/ms147385.aspx
> --
> 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
>
> "Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
> news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>

Peer to Peer 64bit and 32bit question

If I have an existing 64bit P2P transactional replication setup, can I join
a 32bit machine without any expected problems or does it also have to be a
64bit machine?
Any direction to resources would be great as well.
Adam P. Cassidy
Adam,
For SQL Server 2000, the issues were summarised as follows:
As the Microsoft Jet engine is not supported, Microsoft Jet push
subscriptions for merge replication are not supported.
Unless the subscriber provides a 64-bit ODBC or OLE DB driver, transactional
or snapshot push subscriptions for ODBC or OLE DB subscribers are not
supported.
Because of the unavailability of 64-bit Data Transformation Services (DTS),
transformable push subscriptions are not supported.
In Peer-to-peer between 64bit and 32bit I am therefore aware of no issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||This is a tested configuration
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:O%23gjeUCCHHA.4864@.TK2MSFTNGP04.phx.gbl...
> If I have an existing 64bit P2P transactional replication setup, can I
> join
> a 32bit machine without any expected problems or does it also have to be a
> 64bit machine?
> Any direction to resources would be great as well.
> Adam P. Cassidy
>
sql

Peduliar problem in SSRS

Hi all ,

I am encountering a peculiar problem while creating a linked report in SSRS.

The scenario:

My first reports contains 3 columns . The first 3 columns contain data from vendor table in AdventureWorks DB. They are VendorID , Name, CreditRating. The fourth column contains a link to the second report. It is just a link which jumps to the other report and passes the above three values as parameters to the second report.

The second report contains 4 parameters which includes VendorID , Name, CreditRating and one more Postedby parameter which has to be entered manually when the second report is displayed. The three parameters from first report are passed on and one extra parameter has to entered manually then only the second report displays.

It works fine when I check it on Visual Studio preview mode. I click the link for a particular row in First Report and it asks me to enter the Postedby Parameter. After entering it it displays the 2nd report. But the problem arises when I deploy it to report server. When I click the link on first report it throughs an error like " The Parameter 'PostedBy' is missing a value" whereas it should prompt the user to enter the PostedBy Parameter.

I am stuck in this and not able to understand why this happens in ReportServer and not in Visual Studio Preview mode.

Does anybody has any idea why this is happening ?

Any Suggestion will be appreciated.

Regards...

Girija Shankar Beuria

You must specify the default value for the fourth parameter. Visual Studio is an interactive environment, so you get to enter the value on request by the VS. But in production environment, reports are consumed by other applications and the report will not be able to request for an input from the calling application and this applies to Report Manager also which is an external web application that consumes the reporting services.

Shyam

|||

just go to the code and see if there is any parameter hard coded in xml.

Remove that parameter and save it .

This will probably solve your problem.

Vamsi Krishna Korasiga

Pedigree database

I want to write an online database that displays 4-generation pedigrees. Can anyone point me to a tutorial/web site on how to do this? I've written several online databases in asp, but can't figure out how to do this particular algorithm. Any help appreciated.

Basically I have a list of about 400,000 horses each with a unique ID code. In each individual horses record there is its mother and father identified by their ID codes. ie; three columns, the horse's ID, and it's mother and father's ID. So, when you display a horses four generation chart, it should all link uphorse, mother father, grandparents, great-grandparents etc.. It should be a common database task, but Ive searched everywhere on the web and in various books and cant find how to write and link up the SQL statements. Any help/pointers would be appreciated. cheersYour table design should be similar to the following:

Horse Id,Horse Detail,Fathers Id, Mothers Id

Using SELF JOIN (Relating the table to itself by aliasing) you can derive any number of elements in a hierarchy.

e.g. Data

Horse Id | Horse Detail | Father Id | Mother Id
1 | ABC | |
2 | DEF | |
3 | GHI |1 |2
4 | JKL |1 |2
5 | MNO | |
6 | PQR |1 |5

An addtional field Sex (F/M) may be useful for your application

Hope this helps.

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

sql

Peculiar Replication (Could be OT)

We have 2 types of Production Database. One is an OLTP database another contains all the Master Tables. We also have 2 copies of each and this repeats itself for each country where the plant is located. Is there someone who can explain to me how we might
setup this Replication so that the Master tables which are maintained in one location gets replicated over to the other places and also the updated from those locations are also propogated to all locations. We had MS come into the picture and they have cr
eated triggers such that the data is copied over but they seem to delete the record and insert it back again for an Update and sometimes inbetween the delete and insert the leased line goes down and the result is a total failure.
Regards,
Trevor Benedict R
Trevor,
this is typically implemented using merge replication. You'll need to consider partitioning of data for each subscriber, or the posibility of conflicts and how they should be resolved. There's a good set of explanations of general concepts in BOL.
The network failures you experience are usually solved in merge by simply restarting the merge agent.
HTH,
Paul Ibison