Weird Replication Error in SQL 2005

[eggy168]

Hi, I followed all the steps from the MSDN book to do the replication in SQL Server 2005
I had no problem created the Publications. I checked the Launch Replication Monitor, the Agent History said the Status is completed.
Therefore, I followed the instruction and continued creating the New Subscription from the Wizard. The Subscriber is in SQL 2000 ( I assumed it has no problem?) I had everything completed & successed until I checked the
However, the message error came after I created the New Subscriptions, "Line 1: Incorrect syntax near "TYPE". I don't remember I wrote any syntax during the creation of the New Subscriptions.
Can anyone help me to solve this problem?
Thank You.

 

[mrdenny]

Do you have any user defined data types in the database? If so that is the problem.

SQL 2005 uses the CREATE TYPE command to create a user defined data type, while SQL 2000 uses the sp_addtype procedure. When you were setting up the publication it should have asked you what kind of databases were going to be subscribers. You probably selected SQL 2005 only when you should have selected SQL 2000 and SQL 2005 (I don't remember the exact wording, its been a while since I've setup replication).

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[eggy168]

Hi,
Thanks for your reply.

I don't believe it ever asked me to the SQL 2005 or SQL 2000 database while I created the Publication.

Here is the step I followed during the create New Publication Wizard:

1) It asked me choose the database that contains the data or objects you want to publish. So, I chose the datbase.

2) It asked me to choose the publication type, I chose the Transactional publication.

3) It asked me the Articles, I picked all the tables and views

4) Then, I skipped the Filtered Tasks.

5) I checked the Snapshot Agent to create a snaptshot immediately

6) Then, I set up the Agent Security

7) Finally, I checked the Create the Publication and named the Publication Name

 

[eggy168]

Okay, I figured it out.
Thanks for your help, mrdenny

 

[JayKusch]

Can you relay your findings so that others may benefit if they run into a similar issue?

Thanks!

Thanks

J. Kusch

 

[eggy168]

Theory:
1) A replication between SQL Server 2005 and SQL Server 2005
2) A replication between SQL Server 2005 and SQL Server 2000

Configure Distribution in SQL Server 2005:
1) Right click the Replication Folder (from the SQL Serve Management Studio), click the Configure Distribution. The wizard is displayed.
2) Distributor windows is displayed, (I checked) SERVER XXX will act as its own Distributor; SQL server will create a distribution database and log, then click NEXT
3) Snapshot folder will display (I just let it automatically choose the path, unless you want to change it), then click NEXT
4) Distribution Database window is displayed, then I just name the Distribution Database: distribution, again, I leave the automatic paths for the data and log, then click NEXT
5) Publishers window is displayed, make sure the correct name is on this window. Then click NEXT
6) Wizard Action is displayed,(I didn't check the following, Generate a script file with steps to configure distribution), then click NEXT
7) You should have the Success screen shown.

Now, right click the Replication folder in the Mangement Studio, click the Publisher Properties, then choose the database(s) you want to do the replication.

Create the New Publication.
Follow the wizard until it asks you to choose the Replication type. (I chose the Transactional Publication).
It is very straight forward in the wizard.

The tip is
**if the subscriber is SQL Server 2005, the view/stored procedure is easily to publish.
**if the subscriber is SQL Server 2000, they may not go through unless the view/stored procedure has indicated the indexes(I may be wrong, but I did it several times)

After you are done with the New Publication Wizard, right click the Local Publication, and then click Launch Replication Monitor, make sure everything is either Running or Completed.

********Creating th Subscription is the critical point for the replication.

Right click the New Subscription and follow the wizard. Make sure in the Distribution Agent Security, you know which Login you are using in both machine. Try it couple times if you have problem. In my case, I chose

1) 1st section, I chose "Run Under SQL Serve Agent service account"
2) Connect to the Distributor, I chose, "By Impersonating the process account"
3) Connect to the Subscriber: I chose, "Using the following SQL Server login"

Then you just continue finishing the wizard.

After the steps, I have successfully done my first replication between SQL Server 2005 - SQL Serve 2005 and SQL Server 2005 - SQL Server 2000