Replication Monitor didn't work because different collations

[Katja]

Hi,

We have setup WinServer 2003, SQL 2005 filtered merge replication.

We couldn't get Replication Monitor work at our new testing server.
It didn't show the publication we had created.
Finally we realized it was because we had different collations at server
level and at user database/distribution db. ("SQL_SwedishStd_Pref_CP1_CI_AS"
and "Finnish_Swedish_CI_AS").
I guess there is no way to use SQL_SwedishStd_Pref_CP1_CI_AS collation at
normal setup (maybe command prompt setup could...)
We changed collations to match the server collation and Replication monitor
started to work, great.

After this change we started all (4) publication snapshots.
3 went ok, 1 didn't.
We deleted that problem publication and tried to create it again. No luck.
We can't add any articles to the publication.

Does anybody have any ideas what this error could mean?
Perhaps that collation change broke something?

Creating Publication
- Creating Publication 'Keskus96' (Success)
* SQL Server created publication 'Keskus96'.
- Adding article 1 of 1 (Error)
Messages
* SQL Server Management Studio could not create article 'BXB'. (New
Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not resolve expression for schemabound object or constraint.
Changed database context to 'MK2000'. (Microsoft SQL Server, Error: 2791)

- Starting the Snapshot Agent (Stopped)

Any ideas would be appreciated. Thanks.
-Katja

[Hilary Cotter]

can you enable logging to see which object it is breaking on? Then post the
DDL for this object.

Here's a link on how to do that.
http://support.microsoft.com/kb/312292/en-us

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Katja" <Ka...@discussions.microsoft.com> wrote in message
news:8CC06C48-F74F-4BBE...@microsoft.com...

[Katja]

Hi Hilary,

I couldn't set the agent logging on. It refused to set "-Output "-parameter.
"should be integer". One more bug maybe :(

This is the original errror message when we still had publication with
articles in it.
It is probably trying to create some views.

37 2006-07-04 11:48:54.290 0 5 MSSQL_ENG 2791 Message: Could not resolve
expression for schemabound object or constraint. Command Text:
sp_MScreate_article_repl_views Parameters: @publication = Keskus96
Stack: at
Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e,
SqlCommand command) at
Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand
command, Int32 queryTimeout) at
Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate
commandSetupDelegate, Int32 queryTimeout) at
Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.CreateArticleReplViews()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() NULL NULL 0
37 2006-07-04 11:48:54.290 0 5 MSSQL_ENG 2791 Server MLBL013, Level 16,
State 5, Procedure
MSmerge_repl_view_1C25A131141743E58DFFFEFF4CD21B7A_03B5B623081441359A62E54DBEE8E915,
Line 1 Could not resolve expression for schemabound object or
constraint. NULL NULL 0

-Katja

[Raymond Mak [MSFT]]

Hi Katja,

If the base table has computed columns, there may be problems with resolving
the column meta-data if they reference non-existent functions etc. To find
out if this is the case, try doing a simple select from the base table where
1 = 0 and see if you get the same error back. It is also possible that the
[join] filter that you have defined on the articles have references to
non-existent functions so you may want to look them over and see if you can
find anything suspicious. Of course, there can also be bugs in our code so
if you cannot find anything new upon further investigation, we would
appreciate if you can post the base table schema and\or the [join] filter
definitions here so we can reproduce the problem. Better yet, you can log a
bug at the Microsoft Connect site
(http://connect.microsoft.com/site/sitehome.aspx?SiteID=68 registration
required).

The -Output parameter accepts a file path while the -OutputVerboseLevel
parameter accepts an integer, these parameters can only be specified on the
agent command line (including the command line persisted with the agent job
step) but not in the agent profile for now simply because the agent would
have set up the output streams by the time it tries to retrieve the profile
parameters in the current design. I am rather interested in knowing the
details of how you ended up getting the "should be integer" error when
specifying the -Output parameter and so it would be really helpful to us if
you can provide more context information.

