Foreign in Snapshot Replication

When I initialized and started the snapshot agent, how can I move the table
schema along with all the foreign keys? The only way I can think of is add
the script for before and after the initialization.
Does that mean the Replication not support repicate the foreign keys?
If I have the foreign keys in each replicated table, is the transacational
replication smart enough to insert data into into the parent table first?
Thanks
Ed
Hi Ed,
You can replicate fks with the initial snapshot for transactional
replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
article schema option. There are, however a few things you need to watch out
for:
1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
case if you use concurrent snapshot, have circular references (including
self-referencing fk), or you have fks referencing from outside the
publication.
2) Even though transactional replication is suposed to replay what happened
at the publisher in the same order, an update on the primary key is
typically translated into deletes followed by inserts. This can lead to
temporary violation of fks referencing the pk and the only way to workaround
this is to make sure that your fks at the subcriber has the "NOT FOR
REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
a schema option to mark all replicated fks NFR when they are replicated to
the subscriber.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
> When I initialized and started the snapshot agent, how can I move the
> table
> schema along with all the foreign keys? The only way I can think of is
> add
> the script for before and after the initialization.
> Does that mean the Replication not support repicate the foreign keys?
> If I have the foreign keys in each replicated table, is the transacational
> replication smart enough to insert data into into the parent table first?
> Thanks
> Ed
|||In SQL 05, there is an option if I want to replicate the foreign keys. Do I
still have to include all table Dependencies? Replication is smart enough to
know which table/foreign key needs to be created first?
The reason why i ask the question is I have a Prod and a Dev Server.
Developers always ask me to transfer all tables only to the Dev Server. I am
thinking using snapshot / transactional replication to update tables/data for
them.
That's why I would like to make sure moving the tables along with the
foreign keys is good and the replication knows the sequence of creating the
keys.
Thanks
"Raymond Mak [MSFT]" wrote:

> Hi Ed,
> You can replicate fks with the initial snapshot for transactional
> replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
> article schema option. There are, however a few things you need to watch out
> for:
> 1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
> case if you use concurrent snapshot, have circular references (including
> self-referencing fk), or you have fks referencing from outside the
> publication.
> 2) Even though transactional replication is suposed to replay what happened
> at the publisher in the same order, an update on the primary key is
> typically translated into deletes followed by inserts. This can lead to
> temporary violation of fks referencing the pk and the only way to workaround
> this is to make sure that your fks at the subcriber has the "NOT FOR
> REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
> a schema option to mark all replicated fks NFR when they are replicated to
> the subscriber.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
>
>
|||You don't need to include all objects referenced by fks in your publication,
and the fks should be created in the right order (they only have to be
created after the unique\primary keys that they reference). The snapshot
agent history will tell you which fks cannot be replicated because they
reference something not in the publication.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...[vbcol=seagreen]
> In SQL 05, there is an option if I want to replicate the foreign keys. Do
> I
> still have to include all table Dependencies? Replication is smart enough
> to
> know which table/foreign key needs to be created first?
> The reason why i ask the question is I have a Prod and a Dev Server.
> Developers always ask me to transfer all tables only to the Dev Server. I
> am
> thinking using snapshot / transactional replication to update tables/data
> for
> them.
> That's why I would like to make sure moving the tables along with the
> foreign keys is good and the replication knows the sequence of creating
> the
> keys.
> Thanks
> "Raymond Mak [MSFT]" wrote:
|||Thanks.
While I am playing with the table "Customers" and "Orders" in Northwind
database in SQL 2000, when I checked on "Include declared referential
integrity", the foreign keys still not get copied. Am I missing something?
Ed
"Raymond Mak [MSFT]" wrote:

> You don't need to include all objects referenced by fks in your publication,
> and the fks should be created in the right order (they only have to be
> created after the unique\primary keys that they reference). The snapshot
> agent history will tell you which fks cannot be replicated because they
> reference something not in the publication.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...
>
>
|||The Orders table in the Northwind database has FKs referencing the Employees
table and the Shippers table as well. In SQL2000, the snapshot agent will
simply not script any foreign key constraints from the referencing table if
*any* of the referenced tables is not part of the publication. The SQL2005
snapshot agent implements a much more granular approach of analyzing each
individual foreign key so the foreign key from Orders to Customers will be
replicated with the SQL2005 snapshot agent despite the fact that you didn't
include Employees and Shippers in your publication (you will even see in the
snapshot history messages why the FKs referencing Employees and Shippers are
not scripted). Ed, I got exactly the same question (down to the same tables)
from one of our support engineers just two days ago, did you contact
Microsoft Support for this?
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...[vbcol=seagreen]
> Thanks.
> While I am playing with the table "Customers" and "Orders" in Northwind
> database in SQL 2000, when I checked on "Include declared referential
> integrity", the foreign keys still not get copied. Am I missing
> something?
> Ed
> "Raymond Mak [MSFT]" wrote:
|||It was not me but I will play with SQL 05 to see the result.
Thanks again.
"Raymond Mak [MSFT]" wrote:

