does anyone have an idea of what generally could be wrong with a pull merge subscription when it errors out with the following:
sql server The merge process could not retrieve column information for table "tablename"
Also, could you tell me where I can get more details of errors (i.e. where the errorlog for replication is located
I found how to output the merge agent errors. Here is the output. Can anyone shed any light on this?
[5/15/2004 6:28:05 PM]BOSERVER-CSC3.vis-csc3: {call sp_MSgetversion }
Connecting to Publisher 'VIVA-SQLHQ.VIS'
Server: VIVA-SQLHQ
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[5/15/2004 6:28:05 PM]VIVA-SQLHQ.VIS: {call sp_MSgetversion }
Percent Complete: 5
Processing article 'viva_visit_qualified_statuses'
Percent Complete: 5
Processing article 'viva_inoffice_statuses'
Percent Complete: 5
Processing article 'Users'
The merge process could not retrieve column information for table 'dbo.viva_visit_qualified_statuses'.
The merge process could not retrieve column information for table 'dbo.viva_inoffice_statuses'.The merge process could not retrieve column information for table 'dbo.Users'.
The merge process could not retrieve column information for table 'dbo.Applicant Requirements'.
Percent Complete: 0
The merge process could not retrieve column information for table 'dbo.viva_visit_qualified_statuses'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147201016
Message: The merge process could not retrieve column information for table 'dbo.viva_visit_qualified_statuses'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSenumcolumns (?,?)}
Percent Complete: 0
Category:SQLSERVER
Source: BOSERVER-CSC3
Number: 2812
Message: Could not find stored procedure 'sp_sel_82D17F3FEACB41DE7A9684009ECD4868'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147201016
Message: The merge process could not retrieve column information for table 'dbo.viva_inoffice_statuses'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147201016
Message: The process was successfully stopped.
Disconnecting from Subscriber 'BOSERVER-CSC3'
Disconnecting from Publisher 'VIVA-SQLHQ'
Disconnecting from Subscriber 'BOSERVER-CSC3'
Disconnecting from Subscriber 'BOSERVER-CSC3'
Disconnecting from Subscriber 'BOSERVER-CSC3'
Disconnecting from Subscriber 'BOSERVER-CSC3'
Disconnecting from Publisher 'VIVA-SQLHQ'
Disconnecting from Publisher 'VIVA-SQLHQ'
Disconnecting from Publisher 'VIVA-SQLHQ'
Disconnecting from Publisher 'VIVA-SQLHQ'
Disconnecting from Publisher 'VIVA-SQLHQ'
Disconnecting from Distributor 'VIVA-SQLHQ'
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Wednesday, March 28, 2012
Tuesday, March 20, 2012
replication and the identity column
I have been dealing with transactional replication a little bit and I am a
bit confused with the identity column and its affect on replication.
It seems like SS doesn't want to replicate the identity the subscriber. The
Dialogue box directed me to set the property to "not for replication", when I
did that the identify is blank in the subscriber db.
If I want to use the subscriber DB as a DR database, and the app needs the
identity column to be the same, isn't there a way around this limitation.
can someone point me to a resource that explains this concept?
thanks
Hi,
I understand that you would like to replicate the identity column to your
subscriber, however the process failed. You would like to know why.
If I have misunderstood, please let me know.
This is a known by design limitation. Please refer to this article:
Managing Identity Values
http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx
You may perform a test to check if the third method using other columns as
primary keys helps. If it could not fit your requirement, I am afraid that
you may consider to use an application to implement the replication by
yourself.
Please feel free to let me know if you have any other questions or
concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||The simplest solution is to set up the subscriber as a queued updating
subscriber, and allocate a large range for the identity ranges. This will
ensure the identity column is retained, as well as retaining the PKs.
HTH,
Paul Ibison
|||I normally use bi-directional transactional replication with odd values on
one side and even on the other. IE an odd seed on the publisher , and an
even on the subscriber. Set the increment to 2.
Put the schema in place on the subscriber and put the data there. Then do a
nosync and ensure that the identities are the correct values.
Use DBCC checkident to verify that the next assigned value on the Publisher
is odd and even on the subscriber.
While you can use queued updating for this, queued is designed for
situations where the majority of your dml occurs on your publisher not your
subscriber and it uses triggers to track changes occurring on the
subscriber. This will add latency to every DML occurring on the subscriber.
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
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>I have been dealing with transactional replication a little bit and I am a
> bit confused with the identity column and its affect on replication.
> It seems like SS doesn't want to replicate the identity the subscriber.
> The
> Dialogue box directed me to set the property to "not for replication",
> when I
> did that the identify is blank in the subscriber db.
> If I want to use the subscriber DB as a DR database, and the app needs the
> identity column to be the same, isn't there a way around this limitation.
> can someone point me to a resource that explains this concept?
>
> thanks
|||thanks, so this is someting that would be set up when creating the subsciber?
i don't see this option anywhere on my test replication scenario i have
running.
"Paul Ibison" wrote:
> The simplest solution is to set up the subscriber as a queued updating
> subscriber, and allocate a large range for the identity ranges. This will
> ensure the identity column is retained, as well as retaining the PKs.
> HTH,
> Paul Ibison
>
|||thanks for the reply. in this scenario would the identity values be the same
on the subscriber side then? doesn't read that way.
"Hilary Cotter" wrote:
> I normally use bi-directional transactional replication with odd values on
> one side and even on the other. IE an odd seed on the publisher , and an
> even on the subscriber. Set the increment to 2.
> Put the schema in place on the subscriber and put the data there. Then do a
> nosync and ensure that the identities are the correct values.
> Use DBCC checkident to verify that the next assigned value on the Publisher
> is odd and even on the subscriber.
> While you can use queued updating for this, queued is designed for
> situations where the majority of your dml occurs on your publisher not your
> subscriber and it uses triggers to track changes occurring on the
> subscriber. This will add latency to every DML occurring on the subscriber.
> --
> 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
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>
>
|||They would be the same. Rows entered on the publisher will be odd, rows
entered on the subscriber will be even.
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
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:DB5F2603-41BF-4C98-B1C9-D69C2BFCC184@.microsoft.com...[vbcol=seagreen]
> thanks for the reply. in this scenario would the identity values be the
> same
> on the subscriber side then? doesn't read that way.
> "Hilary Cotter" wrote:
|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
bit confused with the identity column and its affect on replication.
It seems like SS doesn't want to replicate the identity the subscriber. The
Dialogue box directed me to set the property to "not for replication", when I
did that the identify is blank in the subscriber db.
If I want to use the subscriber DB as a DR database, and the app needs the
identity column to be the same, isn't there a way around this limitation.
can someone point me to a resource that explains this concept?
thanks
Hi,
I understand that you would like to replicate the identity column to your
subscriber, however the process failed. You would like to know why.
If I have misunderstood, please let me know.
This is a known by design limitation. Please refer to this article:
Managing Identity Values
http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx
You may perform a test to check if the third method using other columns as
primary keys helps. If it could not fit your requirement, I am afraid that
you may consider to use an application to implement the replication by
yourself.
Please feel free to let me know if you have any other questions or
concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||The simplest solution is to set up the subscriber as a queued updating
subscriber, and allocate a large range for the identity ranges. This will
ensure the identity column is retained, as well as retaining the PKs.
HTH,
Paul Ibison
|||I normally use bi-directional transactional replication with odd values on
one side and even on the other. IE an odd seed on the publisher , and an
even on the subscriber. Set the increment to 2.
Put the schema in place on the subscriber and put the data there. Then do a
nosync and ensure that the identities are the correct values.
Use DBCC checkident to verify that the next assigned value on the Publisher
is odd and even on the subscriber.
While you can use queued updating for this, queued is designed for
situations where the majority of your dml occurs on your publisher not your
subscriber and it uses triggers to track changes occurring on the
subscriber. This will add latency to every DML occurring on the subscriber.
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
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>I have been dealing with transactional replication a little bit and I am a
> bit confused with the identity column and its affect on replication.
> It seems like SS doesn't want to replicate the identity the subscriber.
> The
> Dialogue box directed me to set the property to "not for replication",
> when I
> did that the identify is blank in the subscriber db.
> If I want to use the subscriber DB as a DR database, and the app needs the
> identity column to be the same, isn't there a way around this limitation.
> can someone point me to a resource that explains this concept?
>
> thanks
|||thanks, so this is someting that would be set up when creating the subsciber?
i don't see this option anywhere on my test replication scenario i have
running.
"Paul Ibison" wrote:
> The simplest solution is to set up the subscriber as a queued updating
> subscriber, and allocate a large range for the identity ranges. This will
> ensure the identity column is retained, as well as retaining the PKs.
> HTH,
> Paul Ibison
>
|||thanks for the reply. in this scenario would the identity values be the same
on the subscriber side then? doesn't read that way.
"Hilary Cotter" wrote:
> I normally use bi-directional transactional replication with odd values on
> one side and even on the other. IE an odd seed on the publisher , and an
> even on the subscriber. Set the increment to 2.
> Put the schema in place on the subscriber and put the data there. Then do a
> nosync and ensure that the identities are the correct values.
> Use DBCC checkident to verify that the next assigned value on the Publisher
> is odd and even on the subscriber.
> While you can use queued updating for this, queued is designed for
> situations where the majority of your dml occurs on your publisher not your
> subscriber and it uses triggers to track changes occurring on the
> subscriber. This will add latency to every DML occurring on the subscriber.
> --
> 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
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:9445707D-EB52-4214-81E4-1694BFCEC4AE@.microsoft.com...
>
>
|||They would be the same. Rows entered on the publisher will be odd, rows
entered on the subscriber will be even.
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
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:DB5F2603-41BF-4C98-B1C9-D69C2BFCC184@.microsoft.com...[vbcol=seagreen]
> thanks for the reply. in this scenario would the identity values be the
> same
> on the subscriber side then? doesn't read that way.
> "Hilary Cotter" wrote:
|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
replication and identity problem
Every time I start replication i get following error:
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>
Monday, March 12, 2012
Replication 255 column limit
Is there a workaround for this limitation? I.E can I just use the
column filters option when setting replication up to get around this?
You could upgrade, for a out-of-the box support. Otherwise what people tend
to do is partition the table and replicate the partitions, related by a
PK-PK join. You could present the tables as the original one to the users by
using a view and an instead-of trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Rob,
It may also be possible to publish an indexed view containing a subset of
the columns as a logbased article (replicate indexed view as table...) if
you can take the performance hit of having the indexed view hanging around
at the publisher.
-Raymond
<robert@.orisoft.co.uk> wrote in message
news:1161004229.423453.155350@.i42g2000cwa.googlegr oups.com...
> Is there a workaround for this limitation? I.E can I just use the
> column filters option when setting replication up to get around this?
>
|||Sorry - just read Raymond's answer and realised I had misinterpreted the
question. To replicate a subset of the table you could use indexed views or
vertically partition the table in the publication wizard. I'll test this,
but as far as I recall the colid is used to determine the max article size,
and as this might still be > 255 the issues may well remain.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||When you are creating a merge/transactional publication, the wizard
(through advanced options) allows you to specify column filters, if I
only tick upto 255 will that get round it? Its for reporting so we dont
need all the data anyway.
Rob
Paul Ibison wrote:
> Sorry - just read Raymond's answer and realised I had misinterpreted the
> question. To replicate a subset of the table you could use indexed views or
> vertically partition the table in the publication wizard. I'll test this,
> but as far as I recall the colid is used to determine the max article size,
> and as this might still be > 255 the issues may well remain.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||As far as I recall, this will work only if you haven't already deleted some
columns in the table before the 255 you select, assuming these are the
initial 255 columns in the table ie the colid must always be below 255. I'll
verify this but have a big workload at the moment.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
column filters option when setting replication up to get around this?
You could upgrade, for a out-of-the box support. Otherwise what people tend
to do is partition the table and replicate the partitions, related by a
PK-PK join. You could present the tables as the original one to the users by
using a view and an instead-of trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Rob,
It may also be possible to publish an indexed view containing a subset of
the columns as a logbased article (replicate indexed view as table...) if
you can take the performance hit of having the indexed view hanging around
at the publisher.
-Raymond
<robert@.orisoft.co.uk> wrote in message
news:1161004229.423453.155350@.i42g2000cwa.googlegr oups.com...
> Is there a workaround for this limitation? I.E can I just use the
> column filters option when setting replication up to get around this?
>
|||Sorry - just read Raymond's answer and realised I had misinterpreted the
question. To replicate a subset of the table you could use indexed views or
vertically partition the table in the publication wizard. I'll test this,
but as far as I recall the colid is used to determine the max article size,
and as this might still be > 255 the issues may well remain.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||When you are creating a merge/transactional publication, the wizard
(through advanced options) allows you to specify column filters, if I
only tick upto 255 will that get round it? Its for reporting so we dont
need all the data anyway.
Rob
Paul Ibison wrote:
> Sorry - just read Raymond's answer and realised I had misinterpreted the
> question. To replicate a subset of the table you could use indexed views or
> vertically partition the table in the publication wizard. I'll test this,
> but as far as I recall the colid is used to determine the max article size,
> and as this might still be > 255 the issues may well remain.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||As far as I recall, this will work only if you haven't already deleted some
columns in the table before the 255 you select, assuming these are the
initial 255 columns in the table ie the colid must always be below 255. I'll
verify this but have a big workload at the moment.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Friday, March 9, 2012
Replication - Identity Value problem
Hi,
I have a table with a tinyint (identity column). The current value in the
table is 2. And if I check with IDENT_CURRENT the next value is displayed as
3.
This table is an article in a merge replication. In EM if I check this
article the next value is shown as 127. And this is leading to problems in
replication. How can I get the 127 to something more manageable (say 3)?
Thank you.
Regards,
Karthik
You would be best not messing with this. However use DBCC
CheckIdent('tablename',reseed,3)
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
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:ABEDD870-6C5F-473C-9B15-74594B434C29@.microsoft.com...
> Hi,
> I have a table with a tinyint (identity column). The current value in the
> table is 2. And if I check with IDENT_CURRENT the next value is displayed
as
> 3.
> This table is an article in a merge replication. In EM if I check this
> article the next value is shown as 127. And this is leading to problems in
> replication. How can I get the 127 to something more manageable (say 3)?
> Thank you.
> Regards,
> Karthik
|||Hilary Cotter wrote:
> You would be best not messing with this. However use DBCC
> CheckIdent('tablename',reseed,3)
>
I've noticed the same thing -- on an INT identity, the value suddenly
(after 3 inserts) jumps to about 1.5 billion -- 1/2 way through the
range, it seems. Is this a bug, or a "feature"?
I have a table with a tinyint (identity column). The current value in the
table is 2. And if I check with IDENT_CURRENT the next value is displayed as
3.
This table is an article in a merge replication. In EM if I check this
article the next value is shown as 127. And this is leading to problems in
replication. How can I get the 127 to something more manageable (say 3)?
Thank you.
Regards,
Karthik
You would be best not messing with this. However use DBCC
CheckIdent('tablename',reseed,3)
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
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:ABEDD870-6C5F-473C-9B15-74594B434C29@.microsoft.com...
> Hi,
> I have a table with a tinyint (identity column). The current value in the
> table is 2. And if I check with IDENT_CURRENT the next value is displayed
as
> 3.
> This table is an article in a merge replication. In EM if I check this
> article the next value is shown as 127. And this is leading to problems in
> replication. How can I get the 127 to something more manageable (say 3)?
> Thank you.
> Regards,
> Karthik
|||Hilary Cotter wrote:
> You would be best not messing with this. However use DBCC
> CheckIdent('tablename',reseed,3)
>
I've noticed the same thing -- on an INT identity, the value suddenly
(after 3 inserts) jumps to about 1.5 billion -- 1/2 way through the
range, it seems. Is this a bug, or a "feature"?
Wednesday, March 7, 2012
Replication - Alter Column Data Type (How)
I've looked everywhere on this one and not found one jot of info
All I wanna do is alter a Char(40) Column to a Char(80)
SQL 2k sp3 - Merge Replication
Can anyone tell me how to do this without having to Halt Production in a Live environment ?
Looks like either a Drop Subscription
or
Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.
Both methods involve taking the system offline even though it's not for long.
This Column has no Constraints or anything
If this type of Schema Change is possible then this Column is the simplest.
Any help appreciated
thx
GW
PS. I hope someone posts on this one - I don't seem to have much response with Replication Q'sIn general you can use sp_repladdcolumn sp_repldropcolumn to add or drop columns to a previously replicated table. You can also modify this via the UI on the article properties page. In some cases, other schema changes must include to reinitialize the publication.
As its a production database and you're looking for major change in column size then obviously you need to have outage to make sure it affected without any issues. (suggested)
In any case you have to define the outage if not do it in less traffic hour.
The other option of adding temp. column and export data and again re-define the alterations on previous columns is fine though it will not cause outage..|||thx Satya
did'nt quite get what U meant by
In general you can use sp_repladdcolumn sp_repldropcolumn to add or drop columns to a previously replicated table. You can also modify this via the UI on the article properties page
Is this the same as adding - dropping - recreating the field as in my second option ?
Anyway I'll try
Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.
I'll post back if it fails
thx
GW|||Yes, after a thorough look up I suggest to use sp_repladdcolumn sp_repldropcolumn SPs as its a schema change.|||I used Enterprise Manager (Publication Properties) GUI to achieve same thing (sp_repladdcolumn etc) cos it's quicker & I'm Idle
It would of worked as well if it was'nt for the fact that the statement
Update my_Table SET NewCol = OldColumn
Left me with different Values in some of the records in the NewCol
dunno Why - we are using some kind of Binary Data in each Char Value.
The Extended Charachter set that displays with a select certainly looks different in about 5% of the records
O well - Onwards Forever Onwards
GW|||Glad it worked, but always remember to use SPs and functions provided which will give you a chance to get to know the system functionality rather depending on EM, it helps.
All I wanna do is alter a Char(40) Column to a Char(80)
SQL 2k sp3 - Merge Replication
Can anyone tell me how to do this without having to Halt Production in a Live environment ?
Looks like either a Drop Subscription
or
Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.
Both methods involve taking the system offline even though it's not for long.
This Column has no Constraints or anything
If this type of Schema Change is possible then this Column is the simplest.
Any help appreciated
thx
GW
PS. I hope someone posts on this one - I don't seem to have much response with Replication Q'sIn general you can use sp_repladdcolumn sp_repldropcolumn to add or drop columns to a previously replicated table. You can also modify this via the UI on the article properties page. In some cases, other schema changes must include to reinitialize the publication.
As its a production database and you're looking for major change in column size then obviously you need to have outage to make sure it affected without any issues. (suggested)
In any case you have to define the outage if not do it in less traffic hour.
The other option of adding temp. column and export data and again re-define the alterations on previous columns is fine though it will not cause outage..|||thx Satya
did'nt quite get what U meant by
In general you can use sp_repladdcolumn sp_repldropcolumn to add or drop columns to a previously replicated table. You can also modify this via the UI on the article properties page
Is this the same as adding - dropping - recreating the field as in my second option ?
Anyway I'll try
Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.
I'll post back if it fails
thx
GW|||Yes, after a thorough look up I suggest to use sp_repladdcolumn sp_repldropcolumn SPs as its a schema change.|||I used Enterprise Manager (Publication Properties) GUI to achieve same thing (sp_repladdcolumn etc) cos it's quicker & I'm Idle
It would of worked as well if it was'nt for the fact that the statement
Update my_Table SET NewCol = OldColumn
Left me with different Values in some of the records in the NewCol
dunno Why - we are using some kind of Binary Data in each Char Value.
The Extended Charachter set that displays with a select certainly looks different in about 5% of the records
O well - Onwards Forever Onwards
GW|||Glad it worked, but always remember to use SPs and functions provided which will give you a chance to get to know the system functionality rather depending on EM, it helps.
replication
Using merge replication, we made some schema changes to the publishing
database. The changes included the addition of a 1 column to to different
tables, a modification to a view and the addition of 3 contraints. To do this
we created a new publication.
When I created the subscription for the changes and ran the sychronization,
everything except the views were copied over.
Any idea why?
Thanks,
Gracie
Gracie,
Have a look at the article properties of the view to check it is set to drop
if exists.
Also, have a look at the snapshot history to see if the view definition was
created, and the snapshot files themselves if they haven't yet been cleaned
up.
BTW, unless there was a specific reason for having another publication, this
isn't mandatory, and if the new tables are related to existing tables from
the other publication, you might be better adding them there, so as to keep
PK-FK changes together in one synchronization.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
database. The changes included the addition of a 1 column to to different
tables, a modification to a view and the addition of 3 contraints. To do this
we created a new publication.
When I created the subscription for the changes and ran the sychronization,
everything except the views were copied over.
Any idea why?
Thanks,
Gracie
Gracie,
Have a look at the article properties of the view to check it is set to drop
if exists.
Also, have a look at the snapshot history to see if the view definition was
created, and the snapshot files themselves if they haven't yet been cleaned
up.
BTW, unless there was a specific reason for having another publication, this
isn't mandatory, and if the new tables are related to existing tables from
the other publication, you might be better adding them there, so as to keep
PK-FK changes together in one synchronization.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)