.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Server 2005 Transactional Replication Fails to Publish Stored Procedure Containing an Index Crea

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

I've experienced a bizarre problem with a SQL Server 2005 Transactional Publication. The issue is this: If the publication contains an article that is a stored procedure that contains a create index statement, then there is an error thrown when attempting to replicate the schema of the stored procedure to a subscriber.

The behavior is very odd, because even if the create index statement is commented out, it still gives the exception, and it will only work if it is removed altogether.

Here is the exact error that's being returned:

Command attempted: GRANT EXECUTE ON [dbo].[usp_Test] TO [CompanyDatabase_access]

(Transaction sequence number: 0x00000170000008B9000500000000, Command ID: 5)

Error messages: Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151 Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151

The error is accurate, because when I check on the subscriber, the stored procedure wasn't created as expected... but that was the purpose of the publication...

View Complete Post

More Related Resource Links

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......


@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS









FROM [ReportQueries]


[UserID] = @UserID

how to create stored procedure in sql server 2005 and how to use that stored procedure in asp.net


Hi friends ,

       I am very newbie

I have created stored procedure and student database and also asp.net application for asp.net page

but it could not found stored procedure what is the mistake actually I don't no

Please help me

Warm Regards


SQL 2005 Merge Replication : sysmergepublications unique index 'nc1sysmergepublications' fails

I've got one SQL 2005 SP2 server setup as Publisher and distributor which replicates through Merge pull subscriptions on SQL 2000 SP4 machines. Some of these SQL 2000 machines does republishing too. However, after a columndrop on one of the articles at the SQL 2005 machine by using sp_repldropcolumn the whole subscription sync came down on me with failures all over. All had the same error :

"Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'"
This procedure was called by sp_MScreateglobalreplica.

I have found that there are invalid publications in sysmergepublications, but only on publishers for the SQL 2000 subscription machines(excluding valid republishing publications). Deleting these pubID's does not help at all. With the first sync of any pull subscription (after the delete) they re-appear on the SQL 2005 machine.

However, I need to get the pull subscriptions going again. How can I clean the system tables to prevent any errors like above or duplicate keys in the sysmergesubscription table... Please do not recommend an overhaul. I need step by step procedures to get it going.

CRUD Stored Procedure Code/Scripts Generator for SQL SERVER 2005


I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.

Anyone have a solution?

Thanks in Advance.

SQL Server 2005 Grant User Permission to Create Stored Procedure and Grant Execute Access to another


I'm trying to determine the minimal amount of access I can give my developers to be able to run create stored procedure scripts with an inclusion of the grant execute to the application user group.

We have been developing some interesting work arounds, but I think there must be an easier way to grant the access.

Grant Create Stored Procedure to user1 on a specific database

Grant Permission to Grant Execute permission for Stored Procedures on a specific Database to user1 for any user.


SQL Server 2005 SP4 Install Fails - error log references replication system tables


The OS is Windows Server 2003 R2 SP2 (32 bit).  Trying to install SQL Server 2005 SP4 (current version is 9.00.4035).  All SQL products update without error, except for the Database engine.  The error listed in the summary.txt file in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix is:

Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 5000
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log
Error Number              : 29537
Error Description         : MSP Error: 29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Update or insert of view or function 'dbo.syspublications' failed because it contains a derived or constant field.. To continue, correct the problem, and then run SQL Serv

Calling stored procedure from funciton in sql server 2005


Is there any way to call stored procedure from funciton in sql server 2005?

If it is possible, please give some examples.



RAHUL Dot Net Developer

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

SQL Server 2005 Merge Replication

We did the following: We setup a database with all of standard tables, views, etc. We then put in place merge replication for a few of the tables within this database, including a tables called "Areas". We can execute SQL statements to insert rows into this table fine. We then applied an update script for this database to change a few tables by adding fields and changing indexes, etc.  We didn't change the "Areas" table though. After the update to the database structure, we get an error when trying to insert into this "Areas" table. The error message was the following: The insert failed. It conflicted with an identity range check constraint in database 'AETest', replicated table 'dbo.Areas', column 'AREPrimaryId'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.  The statement has been terminated.   What would cause such a problem and how do we resolve it?  The structure of the "Areas" table before and after the update is the following: USE   [AETest] GO /****** Object: Table [dbo].[Areas] Script Date: 08/24/2010 17:42:36 ******/ SET   ANSI_NULLS ON GO SET   QUOTED_IDENTIFIER ON GO SET   ANSI_PADDING ON

Replication between SQL Server 2005 and SQL Server 2008

Hi,    We were using SQL Server Transactional Replication between two SQL Server 2005 servers. Now we are going to upgrade the Publisher server to SQL Server 2008, Distributor Server remains as SQL Server 2005.     Can anyone let me know what is the challenges we need to face when configuring Replication between SQL Server 2005 and SQL Server 2008. Replication Method - Transactional Replication Distributor/Subscriber - SQL Server 2005 Publisher     -  SQL Server 208 Regards, Fazlu.

