3

This type of error is unfortunately not uncommon. One report of many is this one on Stack Overflow: https://stackoverflow.com/questions/11360331/sql-server-merge-replication-error-the-schema-script-xxx-sch-could-not-be-pro

The problem is that in every report of this error I can find the error message displays a file name ending in ".sch" as part of it, here it displays part of a function that is being replicated.

The function in question is using SUSER_SNAME(), which is also used for filtering rows in the replicated tables.

Imagine that the function in question has the following code (this is an example, I'm not going to post the actual code publicly):

ALTER FUNCTION [dbo].[GetDataForCurrentUser]
(
)
RETURNS TABLE
AS
RETURN
(
    select data_id, data_type_id, data_key, data_value
    from UserDataTable
    where user_name = REPLACE(SUSER_SNAME(), 'OURDOMAIN\', '')
        and data_is_valid = 1
    order by data_key
)

Then the actual error that appears is the following:

The schema script ', '')
        and data_is_valid = 1
    order by data_key
)' could not be propagated to the subscriber

In other words, instead of a file name what appears is the last part of the function in question, starting after the string literal, in single quotes.

It seems like either the use of SUSER_SNAME() is causing the problem, or perhaps the backslash that appears as part of the function. However I am not aware of reasons not to use that function in a user-defined function, or a need to escape backslashes in string literals. Also, the function itself works with no problems when actually called.

Is there a problem with the function definition or is this a bug in replication itself?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.