Pleae Help - SQL 2008 - sp_addsubscription

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

SQL Server 2008 Forums

Replication (2008)

Pleae Help - SQL 2008 - sp_addsubscription

Author

Topic

Magadan1
Starting Member

2 Posts

Posted - 2009-12-22 : 02:30:30

Help please

:

Adding a Subscription using the script below results in error - Cannot insert the value NULL into column 'nosync_type', table 'HFHS_2512_PROD.dbo.syssubscriptions'; column does not allow nulls. INSERT fails.The Distributor & Publisher are in the same server (SQL 2008 SP1)
The Subscription server is also SQL 2008 SP1

(we use CMD mode - the parameters are getting throw OK)
Script:----Add subscription
begin try
exec sp_addsubscription
@publication = N'$(PUBLICATION)',
@subscriber = N'$(SUBSCRIBER)',
@destination_db = N'$(SUBSCRIBERDATABASE)',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

end try
begin catch
print error_message()
end catch

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-22 : 02:41:14

Hi

It seems your table not allow nulls.

Open the table definitions and set the property AllowNull to True of the column you want to insert a Null in your table.

-------------------------
R...

Magadan1
Starting Member

2 Posts

Posted - 2009-12-22 : 03:31:01

Thanks Rajdaksha for your reply.
I did what you suggested :

alter table [syssubscriptions] alter column [nosync_type] [tinyint] NULL

Then I got the same error in the Distribution database, table [MSsubscriptions]
So I did the following:
alter table [MSsubscriptions] alter column [nosync_type] [tinyint] NULL DEFAULT ((0))

And it all worked out fine (no errors , still need to check the Replication process).

Do you think it is wise to alter Replication System tables?
What do you think casused this issue in thr first place?

Thanks again for your help :~)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources