replication - default values replicating

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.

All Forums

General SQL Server Forums

New to SQL Server Administration

replication - default values replicating

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-09-09 : 14:40:34

Hi Experts,
i've setup replication (transactional) which is working fine, except default values and non-clustered indexes are not replicating. i changed the status to true in publication propeties "for both copy default values as well as for copy non clustered indexes".

it is asking for generating a new snapshot, and i accepted, and generated it. but again, while checking the properties, these two values are set back to false automaticlly. please help. this is a prod issue.

Arnav
Even you learn 1%, Learn it with 100% confidence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 14:46:37

I always enable copying the non-clustered indexes. You do not need to do a snapshot, just manually copy them over. And then next time you rebuild replication, make sure you set that option.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 14:46:54

Why do the default values matter?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-09-09 : 14:51:11

i'm not sure how that matter, but business is asking for default values are not replicating.

cant i change it from false to true? please tell me a way to do that

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-09-09 : 14:55:32

how can i copy them manually? can you provide me some script please

Arnav
Even you learn 1%, Learn it with 100% confidence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 14:56:37

Generate the index script from SSMS.

I've never used the default values option in replication and yet we've never had a problem with the defaults. So it's outside of my area of expertise.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 14:58:17

Are you referring to default bindings?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-09-09 : 15:02:40

no, i'm referring to default values for not null columns, and non clustered indexes

Arnav
Even you learn 1%, Learn it with 100% confidence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 15:04:23

But what option is it called in the article properties? I don't see it in there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-09-09 : 15:22:08

copy non-clustered indexes,
copy default value specification (immediate after "copy non-clustered indexes")

Arnav
Even you learn 1%, Learn it with 100% confidence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 15:24:59

Not sure how I missed it!

Anyway, we keep it as false and don't have any issues. But maybe it's because the application/sproc code isn't relying on the default values although I know we've got a ton of default constraints.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 15:25:24

But at any rate, you can manually copy over the defaults too. Use SSMS to generate the scripts for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-09-09 : 15:55:02

i did it. everything is fine now. thanks a lot sql server expert (you are Queen of sql server)

Arnav
Even you learn 1%, Learn it with 100% confidence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 15:57:02

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources