Showing posts with label schemas. Show all posts
Showing posts with label schemas. Show all posts

Sunday, March 11, 2012

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.comHi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>|||Bob, Thanks for all your responses!
Neal

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.com
Hi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>
|||Bob, Thanks for all your responses!
Neal

Thursday, March 8, 2012

Changing Remote Schemas

Running Win 2K & SQL 2K
I have 15 remote locations that all have transactional
publications that are pushed to my corp. office. I need
to add a field to one of the tables that are replicated
to the corp. office at all 15 locations. So my question
is this...
What is the easiest way of accomplishing this? I know I
can add a field to a published table and the change in
schema will be replicated to the subscriber, but what
about when there are 15 publishers and only one
subscriber? How will SQL handle this?
Any help is appreciated.
Larry...
for one publisher to many susbcribers you use sp_repladdcolumn or sp_repldropcolumn.
however you have 15 publishers replicating to a single subscriber. You have probably done a no-sync subscription as well.
I suggest the best approach would be to drop the 14 or the 15 subscriptions. Make the changes on one Publisher, when the Susbcriber has received the change and the smoke settles, dust clears, make the change on the remaining 14 subscribers, and then resyn
c again using the nosync option.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html