Field msrepl_tran_version added

Hello there
I've create transacional replication from two databases. At the publisher
new field added to all the tables i mark as replicated: msrepl_tran_version
It cause damage to my database. What i need to do to ged rid of them? and
what i need to do so they won't be created again?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
You have to run a script to drop them. These columns are using by immediate
updating or queued updating publications. Have you need of these
publications?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've create transacional replication from two databases. At the publisher
> new field added to all the tables i mark as replicated:
> msrepl_tran_version
> It cause damage to my database. What i need to do to ged rid of them? and
> what i need to do so they won't be created again?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary:
When i tried to delete the field by this code?
ALTER TABLE Client
DROP COLUMN msrepl_tran_version
I got an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__Client__msrepl_t__02FED618' is dependent on column
'msrepl_tran_version'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
objects access this column.
how can i delete these records?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
> You have to run a script to drop them. These columns are using by
> immediate updating or queued updating publications. Have you need of these
> publications?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
>
|||You will have to drop the constraints before dropping the column.
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23ePeTNMAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
|||I'm sorry with my language. it's one constraint per msrepl_tran_version
column, not constraints...
Sorry.
|||There are default contraints on that field. You'll have to drop those
constraints bfore dropping the column itself. you should do a select on
sysobjects to find the default constraints like '%df_%_msrepl%'. i would
assume, replication is not in place(immediate updating or queued updating).
Once you drop the constraint, it lets u drop the column.
HTH
Tejas
"Roy Goldhammer" wrote:

> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
>
|||Whell Tejas:
It gives me new error: ALTER TABLE DROP COLUMN failed because
'msrepl_tran_version' is currently replicated.
Now what i need to get rid of that, or what i need to cause it not be
created again?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...[vbcol=seagreen]
> There are default contraints on that field. You'll have to drop those
> constraints bfore dropping the column itself. you should do a select on
> sysobjects to find the default constraints like '%df_%_msrepl%'. i would
> assume, replication is not in place(immediate updating or queued
> updating).
> Once you drop the constraint, it lets u drop the column.
> HTH
> Tejas
> "Roy Goldhammer" wrote:
|||You will have to drop your subscription before trying to make these changes.
Is this on the publisher or subscriber?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Whell Tejas:
> It gives me new error: ALTER TABLE DROP COLUMN failed because
> 'msrepl_tran_version' is currently replicated.
> Now what i need to get rid of that, or what i need to cause it not be
> created again?
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...
>
|||Whell Hilary:
It is on the Publisher
And it also addes me new tables Conflict...
I've chosed to use Transactional replication, and it act like merge
replication.
This is realy bad.
What i need to do to use replication without changing the database
Publisher?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#K1WEuYAGHA.3140@.TK2MSFTNGP14.phx.gbl...
> You will have to drop your subscription before trying to make these
changes.[vbcol=seagreen]
> Is this on the publisher or subscriber?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
would[vbcol=seagreen]
them?
>

field level security question

I have a simple interface created with access2002 that just uses an ODBC
connection to 2 tables that reside on a SQL2000 server. I need to give this
utility to persons that should not see a couple particular fields of the
tables. (obviously the utility was not designed for their use.. this is a
recent change in requirement) I see the option to limit permissions right
down to the field level on sql2000 server. If I get rid of all permissions
to just a few fields (not key fields required for joins anywhere in the
utility) for these particular users will the utility still work? I have
launched the utility before adding a user account, by mistake, prior and the
utility opens except their is just no data. For example, a form is based on
a table, the user does not have an account that allows access to this table,
and the form opens but just shows no data. I am hoping if I deny access to
just a few fields that the form would open and only those fields would be
blank. But the rest of the data would be there. Is this the case? Would it
also be the same in the case of forms that display info based on a JOIN of
the 2 tables? (not denying access to primary/foreign key fields that the
join requires)
I want to make sure I'm doing an 'ok' thing before I do this. For example,
even if I don't get errors what else may go wrong that I'm not seeing?
Thanks in advance for any help.If you deny/revoke permissions on a specific column, then the calling
application will receive an error, when it tries to access that column. So
no, the forms application will fail.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"djc" <noone@.nowhere.com> wrote in message
news:OnT1Gkf2DHA.2160@.TK2MSFTNGP12.phx.gbl...
I have a simple interface created with access2002 that just uses an ODBC
connection to 2 tables that reside on a SQL2000 server. I need to give this
utility to persons that should not see a couple particular fields of the
tables. (obviously the utility was not designed for their use.. this is a
recent change in requirement) I see the option to limit permissions right
down to the field level on sql2000 server. If I get rid of all permissions
to just a few fields (not key fields required for joins anywhere in the
utility) for these particular users will the utility still work? I have
launched the utility before adding a user account, by mistake, prior and the
utility opens except their is just no data. For example, a form is based on
a table, the user does not have an account that allows access to this table,
and the form opens but just shows no data. I am hoping if I deny access to
just a few fields that the form would open and only those fields would be
blank. But the rest of the data would be there. Is this the case? Would it
also be the same in the case of forms that display info based on a JOIN of
the 2 tables? (not denying access to primary/foreign key fields that the
join requires)
I want to make sure I'm doing an 'ok' thing before I do this. For example,
even if I don't get errors what else may go wrong that I'm not seeing?
Thanks in advance for any help.|||Thanks for the quick response! So then based on this I assume there is
different behavior based on whether you are opening a form based on a table
that you have no permission on entirely or you are opening a form based on a
table that you are restricted from a few fields but have access to other
fields. Just want to make sure I understand if this is a distinction because
I have opened a form based on a table that the user has no permission on and
I did not receive any errors, just no data. More specifically, it was a
listBox control that gets data from a query of the sql server table. The
list box just remained empty. no error.
thanks again.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uR54Dqf2DHA.556@.TK2MSFTNGP11.phx.gbl...

