December 18, 2011 at 9:27 am
I would like to create two scripts, one to create a publication and a second script to create a subscription. Eventually, I want to place those inside a Visual Studio project so that certain users and use replication to move data from a Test/Development database into the production web site database.
In SSMS, I created a test Publication. I enabled create script. The Replication was created successfully.
To test the new script, I delelted the Publication. I opened a new Query window and the Replication script. When I executed the script I received the following error:
The replication option 'publish' of database 'A' has already been set to true.
Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addpublication_snapshot, Line 123
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".
I have attached the Script as a text file.
Any help you can offer will be appreciated.
Once the Publication Script is working, I will create the Subscription Script in a similar manner.
Thanks,
pat
December 19, 2011 at 4:32 am
Is that script generated using the SSMS option.
It does tend to assume you are starting from scratch to create the publication.
Shouldn't there be accounts that are members of the Publication Access List ???
HTH
Graeme
December 19, 2011 at 5:32 am
Graeme100,
Yes, I am starting from scratch. I have been doing this publication and subscription from scratch for half a year. Now I want to incorporate it into a script.
This script was generated solely by SSMS. And what you see is untouched by me.
I always specify a specific user and password when using SSMS to create the publication. Are you saying that the user and password are not in this script? I think I see where the password goes. I will experiment and repost later.
Thank you.
December 19, 2011 at 3:30 pm
I believe I have found the answer. When creating a script from within SSMS, the script does not contain the code to start the Snapshot agent. Once I added that line, the code worked.
Attached is the finished code to start the Replication, make a snapshot and create a subscription. Look for the line
-- Start the Snapshot Agent job.
EXEC sp_startpublication_snapshot @publication = N'PublishA'
GO
The above line needs to be added to the code generated by SSMS.
Also I replaced some variables.
The @publication originally read, @publication = @publication.
All the @job_passoword = null originally. Be sure to use the N. as in @job_password = N'type_your_password_here'. In my example there were three passwords that had to be assigned. Of course, if you are passing the Passoword to the procedure you will not need to explicitly include the password. In my case, this will be buried inside a Visual Studio project.
December 20, 2011 at 12:25 am
Management studio never (as far as I know) scripts out secure information such as passwords.
The Snapshot agent requires a login and these are scripted out as null and therefore need completing.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply