Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Henrik Svensson
Starting Member
25 Posts |
 Posted - 2004-10-18 : 14:08:39
|
| Hi!I am using WinXP SP2, SQL Server 2000 - 8.00.760. I am replicating a table with transactional replication and have run into some problems.In the Publication Properties->Articles->Table Article Properties->Snapshot->"Copy objects to destination" I am for some reason not allowed to unselect the checkbox "Nonclustered indexes". I want to unselect this property, since I don't want subscribers to automatically get the same indexes as I use in the published table. I have tried to script the publication and changing the @schema_option of the sp_addarticle stored procedure to a value where I exclude 0x40, described as "Generates corresponding nonclustered index(es)" in Books Online, but with no luck. It just seems to be ignored.What have I missed not being able to accomplish this?With best regards,Henrik Svensson |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-19 : 05:12:25
|
| have you reinitialized the subscription so that the changes will take effect?--------------------keeping it simple... |
 |
|
|
Henrik Svensson
Starting Member
25 Posts |
Posted - 2004-10-19 : 06:43:46
|
| The problem is that I can not even create a publication where I have the option "copy nonclustered indexes" unselected. It's "disabled", yet selected. I don't know why it is disabled and I don't know how to make it enabled so I can unselect this option. As mentioned above I failed when expliciyly setting the @schema_option for sp_addarticle. It is simply ignored. Any ideas why? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-21 : 01:56:33
|
quote:
Originally posted by Henrik Svensson
The problem is that I can not even create a publication where I have the option "copy nonclustered indexes" unselected. It's "disabled", yet selected. I don't know why it is disabled and I don't know how to make it enabled so I can unselect this option. As mentioned above I failed when expliciyly setting the @schema_option for sp_addarticle. It is simply ignored. Any ideas why?
so from what you mentioned, you already tried creating a publication but failed to unselect the said option?that's unusual, you're logon with sa privilege? Have you tried setting up replication in another server, just to test if the option is still disabled? narrow it down to the machine...--------------------keeping it simple... |
|
|
|
Henrik Svensson
Starting Member
25 Posts |
Posted - 2004-10-26 : 13:22:17
|
| I have tried it on two different SQL Server 2000 servers. They both have this option disabled and selected. I am logged on as sa on both. Any ideas where I can look to find out how this behaviour is possible?With best regards,Henrik Svensson-----"Rhubarb, that is Barbapapa backwards. It's called a palindrome." |
|
|
|
colby75
Starting Member
1 Post |
Posted - 2005-02-24 : 09:50:15
|
| This true. The NonClustered indexe checkbox is always disabled and check. Has anyone found a was to get around this? In SQL Server 7, the schema_option work around was @schema_option = 0x0000000000000023 but this doesn't work in SQL 2000.Please let me know,John |
|
|
|
Henrik Svensson
Starting Member
25 Posts |
Posted - 2005-05-03 : 12:49:23
|
| The only way to prevent Nonclustered indexes from being replicated that I have found is to first check the "Include declared referential integrity" checkbox. Then "Nonclustered indexes" is enabled, and thus uncheckable. Using the sp_addarticle or sp_changearticle, You could for example set the @schema_option to 0xCE03. But I haven't found any documentation whatsoever stating why it is implemented this way.With best regards,Henrik Svensson-----"Rhubarb, that is Barbapapa backwards. It's called a palindrome." |
|
|
|
|
|
|
|
|
Unselect replication of nonclustered indexes