quote:

---

> If you deny/revoke permissions on a specific column, then the calling
> application will receive an error, when it tries to access that column. So
> no, the forms application will fail.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "djc" <noone@.nowhere.com> wrote in message
> news:OnT1Gkf2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> I have a simple interface created with access2002 that just uses an ODBC
> connection to 2 tables that reside on a SQL2000 server. I need to give

---

this

quote:

---

> utility to persons that should not see a couple particular fields of the
> tables. (obviously the utility was not designed for their use.. this is a
> recent change in requirement) I see the option to limit permissions right
> down to the field level on sql2000 server. If I get rid of all permissions
> to just a few fields (not key fields required for joins anywhere in the
> utility) for these particular users will the utility still work? I have
> launched the utility before adding a user account, by mistake, prior and

---

the

quote:

---

> utility opens except their is just no data. For example, a form is based

---

on

quote:

---

> a table, the user does not have an account that allows access to this

---

table,

quote:

---

> and the form opens but just shows no data. I am hoping if I deny access to
> just a few fields that the form would open and only those fields would be
> blank. But the rest of the data would be there. Is this the case? Would it
> also be the same in the case of forms that display info based on a JOIN of
> the 2 tables? (not denying access to primary/foreign key fields that the
> join requires)
> I want to make sure I'm doing an 'ok' thing before I do this. For example,
> even if I don't get errors what else may go wrong that I'm not seeing?
> Thanks in advance for any help.
>
>

---

|||In both the cases, you get an error. How the application behaves depends on,
how it is trapping each error. The application could be handling the error,
and displaying no data.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"djc" <noone@.nowhere.com> wrote in message
news:u3HU80f2DHA.1752@.tk2msftngp13.phx.gbl...
Thanks for the quick response! So then based on this I assume there is
different behavior based on whether you are opening a form based on a table
that you have no permission on entirely or you are opening a form based on a
table that you are restricted from a few fields but have access to other
fields. Just want to make sure I understand if this is a distinction because
I have opened a form based on a table that the user has no permission on and
I did not receive any errors, just no data. More specifically, it was a
listBox control that gets data from a query of the sql server table. The
list box just remained empty. no error.
thanks again.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uR54Dqf2DHA.556@.TK2MSFTNGP11.phx.gbl...

quote:

---

> If you deny/revoke permissions on a specific column, then the calling
> application will receive an error, when it tries to access that column. So
> no, the forms application will fail.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "djc" <noone@.nowhere.com> wrote in message
> news:OnT1Gkf2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> I have a simple interface created with access2002 that just uses an ODBC
> connection to 2 tables that reside on a SQL2000 server. I need to give

---

this

quote:

---

> utility to persons that should not see a couple particular fields of the
> tables. (obviously the utility was not designed for their use.. this is a
> recent change in requirement) I see the option to limit permissions right
> down to the field level on sql2000 server. If I get rid of all permissions
> to just a few fields (not key fields required for joins anywhere in the
> utility) for these particular users will the utility still work? I have
> launched the utility before adding a user account, by mistake, prior and

---

the

quote:

---

> utility opens except their is just no data. For example, a form is based

---

on

quote:

---

> a table, the user does not have an account that allows access to this

---

table,

quote:

---

> and the form opens but just shows no data. I am hoping if I deny access to
> just a few fields that the form would open and only those fields would be
> blank. But the rest of the data would be there. Is this the case? Would it
> also be the same in the case of forms that display info based on a JOIN of
> the 2 tables? (not denying access to primary/foreign key fields that the
> join requires)
> I want to make sure I'm doing an 'ok' thing before I do this. For example,
> even if I don't get errors what else may go wrong that I'm not seeing?
> Thanks in advance for any help.
>
>

---

|||ok. Thanks.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e9E0y3f2DHA.1720@.TK2MSFTNGP10.phx.gbl...

quote:

---

> In both the cases, you get an error. How the application behaves depends

---

on,

quote:

---

> how it is trapping each error. The application could be handling the

---

error,

quote:

---

> and displaying no data.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "djc" <noone@.nowhere.com> wrote in message
> news:u3HU80f2DHA.1752@.tk2msftngp13.phx.gbl...
> Thanks for the quick response! So then based on this I assume there is
> different behavior based on whether you are opening a form based on a

---

table

quote:

---

> that you have no permission on entirely or you are opening a form based on

---

a

quote:

---

> table that you are restricted from a few fields but have access to other
> fields. Just want to make sure I understand if this is a distinction

---

because

quote:

---

> I have opened a form based on a table that the user has no permission on

---

and

quote:

---

> I did not receive any errors, just no data. More specifically, it was a
> listBox control that gets data from a query of the sql server table. The
> list box just remained empty. no error.
> thanks again.
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uR54Dqf2DHA.556@.TK2MSFTNGP11.phx.gbl...
So[QUOTE]
> this
a[QUOTE]
right[QUOTE]
permissions[QUOTE]
> the
> on
> table,
to[QUOTE]
be[QUOTE]
it[QUOTE]
of[QUOTE]
example,[QUOTE]
>
>

---

Field Description Won't Save in MSDE Table