Transactional publication with updatable subscriptions in SQL Server 2005, SvPk 2

Subscription to "Transactional Publication with Updateable Subscriptions" works only one way.  Changes take effect on subscriber, but the subcriber is unable to update data on publisher.   I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.'  All agents are running at the Distributor (Publishing Server.)   The subscriber is unable to connect to the Distributor using the SQL Server login.    Following is the error message I get:   Creating Subscription(s)... - Creating subscription for 'SQL3' (Warning) Messages Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------MSDTC on server 'SQL3' is unavailable.Changed database context to 'DB_SQL1_to_SQL3_on_3'. (Microsoft SQL Server, Error: 8501)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476   Please suggest procedure to end this dilemma.  The link has no info available.

SQL server 2005 SP2 KB921896 fails to install

Machine Specs: Windows XP SP3 Over 104GB HDD space available   When running the SP2 update I get "ngen.exe - Application Error" "The application failed to initialize properly (oxc0000005) Click ok.... Hit Ok it pops up a second time, hit Ok a small unexpected error message box pops up with no error code   I have tried a complete reinstall of SQL server 2005, I've tried stopping and restarting SQL services.   Reviewing the HotFix log... It fails when it attempts to pause the ngen queue 09/02/2010 09:00:29.984 Registry: Read registry key value "CustomerFeedBack", DWORD value = 0 09/02/2010 09:00:30.609 Registry: Set registry key value "EnableErrorReporting", DWORD value = 0 09/02/2010 09:00:30.609 Registry: Set registry key value "EnableErrorReporting", DWORD value = 0 09/02/2010 09:00:30.609 Registry: Set registry key value "EnableErrorReporting", DWORD value = 0 09/02/2010 09:00:30.609 Registry: Set registry key value "CustomerFeedBack", DWORD value = 0 09/02/2010 09:00:30.609 Registry: Set registry key value "CustomerFeedBack", DWORD value = 0 09/02/2010 09:00:30.609 Registry: Set registry key value "CustomerFeedBack", DWORD value = 0 09/02/2010 09:00:30.640 Locked file: Checking for locked files 09/02/2010 09:00:30.906 Locked file: C:\WINDOWS\system32\sqlncli.dll (MSSQLSERVER

Issue with transactional replication on SQL 2005 SP3 using row filters with bit columns

We recently upgraded from SQL 2000 to SQL 2005 SP3 and I am noticing something that I think is a bug with replication from a publication that has an article in it with row filters.  It seems to be that if the row filter has columns that are bit datatype and that column is updated on the publisher to cause the row filter to exclude it, it is not removed from the subscriber.  If you update a column that is part of the row filter that is not a bit datatype it works as expected.  I can reproduce this in a test environment with a small base table.  In production this is resulting in an occasional 20598 error "The row was not found at the Subscriber when applying the replicated command". Is anyone aware of a hotfix that addresses this? I can think of a few work arounds but the table is rather large and I really would like to avoid having to pull replication, re-snap or changing datatypes on the base table etc. Any suggestions are greatly appreciated.  Thanks!

SQL Server 2008 Stored Procedure output parameter

I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

SQL Server 2005 Express Edition - GUI to set permissions on stored procedures

Hi there, I have SQL Serve 2005 Express Edition (Build 2600: Service Pack 3) installed; I also have the Management Console installed. My problem is that I cannot set execute permission, or any other type of permissions on my stored procedures through the GUI, as the Property menu item is missing from the right click menu. I read somewhere that this happens when you have SP1, but as I stated above I have got SP3 installed... Any help?   Regards, D.

Using SQL Server Replication for SQL Server 2005 and 2008

I have an ASP.NET application that uses replication, and uses the RMO DLLs to do this. I initially built the application against the 2005 version of the DLLs and have it running successfully against SQL Server 2005. However, when I try running it against SQL Server 2008 it doesn't work. Initially I didn't deploy the Microsoft.SqlServer.* DLLs with my app - instead relying on them being in place on the server on which it was installed. However, when trying to make it work against SQL Server 2008 it was looking for the 2005 version DLLs which it wasn't able to locate. Following some other advice I read online I have now swapped out the 2005 DLLs and referenced the 2008 assemblies in my .NET project. In my MSI I now also deploy (i.e. redistribute) the SQL Server 2008 DLLs to the bin folder of my ASP.NET project. Now, when I try to run the application against SQL Server 2005 it fails to load the Microsoft.SqlServer.Replication assembly. I run depends.exe against this and find that it's dependent on SQLNCLI10.DLL which is missing on my machine with only SQL Server 2005 on it. So, what am I to do? Is this possible? How do I get around this?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend