Hi,
We are merge replication more than 20 databases . Currently we added
one new column to a table so it will be added only in last, and now only in
this table the rowguid is not the last column.
I want to know because of this is there is any problem in replication.
Please give me a solution as soon as possible
thanks,
Soura
Soura,
the rowguid column doesn't need t be the last column, and sp_repladdcolumn
is all you need to use in this circumstance.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label rowguid. Show all posts
Showing posts with label rowguid. Show all posts
Wednesday, March 28, 2012
Rowguid Column at the end of my Tables
Hello,
There is a "rowguid" column that was added to the end of my tables, and I
have no clue when and why it was added. Actually i had a Merge Replication on
this database and i removed it. I don't know if it was generated by SQL
Server for Merge Replication or something else.
Ant idea ???
Thanks so much
Mathew
Matthew,
this column is essential (actually a column with the rowguid property) to
merge replication. Removing replication will not remove these columns -
you'll need to do it by hand or craft a script. Be sure however that the
developers haven't coded to include this column.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks so much for your helps. I know how to remove this column either on
the Enterprise Manager or by scripts using sysobject to get the table name
and remove this column, but is there any easier ways or scripts that you know?
I appreciate so much.
Mathew
"Paul Ibison" wrote:
> Matthew,
> this column is essential (actually a column with the rowguid property) to
> merge replication. Removing replication will not remove these columns -
> you'll need to do it by hand or craft a script. Be sure however that the
> developers haven't coded to include this column.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
There is a "rowguid" column that was added to the end of my tables, and I
have no clue when and why it was added. Actually i had a Merge Replication on
this database and i removed it. I don't know if it was generated by SQL
Server for Merge Replication or something else.
Ant idea ???
Thanks so much
Mathew
Matthew,
this column is essential (actually a column with the rowguid property) to
merge replication. Removing replication will not remove these columns -
you'll need to do it by hand or craft a script. Be sure however that the
developers haven't coded to include this column.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks so much for your helps. I know how to remove this column either on
the Enterprise Manager or by scripts using sysobject to get the table name
and remove this column, but is there any easier ways or scripts that you know?
I appreciate so much.
Mathew
"Paul Ibison" wrote:
> Matthew,
> this column is essential (actually a column with the rowguid property) to
> merge replication. Removing replication will not remove these columns -
> you'll need to do it by hand or craft a script. Be sure however that the
> developers haven't coded to include this column.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
rowguid as Primary Key ?
If you are using replication and need a rowguid, and you need a unique
primary key for the table anyway, is there anything wrong with using the
unique rowquid for the Primary Key? If not, what are the downfalls?
Thanks,
Steve
Using rowguids for a pk is a bad choice.
Please consult http://www.aspfaq.com/show.asp?id=2504
However you can use the rowguid column as your primary key and have merge
replication use it.
Requirements are
1) call the column rowguid
2) don't use a pk, but use a unique index - really they are the same thing,
only the unique index allows a single null value.
3) give the rowguid column the ROWGUIDCOL attribute
4) give the rowguid column a newid() default
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
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:E926325C-2B49-41C9-A7EC-71CD6A373C50@.microsoft.com...
> If you are using replication and need a rowguid, and you need a unique
> primary key for the table anyway, is there anything wrong with using the
> unique rowquid for the Primary Key? If not, what are the downfalls?
> Thanks,
> Steve
primary key for the table anyway, is there anything wrong with using the
unique rowquid for the Primary Key? If not, what are the downfalls?
Thanks,
Steve
Using rowguids for a pk is a bad choice.
Please consult http://www.aspfaq.com/show.asp?id=2504
However you can use the rowguid column as your primary key and have merge
replication use it.
Requirements are
1) call the column rowguid
2) don't use a pk, but use a unique index - really they are the same thing,
only the unique index allows a single null value.
3) give the rowguid column the ROWGUIDCOL attribute
4) give the rowguid column a newid() default
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
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:E926325C-2B49-41C9-A7EC-71CD6A373C50@.microsoft.com...
> If you are using replication and need a rowguid, and you need a unique
> primary key for the table anyway, is there anything wrong with using the
> unique rowquid for the Primary Key? If not, what are the downfalls?
> Thanks,
> Steve
RowGuid as primary key
I'm developing on SQL Server 2005 and our application needs to be
'replicable'. I can see how this is done using a RowGuid and marking to be
used for replication, no problem.
My question, however, is if I should make this rowguid column the primary
key in the tables in our database. Will it make any difference on the speed
of the indexes or anything? I would think that since we've got this RowGuid
there, and it's unique, we might as well kill two birds with one stone...
Any comments on this?
If you are using transactional replication the key should be based on the
int data type, preferably using the identity property. If you are using
merge replication let it create a rowguid column along with the unique index
it will also create.
"Brian" <noone@.discussions.microsoft.com> wrote in message
news:8CD86631-6C4D-416C-8831-2010DF1F487F@.microsoft.com...
> I'm developing on SQL Server 2005 and our application needs to be
> 'replicable'. I can see how this is done using a RowGuid and marking to be
> used for replication, no problem.
> My question, however, is if I should make this rowguid column the primary
> key in the tables in our database. Will it make any difference on the
> speed
> of the indexes or anything? I would think that since we've got this
> RowGuid
> there, and it's unique, we might as well kill two birds with one stone...
> Any comments on this?
|||I'd not recommend this. Have a look on the programming newsgroup for a list
of reasons why this is considered a bad idea
My main reason would be space - it occupies 16 bytes, whereas an integer
used as a surrogate key (+/- 2 billion or so) occupies 4 bytes. Therefore the
index will be narrower and faster to search.
Rgds,
Paul Ibison
|||Or you might want to look here -
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:9FADF7A1-C019-4327-8D29-B7B8562AE335@.microsoft.com...
> I'd not recommend this. Have a look on the programming newsgroup for a
> list
> of reasons why this is considered a bad idea
> My main reason would be space - it occupies 16 bytes, whereas an integer
> used as a surrogate key (+/- 2 billion or so) occupies 4 bytes. Therefore
> the
> index will be narrower and faster to search.
> Rgds,
> Paul Ibison
|||Yes, normally I wouldn't consider it, but for replication it has to be there
anyways. So if it HAS to be there, is it bad to use it for the primary key
also?
|||You could add your own guid with the rowguid property and set it as the OK
but there would still be the issue that the index would be a lot wider than
an int index, and hence slower.
Cheers,
Paul Ibison
|||Thanks Paul. It sounds like having an int primary key along with the RowGuid
for replication is the way to go. I've been pondering and doing web searches
for how SQL Server maintains the int between machines with replication. I
haven't really found any problems with this so I'm assuming that SQL server
completely takes care of this for us.
Brian
'replicable'. I can see how this is done using a RowGuid and marking to be
used for replication, no problem.
My question, however, is if I should make this rowguid column the primary
key in the tables in our database. Will it make any difference on the speed
of the indexes or anything? I would think that since we've got this RowGuid
there, and it's unique, we might as well kill two birds with one stone...
Any comments on this?
If you are using transactional replication the key should be based on the
int data type, preferably using the identity property. If you are using
merge replication let it create a rowguid column along with the unique index
it will also create.
"Brian" <noone@.discussions.microsoft.com> wrote in message
news:8CD86631-6C4D-416C-8831-2010DF1F487F@.microsoft.com...
> I'm developing on SQL Server 2005 and our application needs to be
> 'replicable'. I can see how this is done using a RowGuid and marking to be
> used for replication, no problem.
> My question, however, is if I should make this rowguid column the primary
> key in the tables in our database. Will it make any difference on the
> speed
> of the indexes or anything? I would think that since we've got this
> RowGuid
> there, and it's unique, we might as well kill two birds with one stone...
> Any comments on this?
|||I'd not recommend this. Have a look on the programming newsgroup for a list
of reasons why this is considered a bad idea
My main reason would be space - it occupies 16 bytes, whereas an integer
used as a surrogate key (+/- 2 billion or so) occupies 4 bytes. Therefore the
index will be narrower and faster to search.
Rgds,
Paul Ibison
|||Or you might want to look here -
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:9FADF7A1-C019-4327-8D29-B7B8562AE335@.microsoft.com...
> I'd not recommend this. Have a look on the programming newsgroup for a
> list
> of reasons why this is considered a bad idea
> My main reason would be space - it occupies 16 bytes, whereas an integer
> used as a surrogate key (+/- 2 billion or so) occupies 4 bytes. Therefore
> the
> index will be narrower and faster to search.
> Rgds,
> Paul Ibison
|||Yes, normally I wouldn't consider it, but for replication it has to be there
anyways. So if it HAS to be there, is it bad to use it for the primary key
also?
|||You could add your own guid with the rowguid property and set it as the OK
but there would still be the issue that the index would be a lot wider than
an int index, and hence slower.
Cheers,
Paul Ibison
|||Thanks Paul. It sounds like having an int primary key along with the RowGuid
for replication is the way to go. I've been pondering and doing web searches
for how SQL Server maintains the int between machines with replication. I
haven't really found any problems with this so I'm assuming that SQL server
completely takes care of this for us.
Brian
rowguid ?
Hi,
I tried to make a replication between two databases, a merge
replication...
But at the end, when the replication agents started, I found lots of errors
that tells me
that it don't knows who is the column 'rowguid'. This column was added only
on the publisher.
Can somebosy tell me what is wrong? what is the right solution?
Thank you in advance,
Alex
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
".:alex:." <alex_77ro@.yahoo.com> wrote in message
news:Of2SSEjgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I tried to make a replication between two databases, a merge
> replication...
> But at the end, when the replication agents started, I found lots of
errors
> that tells me
> that it don't knows who is the column 'rowguid'. This column was added
only
> on the publisher.
> Can somebosy tell me what is wrong? what is the right solution?
> Thank you in advance,
> Alex
>
>
|||your's book title is:
"A Guide to SQL Server 2000 Transactional and Snapshot Replication"
but I need info about merge replication. In fact a little help, I no need to
learn the replication mechanism , etc... because isn't my job. I need a
little info
like I found here:
http://www.databasejournal.com/featu...le.php/1438231
Thank you for your attention,
Alex
PS: I'm mechanical engineering too ;)
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OFeQ5WjgFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
> --
> 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
> ".:alex:." <alex_77ro@.yahoo.com> wrote in message
> news:Of2SSEjgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> errors
> only
>
|||Can you post the error number, and possibly the exact message.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OFeQ5WjgFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
> --
> 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
> ".:alex:." <alex_77ro@.yahoo.com> wrote in message
> news:Of2SSEjgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> errors
> only
>
|||I don't know a number or the exact message, because I disabled the
publisher and I cannot reproduce the scenario from my station (in this case
the test computer) because I cannot start de SQLServerAgent.
I will research ...
Thank you anyway
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OMiaskjgFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Can you post the error number, and possibly the exact message.
> --
> 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
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OFeQ5WjgFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
sql
I tried to make a replication between two databases, a merge
replication...
But at the end, when the replication agents started, I found lots of errors
that tells me
that it don't knows who is the column 'rowguid'. This column was added only
on the publisher.
Can somebosy tell me what is wrong? what is the right solution?
Thank you in advance,
Alex
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
".:alex:." <alex_77ro@.yahoo.com> wrote in message
news:Of2SSEjgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I tried to make a replication between two databases, a merge
> replication...
> But at the end, when the replication agents started, I found lots of
errors
> that tells me
> that it don't knows who is the column 'rowguid'. This column was added
only
> on the publisher.
> Can somebosy tell me what is wrong? what is the right solution?
> Thank you in advance,
> Alex
>
>
|||your's book title is:
"A Guide to SQL Server 2000 Transactional and Snapshot Replication"
but I need info about merge replication. In fact a little help, I no need to
learn the replication mechanism , etc... because isn't my job. I need a
little info
like I found here:
http://www.databasejournal.com/featu...le.php/1438231
Thank you for your attention,
Alex
PS: I'm mechanical engineering too ;)
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OFeQ5WjgFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
> --
> 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
> ".:alex:." <alex_77ro@.yahoo.com> wrote in message
> news:Of2SSEjgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> errors
> only
>
|||Can you post the error number, and possibly the exact message.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OFeQ5WjgFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
> --
> 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
> ".:alex:." <alex_77ro@.yahoo.com> wrote in message
> news:Of2SSEjgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> errors
> only
>
|||I don't know a number or the exact message, because I disabled the
publisher and I cannot reproduce the scenario from my station (in this case
the test computer) because I cannot start de SQLServerAgent.
I will research ...
Thank you anyway
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OMiaskjgFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Can you post the error number, and possibly the exact message.
> --
> 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
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OFeQ5WjgFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
sql
Rowguid - index in system table
Hi,
I am dealing with merge replication.
The missing statistics event points out [MSmerge_tombstone].[rowguid] in all
our databases. Noticed that no specific index for rowguid created in
[MSmerge_tombstone] table specific on this column.
We would like to know why there is no index created on this column.Do we
need to manually create this index?
Does this missing statistics is an indication of potential problem. If so
what could be the corrective action(s) ?
Thanks,
Soura
indexes are only used if the table is greater than 100 pages. This means
that if more than 1500 rows exist in your tombstone table it would benefit
for queries which are done on the rowguid column alone.
The most significant column in the queries is the generation column and also
on the tablenick and rowguid columns. There are indexes on these columns.
Which queries/procs are complaining about the missing statistics? Does
autocreate and auto update statistics help with the performance of this
database?
Also note that by default the metadata tables are cleaned up each time the
merge agent runs so they should be small.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:16760881-928B-4137-A542-D430C97ACC78@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> The missing statistics event points out [MSmerge_tombstone].[rowguid] in
> all
> our databases. Noticed that no specific index for rowguid created in
> [MSmerge_tombstone] table specific on this column.
> We would like to know why there is no index created on this column.Do we
> need to manually create this index?
> Does this missing statistics is an indication of potential problem. If so
> what could be the corrective action(s) ?
> Thanks,
> Soura
I am dealing with merge replication.
The missing statistics event points out [MSmerge_tombstone].[rowguid] in all
our databases. Noticed that no specific index for rowguid created in
[MSmerge_tombstone] table specific on this column.
We would like to know why there is no index created on this column.Do we
need to manually create this index?
Does this missing statistics is an indication of potential problem. If so
what could be the corrective action(s) ?
Thanks,
Soura
indexes are only used if the table is greater than 100 pages. This means
that if more than 1500 rows exist in your tombstone table it would benefit
for queries which are done on the rowguid column alone.
The most significant column in the queries is the generation column and also
on the tablenick and rowguid columns. There are indexes on these columns.
Which queries/procs are complaining about the missing statistics? Does
autocreate and auto update statistics help with the performance of this
database?
Also note that by default the metadata tables are cleaned up each time the
merge agent runs so they should be small.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:16760881-928B-4137-A542-D430C97ACC78@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> The missing statistics event points out [MSmerge_tombstone].[rowguid] in
> all
> our databases. Noticed that no specific index for rowguid created in
> [MSmerge_tombstone] table specific on this column.
> We would like to know why there is no index created on this column.Do we
> need to manually create this index?
> Does this missing statistics is an indication of potential problem. If so
> what could be the corrective action(s) ?
> Thanks,
> Soura
rowguid
Hello,
When I use the Replication and the Publication mechanism - why is created
for every table which I want to synchronize an additional column with the
name rowguid? I have already set a primary key for all tables which I want to
synchronize.
Regards
mat
the rowguid is used to track which row has been modified in a database. By
examining the guid the merge agent knows what table it belongs too, and can
track its change history.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"mathon" <mathon@.discussions.microsoft.com> wrote in message
news:2D7CC7C9-8925-4BED-8DCB-9DF24CE88512@.microsoft.com...
> Hello,
> When I use the Replication and the Publication mechanism - why is created
> for every table which I want to synchronize an additional column with the
> name rowguid? I have already set a primary key for all tables which I want
> to
> synchronize.
> Regards
> mat
When I use the Replication and the Publication mechanism - why is created
for every table which I want to synchronize an additional column with the
name rowguid? I have already set a primary key for all tables which I want to
synchronize.
Regards
mat
the rowguid is used to track which row has been modified in a database. By
examining the guid the merge agent knows what table it belongs too, and can
track its change history.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"mathon" <mathon@.discussions.microsoft.com> wrote in message
news:2D7CC7C9-8925-4BED-8DCB-9DF24CE88512@.microsoft.com...
> Hello,
> When I use the Replication and the Publication mechanism - why is created
> for every table which I want to synchronize an additional column with the
> name rowguid? I have already set a primary key for all tables which I want
> to
> synchronize.
> Regards
> mat
rowguid
After replication done, both source database and subscription database have
a extra column--rowguid.
SQL server did not clean them up.
Should I clean then one table by one table manually or there is a was to
clean them up.
Thanks
After replication done, both source database and subscription database have
a extra column--rowguid.
SQL server did not clean them up.
I delete the publication. but the column rowguid still there
Do I have to clean them one table by one table manually or there is a way
to
clean them up.
Thanks
|||This is quite normal behaviour. It may be that a (DBA) user's application
relies on these columns in some way, so it is left up to the user to remove
them. There are no inbuilt stored procs to remove these columns, but
creating yourself a script shouldn't be too difficult - syscolumns and
sysobjects are the tables you'll need. If it's just a cast of a couple of
tables I'd do it manually, but if not or if you want more of a challenge,
have a go at the script and post back if you can't do it and I'll create one
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have hundreds tables.
It will appreciate very much if you can post a sample script for this.
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ug15GiVvFHA.720@.TK2MSFTNGP15.phx.gbl...
> This is quite normal behaviour. It may be that a (DBA) user's application
> relies on these columns in some way, so it is left up to the user to
remove
> them. There are no inbuilt stored procs to remove these columns, but
> creating yourself a script shouldn't be too difficult - syscolumns and
> sysobjects are the tables you'll need. If it's just a cast of a couple of
> tables I'd do it manually, but if not or if you want more of a challenge,
> have a go at the script and post back if you can't do it and I'll create
one
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Chen,
I'll post it up tomorrow - what type of replication are you using?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks.
I did merge replication and I deleted the publication days ago.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGxuc$VvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Chen,
> I'll post it up tomorrow - what type of replication are you using?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>
|||Chen,
run each of these in turn and then run the output (backup your database
first)
-- drop rowguid indexes
select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
where objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
64)=0
and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
order by sysindexes.indid
-- remove rowguid default constraints
select 'alter table ' + b.name + ' drop constraint ' + a.name from
sysobjects a
inner join syscolumns on syscolumns.id = a.parent_obj
inner join sysobjects b on syscolumns.id = b.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(b.name),'IsMSShipped') = 0
and a.xtype = 'D'
-- remove rowguid columns
select 'alter table ' + sysobjects.name + ' drop column ''rowguid'' ' from
syscolumns
inner join sysobjects on syscolumns.id = sysobjects.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks
Chen
"Paul Ibison" wrote:
> Chen,
> run each of these in turn and then run the output (backup your database
> first)
> -- drop rowguid indexes
> select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
> sysindexes
> inner join sysobjects
> on sysindexes.id = sysobjects.id
> where objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
> and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
> 64)=0
> and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
> order by sysindexes.indid
> -- remove rowguid default constraints
> select 'alter table ' + b.name + ' drop constraint ' + a.name from
> sysobjects a
> inner join syscolumns on syscolumns.id = a.parent_obj
> inner join sysobjects b on syscolumns.id = b.id
> where syscolumns.name = 'rowguid'
> and objectproperty(object_id(b.name),'IsMSShipped') = 0
> and a.xtype = 'D'
> -- remove rowguid columns
> select 'alter table ' + sysobjects.name + ' drop column ''rowguid'' ' from
> syscolumns
> inner join sysobjects on syscolumns.id = sysobjects.id
> where syscolumns.name = 'rowguid'
> and objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
a extra column--rowguid.
SQL server did not clean them up.
Should I clean then one table by one table manually or there is a was to
clean them up.
Thanks
After replication done, both source database and subscription database have
a extra column--rowguid.
SQL server did not clean them up.
I delete the publication. but the column rowguid still there
Do I have to clean them one table by one table manually or there is a way
to
clean them up.
Thanks
|||This is quite normal behaviour. It may be that a (DBA) user's application
relies on these columns in some way, so it is left up to the user to remove
them. There are no inbuilt stored procs to remove these columns, but
creating yourself a script shouldn't be too difficult - syscolumns and
sysobjects are the tables you'll need. If it's just a cast of a couple of
tables I'd do it manually, but if not or if you want more of a challenge,
have a go at the script and post back if you can't do it and I'll create one
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have hundreds tables.
It will appreciate very much if you can post a sample script for this.
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ug15GiVvFHA.720@.TK2MSFTNGP15.phx.gbl...
> This is quite normal behaviour. It may be that a (DBA) user's application
> relies on these columns in some way, so it is left up to the user to
remove
> them. There are no inbuilt stored procs to remove these columns, but
> creating yourself a script shouldn't be too difficult - syscolumns and
> sysobjects are the tables you'll need. If it's just a cast of a couple of
> tables I'd do it manually, but if not or if you want more of a challenge,
> have a go at the script and post back if you can't do it and I'll create
one
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Chen,
I'll post it up tomorrow - what type of replication are you using?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks.
I did merge replication and I deleted the publication days ago.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGxuc$VvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Chen,
> I'll post it up tomorrow - what type of replication are you using?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>
|||Chen,
run each of these in turn and then run the output (backup your database
first
)-- drop rowguid indexes
select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
where objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
64)=0
and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
order by sysindexes.indid
-- remove rowguid default constraints
select 'alter table ' + b.name + ' drop constraint ' + a.name from
sysobjects a
inner join syscolumns on syscolumns.id = a.parent_obj
inner join sysobjects b on syscolumns.id = b.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(b.name),'IsMSShipped') = 0
and a.xtype = 'D'
-- remove rowguid columns
select 'alter table ' + sysobjects.name + ' drop column ''rowguid'' ' from
syscolumns
inner join sysobjects on syscolumns.id = sysobjects.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks
Chen
"Paul Ibison" wrote:
> Chen,
> run each of these in turn and then run the output (backup your database
> first
)> -- drop rowguid indexes
> select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
> sysindexes
> inner join sysobjects
> on sysindexes.id = sysobjects.id
> where objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
> and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
> 64)=0
> and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
> order by sysindexes.indid
> -- remove rowguid default constraints
> select 'alter table ' + b.name + ' drop constraint ' + a.name from
> sysobjects a
> inner join syscolumns on syscolumns.id = a.parent_obj
> inner join sysobjects b on syscolumns.id = b.id
> where syscolumns.name = 'rowguid'
> and objectproperty(object_id(b.name),'IsMSShipped') = 0
> and a.xtype = 'D'
> -- remove rowguid columns
> select 'alter table ' + sysobjects.name + ' drop column ''rowguid'' ' from
> syscolumns
> inner join sysobjects on syscolumns.id = sysobjects.id
> where syscolumns.name = 'rowguid'
> and objectproperty(object_id(sysobjects.name),'IsMSShi pped') = 0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Rowguid
Rowguid column ( globally unique identifier) was added to all tables in the
database. What may cause that?
Thanks for help in advance."Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve|||I inabled replication and it hosed a production db. We are still having tro
uble updating. I disabled and dropped the distribution DB. We then found a
new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_C
ommon_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls"
. I ran a script and the rowguid is no longer associated with the CONTACT2
table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having t
rouble updating. I disabled and dropped the distribution DB. We then found
a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine
_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow null
s". I ran a script and the rowguid is no longer associated with the CONTACT
2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine s
upports Post Alerts, Ratings, and Searching.
>
database. What may cause that?
Thanks for help in advance."Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve|||I inabled replication and it hosed a production db. We are still having tro
uble updating. I disabled and dropped the distribution DB. We then found a
new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_C
ommon_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls"
. I ran a script and the rowguid is no longer associated with the CONTACT2
table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having t
rouble updating. I disabled and dropped the distribution DB. We then found
a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine
_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow null
s". I ran a script and the rowguid is no longer associated with the CONTACT
2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine s
upports Post Alerts, Ratings, and Searching.
>
RowGuid
Hi
What impact is there in setting a unqiueidentifier primary key to be a row guid?
How does this impact performance?
How does this impact data file size?
Does it impact anything else?
Thanks
Hi,I guess you don′t want to open up taht religious discussion, right ? There were many, many, many discussions already about this out there which can be find googling / MSNning around:
http://www.google.de/search?hl=de&q=GUID+Primary+Key&meta=
There are really many opinions out there, for me (for my personal opinion) you should avoid them, as long as you don′t really need them, like for distributed applications)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Hi Jens
Thanks for the response but I wasn't asking if I should use a UNIQUEIDENTIFIER as a primary key.
I was just wondering what the impact was in setting the rowguid flag.
For example...
CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)
CREATE TABLE Schema.Table (Id UNIQUEIDENTIFIER NOT NULL CONSTRAINT PTableId PRIMARY KEY NONCLUSTERED WITH FILLFACTOR=60)
sqlRowguid
Rowguid column ( globally unique identifier) was added to all tables in the
database. What may cause that?
Thanks for help in advance.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve
|||I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>
database. What may cause that?
Thanks for help in advance.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve
|||I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>
Subscribe to:
Posts (Atom)