I've been working with numerous tables (creating them, that is) in
MSDE.
Everything's working fine aside from the fact that I can't seem to get
any descriptive information on any field in any table to save.
I add the descriptive text to the field, click to save the table with
the newly-entered description, the hourglass displays and the table is
being saved, and then when the design view of the table refreshes, the
descriptions I've just entered on the various fields are gone.
Anyone else have problems with this?
Thanks!
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
What application are you using to design the table?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405070905.9a523cb@.posting.google.co m...
> I've been working with numerous tables (creating them, that is) in
> MSDE.
> Everything's working fine aside from the fact that I can't seem to get
> any descriptive information on any field in any table to save.
> I add the descriptive text to the field, click to save the table with
> the newly-entered description, the hourglass displays and the table is
> being saved, and then when the design view of the table refreshes, the
> descriptions I've just entered on the various fields are gone.
> Anyone else have problems with this?
> Thanks!
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com
|||I'm using MSDE Manager by Vale Software and haven't found anything
dealing with this issue on their website.
Thanks for any additional information/assistance/recommendations you
might be able to offer.
Sincerely,
Brad H. McCollum
bmccoll1@.midsouth.rr.com
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#DZOEnFNEHA.808@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> What application are you using to design the table?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
> news:52031869.0405070905.9a523cb@.posting.google.co m...
|||hi Brad,
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> ha scritto nel messaggio
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
>
coul'd be a "feature" of MSDE Manager, as both Enterprise Manager and my own
management tool, available at the link following my sign., provide the
expectet behaviour...
please contact the application provider...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You should contact the vendor directly. I imagine it's not supported
because column descriptions are kind of superfluous. I tend to shy away
from storing column descriptions in the database, and instead opt for good
documentation on my tables. A user can always look at the documentation but
shouldn't be forced to read a (max 255?) character description by connecting
to the database.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0405100708.c92ddd@.posting.google.com ...
> I'm using MSDE Manager by Vale Software and haven't found anything
> dealing with this issue on their website.
> Thanks for any additional information/assistance/recommendations you
> might be able to offer.
> Sincerely,
> Brad H. McCollum
> bmccoll1@.midsouth.rr.com

field definitions for all user tables

