Conversion of IDENTITY to UNIQUEIDENTIFIER during Replication?

Hi,
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.

> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).

> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert

CONTAINS simple terms search acting like prefix search.

MSSQL 2000 sp3a, on Windows 2003.
I will use examples from BOL. I am searching with a CONTAINS like this:
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
Plurals.
Strange. I have performed a full population and this continues to
happen.
Charlie
Those aren't plurals - those are possessives.
Try "sasquatchs ale" OR "steeleyes stout"
"sqldba" <sqldba@.comcast.net> wrote in message
news:1129915220.977952.192440@.f14g2000cwb.googlegr oups.com...
> MSSQL 2000 sp3a, on Windows 2003.
> I will use examples from BOL. I am searching with a CONTAINS like this:
> WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
> and am receiving results like 'sasquatch's ale' and 'steeleye's stout'.
> Plurals.
> Strange. I have performed a full population and this continues to
> happen.
> Charlie
>
|||Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
and I get 'sasquatch's ale' . Why?
|||Pardon the spelling the last message. I think I may have resolved the issue.
I changed from an English[United States] Language for Word Breaker to Neutral
and it seems to work now. Not sure if this is going to introduce any new
anomalies or not but....we just testing now so bring them on.
"sqldba" wrote:

> Plurals - Possessives. Wither way I ask for the phrase "sasquatch ale"
> and I get 'sasquatch's ale' . Why?
>

Contains Not Working

Hello,
I have done the following as per BOL on a text field
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'TextSearchBody',
'create'
EXEC sp_fulltext_table 'NewsItems',
'create',
'TextSearchBody',
'PK_newsItems'
EXEC sp_fulltext_column 'NewsItems',
'Body',
'add'
EXEC sp_fulltext_table 'NewsItems',
'activate'
GO
EXEC sp_fulltext_catalog 'TextSearchBody',
'start_full'
After doing this the query
SELECT body
FROM newsitems
WHERE contains (body, 'Triumph' )
does not return any rows.
Any idea what else can I do to get it working. All help appriciated.
1) check the application log in event viewer for any messages from MSSearch
or MSSCI.
2) issue the below queries in your full text enabled database.
select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
go
select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
go
select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Anoop" <Anoop@.discussions.microsoft.com> wrote in message
news:3DE601E2-ACA0-4789-ACE7-E42F5945AC73@.microsoft.com...
> Hello,
> I have done the following as per BOL on a text field
> EXEC sp_fulltext_database 'enable'
> EXEC sp_fulltext_catalog 'TextSearchBody',
> 'create'
> EXEC sp_fulltext_table 'NewsItems',
> 'create',
> 'TextSearchBody',
> 'PK_newsItems'
> EXEC sp_fulltext_column 'NewsItems',
> 'Body',
> 'add'
> EXEC sp_fulltext_table 'NewsItems',
> 'activate'
> GO
>
> EXEC sp_fulltext_catalog 'TextSearchBody',
> 'start_full'
> After doing this the query
> SELECT body
> FROM newsitems
> WHERE contains (body, 'Triumph' )
> does not return any rows.
> Any idea what else can I do to get it working. All help appriciated.
>
>
|||Hillary,
I have looked at the event log and there are warning in the log and the details are as follows
Event id :3024 , category : gatherer
and discription :The crawl for project <SQLServer$TST1 SQL0002600005> could not be started, because no crawl seeds could be accessed. Fix the errors and try the crawl again.
Also the output of the Select statements is as follows
select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
go
NULL
select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
go
0
select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
go
1
Please let me know if you need more info.
Thanks
"Hilary Cotter" wrote:

> 1) check the application log in event viewer for any messages from MSSearch
> or MSSCI.
> 2) issue the below queries in your full text enabled database.
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
> go
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:3DE601E2-ACA0-4789-ACE7-E42F5945AC73@.microsoft.com...
>
>
|||Hi Hillary,
I have now checked the error on MSDN and changed the login account being used to start the search and it seems to be all working.
Thanks for the help.
Anoop
"Hilary Cotter" wrote:

> 1) check the application log in event viewer for any messages from MSSearch
> or MSSCI.
> 2) issue the below queries in your full text enabled database.
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','populate _status')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','ItemCoun t')
> go
> select FULLTEXTCATALOGPROPERTY('TextSearchBody','UniqueKe yCount')
> go
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:3DE601E2-ACA0-4789-ACE7-E42F5945AC73@.microsoft.com...
>
>