Rebuild SQL 2000 indexes on-line

I’ve asked several “experts” and none of them have a way to do this. Do you
know of any way to do this while the users are on line (without using DBCC
Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
rebuild the indexes anyway.
I have 3 databases and each of them has several very large tables (one of
them exceeds 182 million rows). In order to truly be 7x24, I need to find a
way to rebuild the indexes while the users are in the database.
Thanks in advance for any help you can provide,
Regards,
George
You can't with SQL 2000. The best you can do without disrupting users
is to use DBCC INDEXDEFRAG, which will reorder pages in the index but
not shuffle extents so that they're contiguous. It will improve the
logical fragmentation as reported by DBCC SHOWCONTIG but not the extent
fragmentation.
I guess your options are 1) to upgrade to SQL 2005, which allows online
index rebuild operations (although there are a few caveats around those,
for example they're only available with the Enterprise Edition, can't be
done on clustered indexes when the underlying table contains LOB data,
can't be done on nonclustered indexes that contain LOB columns...), or
2) have an outage while you rebuild your clustered index on your large
tables (nonclustered indexes are normally not as big a problem).
Personally, I'm looking for a good excuse to rebuild one of our big
clustered indexes online in SQL 2005 just for the fun of it...and
because we can. <g>
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:

>I’ve asked several “experts” and none of them have a way to do this. Do you
>know of any way to do this while the users are on line (without using DBCC
>Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
>rebuild the indexes anyway.
>I have 3 databases and each of them has several very large tables (one of
>them exceeds 182 million rows). In order to truly be 7x24, I need to find a
>way to rebuild the indexes while the users are in the database.
>Thanks in advance for any help you can provide,
>Regards,
>George
>

Rebuild SQL 2000 indexes on-line

I’ve asked several “experts” and none of them have a way to do this. D
o you
know of any way to do this while the users are on line (without using DBCC
Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
rebuild the indexes anyway.
I have 3 databases and each of them has several very large tables (one of
them exceeds 182 million rows). In order to truly be 7x24, I need to find a
way to rebuild the indexes while the users are in the database.
Thanks in advance for any help you can provide,
Regards,
GeorgeYou can't with SQL 2000. The best you can do without disrupting users
is to use DBCC INDEXDEFRAG, which will reorder pages in the index but
not shuffle extents so that they're contiguous. It will improve the
logical fragmentation as reported by DBCC SHOWCONTIG but not the extent
fragmentation.
I guess your options are 1) to upgrade to SQL 2005, which allows online
index rebuild operations (although there are a few caveats around those,
for example they're only available with the Enterprise Edition, can't be
done on clustered indexes when the underlying table contains LOB data,
can't be done on nonclustered indexes that contain LOB columns...), or
2) have an outage while you rebuild your clustered index on your large
tables (nonclustered indexes are normally not as big a problem).
Personally, I'm looking for a good excuse to rebuild one of our big
clustered indexes online in SQL 2005 just for the fun of it...and
because we can. <g>
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:

>I’ve asked several “experts” and none of them have a way to do this.
Do you
>know of any way to do this while the users are on line (without using DBCC
>Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
>rebuild the indexes anyway.
>I have 3 databases and each of them has several very large tables (one of
>them exceeds 182 million rows). In order to truly be 7x24, I need to find a
>way to rebuild the indexes while the users are in the database.
>Thanks in advance for any help you can provide,
>Regards,
>George
>

Rebuild Master

Hello experts,
I'm making my "questions" again cause nobody answered 4 days now.
I want to rebuild my master DB because I want to change my Collation. What
are my problems:
1) I don't know which SQL collation to choose from the list to get collation
SQL_Latin1_General_CP1253_CS_AS
2) When I specify the Data folder then an error occurs and "sais" that
cannot copy the master files (something like that). I'm not in position to
tell you the exact message.
Thanks in advance
hI
You don't need to rebuild master database in order to change a collation.
Look at ALTER DATABASE command in the BOL.
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> Hello experts,
> I'm making my "questions" again cause nobody answered 4 days now.
> I want to rebuild my master DB because I want to change my Collation. What
> are my problems:
> 1) I don't know which SQL collation to choose from the list to get
collation
> SQL_Latin1_General_CP1253_CS_AS
> 2) When I specify the Data folder then an error occurs and "sais" that
> cannot copy the master files (something like that). I'm not in position to
> tell you the exact message.
> Thanks in advance
>
|||aha, one more thing, master's collation is Server's collation too right?
"Uri Dimant" wrote:

> hI
> You don't need to rebuild master database in order to change a collation.
> Look at ALTER DATABASE command in the BOL.
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> collation
>
>
|||I'm pretty certain you cannot change collation for the system databases through ALTER DATABASE
(although I couldn't find it in BOL from a quick search). I know that there exists a KB article
describing that it is not supported to have different collation for the system databases.
Also, changing collation through ALTER DATABASE does not change collation for existing objects.
IMO, you are in for rebuildm.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...[vbcol=seagreen]
> aha, one more thing, master's collation is Server's collation too right?
> "Uri Dimant" wrote:
|||Yes I agree, I don't think is a good idea to ALTER my system DBs to change
their collation, do you have an answer for the collation tha I wish to choose
from the list of rebuildm.exe UI? I'd like the
"SQL_Latin1_General_CP****_CS_AS" ones.
Thanks in advance
"Tibor Karaszi" wrote:

> I'm pretty certain you cannot change collation for the system databases through ALTER DATABASE
> (although I couldn't find it in BOL from a quick search). I know that there exists a KB article
> describing that it is not supported to have different collation for the system databases.
> Also, changing collation through ALTER DATABASE does not change collation for existing objects.
> IMO, you are in for rebuildm.exe.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...
>
>
|||I don't have a machines form where I can run setup at the moment. But it is definitely an SQL
collation (the compatibility ones, at the lower part of the dialog). Latin1 might be called ANSI or
ISO. And then you should look for a collation with the number you want (1253 in your case). Also, CS
means Case Sensitive and AS means Accent Sensitive.
Note that not all collations are available through SETUP / REBUILD. Some of the Swedish are among
that list. In such cases, you can run an unattended install and specify the collation in the ISS
file. I'm not sure how to do this if you want to do a rebuild, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:443C0FB1-299A-4309-BD91-A66AFCA86872@.microsoft.com...[vbcol=seagreen]
> Yes I agree, I don't think is a good idea to ALTER my system DBs to change
> their collation, do you have an answer for the collation tha I wish to choose
> from the list of rebuildm.exe UI? I'd like the
> "SQL_Latin1_General_CP****_CS_AS" ones.
> Thanks in advance
> "Tibor Karaszi" wrote:
|||I see, thank you very much for your time.
"Tibor Karaszi" wrote:

> I don't have a machines form where I can run setup at the moment. But it is definitely an SQL
> collation (the compatibility ones, at the lower part of the dialog). Latin1 might be called ANSI or
> ISO. And then you should look for a collation with the number you want (1253 in your case). Also, CS
> means Case Sensitive and AS means Accent Sensitive.
> Note that not all collations are available through SETUP / REBUILD. Some of the Swedish are among
> that list. In such cases, you can run an unattended install and specify the collation in the ISS
> file. I'm not sure how to do this if you want to do a rebuild, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:443C0FB1-299A-4309-BD91-A66AFCA86872@.microsoft.com...
>
>

Rebuild Master

Hello experts,
I'm making my "questions" again cause nobody answered 4 days now.
I want to rebuild my master DB because I want to change my Collation. What
are my problems:
1) I don't know which SQL collation to choose from the list to get collation
SQL_Latin1_General_CP1253_CS_AS
2) When I specify the Data folder then an error occurs and "sais" that
cannot copy the master files (something like that). I'm not in position to
tell you the exact message.
Thanks in advancehI
You don't need to rebuild master database in order to change a collation.
Look at ALTER DATABASE command in the BOL.
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> Hello experts,
> I'm making my "questions" again cause nobody answered 4 days now.
> I want to rebuild my master DB because I want to change my Collation. What
> are my problems:
> 1) I don't know which SQL collation to choose from the list to get
collation
> SQL_Latin1_General_CP1253_CS_AS
> 2) When I specify the Data folder then an error occurs and "sais" that
> cannot copy the master files (something like that). I'm not in position to
> tell you the exact message.
> Thanks in advance
>|||aha, one more thing, master's collation is Server's collation too right?
"Uri Dimant" wrote:

> hI
> You don't need to rebuild master database in order to change a collation.
> Look at ALTER DATABASE command in the BOL.
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> collation
>
>|||I'm pretty certain you cannot change collation for the system databases thro
ugh ALTER DATABASE
(although I couldn't find it in BOL from a quick search). I know that there
exists a KB article
describing that it is not supported to have different collation for the syst
em databases.
Also, changing collation through ALTER DATABASE does not change collation fo
r existing objects.
IMO, you are in for rebuildm.exe.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...[vbcol=seagreen]
> aha, one more thing, master's collation is Server's collation too right?
> "Uri Dimant" wrote:
>|||Yes I agree, I don't think is a good idea to ALTER my system DBs to change
their collation, do you have an answer for the collation tha I wish to choos
e
from the list of rebuildm.exe UI? I'd like the
"SQL_Latin1_General_CP****_CS_AS" ones.
Thanks in advance
"Tibor Karaszi" wrote:

> I'm pretty certain you cannot change collation for the system databases th
rough ALTER DATABASE
> (although I couldn't find it in BOL from a quick search). I know that ther
e exists a KB article
> describing that it is not supported to have different collation for the sy
stem databases.
> Also, changing collation through ALTER DATABASE does not change collation
for existing objects.
> IMO, you are in for rebuildm.exe.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...
>
>|||I don't have a machines form where I can run setup at the moment. But it is
definitely an SQL
collation (the compatibility ones, at the lower part of the dialog). Latin1
might be called ANSI or
ISO. And then you should look for a collation with the number you want (1253
in your case). Also, CS
means Case Sensitive and AS means Accent Sensitive.
Note that not all collations are available through SETUP / REBUILD. Some of
the Swedish are among
that list. In such cases, you can run an unattended install and specify the
collation in the ISS
file. I'm not sure how to do this if you want to do a rebuild, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:443C0FB1-299A-4309-BD91-A66AFCA86872@.microsoft.com...[vbcol=seagreen]
> Yes I agree, I don't think is a good idea to ALTER my system DBs to change
> their collation, do you have an answer for the collation tha I wish to cho
ose
> from the list of rebuildm.exe UI? I'd like the
> "SQL_Latin1_General_CP****_CS_AS" ones.
> Thanks in advance
> "Tibor Karaszi" wrote:
>|||I see, thank you very much for your time.
"Tibor Karaszi" wrote:

> I don't have a machines form where I can run setup at the moment. But it i
s definitely an SQL
> collation (the compatibility ones, at the lower part of the dialog). Latin
1 might be called ANSI or
> ISO. And then you should look for a collation with the number you want (12
53 in your case). Also, CS
> means Case Sensitive and AS means Accent Sensitive.
> Note that not all collations are available through SETUP / REBUILD. Some o
f the Swedish are among
> that list. In such cases, you can run an unattended install and specify th
e collation in the ISS
> file. I'm not sure how to do this if you want to do a rebuild, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:443C0FB1-299A-4309-BD91-A66AFCA86872@.microsoft.com...
>
>

Rebuild Master

Hello experts,
I'm making my "questions" again cause nobody answered 4 days now.
I want to rebuild my master DB because I want to change my Collation. What
are my problems:
1) I don't know which SQL collation to choose from the list to get collation
SQL_Latin1_General_CP1253_CS_AS
2) When I specify the Data folder then an error occurs and "sais" that
cannot copy the master files (something like that). I'm not in position to
tell you the exact message.
Thanks in advancehI
You don't need to rebuild master database in order to change a collation.
Look at ALTER DATABASE command in the BOL.
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> Hello experts,
> I'm making my "questions" again cause nobody answered 4 days now.
> I want to rebuild my master DB because I want to change my Collation. What
> are my problems:
> 1) I don't know which SQL collation to choose from the list to get
collation
> SQL_Latin1_General_CP1253_CS_AS
> 2) When I specify the Data folder then an error occurs and "sais" that
> cannot copy the master files (something like that). I'm not in position to
> tell you the exact message.
> Thanks in advance
>|||aha, one more thing, master's collation is Server's collation too right?
"Uri Dimant" wrote:
> hI
> You don't need to rebuild master database in order to change a collation.
> Look at ALTER DATABASE command in the BOL.
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> > Hello experts,
> >
> > I'm making my "questions" again cause nobody answered 4 days now.
> >
> > I want to rebuild my master DB because I want to change my Collation. What
> > are my problems:
> >
> > 1) I don't know which SQL collation to choose from the list to get
> collation
> > SQL_Latin1_General_CP1253_CS_AS
> >
> > 2) When I specify the Data folder then an error occurs and "sais" that
> > cannot copy the master files (something like that). I'm not in position to
> > tell you the exact message.
> >
> > Thanks in advance
> >
>
>|||I'm pretty certain you cannot change collation for the system databases through ALTER DATABASE
(although I couldn't find it in BOL from a quick search). I know that there exists a KB article
describing that it is not supported to have different collation for the system databases.
Also, changing collation through ALTER DATABASE does not change collation for existing objects.
IMO, you are in for rebuildm.exe.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...
> aha, one more thing, master's collation is Server's collation too right?
> "Uri Dimant" wrote:
>> hI
>> You don't need to rebuild master database in order to change a collation.
>> Look at ALTER DATABASE command in the BOL.
>>
>> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
>> news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
>> > Hello experts,
>> >
>> > I'm making my "questions" again cause nobody answered 4 days now.
>> >
>> > I want to rebuild my master DB because I want to change my Collation. What
>> > are my problems:
>> >
>> > 1) I don't know which SQL collation to choose from the list to get
>> collation
>> > SQL_Latin1_General_CP1253_CS_AS
>> >
>> > 2) When I specify the Data folder then an error occurs and "sais" that
>> > cannot copy the master files (something like that). I'm not in position to
>> > tell you the exact message.
>> >
>> > Thanks in advance
>> >
>>|||Yes I agree, I don't think is a good idea to ALTER my system DBs to change
their collation, do you have an answer for the collation tha I wish to choose
from the list of rebuildm.exe UI? I'd like the
"SQL_Latin1_General_CP****_CS_AS" ones.
Thanks in advance
"Tibor Karaszi" wrote:
> I'm pretty certain you cannot change collation for the system databases through ALTER DATABASE
> (although I couldn't find it in BOL from a quick search). I know that there exists a KB article
> describing that it is not supported to have different collation for the system databases.
> Also, changing collation through ALTER DATABASE does not change collation for existing objects.
> IMO, you are in for rebuildm.exe.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...
> > aha, one more thing, master's collation is Server's collation too right?
> >
> > "Uri Dimant" wrote:
> >
> >> hI
> >> You don't need to rebuild master database in order to change a collation.
> >> Look at ALTER DATABASE command in the BOL.
> >>
> >>
> >> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> >> news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> >> > Hello experts,
> >> >
> >> > I'm making my "questions" again cause nobody answered 4 days now.
> >> >
> >> > I want to rebuild my master DB because I want to change my Collation. What
> >> > are my problems:
> >> >
> >> > 1) I don't know which SQL collation to choose from the list to get
> >> collation
> >> > SQL_Latin1_General_CP1253_CS_AS
> >> >
> >> > 2) When I specify the Data folder then an error occurs and "sais" that
> >> > cannot copy the master files (something like that). I'm not in position to
> >> > tell you the exact message.
> >> >
> >> > Thanks in advance
> >> >
> >>
> >>
> >>
>
>|||I don't have a machines form where I can run setup at the moment. But it is definitely an SQL
collation (the compatibility ones, at the lower part of the dialog). Latin1 might be called ANSI or
ISO. And then you should look for a collation with the number you want (1253 in your case). Also, CS
means Case Sensitive and AS means Accent Sensitive.
Note that not all collations are available through SETUP / REBUILD. Some of the Swedish are among
that list. In such cases, you can run an unattended install and specify the collation in the ISS
file. I'm not sure how to do this if you want to do a rebuild, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:443C0FB1-299A-4309-BD91-A66AFCA86872@.microsoft.com...
> Yes I agree, I don't think is a good idea to ALTER my system DBs to change
> their collation, do you have an answer for the collation tha I wish to choose
> from the list of rebuildm.exe UI? I'd like the
> "SQL_Latin1_General_CP****_CS_AS" ones.
> Thanks in advance
> "Tibor Karaszi" wrote:
>> I'm pretty certain you cannot change collation for the system databases through ALTER DATABASE
>> (although I couldn't find it in BOL from a quick search). I know that there exists a KB article
>> describing that it is not supported to have different collation for the system databases.
>> Also, changing collation through ALTER DATABASE does not change collation for existing objects.
>> IMO, you are in for rebuildm.exe.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
>> news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...
>> > aha, one more thing, master's collation is Server's collation too right?
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> hI
>> >> You don't need to rebuild master database in order to change a collation.
>> >> Look at ALTER DATABASE command in the BOL.
>> >>
>> >>
>> >> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
>> >> news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
>> >> > Hello experts,
>> >> >
>> >> > I'm making my "questions" again cause nobody answered 4 days now.
>> >> >
>> >> > I want to rebuild my master DB because I want to change my Collation. What
>> >> > are my problems:
>> >> >
>> >> > 1) I don't know which SQL collation to choose from the list to get
>> >> collation
>> >> > SQL_Latin1_General_CP1253_CS_AS
>> >> >
>> >> > 2) When I specify the Data folder then an error occurs and "sais" that
>> >> > cannot copy the master files (something like that). I'm not in position to
>> >> > tell you the exact message.
>> >> >
>> >> > Thanks in advance
>> >> >
>> >>
>> >>
>> >>
>>|||I see, thank you very much for your time.
"Tibor Karaszi" wrote:
> I don't have a machines form where I can run setup at the moment. But it is definitely an SQL
> collation (the compatibility ones, at the lower part of the dialog). Latin1 might be called ANSI or
> ISO. And then you should look for a collation with the number you want (1253 in your case). Also, CS
> means Case Sensitive and AS means Accent Sensitive.
> Note that not all collations are available through SETUP / REBUILD. Some of the Swedish are among
> that list. In such cases, you can run an unattended install and specify the collation in the ISS
> file. I'm not sure how to do this if you want to do a rebuild, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> news:443C0FB1-299A-4309-BD91-A66AFCA86872@.microsoft.com...
> > Yes I agree, I don't think is a good idea to ALTER my system DBs to change
> > their collation, do you have an answer for the collation tha I wish to choose
> > from the list of rebuildm.exe UI? I'd like the
> > "SQL_Latin1_General_CP****_CS_AS" ones.
> >
> > Thanks in advance
> >
> > "Tibor Karaszi" wrote:
> >
> >> I'm pretty certain you cannot change collation for the system databases through ALTER DATABASE
> >> (although I couldn't find it in BOL from a quick search). I know that there exists a KB article
> >> describing that it is not supported to have different collation for the system databases.
> >>
> >> Also, changing collation through ALTER DATABASE does not change collation for existing objects.
> >>
> >> IMO, you are in for rebuildm.exe.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> >> news:A9E3061A-F52B-4ECB-89EC-B6B9036C4B25@.microsoft.com...
> >> > aha, one more thing, master's collation is Server's collation too right?
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> hI
> >> >> You don't need to rebuild master database in order to change a collation.
> >> >> Look at ALTER DATABASE command in the BOL.
> >> >>
> >> >>
> >> >> "x-rays" <xrays@.discussions.microsoft.com> wrote in message
> >> >> news:DDC20EBC-F751-4B4E-92E5-0A2ECDEDBDAE@.microsoft.com...
> >> >> > Hello experts,
> >> >> >
> >> >> > I'm making my "questions" again cause nobody answered 4 days now.
> >> >> >
> >> >> > I want to rebuild my master DB because I want to change my Collation. What
> >> >> > are my problems:
> >> >> >
> >> >> > 1) I don't know which SQL collation to choose from the list to get
> >> >> collation
> >> >> > SQL_Latin1_General_CP1253_CS_AS
> >> >> >
> >> >> > 2) When I specify the Data folder then an error occurs and "sais" that
> >> >> > cannot copy the master files (something like that). I'm not in position to
> >> >> > tell you the exact message.
> >> >> >
> >> >> > Thanks in advance
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

