While creating a snapshot...

Ekrem &#214&#59;nsoy

Hello,

Here's the idea: There is a central SQL Cluster Server and there are branches in different cities. I'll replicate databases from branch SQL Servers to the central SQL Server. Every city will have a database in the central SQL Server.

While i was creating publication for replication, i get the following errors from different (6-7) tables among 289 articles.

----------------------------------------------------

2007-04-20 12:00:45.18 [46%] The replication agent had encountered an exception.
2007-04-20 12:00:45.18 Source: Replication
2007-04-20 12:00:45.18 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-04-20 12:00:45.18 Exception Message: Incorrect syntax near the keyword 'percent'.
2007-04-20 12:00:45.18 Must declare the scalar variable "@metadata_type".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@p8".
2007-04-20 12:00:45.18 Failed to generate merge replication stored procedures for article 'mf_pro_processeditem_financial'.
2007-04-20 12:00:45.18 Message Code: 156
2007-04-20 12:00:45.18

----------------------------------------------------

These articles are belong to a software which is developed by another company.

Any ideas

SQL Version : Enterprise 2005 with SP2

Replication type: Merge

Thanks,

Eko

Greg Y


There must be something specific about that stored procedure that's causing the failure, somewhere near the word "percent". Can you look at it and try replicating just the one procedure, if it continues to fail, you may want to consider profiling the agent to see what exactly it's failing on.

enaksanamun

I traced the processes and I concluded that there is a problem with the "percent" User-Defined Data Type.

It' s defined as "Tinyint" and it's length is 1. I observed that only the tables which has a column which is defined as "percent", causes this error.

Why do you think I faced a problem like this And do you guys think my conclusion is correct I'll contact with the software company in case I think this User-Defined Data Type is exactly the problem.

Greg Y

If you look in the snapshot folder, find the file that contains the proc definition and try to manually run the proc. I can only guess that either the datatype is not defined, "percent" is not properly quoted, you have case-sensitive collation mismatch, or the stored proc was scripted incorrectly. Please let me know your results.

enaksanamun

Hi Greg,

I solved the problem. It' s as i expected.

There is a User Defined Data Type which is named "percent". While publication is being prepared, some stored procedures are prepared.

And while system preparing the stored procedures, it assumes "percent" as a sql command (cause as you know it's a sql command) and

expects some parameters next to it and can't find any parameters with it so raises that error.

When I change the name of that User Defined Data Type as "percent_" then everything is ok. (Name of the mentioned UDDT is updated

in the tables too) Publication is created successfully.

So this proves my theory

Ekrem Onsoy

Greg Y

rather than rename it, you should be able to quote it properly i.e. [percent].