Errors creating push subscriptions...

Hi All,
Had to recreate our SQLServer instance after a rebulid of
the server. Unfortuantely I was not able to
recreate/reload a backup so had to create from scratch.
In trying to setup replication I have the following error
message trying to create a push subscriptions:
SQL Server Enterprise Manager encountered errors creating
push subscriptions for the following Subscribers:
'FHMYDATABSE: Error 14053: Cannot load the DLL
replincrementlsn extended procedure, or one of the DLLs it
references. Reason: 126(The specified module could not be
found.).
The subscription could not be updated at this time.
The subscription could not be created.'
Any ideas? I tried locating the extended proc mention but
no luck. Any help very much appreciated, as I've had this
problem for about 2 weeks now and the powers that bey want
a swift fix! Thanks...
Davej
Dave,
perhaps your service pack failed to fully install. Have a look at sqlsp.log
file from the c:\windows directory this might shed some light on any failed
actions during the sp installation.
HTH,
Paul Ibison
|||Thanks Paul for the note. I re-ran the sp update and it
must of cured something because the first error has gone
but I get the following!
FHMYDATABASE: Error 515: Cannot insert the value NULL
into column 'step_name', table 'msdb.dbo.sysjobsteps';
column does not allow nulls. INSERT fails.
why? any ideas?
Cheers...

>--Original Message--
>Dave,
>perhaps your service pack failed to fully install. Have a
look at sqlsp.log
>file from the c:\windows directory this might shed some
light on any failed
>actions during the sp installation.
>HTH,
>Paul Ibison
>
>.
>
|||Dave,do you have any remaining subscriptions/jobs from the previous
implementation of replication perhaps? You might try running
sp_remopvedbreplication on the databases involved prior to setting it up
again. If there are any orphaned records in the table
MSreplication_subscriptions on the subscriber database then these can be
deleted. After that, refresh the jobs folder and then implement as per
normal.
HTH,
Paul Ibison
|||Hi Paul,
Did what you suggested. Went right back to basics removing
replication from the database. Then went trough the
procedure using stored procs insetadt of EM. Came to
creating a push sub and got the following, bit more
detailed errors:
Server: Msg 515, Level 16, State 2, Procedure
sp_add_jobstep_internal, Line 129
Cannot insert the value NULL into column 'step_name',
table 'msdb.dbo.sysjobsteps'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
Server: Msg 14266, Level 16, State 1, Procedure
sp_verify_jobstep, Line 46
The specified '@.step_id' is invalid (valid values are:
1..1).
Server: Msg 14070, Level 16, State 1, Procedure
sp_changesubstatus, Line 993
Could not update the distribution database subscription
table. The subscription status could not be changed.
Server: Msg 14057, Level 16, State 1, Procedure
sp_addsubscription, Line 971
The subscription could not be created.
the script is:
use qboard
go
/* adds a publication to the database*/
exec sp_addpublication @.publication = 'Q Published Data
(from Script)'
,@.sync_method = 'native'
,@.repl_freq = 'continuous'
,@.status = 'active'
,@.allow_push = 'true'
go
/* adds a subscription to the publication just defined */
exec sp_addsubscription @.publication = 'Q Published Data
(from Script)'
,@.subscriber = 'FHQDB'
,@.subscription_type = 'push'
go
Is there someting I'm missing?
Thanks for any insights...
Davej

>--Original Message--
>Dave,do you have any remaining subscriptions/jobs from
the previous
>implementation of replication perhaps? You might try
running
>sp_remopvedbreplication on the databases involved prior
to setting it up
>again. If there are any orphaned records in the table
>MSreplication_subscriptions on the subscriber database
then these can be
>deleted. After that, refresh the jobs folder and then
implement as per
>normal.
>HTH,
>Paul Ibison
>
>.
>
|||Davej,
there seems to be some parts of you script missing. Here is a script for
transactional replication of CustomerDemographics:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'Northwind', @.optname = N'publish',
@.value = N'true'
GO
use [Northwind]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'Northwindcds', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of Northwind database from Publisher
PLL-DT-16.', @.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 = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'PLL-DT-16-Northwind-4'
exec sp_addpublication_snapshot @.publication =
N'Northwindcds',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 222700,
@.active_end_time_of_day = 0, @.snapshot_job_name =
N'PLL-DT-16-Northwind-Northwindcds-10'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'PLL-DT-16\xxx
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'domain\vvv'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'Northwindcds', @.article =
N'CustomerDemographics', @.source_owner = N'dbo', @.source_object =
N'CustomerDemographics', @.destination_table = N'CustomerDemographics', @.type
= N'logbased', @.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3, @.status =
16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
sp_MSupd_CustomerDemographics', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'Northwindcds', @.article = N'all',
@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
HTH,
Paul Ibison
|||Paul,
with these stored procs, do you need to specify all the
prameters or can some be defaulted? I'll use your example
as a template...
Thanx
davej
>--Original Message--
>Davej,
>there seems to be some parts of you script missing. Here
is a script for
>transactional replication of CustomerDemographics:
>-- Enabling the replication database
>use master
>GO
>exec sp_replicationdboption @.dbname = N'Northwind',
@.optname = N'publish',
>@.value = N'true'
>GO
>use [Northwind]
>GO
>-- Adding the transactional publication
>exec sp_addpublication @.publication = N'Northwindcds',
@.restricted =
>N'false', @.sync_method = N'native', @.repl_freq =
N'continuous', @.description
>= N'Transactional publication of Northwind database from
Publisher
>PLL-DT-16.', @.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 =
336,
>@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder
= N'true',
>@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login
= N'anonymous',
>@.allow_dts = N'false', @.allow_subscription_copy =
N'false',
>@.add_to_active_directory = N'false', @.logreader_job_name =
>N'PLL-DT-16-Northwind-4'
>exec sp_addpublication_snapshot @.publication =
>N'Northwindcds',@.frequency_type = 4, @.frequency_interval
= 1,
>@.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 1,
>@.frequency_subday = 1, @.frequency_subday_interval = 0,
@.active_start_date =
>0, @.active_end_date = 0, @.active_start_time_of_day =
222700,
>@.active_end_time_of_day = 0, @.snapshot_job_name =
>N'PLL-DT-16-Northwind-Northwindcds-10'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'BUILTIN\Administrators'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'distributor_admin'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'PLL-DT-16\xxx
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'domain\vvv'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'sa'
>GO
>-- Adding the transactional articles
>exec sp_addarticle @.publication = N'Northwindcds',
@.article =
>N'CustomerDemographics', @.source_owner = N'dbo',
@.source_object =
>N'CustomerDemographics', @.destination_table =
N'CustomerDemographics', @.type
>= N'logbased', @.creation_script = null, @.description =
null,
>@.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status =
>16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
>sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
>sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
>sp_MSupd_CustomerDemographics', @.filter = null,
@.sync_object = null,
>@.auto_identity_range = N'false'
>GO
>-- Adding the transactional subscription
>exec sp_addsubscription @.publication = N'Northwindcds',
@.article = N'all',
>@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep',
@.sync_type =
>N'automatic', @.update_mode = N'read only', @.offloadagent
= 0,
>@.dts_package_location = N'distributor'
>GO
>
>HTH,
>Paul Ibison
>
>.
>
|||Dave,
the vast majority can be missed out - too many to mention, but details in
BOL (see sp_addpublication, sp_addarticle). You could get EM to generate the
script from another server if you restore your database and create the
publication there, and want to use scripts.
Regards,
Paul Ibison