none
Getting error when synchronizing Subscriber!

    Question

  • I am new to SQL Server, but am quick to learn and I know where to find answers. However, I have found this issue on this and other forums and I have tried the solutions and am still getting a failure. This is the following error I get:

    The schema script 'ap_OnePermissidbf20687_253.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
    Get help: http://help/MSSQL_REPL-2147201001
    Invalid column name 'CompanyID'. (Source: MSSQLServer, Error number: 207)
    Get help: http://help/207

    It appears that the tables and views and everything are copying over, but I am never able to get a full sync done without running into these errors. 

    To give a quick background, we have two newly built SQL servers that I restored a backup of another database onto the Distributor/Publisher server. I have gone through the Distribution Wizard and Publication wizard and the snapshot creates with no issues. I have set the Replication location as a shared UNC name and can access it from another terminal using the log in that I am using for the process login.

    Any help is greatly appreciated as I have been slamming my head against this same wall for over a week now. If any more info is needed I would be happy to supply that.

    Thanks,

    Robert

    Wednesday, September 18, 2013 5:20 PM

Answers

  • Try running this proc to find all the errors in your procs.  It will recompile and report any errors.

    CREATE PROC [dbo].[usp_RefreshAllProcs]
    AS
    SET NOCOUNT ON;
    
    -- table variable to store procedure names
    DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)
    
    -- retrieve the list of stored procedures
    INSERT INTO @v(spname)    
    	SELECT '[' + s.[name] + '].[' + sp.name + ']'     
    	FROM sys.procedures sp    
    	INNER JOIN sys.schemas s ON s.schema_id = sp.schema_id    
    	WHERE is_ms_shipped = 0
    		AND sp.name NOT LIKE 'sp_MS%'
    	ORDER BY  s.[name], sp.name
    
    -- counter variables
    DECLARE @cnt INT, @Tot INT
    
    SELECT @cnt = 1
    SELECT @Tot = COUNT(*) FROM @v
    
    DECLARE @spname sysname
    -- start the loop
    
    WHILE @Cnt <= @Tot 
    BEGIN    
    	SELECT @spname = spname        
    	FROM @v        
    	WHERE RecID = @Cnt    
    
    	PRINT 'Refreshing...' + @spname    
    	BEGIN TRY        
    	-- refresh the stored procedure        
    		EXEC sp_refreshsqlmodule @spname    
    	END TRY    
    	BEGIN CATCH        
    		PRINT '     Validation failed for : ' +  @spname + ', Error:' + ERROR_MESSAGE()    
    		IF @@TRANCOUNT > 0 ROLLBACK TRAN
    	END CATCH    
    	SET @Cnt = @cnt + 1
    END
    

    Wednesday, September 18, 2013 6:52 PM
  • Hi Robert,

    The problem was that the files in the snapshot with .sch are script files that creates the schema of a table and for some reason generates an error.Can you locate this file 'ap_OnePermissidbf20687_253.sch' in your snapshot folder, and manually run it at the subscriber to see what the error is? Additionally,please try to use Tom's codes to check your internal specific stored procedure and check whether it exists some logic error.

    Thanks

    Candy Zhou

    Friday, September 20, 2013 3:38 AM

All replies

  • This looks like a stored procedure. Can you run this stored procedure on the publisher?

    What happens if you remove this from the publication?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, September 18, 2013 6:01 PM
    Moderator
  • It appears that there are several stored procedures that are not being used or have been modified by other users and not updated in our performance records. I have found some culprits and I am going to attempt to filter these out.

    I will let you know if this works or if I run into more issues.

    Wednesday, September 18, 2013 6:13 PM
  • Try running this proc to find all the errors in your procs.  It will recompile and report any errors.

    CREATE PROC [dbo].[usp_RefreshAllProcs]
    AS
    SET NOCOUNT ON;
    
    -- table variable to store procedure names
    DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)
    
    -- retrieve the list of stored procedures
    INSERT INTO @v(spname)    
    	SELECT '[' + s.[name] + '].[' + sp.name + ']'     
    	FROM sys.procedures sp    
    	INNER JOIN sys.schemas s ON s.schema_id = sp.schema_id    
    	WHERE is_ms_shipped = 0
    		AND sp.name NOT LIKE 'sp_MS%'
    	ORDER BY  s.[name], sp.name
    
    -- counter variables
    DECLARE @cnt INT, @Tot INT
    
    SELECT @cnt = 1
    SELECT @Tot = COUNT(*) FROM @v
    
    DECLARE @spname sysname
    -- start the loop
    
    WHILE @Cnt <= @Tot 
    BEGIN    
    	SELECT @spname = spname        
    	FROM @v        
    	WHERE RecID = @Cnt    
    
    	PRINT 'Refreshing...' + @spname    
    	BEGIN TRY        
    	-- refresh the stored procedure        
    		EXEC sp_refreshsqlmodule @spname    
    	END TRY    
    	BEGIN CATCH        
    		PRINT '     Validation failed for : ' +  @spname + ', Error:' + ERROR_MESSAGE()    
    		IF @@TRANCOUNT > 0 ROLLBACK TRAN
    	END CATCH    
    	SET @Cnt = @cnt + 1
    END
    

    Wednesday, September 18, 2013 6:52 PM
  • Hi Robert,

    The problem was that the files in the snapshot with .sch are script files that creates the schema of a table and for some reason generates an error.Can you locate this file 'ap_OnePermissidbf20687_253.sch' in your snapshot folder, and manually run it at the subscriber to see what the error is? Additionally,please try to use Tom's codes to check your internal specific stored procedure and check whether it exists some logic error.

    Thanks

    Candy Zhou

    Friday, September 20, 2013 3:38 AM