Replication Without Generating Snap Shot- step by step

--STEP1 - @PUBLISHER
select immediate_sync , allow_anonymous from syspublications

--STEP2 - @PUBLISHER
EXEC sp_changepublication @publication = 'DBNAME', @property =

N'allow_anonymous', @value='False'

Go

EXEC sp_changepublication @publication = 'DBNAME', @property =

N'immediate_sync', @value='false'

Go

--STEP3- @PUBLISHER DB
select immediate_sync , allow_anonymous from syspublications


--STEP4 ADD ARTICLES --@PUBLISHER DB

EXEC sp_addarticle @publication = 'DBNAME', @article ='ctxxxxxxxxxx',

@source_object='ctxxxxxxxx', @force_invalidate_snapshot=1

--STEP5 Verify if you are using CONCURRENT or NATIVE method for synchronization by running the following command.

select sync_method from syspublications --Here its 3

--STEP6 If the value is 3 or 4 then it is CONCURRENT and if it is 0 then it is NATIVE.If you are using the NATIVE method for synchronization then the parameter

--@reserved=’Internal’ is optional but there is no harm in using it anyways. But if it is CONCURRENT then you have to use that parameter. Else the next time you run the snapshot agent it is going to generate a snapshot for all the articles.

EXEC sp_addsubscription @publication = 'DBNAME', @article = 'ctxxxxxxxxx',

@subscriber ='PRA*******\SQL**', @destination_db = 'DBNAMEKKK',

@reserved='Internal'



--STEP 7
--start the SNAPSHOT AGENT job from the job activity monitor.

--STEP 8

--Verify that the snapshot was generated for only one article.

--REPEAT ADD artical and generate snapshot


-- Generate the snapshot
--http://thinknook.com/sql-replication-the-initial-snapshot-for-publication-is-not-yet-available-2012-04-19/

/*


    The Snapshot Agent did not run after a new subscription has been created: Basically if a new subscription is created, the Snapshot Agent needs to run once to generate a recent snapshot, in order to initialize a subscription.

    The Snapshot Agent did not run after a subscription has been initialized: Similar scenario to the above, if a subscription has been initialized you need to run the Snapshot Agent and generate a recent snapshot.

    A database that contains a subscription has been restored: If a database has been restored, you will need to initialize the subscription by generating a new Snapshot.


    */

Could not load file or assembly 'Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

Error:

[Execute SQL Task] Error: Executing the query "Exec sp_NNNNNNNNN" failed with the following error: "An error occurred in the Microsoft .NET Framework while trying to load assembly id 65547. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 'XXXXXXXX, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:

   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.Load(String assemblyString)

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Solution :-

1. Go to Database

2. Expand Programmability

3. Expand Assemblies

4. Choose your assembly XXXXX

5. Right Click and go to properties

6. Change the Permission Set option to External

Note :- Make sure DB is in trustworthy, check options from properties. If its false 

ALTER DATABASE DBNAME SET TRUSTWORTHY ON

This will resolve.

Some times it will throw eerror with DB owner SIS issue for alter authorization, due to mismatch in SID of DB and Master db entry.

Change your DB owner to SA. then repeat step same as stated above. Will help to resolve issue.

EXEC sp_changedbowner ‘sa’

 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

The operation could not be completed.

------------------------------
ADDITIONAL INFORMATION:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

------------------------------

Solution :- http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

Kill Windows Seesions Remotly

To kill the sessions follow below sequence.

Step 1:  qwinsta /server: [ServerNAME]
Step 2: rwinsta [Session ID] /server: [ServerNAME]

The second step will take small amount of time, as it needs to close the open sessions.

The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

Step1 :- Go to the properties account
Step2:- Choose User Mapping
Step 3:- Choose Respective DB
Step 4:- Remove db_denydatareader (uncheck)
Step 5:- Click Ok and Refresh


Another Issue :- http://blog.sqlauthority.com/2012/06/19/sql-server-solution-user-not-able-to-see-any-user-created-object-in-tables-security-and-permissions-issue/

A snapshot was not generated because no subscriptions needed initialization

Thanks To :- http://social.msdn.microsoft.com/Forums/lv/sqlreplication/thread/12c21ca8-7031-4a9e-b813-0122f59a7b9d

Adding new article without generating a complete snapshot :




1) Make sure that your publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE or 0.



Use yourDB

select immediate_sync , allow_anonymous from syspublications



If either of them is TRUE then modify that to FALSE by using the following

command



EXEC sp_changepublication @publication = 'yourpublication', @property =

N'allow_anonymous', @value='False'

Go

EXEC sp_changepublication @publication = 'yourpublication', @property =

N'immediate_sync', @value='false'

Go



2) Now add the article to the publication



Use yourDB

EXEC sp_addarticle @publication = 'yourpublication', @article ='test',

@source_object='dbo.test', @force_invalidate_snapshot=1



If you do not use the @force_invalidate_snapshot option then you will receive the

following error

Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99

Cannot make the change because a snapshot is already generated. Set

@force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.



3) Verify if you are using CONCURRENT or NATIVE method for synchronization by running the following command.



Use yourdb

select sync_method from syspublications



If the value is 3 or 4 then it is CONCURRENT and if it is 0 then it is NATIVE.

For more information check

http://msdn.microsoft.com/en-us/library/ms189805.aspx



4) Then add the subscription for this new article using the following command



EXEC sp_addsubscription @publication = 'yourpublication', @article = 'test',

@subscriber =‘subs_servername', @destination_db = 'subs_DBNAME',

@reserved='Internal'



If you are using the NATIVE method for synchronization then the parameter

@reserved=’Internal’ is optional but there is no harm in using it anyways. But if it is CONCURRENT then you have to use that parameter. Else the next time you run the snapshot agent it is going to generate a snapshot for all the articles.



Lastly start the SNAPSHOT AGENT job from the job activity monitor. To find

the job name follow these steps.



· select * from msdb..sysjobs where name like '%yourpublication%'

· Right click on each of those jobs and find which one contains the step

‘Snapshot Agent startup message’. This is the job that you want to

start from the first step.



Verify that the snapshot was generated for only one article.

Adding Artical To T-Repl

Step 1  :- Add Artical


USE [Publication DB NAME]
GO

EXEC sp_addarticle
    @publication = '[Publication Name]',
    @article = N'[ARTICAL NAME]',
    @source_object = N'[ARTICALNAME]',
    @force_invalidate_snapshot=1
GO


Step 2:-


EXEC sp_changepublication
@publication = '[publication  name]',
@property = N'allow_anonymous',
@value = 'false'
GO


EXEC sp_changepublication
@publication = '[publication name]',
@property = N'immediate_sync',
@value = 'false'
GO

Step 3:- Run the snapshot agent either from replication monitor or from agent.

Step 4: Make Flags true used in Step 2

More Help on :- http://support.microsoft.com/kb/830210

http://dba.stackexchange.com/questions/12725/add-article-to-transactional-publication-without-generating-new-snapshot