Hope that helps,

-Raymond

"Katja" <Ka...@discussions.microsoft.com> wrote in message

news:136DF8DA-EEF0-40F3...@microsoft.com...

[Katja]

Hi Raymond,

We do not have any computed columns at the base table and the filter
condition is quite simple (WHERE MK = 96).
I don't get any errors if do "Select * from table where 1=0".
I don't think the problem is at base table or filters.
The publication we had, used to work fine before we changed the collations
at our user db and distribution db.
The other 3 publications which are using same base tables are working fine
(as far as i know).

The -Output parameter:
I tried to create new user-defined Agent profile at Replication Monitor like
the BOL advices.
If these parameters work only from command line, it would be nice to see
some instructions at the BOL (i didn't).
-OutputVerboseLevel = 2 went ok.
-Output C:\ReplLog.txt gave an error:

TITLE: Replication Monitor
------------------------------
Replication Monitor could not save parameters for the agent profile.
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.UI.ReplicationDialogErrorSR&EvtID=CantSaveParametersForAgentProfile&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------

'C:\ReplLog.txt' is not a valid value for the '-Output' parameter. The value
must be an integer.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21805)

Thanks for your answers,
-Katja

[Raymond Mak [MSFT]]

Hi Katja,

This looks like a bug until proven otherwise, it would be really helpful to
us if you can use SQL Profiler to trace individual statement of the failing
stored procedure and see if you can get more detail information.

-Raymond

"Katja" <Ka...@discussions.microsoft.com> wrote in message

news:5D9B9730-BA7E-456B...@microsoft.com...

[Katja]

Hi Raymond,

The execption occurs at: sp_MScreate_article_repl_view
Unfortunately i couldn't find a way to send feedback through Microsoft
Connect site (no available Sql programs).

-- Adding the merge articles (This is what used to create the article)
use [MK2000]
exec sp_addmergearticle @publication = N'Keskus96', @article = N'CXB',
@source_owner = N'dbo', @source_object = N'CXB', @type = N'table',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000C034FD1, @identityrangemanagementoption =
N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1,
@column_tracking = N'false', @subset_filterclause = N'Mk = 96',
@vertical_partition = N'false', @verify_resolver_signature = 1,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking =
N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false',
@partition_options = 0
GO

