Question about schema collection

Hi,
I've created a schema collection with the following statement:
/****** Object: XmlSchemaCollection [dbo].[IncVarTypeTestCollection]
Script Date: 12/04/2007 09:01:56 ******/
CREATE XML SCHEMA COLLECTION [dbo].[IncVarTypeTestCollection] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" />
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
This works fine except that it won't allow Nulls in the numeric fields.
I tried to add the minoccurs="0" to see whether that would work but get an
error message saying that it is not valid in this schema context.
Can anyone help?
JS> I tried to add the minoccurs="0" to see whether that would work but
JS> get an error message saying that it is not valid in this schema
JS> context.
I find that Nillable is easier to work with. Consider:
use scratch
go
create xml schema collection foo
as '<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" nillable="true"/>
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" nillable="true"/>
<xsd:element name="vInteger" type="xsd:integer" nillable="true"/>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
go
declare @.don xml(foo)
declare @.t1 table(vDateTime dateTime,
vSmallInt smallint,
vVarChar varchar(20),
vInteger int)
insert into @.t1 values (getdate(),null,null,null)
select @.don=(
select convert(nvarchar(50),vDateTime,127)+'Z' as vDateTime,vSmallInt,vVarChar,vInteger
from @.t1 for xml path(''),root('inc'),elements xsinil,type)
select @.don
go
drop xml schema collection foo
go
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi,
tried that but when I run the following:
insert into incVariables
(IDField, XMLField)
values
(Null,'<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vSmallInt></vSmallInt>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
I get the following error:
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ''. Location:
/*:inc[1]/*:vSmallInt[1]
|||You are trying to have your cake and eat it too.
If you want to represent a null value, you have two choices: represent it
by an element marked as nil (which is what I do, nice and explicit) OR omit
the element for the document (which is what SQL Server normally does).
What you have in your example of vSmallInt isn't a null value as XML represents
them: its an empty element. XML distinguishes between empty elements and
elements marked nil for exactly this purpose.
And you can't emit an empty element and have minOccurs=0. Since you have
an element, it occurs. Since it occurs, it must comply with the xsd:shortInt
spec.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Ken,
Thanks for your time and patience.
What I'm trying to do is convert and existing table with about a million
records into a table that contains an ID field and a XML field.
The existing table contains a large number of fields, many of which are
rarely used or if one is used, another isn't. If that makes sense.
The bits I posted with my question are from a test where I set up one field
of each type currently used to try to work out exactly how I treat each type
when writing the conversion routine.
Because I'm going to create the routine, I don't mind whether the field is
translated into an empty element or left out completely as long as I can get
the data for that record to insert itself.
Following on from your latest post, I tried leaving out the element
completely whilst leaving the XML Schema element set to nilable="True" and I
still can't get it to run, so I must be doing something worng and therefore
it's fairly obvious that I don't have a clue what I'm doing.
Any more help you could give would be appreciated otherwise it looks like
we'll have to find someone from outside to come and set things up.
Thanks
|||Hi,
I reproduced your issue at my side. The empty element cannot be validated
in SQL Server typed xml field but can be validated in IE. Unfortunately I
have not found a clear explanation regarding why the empty element failed
the validation. Anyway I will try to consult the product team for the
confirmation and let you know the response as soon as possible. I also
recommend that you leave me (changliw_at_microsoft_dot_com) an email
response so that I can timely update you when I get the answer.
Look back to your issue, as a possible workaround, you may consider the
following two methods:
1. Add the attribute 'minOccurs="0"' to those elements which are allowed
empty. When you convert the records of your existing table to XML
statements, eliminate the related marks if the fields are NULL;
For example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
minOccurs="0">
If the original vSmallInt column is NULL, ensure that the generated XML
does not include vSmallInt mark:
<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>
2. You may assign each element a default value if the element is empty. For
example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
default="0"/>
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!
Best regards,
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.
================================================== ====
|||Charles, thanks for your reply.
I seem to have hit something strange. I tried to create the schema
collection including the minoccurs="0" and got an error message suggesting
that it is not relevent in that part of the script. I then deleted that bit
and tried it again and it worked. This seems to have happened several times.
Has this happened to anyone else?
Once I manage to get the schema collection created, the minoccurs="0" works
OK.
|||Hi,
I could not reproduce the issue regarding minOccurs. Is it stable now?
Unfortunately I have not been able to get the confirmation from the product
team. In this case I submitted a product issue request internally to the
product team. Also we recommend that you give Microsoft feedback via
https://connect.microsoft.com/sql. Your feedback will be routed to the
product team and if there is any response from the product team, you will
get an email notification.
Please feel free to let me know if you have any other questions or
concerns. It is always my pleasure to be of assistance.
Best regards,
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.
================================================== ====
|||Hi,
The current problem is that the insert fails if I include empty elements and
the XML from a SELECT ... FOR XML query seems to insist on putting them in
even if I don't ask for them.
I'm OK with everything else.
Thanks
JS
|||Hi,
What is the result if you run "SELECT * FROM [tablename] FOR XML AUTO"? I
performed a test at my side and it worked fine.
My test was based on the following xml schema and table:
//1. Create a xml schema
CREATE XML SCHEMA COLLECTION [dbo].foo1 AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" minOccurs="0"
nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
//2. Create a table
CREATE TABLE [dbo].[IncVariables](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XMLField] [xml](CONTENT [dbo].[foo1]) NOT NULL,
CONSTRAINT [PK_IncVariables] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
//3. Insert into a record
INSERT INTO IncVariables values ('<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
//4. Query on the table
SELECT * FROM IncVariables FOR XML AUTO
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of your assistance.
Best regards,
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.
================================================== ====

Question about schema collection

Hi,
I've created a schema collection with the following statement:
/****** Object: XmlSchemaCollection [dbo].[IncVarTypeTestCollection]
Script Date: 12/04/2007 09:01:56 ******/
CREATE XML SCHEMA COLLECTION [dbo].[IncVarTypeTestCollection] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" />
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
This works fine except that it won't allow Nulls in the numeric fields.
I tried to add the minoccurs="0" to see whether that would work but get an
error message saying that it is not valid in this schema context.
Can anyone help?JS> I tried to add the minoccurs="0" to see whether that would work but
JS> get an error message saying that it is not valid in this schema
JS> context.
I find that Nillable is easier to work with. Consider:
use scratch
go
create xml schema collection foo
as '<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" nillable="true"/>
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" nillable="true"/>
<xsd:element name="vInteger" type="xsd:integer" nillable="true"/>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
go
declare @.don xml(foo)
declare @.t1 table(vDateTime dateTime,
vSmallInt smallint,
vVarChar varchar(20),
vInteger int)
insert into @.t1 values (getdate(),null,null,null)
select @.don=(
select convert(nvarchar(50),vDateTime,127)+'Z' as vDateTime,vSmallInt,vVarCh
ar,vInteger
from @.t1 for xml path(''),root('inc'),elements xsinil,type)
select @.don
go
drop xml schema collection foo
go
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Hi,
tried that but when I run the following:
insert into incVariables
(IDField, XMLField)
values
(Null,'<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vSmallInt></vSmallInt>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
I get the following error:
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ''. Location:
/*:inc[1]/*:vSmallInt[1]|||You are trying to have your cake and eat it too.
If you want to represent a null value, you have two choices: represent it
by an element marked as nil (which is what I do, nice and explicit) OR omit
the element for the document (which is what SQL Server normally does).
What you have in your example of vSmallInt isn't a null value as XML represe
nts
them: its an empty element. XML distinguishes between empty elements and
elements marked nil for exactly this purpose.
And you can't emit an empty element and have minOccurs=0. Since you have
an element, it occurs. Since it occurs, it must comply with the xsd:shortInt
spec.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Hi Ken,
Thanks for your time and patience.
What I'm trying to do is convert and existing table with about a million
records into a table that contains an ID field and a XML field.
The existing table contains a large number of fields, many of which are
rarely used or if one is used, another isn't. If that makes sense.
The bits I posted with my question are from a test where I set up one field
of each type currently used to try to work out exactly how I treat each type
when writing the conversion routine.
Because I'm going to create the routine, I don't mind whether the field is
translated into an empty element or left out completely as long as I can get
the data for that record to insert itself.
Following on from your latest post, I tried leaving out the element
completely whilst leaving the XML Schema element set to nilable="True" and I
still can't get it to run, so I must be doing something worng and therefore
it's fairly obvious that I don't have a clue what I'm doing.
Any more help you could give would be appreciated otherwise it looks like
we'll have to find someone from outside to come and set things up.
Thanks|||Hi,
I reproduced your issue at my side. The empty element cannot be validated
in SQL Server typed xml field but can be validated in IE. Unfortunately I
have not found a clear explanation regarding why the empty element failed
the validation. Anyway I will try to consult the product team for the
confirmation and let you know the response as soon as possible. I also
recommend that you leave me (changliw_at_microsoft_dot_com) an email
response so that I can timely update you when I get the answer.
Look back to your issue, as a possible workaround, you may consider the
following two methods:
1. Add the attribute 'minOccurs="0"' to those elements which are allowed
empty. When you convert the records of your existing table to XML
statements, eliminate the related marks if the fields are NULL;
For example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
minOccurs="0">
If the original vSmallInt column is NULL, ensure that the generated XML
does not include vSmallInt mark:
<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>
2. You may assign each element a default value if the element is empty. For
example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
default="0"/>
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!
Best regards,
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.
========================================
==============|||Charles, thanks for your reply.
I seem to have hit something strange. I tried to create the schema
collection including the minoccurs="0" and got an error message suggesting
that it is not relevent in that part of the script. I then deleted that bit
and tried it again and it worked. This seems to have happened several times.
Has this happened to anyone else?
Once I manage to get the schema collection created, the minoccurs="0" works
OK.|||Hi,
I could not reproduce the issue regarding minOccurs. Is it stable now?
Unfortunately I have not been able to get the confirmation from the product
team. In this case I submitted a product issue request internally to the
product team. Also we recommend that you give Microsoft feedback via
https://connect.microsoft.com/sql. Your feedback will be routed to the
product team and if there is any response from the product team, you will
get an email notification.
Please feel free to let me know if you have any other questions or
concerns. It is always my pleasure to be of assistance.
Best regards,
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.
========================================
==============|||Hi,
The current problem is that the insert fails if I include empty elements and
the XML from a SELECT ... FOR XML query seems to insist on putting them in
even if I don't ask for them.
I'm OK with everything else.
Thanks
JS|||Hi,
What is the result if you run "SELECT * FROM [tablename] FOR XML AUTO"? I
performed a test at my side and it worked fine.
My test was based on the following xml schema and table:
//1. Create a xml schema
CREATE XML SCHEMA COLLECTION [dbo].foo1 AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" minOccurs="0"
nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
//2. Create a table
CREATE TABLE [dbo].[IncVariables](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XMLField] [xml](CONTENT [dbo].[foo1]) NOT NULL,
CONSTRAINT [PK_IncVariables] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
//3. Insert into a record
INSERT INTO IncVariables values ('<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
//4. Query on the table
SELECT * FROM IncVariables FOR XML AUTO
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of your assistance.
Best regards,
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.
========================================
==============

Question about porting data into a new database.

We rewrote one of our legacy C#/asp.net applications that accesses a sql server 2000 database. The new database schema looks very similar to the old one. The major difference is that some of the atributes in the database tables are different. But pretty much we are using the same tables, plus or minus a few.

We need to import the old data into the new database. I have never had to undertake this type of thing before as I am not a DBA, but a developer. I feel a little scared about this whole process. My boss is open to us hiring a contractor to help with the process.

My biggest concern is the referential integrity of the database.

Can someone help me out. Does this sound like something that can be easily done or should I ask for some help.

Ralph

Can you backup existing DB and restore to the new server and "fix" it in place? As in adding/removing any columns, adding any new tables etc. This will at least guarantee you your existing data integrity. Or is this not an option?

|||

That sounds like a really good idea. I will give it some deep thought!

sql

Question about create database,login,user,schema and grant permissions.

Hi,

I created a database,login,user and schema like belows.

---

-- 2. create database
CREATE DATABASE MyTempDatabase;

-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;

--
USE MyTempDatabase;

-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;

-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;

---

The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.

How can I grant permissions to the user?(or schema?)

I failed to grant by T-SQL query.

Additionally, what is purppose of the ROLE? Should I create or use it?

I'm confusing in security concept(login,user,schema,role).

Thanks.

i will give u a capsule form of all these database objects

Login : SQL Server uses Two level Security architecture. Server level and Database Level. To access database you should have server level access. But if you have server level access it does not mean that you have database level access. So , Login is nothing but the Server Level Security. User is Database Level Security. If you create a login , you should add that login to a database as Database User, to have access to that database.

Role : is just like Groups in Windows. It is to group users for better admistration so that you can grant permission to the role rather than granting to each user. i think you must use this.

Schema : this is a new concept in sql 2005. This basically groups the objects or it is a container. In earlier versions of sql server , objects were owned by user and both were tightlty coupled. So if you want to drop a user then you need to transfer the objects owned by the user to any other user. In sql server 2005 users are not the owner of the objects but schema is the object owner.

i think Books online is the best resource to uderstand these objects. read and practice

Madhu

|||

To allow the user to create and delete tables make them a member of the ddl_admin role within the MyTempDatabase.

Code Snippet

Use MyTempDatabase

GO

exec sp_addrolemember @.rolename='ddl_admin', @.membername='MyTempLogin'

GO

To allow the user to bulk insert data from a text file they need to be a member of the Buk Insert Fixed Server Role. This is a server wide setting which simply allows them to load data from a text file.

Code Snippet

exec sp_addsrvrolemember @.loginame='MyTempLogin', @.rolename='bulkadmin'

go

Roles are used to make granting permissions to a group of database users easier. Say you have 5 users who all need the same permissions. Instead of setting up there permissions on a user by user basis, you would setup a role and make all the users members of the role. You can then grant the role what ever rights the users need and the users will inheriate those rights from the role. And users can be in more than one role. So if two of your five users need additional rights that the other three don't need you can create a second role, put in the two users and grant that role the extra rights. When this comes into play is when you add a sixth user. You now don't need to remember what rights they need, you simply drop them into the role and they will have the rights.

When using database roles any right which can be granted within a database can be granted to a role.

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx|||

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx|||

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx|||

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx|||

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?