Hello.
Thank you in advance for reading this question.
I posted this question in the newbie area, but have not had a response.
Is there a way to get all the fields for all user tables that are returned
when you do sp_help tablename? I think I have found the columns I need in
'syscolumns' but it is not clear to me how to link the table names to that
syscolumns table to get what I need.
My apolgies for the newbie question. Any help is appreciated!
DianeI just replied to the newusers post.
However, to get table names from syscolumns you can simply call the
OBJECT_NAME() function passing in the id column from syscolumns
(although it's more kosher to use the INFORMATION_SCHEMA views). Like this:
select object_name(id) as [TableName], * from dbo.syscolumns
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Diane wrote:

>Hello.
>Thank you in advance for reading this question.
>I posted this question in the newbie area, but have not had a response.
>Is there a way to get all the fields for all user tables that are returned
>when you do sp_help tablename? I think I have found the columns I need in
>'syscolumns' but it is not clear to me how to link the table names to that
>syscolumns table to get what I need.
>My apolgies for the newbie question. Any help is appreciated!
>Diane
>
>|||> Is there a way to get all the fields for all user tables that are returned
> when you do sp_help tablename? I think I have found the columns I need in
> 'syscolumns'
I strongly recommend avoiding the sys tables when possible.
How about http://www.aspfaq.com/2177 ?|||my choice is
sp_msforeachtable @.command1 = "sp_help '?' "
--
Regards
R.D
--Knowledge gets doubled when shared
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly recommend avoiding the sys tables when possible.
> How about http://www.aspfaq.com/2177 ?
>
>|||> my choice is
> sp_msforeachtable @.command1 = "sp_help '?' "
Well, sp_msforeachtable is convenient, sure. However I have a two issues
with this approach:
(a) it returns a resultset for each table, whereas with
INFORMATION_SCHEMA.COLUMNS you can easily get all the results in a single
set.
(b) it is an undocumented and unsupported feature, so its behavior can
change or it could disappear from the product altogether with a service
pack, security release or new version of SQL Server.

field added to replicated tables?

Hi all,
i found a column "msrepl_synctran_ts" have been added to one of my replicated tables but not found in other replicated tables. I
guess this field may be added by the DB server, i would like to ask under what situation or configuration this field will be added
and the field is timestamp type (the replication is trasactional replication). thanks
Hi Cweb,
Thanks for your post.
From your descriptions, I understood that you would like to know why there
will be a column named "msrepl_synctran_ts" in one of your replication
tables. Have I understood you? Correct me if I was wrong.
Based on my knowledge, the column msrepl_synctran_ts is added to SQL Server
tables if you publish the table and enable it for 'Immediate updating
subscriptions'. If it was allowed, a Subscriber can modify subscription
data and have those changes replicated to the Publisher.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing ,
it is me again, the strange thing is that i have not enable the "Immediate updating subscriptions" option but the column have
been added. Can i remove it? or need to remove the subscription to truncate the coulumn and push subscription again? thanks
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:%23doEXJTrEHA.1344@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your post.
> From your descriptions, I understood that you would like to know why there
> will be a column named "msrepl_synctran_ts" in one of your replication
> tables. Have I understood you? Correct me if I was wrong.
> Based on my knowledge, the column msrepl_synctran_ts is added to SQL Server
> tables if you publish the table and enable it for 'Immediate updating
> subscriptions'. If it was allowed, a Subscriber can modify subscription
> data and have those changes replicated to the Publisher.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Nice to see you again and thanks for your prompt updates!
Based on my scope, if you do not want your tables to be replicated, you
could definitely delete this column, otherwise, it is not recommend to do
this. msrepl_synctan_ts is the timestamp column that replication uses to
determine which is the latest version of a given row accross the servers
participating in the replication. My suggest is let SQL Server handle the
column. If you have big concerns with this additional column, please send
me your replication script and I would love to see whether there is any
workaround for us to omit this.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
here is the script of table and publication
CREATE TABLE [dbo].[MisTransactionReply] (
[TransID] [int] IDENTITY (1, 1) NOT NULL ,
[RequestTransID] [int] NOT NULL ,
[CompanyID] [varchar] (15) NOT NULL ,
[City] [varchar] (3) NULL ,
[UserID] [varchar] (15) NOT NULL ,
[SenderPIMA] [varchar] (35) NULL ,
[RecipientPIMA] [varchar] (35) NULL ,
[CAR] [varchar] (35) NOT NULL ,
[MsgType] [varchar] (6) NOT NULL ,
[MsgID] [int] NOT NULL ,
[AWBPrefix] [varchar] (3) NULL ,
[AWBSuffix] [varchar] (8) NULL ,
[Origin] [varchar] (3) NULL ,
[Destination] [varchar] (3) NULL ,
[Remark] [varchar] (100) NULL ,
[CreateDT] [datetime] NOT NULL ,
[MsgDateTime] [varchar] (10) NULL ,
[Unread] [bit] NOT NULL ,
[Deleted] [bit] NOT NULL ,
[msrepl_synctran_ts] [timestamp] NOT NULL --original schema not have this column
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MisTransactionReply] ADD
CONSTRAINT [DF_MisTransactionReply_RequestTransID] DEFAULT (0) FOR [RequestTransID],
CONSTRAINT [DF_MisTransactionReply_CreateDT] DEFAULT (dateadd(hour,(-8),getdate())) FOR [CreateDT],
CONSTRAINT [DF_MisTransactionDetail_Unread] DEFAULT (1) FOR [Unread],
CONSTRAINT [DF_MisTransactionReply_Deleted] DEFAULT (0) FOR [Deleted],
CONSTRAINT [PK_MisTransactionReply] PRIMARY KEY NONCLUSTERED
(
[TransID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply] ON [dbo].[MisTransactionReply]([CompanyID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_1] ON [dbo].[MisTransactionReply]([MsgType]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_10] ON [dbo].[MisTransactionReply]([SenderPIMA], [CompanyID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_2] ON [dbo].[MisTransactionReply]([MsgID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_3] ON [dbo].[MisTransactionReply]([Origin]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_4] ON [dbo].[MisTransactionReply]([Destination]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_5] ON [dbo].[MisTransactionReply]([AWBPrefix]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_6] ON [dbo].[MisTransactionReply]([AWBSuffix]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_7] ON [dbo].[MisTransactionReply]([CreateDT]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_8] ON [dbo].[MisTransactionReply]([SenderPIMA]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_9] ON [dbo].[MisTransactionReply]([RecipientPIMA]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_11] ON [dbo].[MisTransactionReply]([SenderPIMA], [CreateDT], [MsgType]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_12] ON [dbo].[MisTransactionReply]([RequestTransID]) ON [PRIMARY]
GO
--Publication.sql--
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'ACE_GLSP2', @.optname = N'publish', @.value = N'true'
GO
use [ACE_GLSP2]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.restricted = N'false', @.sync_method = N'native',
@.repl_freq = N'continuous', @.description = N'Transactional publication of ACE_GLSP2 database from Publisher DB02.TRAXON.NET.',
@.status = N'active', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.retention =
0
exec sp_addpublication_snapshot @.publication = N'ACE_GLSP2_UserData_Trans_Reply',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1,
@.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'TXNEDS\SQLAdmin'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.source_owner = N'dbo',
@.source_object = N'MisTransactionReply', @.destination_table = N'MisTransactionReply', @.type = N'logbased', @.creation_script = null,
@.description = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x0000000000000063, @.status = 16, @.vertical_partition = N'false',
@.ins_cmd = N'CALL sp_MSins_MisTransactionReply', @.del_cmd = N'CALL sp_MSdel_MisTransactionReply', @.upd_cmd = N'MCALL
sp_MSupd_MisTransactionReply', @.filter = null, @.sync_object = null, @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the article filter
EXEC sp_articlefilter @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.filter_name =
N'dbo.FLTR_MisTransactionReply_1__36', @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the article synchronization object
EXEC sp_articleview @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.view_name =
N'SYNC_ACE_GLSP2_UserData_Trans_Reply_MisTransacti onReply', @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'all', @.subscriber = N'192.168.0.1',
@.destination_db = N'ACE_GLSP2', @.sync_type = N'automatic', @.update_mode = N'read only'
GO
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:tK1eXo2rEHA.2500@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Nice to see you again and thanks for your prompt updates!
> Based on my scope, if you do not want your tables to be replicated, you
> could definitely delete this column, otherwise, it is not recommend to do
> this. msrepl_synctan_ts is the timestamp column that replication uses to
> determine which is the latest version of a given row accross the servers
> participating in the replication. My suggest is let SQL Server handle the
> column. If you have big concerns with this additional column, please send
> me your replication script and I would love to see whether there is any
> workaround for us to omit this.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Thanks for your detailed script providing such detailed information.
I setup a transactional replication based on the scripts you provided and
it seems not adding that additional column "msrepl_synctran_ts" on Table
[MisTransactionReply]. You could pick up a test machine and have a try on
this.
Based on my scope, Published table will cause upgrade to fail so that
column msrepl_synctran_ts gets added to SQL Server tables, if you publish
the table and enable it for 'Immediate updating subscriptions'. This column
will not be removed even after unpublishing the table OR uninstalling
replication. During an SMS upgrade we add values to the table and fail
because of this new column that is created.
You could run the following script to update all the sysobjects.replinfo
column to 0
/***********************************/
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
sp_configure 'allow updates', 0
go
reconfigure with override
go
/***********************************/
Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
from the table.
If the you have multiple tables that need the timestamp column removed, the
following script will drop the timmestamp column for every table that has
one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
removal.
/*************************************/
DECLARE repl_remove CURSOR
FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
= 'u'
and a.id = b.id
and b.name = 'msrepl_synctran_ts'
OPEN repl_remove
DECLARE @.TableName sysname
FETCH NEXT FROM repl_remove into @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
FETCH NEXT FROM repl_remove into @.TableName
END
CLOSE repl_remove
DEALLOCATE repl_remove
GO
/***********************************************/
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||thx
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:3GGmGNDsEHA.4016@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your detailed script providing such detailed information.
> I setup a transactional replication based on the scripts you provided and
> it seems not adding that additional column "msrepl_synctran_ts" on Table
> [MisTransactionReply]. You could pick up a test machine and have a try on
> this.
> Based on my scope, Published table will cause upgrade to fail so that
> column msrepl_synctran_ts gets added to SQL Server tables, if you publish
> the table and enable it for 'Immediate updating subscriptions'. This column
> will not be removed even after unpublishing the table OR uninstalling
> replication. During an SMS upgrade we add values to the table and fail
> because of this new column that is created.
> You could run the following script to update all the sysobjects.replinfo
> column to 0
> /***********************************/
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> /***********************************/
> Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
> from the table.
> If the you have multiple tables that need the timestamp column removed, the
> following script will drop the timmestamp column for every table that has
> one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
> removal.
> /*************************************/
> DECLARE repl_remove CURSOR
> FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
> = 'u'
> and a.id = b.id
> and b.name = 'msrepl_synctran_ts'
> OPEN repl_remove
> DECLARE @.TableName sysname
> FETCH NEXT FROM repl_remove into @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
> FETCH NEXT FROM repl_remove into @.TableName
> END
> CLOSE repl_remove
> DEALLOCATE repl_remove
> GO
> /***********************************************/
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Mingqing,
i got this error when trying to delete the column, should i stop the publishing first and delete the column and re-publish
again?
'MisTransactionReply' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter the table 'MisTransactionReply' because it is being
published for replication.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:3GGmGNDsEHA.4016@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your detailed script providing such detailed information.
> I setup a transactional replication based on the scripts you provided and
> it seems not adding that additional column "msrepl_synctran_ts" on Table
> [MisTransactionReply]. You could pick up a test machine and have a try on
> this.
> Based on my scope, Published table will cause upgrade to fail so that
> column msrepl_synctran_ts gets added to SQL Server tables, if you publish
> the table and enable it for 'Immediate updating subscriptions'. This column
> will not be removed even after unpublishing the table OR uninstalling
> replication. During an SMS upgrade we add values to the table and fail
> because of this new column that is created.
> You could run the following script to update all the sysobjects.replinfo
> column to 0
> /***********************************/
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> /***********************************/
> Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
> from the table.
> If the you have multiple tables that need the timestamp column removed, the
> following script will drop the timmestamp column for every table that has
> one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
> removal.
> /*************************************/
> DECLARE repl_remove CURSOR
> FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
> = 'u'
> and a.id = b.id
> and b.name = 'msrepl_synctran_ts'
> OPEN repl_remove
> DECLARE @.TableName sysname
> FETCH NEXT FROM repl_remove into @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
> FETCH NEXT FROM repl_remove into @.TableName
> END
> CLOSE repl_remove
> DEALLOCATE repl_remove
> GO
> /***********************************************/
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi CWeb,
Thanks for your prompt updates!
Yes, you will have to stop the replication if you want to delete this
column.
Note that make sure you have a full backup of the database if it has
critical data for you.
Tell me the result of your test and let me know whether you have any
questions or concerns, don't hesitate to let me know. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Fetching sungle record from join

Hello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul JhaHello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul Jha

Are they 3 identical records, or is there something different about them ? If they are identical you can cheese it with a distinct or a group by.|||which one do you want?|||I've never heard of a sungle record|||Rudy asks the correct question here - which of the 3 corresponding records do you want to return? And the answer "it doesn't matter/any of them" doesn't cut it ;)|||And the answer "it doesn't matter/any of them" doesn't cut it ;)Why? He could simply using MAX() or MIN() to get only one record|||MAX or MIN will of course return only one value out of the joined row

what about "the row with the max value"|||Just checking in, pulling up a chair, putting my feet up on the ottoman, leaning back, opening a beer, putting my 3-D glasses on, and waiting for the show...|||BTW, Brett, a "sungle row" is simply a Single row from amongst a Jungle of rows.|||Or he's from New Zealand|||I just might take a stab at this one.

It sounds like rows from t2 are different in some way. If you had data in t2 having to do with say a person and all of the phone numbers they could possibly have, you would get a different row for every phone number.

This is of course, if I am understanding the question correctly.

Fastest way to copy tables and their indexes between servers?

The DTS Task Copy Server Objects is PAINFULLY slow.

The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.

Any tips or tricks to copy tables, data and indexes and a reasonable speed?

Thanks,

Carl

Without fully understanding the specifics but going by what you've done so far, one other option would be to script the database objects to file, run the generated scripts on the second server then use the BCP utility BCP.EXE (bulk copy program) to copy the data over.

See http://msdn2.microsoft.com/en-us/library/aa337544.aspx for more information on how to use the BCP utility.

Regards,

Uwa.

Fastest way to build a database out of SQL scripts

What is the fastest way to generate an SQL 2000 database
out of SQL scripts.

The SQL scripts contain the create tables, views, stored
procedures, triggers, constraints, and the tables DATA
records.

What are my options? isql? osql? are there other ways?

Thank youHi

Using the command line utility unless you specify the :r option for the list
of files you are most likely to be creating a connection for each script.
Check out http://tinyurl.com/5299q

You can use a program that uses DMO (or ADO) to open each file and then
execute the commands within them, this can re-use a single connection, and
so be faster.

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:pJ%qe.22686$Es6.203309@.wagner.videotron.net.. .
> What is the fastest way to generate an SQL 2000 database
> out of SQL scripts.
> The SQL scripts contain the create tables, views, stored
> procedures, triggers, constraints, and the tables DATA
> records.
> What are my options? isql? osql? are there other ways?
> Thank you|||Hi

Using the command line utility unless you specify the :r option for the list
of files you are most likely to be creating a connection for each script.
Check out http://tinyurl.com/5299q

You can use a program that uses DMO (or ADO) to open each file and then
execute the commands within them, this can re-use a single connection, and
so be faster.

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:pJ%qe.22686$Es6.203309@.wagner.videotron.net.. .
> What is the fastest way to generate an SQL 2000 database
> out of SQL scripts.
> The SQL scripts contain the create tables, views, stored
> procedures, triggers, constraints, and the tables DATA
> records.
> What are my options? isql? osql? are there other ways?
> Thank you|||There is a tool called DB Ghost that builds databases from individual
drop/create scripts (schema) and static data insert scripts (data) and
it is, by far, the fastest method to build a database. It beats hand
coded scripts executed via QA or osql by a long way.

DB Ghost can also compare databases, produce a delta upgrade script and
it is possible to execute it via the command line which means that one
tool can give you a fully automated database change management
solution. When you keep the drop/create scripts in a source control
system you also get a full audit trail of changes made.

It's a very powerful way of making changes to SQL Server databases, I
highly recommend you check it out.

Malc|||There is a tool called DB Ghost that builds databases from individual
drop/create scripts (schema) and static data insert scripts (data) and
it is, by far, the fastest method to build a database. It beats hand
coded scripts executed via QA or osql by a long way.

DB Ghost can also compare databases, produce a delta upgrade script and
it is possible to execute it via the command line which means that one
tool can give you a fully automated database change management
solution. When you keep the drop/create scripts in a source control
system you also get a full audit trail of changes made.

It's a very powerful way of making changes to SQL Server databases, I
highly recommend you check it out.

Malc|||> Using the command line utility unless you specify the :r option for the
list
> of files you are most likely to be creating a connection for each script.
> Check out http://tinyurl.com/5299q
> You can use a program that uses DMO (or ADO) to open each file and then
> execute the commands within them, this can re-use a single connection, and
> so be faster.

Thank you John.|||> Using the command line utility unless you specify the :r option for the
list
> of files you are most likely to be creating a connection for each script.
> Check out http://tinyurl.com/5299q
> You can use a program that uses DMO (or ADO) to open each file and then
> execute the commands within them, this can re-use a single connection, and
> so be faster.

Thank you John.

Fastest way

I've got a complex view (lots of inner and outer joins from several tables) in my SQL Server 2000 Database.

In my VB program, I've connected a grid to this view via an Ado Control
but the performances are not so great (even bad).

How can I boost the execution of this view ?

ThxI would look at those joins and see if there is any non-indexed scan first. You can also run the wizard to see if there is any room for improvement. Good luck!|||Hi again Joe from Texas (is it hot there at this moment ?)

I've put indexes on all the needed columns
but I've seen somewhere that we can create indexes on views

would that help ?|||If you look up "Creating an Indexed View" in Books Online, you'll see that there are a lot of restrictions on what types of views can be indexed. In particular, you can't index your view if it contains any of these elements:
Subqueries
Outer joins
Self joins
DISTINCT keyword
SUM functions that reference nullable expressions
.
.
. etc...

If your view is as complex as you say it is, there is a good chance that it cannot be indexed. Also, when you index a view SQL Server creates a permanent virtual table with the results of that view, and which is then updated any time any of the values in the underlying tables are updates. This can slow down other processing.

I think you would be better off rewriting your statement as a stored procedure rather than as a view. Stored procedures are more efficient than views.

Also, post your code and we may be able to find ways to make it more efficient.

blindman

Faster than Access?

I currently have an Access database that has numerous forms, tables, reports, etc. Although it works fine over our LAN, it is very slow when accessed from a second site over our VPN. Will migrating this application to SQL Server give me faster response?
Ken
Probably not; it sounds like your response issues are due to network
latency. Data is still data whether it's being sent by SQL Server or
Access...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:475A7CCC-3838-4216-8DCD-D67F7BCAF00E@.microsoft.com...
> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
> Ken
|||> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
In and of itself? Probably not. Your bottleneck is much more likely to be
the network as opposed to the data access layer.
While you can't just migrate your forms and reports to SQL Server, it is
likely that redesigning them might offer you the opportunity to improve
efficiency there...
http://www.aspfaq.com/
(Reverse address to reply.)
|||If you can move your users to Terminal Services and connect to the machine
(assuming it's hosted on Windows Server) hosting the Access database (or at
least a machine on the LAN), the speed will increase dramatically. I had a
client with the same problem and it appeared the latency in copying the .mdb
file(s) over VPN was what was slowing the application down.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:475A7CCC-3838-4216-8DCD-D67F7BCAF00E@.microsoft.com...
> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
> Ken

Faster than Access?

I currently have an Access database that has numerous forms, tables, reports, etc. Although it works fine over our LAN, it is very slow when accessed from a second site over our VPN. Will migrating this application to SQL Server give me faster response?
KenProbably not; it sounds like your response issues are due to network
latency. Data is still data whether it's being sent by SQL Server or
Access...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:475A7CCC-3838-4216-8DCD-D67F7BCAF00E@.microsoft.com...
> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
> Ken|||> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
In and of itself? Probably not. Your bottleneck is much more likely to be
the network as opposed to the data access layer.
While you can't just migrate your forms and reports to SQL Server, it is
likely that redesigning them might offer you the opportunity to improve
efficiency there...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||If you can move your users to Terminal Services and connect to the machine
(assuming it's hosted on Windows Server) hosting the Access database (or at
least a machine on the LAN), the speed will increase dramatically. I had a
client with the same problem and it appeared the latency in copying the .mdb
file(s) over VPN was what was slowing the application down.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:475A7CCC-3838-4216-8DCD-D67F7BCAF00E@.microsoft.com...
> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
> Ken

Faster than Access?

I currently have an Access database that has numerous forms, tables, reports
, etc. Although it works fine over our LAN, it is very slow when accessed f
rom a second site over our VPN. Will migrating this application to SQL Serv
er give me faster response?
KenProbably not; it sounds like your response issues are due to network
latency. Data is still data whether it's being sent by SQL Server or
Access...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:475A7CCC-3838-4216-8DCD-D67F7BCAF00E@.microsoft.com...
> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
> Ken|||> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
In and of itself? Probably not. Your bottleneck is much more likely to be
the network as opposed to the data access layer.
While you can't just migrate your forms and reports to SQL Server, it is
likely that redesigning them might offer you the opportunity to improve
efficiency there...
http://www.aspfaq.com/
(Reverse address to reply.)|||If you can move your users to Terminal Services and connect to the machine
(assuming it's hosted on Windows Server) hosting the Access database (or at
least a machine on the LAN), the speed will increase dramatically. I had a
client with the same problem and it appeared the latency in copying the .mdb
file(s) over VPN was what was slowing the application down.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:475A7CCC-3838-4216-8DCD-D67F7BCAF00E@.microsoft.com...
> I currently have an Access database that has numerous forms, tables,
reports, etc. Although it works fine over our LAN, it is very slow when
accessed from a second site over our VPN. Will migrating this application
to SQL Server give me faster response?
> Ken

faster query - smaller tables?

My database is much faster now because I found that I only need around
150,000 records in a table for any single query. The date selection will
always be in a certain range.
So if I break my tables up into smaller tables based on the maximum date
range, its much faster.
I don't understand why its 2x's slower if there are more than x-number of
records in the table.. I have tried non-clustered and then clustered indexes
on the date field, and the date + customer id as the key fields... but its
still slower as more records are added to the table. Is this normal?
SELECT DISTINCT A.CustID, B.Discount
FROM Sales A, Discounts B WHERE
A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0.2
If I have 9.5 million records in Sales, the query takes 2 seconds to 4
seconds.
If I have about 60 tables of 150000 records each, and I select from the
appropriate table, the query is about a half second or less.
Is this a common practice, to break tables up based on date range or am I
still doing something wrong?
CREATE TABLE [Sales] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[SaleAmt] [DECIMAL (8,3)] NOT NULL
) ON [PRIMARY]
CREATE TABLE [Discounts] (
[DiscountDate] [datetime] NOT NULL ,
[Discount] [DECIMAL (8,3)] NOT NULL
[Code] [numeric] NOT NULL
) ON [PRIMARY]Rich
You may want to read 'Creating a Partitioned View' article in the BOL.
Does the optimizer available to use indexes defined on the table?
"Rich" <no@.spam.invalid> wrote in message
news:UekHe.54059$4o.18050@.fed1read06...
> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered
> indexes
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount >
> 0.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>|||Hi
Where are your indexes?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" wrote:

> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>
>|||Dear Rich,
When processing a join, The SQL optimizer evaluates all reasonable join
permutations and estimates the total I/O cost, in terms of I/O time. The pla
n
resulting in the lowest estimate of I/O time is the plan chosen.
Please Note that As the number of tables increases, then the number of
permutations that the optimizer must evaluate increases as a factorial of th
e
number of tables in the query:
I.E: Nbr Of Tables is 2 then Nbr Of Permutions On SQL is 2!
Nbr Of Tables is 3 then Nbr Of Permutions On SQL is 3!
Nbr Of Tables is 4 then Nbr Of Permutions On SQL is 4!
and so on...
In Your case you have 2 tables and as i can see they are not related(No Join
In Between), one way to solve your problem and optimize your query processin
g
time is either you filter your tables Discounts and Sales and then build the
join,
Or you build a relation between both tables on primary indexes and then make
your criteria fields as clustered indexes(i.e: SaleDate , DiscountDate,
Discount)
i prefer you combine both ways
Good Luck
Mario Aoun
"Rich" wrote:

> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>
>|||I was just curious about what is the approximate criterion
which allows an optimizer to tell that
T3 joined T1 joined T2
is faster than
T1 joined T2 joined T3 ?
Is it based on the number of columns? Can't be based on
rows because their number is unknown before the join
and would take to much time to compute it...
Any information or pointer on this problem?
Thank you
- Pamela
.NET developer|||Rich,
Why aren't the two tables joined in the query? Do all Customers get all
Discounts? That doesn't seem to make sense (from a data model point of
view).
Assuming you do have some common key (and you join on it in the query),
then this type of query would benefit from a clustered index on the date
range, or from a covering index (see BOL for more details).
Hope this helps,
Gert-Jan
Rich wrote:
> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42EE6C4F.DD1000E0@.toomuchspamalready.nl...
> Rich,
> Why aren't the two tables joined in the query? Do all Customers get all
> Discounts? That doesn't seem to make sense (from a data model point of
> view).
> Assuming you do have some common key (and you join on it in the query),
> then this type of query would benefit from a clustered index on the date
> range, or from a covering index (see BOL for more details).
> Hope this helps,
> Gert-Jan
That helps, thanks.
Richard
> Rich wrote:
of
indexes
its
0.2
I

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/users look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays' data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method for the Account table.
If possible, you might want to drop the main table indexes just before you load the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/u
sers look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays'
data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to
not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method
for the Account table.
If possible, you might want to drop the main table indexes just before you l
oad the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Family Tree Branches

Okie, this one has me a little stumped so bear with me an I will explain as best I can...

I have a family tree database...

The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).

Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.

