Posts

Showing posts from December, 2010

search text in Stored Procedures

Do you want to find any text in Stored Procedures in SQL Server Database ?
"select * from INFORMATION_SCHEMA.ROUTINES" returns Procedure,Function exist in specific database. You will see objects and details in columns.
P.S:Please do not hesitate to share your comments.

Notes about Merge Replication

some usefull stored procedures you may use during merge replication;
* - EXEC sp_adjustpublisheridentityrange @publication='Publication_Name' //adjust identity range,if you get any error related with identity renge , you can reset identity offset managed by agent
* - EXEC sp_helpmergearticle @publication='Publication_Name' //retrieve article(s) information in given Publication_Name
* - EXEC sp_changemergearticle @publication='Publication_Name', @article = 'Article_Name' , @Property = 'identityrangemanagementoption', //What would you like to do.ie disable identity management by agent @Value = 'Manual', @force_invalidate_snapshot = 0
//change article property ( http://msdn.microsoft.com/en-us/library/ms174386.aspx )


ERROR MESSAGE :
The Publisher failed to allocate a new set of identity ranges for the subscription.
This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization.
If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
EXECUTE : sp_adjustpublisheridentityrange @publication='PUBLISHED_DATABASE_NAME' (ref : http://msdn.microsoft.com/en-us/library/ms181527.aspx )

set Not For Replication True via Stored Procedure

Some administrator may not prefer to use SQL Server Management Studio for the strictions such as timeout or following Foreign Key exists;
To set or UnSet "Not for Replication" property of any column you want to use in Replication;
DECLARE @ObjectID INT = OBJECT_ID('dbo.tFileProvider') //get Object Id

execsys.sp_identitycolumnforreplication @object_id = 246291937, @value = 1 //0 : No ; 1 : yes