> The Orders table in the Northwind database has FKs referencing the Employees
> table and the Shippers table as well. In SQL2000, the snapshot agent will
> simply not script any foreign key constraints from the referencing table if
> *any* of the referenced tables is not part of the publication. The SQL2005
> snapshot agent implements a much more granular approach of analyzing each
> individual foreign key so the foreign key from Orders to Customers will be
> replicated with the SQL2005 snapshot agent despite the fact that you didn't
> include Employees and Shippers in your publication (you will even see in the
> snapshot history messages why the FKs referencing Employees and Shippers are
> not scripted). Ed, I got exactly the same question (down to the same tables)
> from one of our support engineers just two days ago, did you contact
> Microsoft Support for this?
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...
>
>

Force all months along X axis when some months missing data

Hi,
I want to display numeric information by month in a column chart. The
data does not have records for every month. I can't seem to display
all months along the x axis while still using month names instead of
month numbers.
My current chart setup is as follows:
Data > Category group
Name: MonthCategory
Group on (Expression): =Month(Fields!AcceptedDate.Value)
Label: =Fields!AcceptedDate.Value
X Axis
Show labels: <true>
Format Code: MMM
Scale Minimum: 1
Scale Maximum: 12
With this configuration, I get x axis labels for the months that have
data, but the months with no data are skipped.
If I turn on "Numeric or time-scale values", I get all 12 months, but
they are displayed as the literal string "MMM". It seems that the
"Numeric or time-scale values" setting won't allow the labels to
display anything other than numeric values.
Any suggestions?
Thanks in advance,
EricHey Eric,
Can your Format: be an expression? If so you may be able to try
=Choose(xValue, "january", "february","march",etc...) for the 12 months.
Michael C
"Eric" wrote:
> Hi,
> I want to display numeric information by month in a column chart. The
> data does not have records for every month. I can't seem to display
> all months along the x axis while still using month names instead of
> month numbers.
> My current chart setup is as follows:
> Data > Category group
> Name: MonthCategory
> Group on (Expression): =Month(Fields!AcceptedDate.Value)
> Label: =Fields!AcceptedDate.Value
> X Axis
> Show labels: <true>
> Format Code: MMM
> Scale Minimum: 1
> Scale Maximum: 12
> With this configuration, I get x axis labels for the months that have
> data, but the months with no data are skipped.
> If I turn on "Numeric or time-scale values", I get all 12 months, but
> they are displayed as the literal string "MMM". It seems that the
> "Numeric or time-scale values" setting won't allow the labels to
> display anything other than numeric values.
> Any suggestions?
> Thanks in advance,
> Eric
>|||That's close Michael. Though when I use:
=choose(month(Fields!AcceptedDate.Value), "Jan", "Feb", "Mar",
"Apr"...)
I get "Jan" listed for every month.
On Jul 25, 5:28 pm, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Hey Eric,
> Can your Format: be an expression? If so you may be able to try
> =Choose(xValue, "january", "february","march",etc...) for the 12 months.
> Michael C
>
> "Eric" wrote:
> > Hi,
> > I want to display numeric information by month in a column chart. The
> > data does not have records for every month. I can't seem to display
> > all months along the x axis while still using month names instead of
> > month numbers.
> > My current chart setup is as follows:
> > Data > Category group
> > Name: MonthCategory
> > Group on (Expression): =Month(Fields!AcceptedDate.Value)
> > Label: =Fields!AcceptedDate.Value
> > X Axis
> > Show labels: <true>
> > Format Code: MMM
> > Scale Minimum: 1
> > Scale Maximum: 12
> > With this configuration, I get x axis labels for the months that have
> > data, but the months with no data are skipped.
> > If I turn on "Numeric or time-scale values", I get all 12 months, but
> > they are displayed as the literal string "MMM". It seems that the
> > "Numeric or time-scale values" setting won't allow the labels to
> > display anything other than numeric values.
> > Any suggestions?
> > Thanks in advance,
> > Eric- Hide quoted text -
> - Show quoted text -

For Xml Auto Help

In sql 2005 if I have a query along the lines of:
select * from myTable
for xml auto, elements xsinil, root('myRoot')
I will get back one row, one column, filled with a nice xml string.
The problem I'm having is figuring out how to access that data, such that I
can put it into a local var. What I want to do looks something like this:
declare @.foo varchar(max)
select @.foo = * from myTable
for xml auto, elements xsinil, root('myRoot')
select @.foo
This brings about an error of course, even though the final result is just
one row and column of data.
Does anybody know how I can get the results of a query using "for xml auto"
into a local variable?
Thanks,
KevinJust use an xml type variable e.g.
declare @.x xml
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'))
select @.x as 'XML Result'
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>|||Just to add a tiny bit:
The TYPE directive mean that the result from the query is of the datatype XM
L instead of a string.
It doesn't make any difference in Jasper's example, as there would be an imp
licit datatype
conversation from string to xml anyhow, but it might be useful in other case
s. The TYPE directive is
obviously new for 2005 (as the xml datatype is).
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'), type)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u9$Y3ODDGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Just use an xml type variable e.g.
> declare @.x xml
> set @.x = (select [name]
> from sys.databases as [database]
> for xml auto,root('databases'))
> select @.x as 'XML Result'
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
> news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Jasper and Tibor, that's just what I needed.
Kevin
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>