SQL Subscription Release Notes

Individual user issues

Use master
Go
DECLARE @SQL varchar (max)
Set @SQL = ' '
Select @[email protected]+ '; Kill ' +rtrim (SPID)
From Master. sysprocesses
where dbid=db_id (' DBNAME ')
EXEC (@SQL)
Go

ALTER DATABASE DBNAME set Multi_user

Create a publication for SQL database replication, problem handling for subscriptions

Some tips for using the operation (transactional replication type):
1. How can I modify the properties of a published table?
All subscriptions to the publication are deleted, (the publication does not have to be deleted), the table is canceled in the project that publishes the properties, and then

You can modify the table, and then add the table to the publication.
2. Common Operating Interface:
(1) Generally in the monitor-publish the project, you can see the snapshot distribution and the instant information of the subscription.

(2) Another action place is the published property, (in the properties--state can start the agent)

(3) There is also a tool-copy-configure publishing, subscribers and distribution, the server that needs to be subscribed must

You must specify a post-authorization subscription here to succeed.
============
Problem:
The old problem that the database cannot log on: Database Properties-Security inside, don't choose Windows only, change another

A mode.

0. A subscription to a snapshot publication (not transactional replication) error, "21,776 name xxx in transpublication set

Close not found ", in Enterprise Manager, both the publication database and the subscription database are stopped or disconnected, and then

Start, reconnect to subscribe to the snapshot release. It's weird, there's a problem. You can try restarting

Server to try, sometimes the next machine can control the subscription operation, while the other machine is subscribed

Operation error, also reconnect server try again.

1. At the beginning of the problem is that the registered local server can not be used, deleted after the renaming done,

The 2.sql Server Agent is using the system account, which causes replication to fail. Once this cue point is determined,

The option for this account is gray,

The original is configured to publish the server is not native, to the local operation published, you can choose "This account".

3. Also encountered a non-signed subscription, "Do not allow the requested signature subscription", obviously published and subscribers both

Registered, and both sides have established

Subscriber account number, Reason: In this step, point to register the server, and then cannot select the default analog SQL login, and

is to select Use SQL Authentication, and then enter a name and password.

If the "Do not allow the requested attribution subscription" is always popped, you cannot enter the login mode to opt out of the subscription

Wizard, in Enterprise Manager, go to Publisher, copy-right-"Configure publishing, Subscribers, and sub-

"-subscribers-Add the Subscriber machine name and account number, if it is already, directly determine the next.

Subscribing to a Subscriber does not display an unsigned subscription.
The above process may find that the name of the Subscriber has already been added to the publisher, or this question appears

Server, and then add the name of the Subscriber, why

This may be after the publisher has specified the name of the subscriber to change, but the Subscriber is not refreshed.

You can disconnect subscribers and publishers, and then subscribe or resolve them.

4. The pull subscription has been established, but the subscription status shows "retrying", Last action "process failed to connect

To subscriber Shd-shd ",
The reason is that after I installed SQL, I changed the name of my computer and changed it back to SHD-SHD. (Some people are because SQL Server

The port has been modified). It's also possible that the MSSQLServer and SQLServerAgent services do not use Domain Users

Start up and run,
(So I add B to the domain where a is, and add the Admin user of A to B's SQLServerAgent service.)

Logged in user,)

5. But the subscription status shows "request sharing agent failed", "the process could not connect to distributor

", when the suspect is a subscription setting, the connection
There is a problem with the account number of the publishing database, and you see the specific cause of the error in the history of the subscription (right-click). Account

Right after the history of the record again said

Subscriber machine is not configured, so all the subscription and release to try, the results of the release can not delete the publication, out

Now 20015 error, original is subscription

The machine passes the local area network, the shared directory that opened the snapshot is not closed, and after the shutdown, the publication is successfully deleted.
If you cannot delete it, simply unshared the shared snapshot directory, then delete and reconfigure the snapshot directory share

.

6. The subscription at the Subscriber cannot be deleted, prompting "Unable to update or delete from the MSX server

Initiated Job ",

This is because this error occurs after the computer change name and you cannot modify the job that you created.

Workaround:
Change the value in Originating-server to the changed computer name in the Sysjobs table in msdb

Update msdb: Sysjobs Set originating_server = ' Present server name '

Sometimes you can go to manage-sql Server Agent-jobs to see more situations and actions.
7. All re-creation of the publication and subscription succeeded, but the "Publish content null" prompt appears because the order