REAL REAL Experts needed for pretending to be replication_agent

Hi All,
We know that, "NOT FOR REPLICATION" option makes it possible for
replication agents ,let's say, to insert rows without being
constrained by IDENTITY restriction or check constraints(And also it
does not change SEED value in the table).
That means, being a replication agent is like having a PASS CARD that
allows them to get rid of constraint checks. (But disabling
constraints is not a solution)
But, during conflict resolution coding, I need to INSERT some data to
some tables (related tables), but I am facing CONSTRAINTS that merge
agent does not face.
Is there any way of pretending to be like merge_agent for constraint
overcoming? THIS IS THE CORE QUESTION.
To be more specific, is there any way of setting sessionproperty for
replication_agent? That is: when you execute the following query in a
normal connection;
select sessionproperty('replication_agent')
you get "0".
but for replication_agent that function returns "1". Microsoft guys
somehow set connection property for agent, and how they do it is
scritly undocumented.
I tried setting context_info in sysprocesses table to "8" but it did
not work. That SP also is not enough by itself
:sp_MSsetcontext_replagent
Please help, if any way of pretending to be merge agent.
PS:I event wrote an application with ReplMerg.exe process name
guessing that SQL may know agent from its process name.
And also please don't suggest disabling constraints because it is not
enough, e.g. replication_agent does not affect SEED value for
inserts that it does..
I hope some REAL expert will hear my SCREAM.
Thanks alot in advance,
Nury SWORD
MCDBA - MCSD
Toronto
I suggest you contact PSS for an answer to your question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Nury SWORD" <nurysword@.hotmail.com> wrote in message
news:16342dc6.0409170707.65ef87e1@.posting.google.c om...
> Hi All,
> We know that, "NOT FOR REPLICATION" option makes it possible for
> replication agents ,let's say, to insert rows without being
> constrained by IDENTITY restriction or check constraints(And also it
> does not change SEED value in the table).
> That means, being a replication agent is like having a PASS CARD that
> allows them to get rid of constraint checks. (But disabling
> constraints is not a solution)
> But, during conflict resolution coding, I need to INSERT some data to
> some tables (related tables), but I am facing CONSTRAINTS that merge
> agent does not face.
> Is there any way of pretending to be like merge_agent for constraint
> overcoming? THIS IS THE CORE QUESTION.
> To be more specific, is there any way of setting sessionproperty for
> replication_agent? That is: when you execute the following query in a
> normal connection;
> select sessionproperty('replication_agent')
> you get "0".
> but for replication_agent that function returns "1". Microsoft guys
> somehow set connection property for agent, and how they do it is
> scritly undocumented.
> I tried setting context_info in sysprocesses table to "8" but it did
> not work. That SP also is not enough by itself
> :sp_MSsetcontext_replagent
> Please help, if any way of pretending to be merge agent.
> PS:I event wrote an application with ReplMerg.exe process name
> guessing that SQL may know agent from its process name.
> And also please don't suggest disabling constraints because it is not
> enough, e.g. replication_agent does not affect SEED value for
> inserts that it does..
> I hope some REAL expert will hear my SCREAM.
> Thanks alot in advance,
> Nury SWORD
> MCDBA - MCSD
> Toronto
|||I finally found a merge replication guru guy. He is actually a
contractor in Toronto and selling a special component which is totally
able to pretend to be merge agent.
It is simple to use but may be a little bit expensive for start-up
companies. Since we desperately needed that feature the company just
paid for it instead of digging for weeks over weeks.
It gets SQL Server credentials as class properties and you call
ExecuteSQL method, it executes it as if it is merge agent.
For example I can execute the following SQL against my DB using that
component:
INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
test')
and it works!!
you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
constraints.
If you need to contact that merge replication consultant just send me
an email.
Nury Sword
NurySword@.hotmail.com
MCSD - MCDBA
Toronto
|||Nury,
as far as I understand, it is not possible to take the context of the merge
agent. Are you sure that this is how the component works?
Do you have to 'tell' the component what table you are working with? If so,
it might just be doing some dynamic SQL:
exec('set identity_insert tcompany on;insert into tcompany(id, companyname)
values(34,''test'');set identity_insert tcompany off')
Can you send me the details of this consultant as I'd like to ask him about
it.
Regards,
Paul Ibison (SQL Server MVP)
"Nury" <nurysword@.hotmail.com> wrote in message
news:1106863095.810873.7130@.z14g2000cwz.googlegrou ps.com...
> I finally found a merge replication guru guy. He is actually a
> contractor in Toronto and selling a special component which is totally
> able to pretend to be merge agent.
> It is simple to use but may be a little bit expensive for start-up
> companies. Since we desperately needed that feature the company just
> paid for it instead of digging for weeks over weeks.
> It gets SQL Server credentials as class properties and you call
> ExecuteSQL method, it executes it as if it is merge agent.
> For example I can execute the following SQL against my DB using that
> component:
> INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
> test')
> and it works!!
> you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
> constraints.
> If you need to contact that merge replication consultant just send me
> an email.
> Nury Sword
> NurySword@.hotmail.com
> MCSD - MCDBA
> Toronto
>