If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.

I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.Are you just looking for all the roots? If that's the case, this would show you that:

SELECT c1.PersonId
FROM
Child c1
LEFT OUTER JOIN Child c2 ON c1.PersonId = c2.ChildPersonId
WHERE c2.ChildPersonId IS NULL

This will give you the root of all branches. You can then build your individual branches from there. Make sense?|||linking table can be modified with an identity column for each parent-child
relation , so that any branch of tree can be identified with the identity column.|||Are you sure that is going to work...? It would find the root nodes but there could be multiple root nodes for a tree couldn't there? The family tree itself isn't really a tree structure... it expands in both directions from any given point.

It expands up (for example) due to the children nodes and up because of the parent nodes...|||currently what I am doing is

1. Getting all PersonIds and creating an array.
2. Take the first Id and get all the children and parent and spouse ids
3. Get all the child and parent and spouse id's of those found in step 2.
- repeat until no more ids are found, this means the end of a branch
4. go back to array and select the first id that is not one of those that has been found so far
5. return to step 2 until all ideas have been found or searched on

this gives me all the branches.... not sure how to structure the display be it's a start.|||Can u post the table structure?|||CREATE TABLE [dbo].[Child] (
[PersonId] [int] NULL ,
[ChildPersonId] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Person] (
[PersonId] [int] NOT NULL ,
[Firstnames] [nvarchar] (60) NULL ,
[Surname] [nvarchar] (60) NULL ,
[Title] [nvarchar] (100) NULL ,
[Gender] [nvarchar] (1) NULL ,
[DOBDay] [int] NULL ,
[DOBMonth] [int] NULL ,
[DOBYear] [int] NULL ,
[POBLocale] [nvarchar] (50) NULL ,
[POBCountry] [nvarchar] (50) NULL ,
[DODDay] [int] NULL ,
[DODMonth] [int] NULL ,
[DODYear] [int] NULL ,
[PODLocale] [nvarchar] (50) NULL ,
[PODCountry] [nvarchar] (50) NULL ,
[POBurial] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

Now for the Child table both ChildPersonId and PersonId link to a valid PersonId in the Person table|||I would like to suggest 3 more columns in Child Table. RealtionId and ParentRealtionId
And RelationHierarchy.

CREATE TABLE [dbo].[Child] (
[RelationId] [int] NOT NULL,
[ParentRealtionId] NULL,
[PersonId] [int] NULL ,
[ChildPersonId] [int] NULL ,
[RelationHierarchy] varchar(4000)
) GO

so the tables look like

Person Table:

[PersonId] [Firstnames] [Surname]
-------------------
1 Gauthier van Halewyn ..

2 Rogier van Halewyn ..

3 Jacques van Halewyn..

4 Roland van Halewyn ..


Child Table:

[RelationId] [ParentRealtionId] [PersonId] [ChildPersonId] [RelationHierarchy]
-----------------------------
10 null null 1 null
20 10 1 2 10~
30 20 2 3 10~20~
40 30 3 4 10~20~30~

assumptions: Every PersonId occurs only once as a Child PersonId in Child Table.

So to find the children and grand children of a person
All u have to do is select the RelationHierarchy and RelationId from the child table
Concat both and search.

To find the family tree of Gauthier van Halewyn (PersonId=1)



DECLARE @. RelationHierarchy VARCHAR(4000)

SELECT @. RelationHierarchy= isnull(RelationHierarchy, ) + ~ + CONVERT(Varchar(20), RelationId) + ~% FROM [Child] WHERE [ChildPersonId]= 1

SELECT * FROM [Child ] WHERE RelationHierarchy LIKE @. RelationHierarchy

I'm not sure this is going to answer all your questions but this will definetly prove helpful.|||Hmmm,... I see what you are saying... I have though about that myself,.. adding a generation field or something somewhere... I don't want to complicate things too much though,.. but I think I might need to in order to achieve what I would like... thanks for the input