Here is few rows from the Profiler.
EventClass TextData
44 select @repl_view_name = 'dbo.MSmerge_repl_view_' + @pubid_str + '_' +
@artid_str
45 select @repl_view_name = 'dbo.MSmerge_repl_view_' + @pubid_str + '_' +
@artid_str
44 if object_id(@repl_view_name) is not null
45 if object_id(@repl_view_name) is not null
44 select @qualified_table_name = (select
quotename(SCHEMA_NAME(o.schema_id)) from sys.objects o where o.object_id =
v.objid) + '.' + quotename(object_name(v.objid)),
@source_objid = v.objid from dbo.sysmergepartitioninfoview v where
v.artid = @artid and v.pubid = @pubid -- the following will be
true for a light weight subscription
45 select @qualified_table_name = (select
quotename(SCHEMA_NAME(o.schema_id)) from sys.objects o where o.object_id =
v.objid) + '.' + quotename(object_name(v.objid)),
@source_objid = v.objid from dbo.sysmergepartitioninfoview v where
v.artid = @artid and v.pubid = @pubid -- the following will be
true for a light weight subscription
44 if @qualified_table_name is NULL
45 if @qualified_table_name is NULL
44 select @cmd = 'create view ' + @repl_view_name + ' as select * from ' +
@qualified_table_name + ' where ({fn ISPALUSER(''' +
convert(nvarchar(36), @pubid) + ''')} = 1 or permissions(' +
convert(nvarchar(12),@source_objid) + ') & 0x1b <> 0)'

45 select @cmd = 'create view ' + @repl_view_name + ' as select * from ' +
@qualified_table_name + ' where ({fn ISPALUSER(''' +
convert(nvarchar(36), @pubid) + ''')} = 1 or permissions(' +
convert(nvarchar(12),@source_objid) + ') & 0x1b <> 0)'

44 exec (@cmd)

34 create view
dbo.MSmerge_repl_view_501A7E4B71C144BEBD8E4C843DA97236_34AC134D1997471D9B135987608B1E0F
as select * from [dbo].[CXB] where ({fn
ISPALUSER('501A7E4B-71C1-44BE-BD8E-4C843DA97236')} = 1 or
permissions(645577338) & 0x1b <> 0)
33 Error: 2791, Severity: 16, State: 5
162 Could not resolve expression for schemabound object or constraint.

43 exec @retcode = sys.sp_MScreate_article_repl_view @pubid, @artid

43 exec sp_addmergearticle @publication = N'Keskus96', @article = N'CXB',
@source_owner = N'dbo', @source_object = N'CXB', @type = N'table',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000C034FD1, @identityrangemanagementoption =
N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1,
@column_tracking = N'false', @subset_filterclause = N'Mk = 96',
@vertical_partition = N'false', @verify_resolver_signature = 1,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking =
N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false',
@partition_options = 0

12 -- Adding the merge articles use [MK2000] exec sp_addmergearticle
@publication = N'Keskus96', @article = N'CXB', @source_owner = N'dbo',
@source_object = N'CXB', @type = N'table', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x000000000C034FD1, @identityrangemanagementoption = N'none',
@destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking
= N'false', @subset_filterclause = N'Mk = 96', @vertical_partition =
N'false', @verify_resolver_signature = 1, @allow_interactive_resolver =
N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0,
@subscriber_upload_options = 0, @delete_tracking = N'true',
@compensate_for_errors = N'false', @stream_blob_columns = N'false',
@partition_options = 0

- Katja

[Raymond Mak [MSFT]]

Can you try running the select statement in the view and see what happens?

select * from [dbo].[CXB] where ({fn
ISPALUSER('501A7E4B-71C1-44BE-BD8E-4C843DA97236')} = 1 or
permissions(645577338) & 0x1b <> 0)

My suspicion is that either the fn ISPALUSER or the permissions() function
is broken, so if you get the same error again you can try to remove either
one of those and try again. Can you also post the result of 'select
@@version' at the publisher please? Thanks.

-Raymond

"Katja" <Ka...@discussions.microsoft.com> wrote in message

news:BB97813D-032F-42D5...@microsoft.com...

[Katja]

Hi Raymond,

If i run that select statement alone, i don't get any errors.

select * from [dbo].[CXB] where ({fn
ISPALUSER('501A7E4B-71C1-44BE-BD8E-4C843DA97236')} = 1 or

permissions(645577338) & 0x1b <> 0) ...this works

If i run it inside the create view statement i get the error.

create view
dbo.MSmerge_repl_view_501A7E4B71C144BEBD8E4C843DA97236_34AC134D1997471D9B135987608B1E0F as
select * from [dbo].[CXB] where ({fn
ISPALUSER('501A7E4B-71C1-44BE-BD8E-4C843DA97236')} = 1 or

permissions(645577338) & 0x1b <> 0) ...does not work.

SQL version is:
Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 1)

-Katja

[Raymond Mak [MSFT]]

This looks really strange indeed, and it may be something deeper than
replication. If possible, please open a bug at the Connect site with the
backup of your publisher database or you can send it directly to me
(rmakATmicrosoft.com).

You may also be able to isolate which column(s) is(are) causing you trouble
by systematically removing columns from the column list in the view
definition and perhaps that will give all of us better idea of what is going
wrong.

-Raymond

"Katja" <Ka...@discussions.microsoft.com> wrote in message

news:689C33EC-138F-4CB1...@microsoft.com...