The server is not connected to the Distributor, (1) SQLServerAgent is not started. (2) MSSQLServer and

The SQLServerAgent service must be started and run as a domain user.

Check the above questions at any time SQLServerAgent (Control Panel-Management-service) service has been restarted

Move

8. The subscriber has landed on the publisher, but the server is not visible when the subscription is established,

Because no distribution is configured
Server, Tools-Replication-"Configure publishing, subscribers, and Distribution", and then check if SQLServerAgent

Run.
Then in the publication Properties-Publish access list, add Subscriber login name, subscriber finally see

was released.

9. The initial snapshot of release ' xxx ' is not yet available.

Ensure that the SQLServerAgent has been run,
copy = "Publish content =" Publish Project mytab= right click on the right side of the subscription = "Re-initialization
If it doesn't work,
Replication Monitor--a snapshot of the project published by the publisher--xxx--, see
The process could not create the file "\\XXZ\SQLPUB\unc" to reconfigure the SQL account to that folder.

The folder permission is "full".

10. Subscriber generated "snapshot not yet available for operating system error 53", Subscriber account Access snapshot

File
folder permissions are insufficient, permissions are assigned, and inspection firewalls are disabled for access.

11. A table cannot be published, the selection box shows a red fork, and a table without a primary key cannot be published.

12. The process failed from the table "[dbo]. [syncobj_0x3745373834413345] "bulk copy outward.
I/O error occurred while writing BCP data file (Source: ODBC SQL Server Driver (ODBC);

Error code: 0)
Workaround: All the character types are changed to the N type of problem solving (char-nchar,varchar-

Nvarchar,ntext).
13, in the data release times the following error message:
Unexpected EOF encountered in bcp data file (Source: ODBC SQL Server Driver (ODBC): error code

: S1000)
This is because the database contains text or ntext or an image field, as long as a publication

The problem can be resolved by selecting < snapshot >-< snapshot format >-< character mode in the Properties page.
14, in addition, the construction data published, reported the following error:
The primary key constraint is violated.

Insert a repeating key in the object ' #7EB71BA1 '.
(Source: Zzb_stockserver (data source); Error code: 2627)
This is due to the fact that the database data is performing an insert or modify action when the publication is under construction, as long as the

A snapshot of the publication can be run again.
15, some large data tables can not generate snapshots of the problem, the database reported the following error:
The process failed from the table "[dbo]. [syncobj_0x3436383044343035] "Outward bulk copy

An I/O error occurred while writing the BCP data file (Source: ODBC SQL Server Driver (ODBC);

Error code: 0)
This is because the table has a problem or the table data is too large, you can try to solve the following methods:
A, use DBCC CHECKTABLE to check the table, the data table is not a problem, and then try another

To regenerate the data snapshot.
b, if the above problems still cannot be resolved, you can use the data export function to guide the data table to the subscription service

The device.
C, then rebuild a data publication for that data table, choose not to generate a snapshot feature, and then ship

Resolve the problem as described in question 4 below.
16, when establishing a subscription, choose not to initialize the Subscriber data table, the Subscriber does not produce the corresponding

The replication storage process, when running the publication, will report the following error:
Failed to find storage process ' sp_msupd_xxx ' ...
The reasons are as follows:
Problem Deion:
======
Replication Distribution Agent reports error. The error message is "It

Could not
Find the stored procedure sp_msins_*** ".

Reason:
======
If we choose "The Subscriber has a schema and data already" option when

We create the
Subion, those three system auto created stored procedures would not be

Created
In the subscribing database side, they is
"sp_msins_***"/"sp_msupd_***"/"sp_msdel_***".

Soltuion:
======
We could use the system command "Sp_publicationcustomprocs

' Pulication_name ' "
To create the s of those three system stored procedures. Then we run

These
s in the subscribing database. The problem would be resolved.
Solution:
Run the following command at the Publisher: Sp_publicationcustomprocs ' xxxx ', where

XXXX is the publication name, and the command generates a script that creates the three stored procedures, and then the subscription service

Three storage processes can be generated by the operation of the device.

17. When subscribing to data, the following error occurred:
The subscription has been marked as inactive and must be reinitialized. Need to delete and re-create NoSync

Subscription.
(Source: Zzb_stockserver (data source); Error code: 21074)
Solution:
At the Publisher, select a publication to reinitialize in properties-subscription-. Can solve the

The issues mentioned

18. When the data is published, the following error occurred:
cannot be inserted in object ' Tbmeetingmemo ' with unique index ' Pk_tbmeetingmemo '

The row of the repeating key.
(Source: Zzb-xhstock (data source); Error code: 2601)
Cause: It is possible that when you publish the table, there is a case in which the table is published multiple times in different publications.
Workaround:
A. Find out if there have been multiple releases of the table.
In the publisher's publication database, run the following SQL statement: SELECT * from Sysarticles

ORDER BY dest_table
To see if there is more information about the table in the Dest_table column, that is, the table has been repeatedly

Publish, you can use the method described in the next step to see which publications published the table.
B, run the statement: SELECT * from syspublications, view pubid the same as the above information

PubID, you can determine which publication published the same data table.
C, after the publication is found, if you increase the data table, you do not have to delete all subscriptions under the publication, if it is

Remove the Published data table, you need to delete all the subscription information under the publication, modify the corresponding publication

, and re-establish the corresponding subscription.

19. Create a publication, 14294 error, "Please provide @job_id identification job",
is because you deleted jobs that were automatically generated by the previous system. Method: Tools-Copy-disable publish, reconfigure

Publish, subscribe to servers, and distribute, when completed, the published database is displayed as a hand-shaped share icon,

Build Release

20. The subscription and publication are deleted, but disabling the publication is unsuccessful: Unable to drop distribution data

Library ' distribution ', the database is in use. Stop the database, start the database, and then disable publishing.

21. Configure the Publish and Distribute wizard and finalize error 18481: Configure Distributor: Failed to connect to

Server because distributor_admin is not defined as a remote login on this server
You must have changed the computer name after you loaded SQL Server, although SQL Server is still available, but a

When you copy, SQL Server reads the old computer instance name, not the letter, you try
SELECT @ @SERVERNAME
Displayed, certainly not your current computer name.

So, you use
Sp_dropserver < (old name shown with SELECT @ @SERVERNAME) >
Go
sp_addserver < your current computer name;, LOCAL
Go

Example: Sp_dropserver ' e0184d34429345f\sql2k '
Go
sp_addserver ' deboywang\sql2k ', LOCAL
Go

Restarting the SQL Server service will solve the problem

In addition, if you want to modify the @ @Servername you can also use the following method:

1. exec sp_helpserver Check your server name
2. If not correct, use exec sp_setnetname to fix

In addition, when the registered name is changed to another name, the registration is successful (added in the Client tool

anonymous), this issue occurs when you publish.

22. Push subscriptions cannot be deleted: after the subscription and publication have been deleted at the publisher, the strong

Subscription is always present, cannot be deleted, ask Microsoft Support, conclusion:
Sp_removedbreplication ' Job '
The name of the database that the job replicates for the subscription.

Other questions:
=============
Note:
0. Do not delete the contents of the Management--sql Server Agent-the job. Strictly in accordance with the first to delete the subscription, and then delete the release

To proceed.
1. How to ignore the error in distribution
A, in the management of the publisher-"SQL Server Agent--" jobs, locate the publication of the publication

JOB, in Properties---Step->2 Run code Program--"Edit--" command---add at the end of the command statement-

SkipErrors xxxx (xxxx is the error number of the distribution times), refers to the meaning of ignoring the error.
b, and then restart the distribution engine.

2. How do I output an error message?
At the end of the job command to output an error message, add-output c:\temp\error.txt,-output

is the command, followed by the file path and folder name to be output.
================

Because the publication does not allow updatable subscriptions, when the IDENTITY column is transferred to the Subscriber, the

The IDENTITY property is not transferred. (for example, a column defined as an INT IDENTITY at the publisher

Defined as INT on the Subscriber. ）

If you want to transfer the IDENTITY property to a subscription table, you must do the following:

? Manually create a subscription table using the IDENTITY property and the Not for REPLICATION option. You

You can perform this operation by having SQL Server execute a custom script before applying the snapshot. To refer to

Custom script, create a publication, create a script, and then on the Snapshot tab of the publication properties

Enter the name of the script on the

? Set name conflict item properties so that SQL Server does not apply a snapshot at the Subscriber

The existing tables are dropped. This property is on the Snapshot tab of the project properties.

The following published table contains the identity column:

[dbo]. [Bookindex]
===================

SQL Server requires that all objects referenced by the published stored procedure, such as tables and user-defined functions, be

Available on both servers. If the referenced objects are not published as items in this publication, they must be

Manually created at the Subscriber.

The following stored procedures are published in this publication:

[dbo]. [Getchanamelist]
[dbo]. [Setupwebsite]

SQL Subscription Release Notes