Creating publications

	Chapter 8. SQL Remote Design for Adaptive Server Enterprise

Creating publications
In this section	This section describes how to create simple publications consisting of whole
	tables, or of column-wise subsets of tables.

☞ Simple publications are also discussed in the chapter “A Tutorial for Adaptive Server Enterprise Users” on page 53 .

Creating whole-table articles

The simplest type of article is one that includes all the rows and columns of a database table.

❖To create an article that includes all the rows and columns of a table

1.Mark the table for replication. You do this by executing the sp_add_remote_table procedure:

sp_add_remote_table table-name

	2.	Add the table to the publication. You do this by executing the
		sp_add_article procedure:
		sp_add_article publication-name, table-name
Example	♦ The following commands add the table SalesRep to the SalesRepData
		publication:

sp_add_remote_table ’SalesRep’

sp_add_article ’SalesRepData’, ’SalesRep’ go

Creating articles containing some of the columns in a table

To create an article that includes only some of the columns from a table, you need to list the columns that you wish to include, using sp_add_article_col. If no columns are listed, the article includes all columns of the table.

❖To create an article that includes some of the columns and all the rows of a table

1.Mark the table for replication. You do this by executing the sp_add_remote_table procedure:

sp_add_remote_table table-name go

143

Page 161

Image 161

Sybase DC38133-01-0902-01 manual Creating publications, Creating whole-table articles

Contents

Part number DC38133-01-0902-01 Last modiﬁed October SQL Remote User’s GuidePage Contents Principles of SQL Remote Design SQL Remote Administration 199SQL Remote Design for Adaptive Server Anywhere SQL Remote Design for Adaptive Server Enterprise 141 Using SQL Remote with Replication Server 277 Administering SQL Remote for Adaptive Server Anywhere241System Objects for Adaptive Server Anywhere 325 Utilities and Options Reference 291 Command Reference for Adaptive Server Enterprise 379 Command Reference for Adaptive Server Anywhere 353 SQL Remote for Adaptive Server Enterprise and Adaptive Server Anywhere Differences 437Supported Platforms and Message Links 445 Index 449 Viii Systems About This ManualMail or ﬁle transfer Adaptive Server Anywhere Database Administration Guide This SQL Anywhere Studio documentationPage Page Release Savepoint savepoint-name Documentation conventions Client application Following icons are used in this documentationDatabase server, such as Sybase Adaptive Server Anywhere Xiv Asademo.db Adaptive Server Anywhere sample database Finding out more and providing feedback Page Xviii Introduction to SQL Remote Page This chapter introduces SQL Remote and the documentation Welcome to SQL Remote About SQL Remote SQL Remote for Adaptive Server Anywhere Enables replication To install the SQL Remote software Windows Product installationTo install the SQL Remote software Unix About this manualPage SQL Remote SQL Remote Concepts SQL Remote components Message Agent Data serverClient applications Message system client Two-table synchronization definition Publications and subscriptions At the remote database Replication SQL Remote features Page Server-to-laptop replication for mobile workforces Some sample installations Server-to-server replication among ofﬁces More Page Setting Up SQL Remote Each user in the installation Setup overviewSybase System Administrator should perform all SQL Remote You must take some steps to To install the SQL Remote system objects Installing the SQL Remote system objectsPreparing your Adaptive Server Enterprise server Ensuring Tempdb is large enough SQL Remote system objects To install the stable queue Command-line installation of the stable queuePage Upgrading SQL Remote for Adaptive Server Enterprise To upgrade SQL Remote To uninstall the stable queue from a database Uninstalling SQL RemoteTo uninstall the SQL Remote objects from a database Tutorials for Adaptive Server Anywhere Users Introduction Just on those issues important for replicationGoals Database Tables are described in more detail as follows Tutorials for Adaptive Server Anywhere Users Repkey Replication goalsName Custkey Sybase Central or command-line utilities To prepare for the tutorial Preparing for the Sybase Central replication tutorial To add tables to the consolidated database Add a SQL Remote message type Setting up a consolidated database Add the publisher and remote user to the database To add an address to a message type To create a new user as the publisher To add a publication To add a remote userAdd publications and subscriptions Enter the expression repkey Set up the remote database in Sybase CentralClick Finish to create the publication Publication for the remote database user ﬁelduser To extract a database To create the databases and directories for the tutorial Preparing for the replication tutorial Create a SQL Remote message type Set up the consolidated databaseTo add the tables to the consolidated database To create a publisher for the database To create the message typeGrant Publish and Remote at the consolidated database Table SalesRep, and some of the rows of the table Customer Create publications and subscriptionsSQL, to create a publication To create the subscription Set up the remote databaseTo create the publication To load the database information Extract the remote database informationLoad the remote database information Select * from SalesRep Select * from Customer Enter data at the consolidated database Start replicating dataSend data from the consolidated database First, enter some data into the consolidated database To receive data at the remote database Receive data at the remote databaseTo send the data to the remote database Commit the insertion by executing the following statement To verify that the data has arrived Custkey Name Repkey To add the publication to the sample database Sample publicationPage Tutorial for Adaptive Server Enterprise Users Database schema for the tutorial is illustrated in the ﬁgure SalesRep table is many-to-one Tutorial for Adaptive Server Enterprise UsersPage First steps Tutorial Adaptive Server Enterprise replication To install SQL Remote into the hq database Create the Customer table with the following statement Setting up the consolidated databaseCreate the SalesRep table with the following statement Create the necessary users and permissions Create the message links and addresses To create the subscriber To create the publisher Create an empty publication Create the publication and subscription Create a subscription to SalesRepData for ﬁelduser , with a To create a subscriptionSubscription value of rep1 Extract the remote database To create a database ﬁle named ﬁeld.db To load the data into the database using Interactive SQLUtility on Run the script from Interactive SQL Connect to the server using the Interactive SQL utilityTo load the data into the database as a batch process Connect to the Adaptive Server Enterprise server from isql To enter data at the Adaptive Server Enterprise database To receive the data at Adaptive Server Anywhere To replicate the data from Adaptive Server Enterprise Commit the row Custkey Name Repkey Page Replication Design for SQL Remote Page Principles of SQL Remote Design Design overview Adaptive Server Enterprise System Administrator or databaseEnsuring compatible databases SQL Remote replicates data SQL Remote Message Agent does not perform any character set Using compatible sort orders and character setsConversions Between Ssremote and Adaptive Server Enterprise whenever 819LATIN1 Eucjapan Replication of inserts and deletes Replication of updatesHow statements are replicated User, the row to be updated is not found Clause of the Update statementStatement Representatives This case, the Update does not update anything Replication of proceduresProcedure call is not replicated Replication of triggers Body of your triggers Message causing the conﬂictActions carried out by conﬂict Some Before triggers can produce Replication of data deﬁnition statements Replication of blobs How data types are replicated Replication of dates and times Select Who gets what? Principles of SQL Remote Design Replication conﬂicts Replication errors and conﬂictsReplication errors Issue the following statement Tracking SQL errorsVARCHAR, or Long VARCHAR. The procedure is called once with DetectedPage SQL Remote Design for Adaptive Server Anywhere Is shared among which databases Publications determine what informationSubscriptions determine what Information each user receives Publishing whole tables Publishing dataTo publish one or more entire tables Sybase Central To publish one or more entire tables SQL To publish only some columns in a table Sybase Central Publishing only some columns in a tableFor more information, see the Create Publication statement To publish only some columns in a table SQL Publishing only some rows in a table To create a publication using a Where clause SQL Publishing only some rows using a Where clauseTo create a publication using a Where clause Sybase Central Publishing only some rows using a subscription expression Column To create an article using a subscription expression SQL To add articles Sybase Central Altering existing publications To modify an existing publication SQL Dropping publicationsTo remove articles Sybase Central See also the Drop Publication statement ASA SQL Reference, To delete a publication Sybase CentralTo delete a publication SQL Publication design for Adaptive Server Anywhere Design issues overview Design tips for performance Conditions for valid articles Pay attention to Update Publication Triggers In particular Three tables are described in more detail as follows Here is a simple database that illustrates the problemContact example 106 Following informationThose customers assigned to them, from the Customer table Territory realignment in the Contact example Partitioning the Customer table in the Contact examplePartitioning the Contact table in the Contact example Properly transferred to the new sales representative RepsAlong with the Customer For which there is no longer a Customer Special Update statement for publications Assume the following data Understanding this helps in designing efﬁcient publications∙ SalesRep table ∙ Customer table SalesRepData Publication Name rep1 Before list Many-to-many relationship in the database Sharing rows among several subscriptionsPolicy example Entire SalesRep table With the following informationInvolving the sales rep subscribed to the data 113 Publication Contact example on Territory realignment with a many-to-many relationship 116 Remote databases contain the proper data That this subquery can be multi-valued Customer being transferred Where custkey = OldRow.custkeyCan be multi-valued Dealing with the customer, taken from the Policy table Customer SQL Remote Design for Adaptive Server Anywhere ID Rep Dept Ann Marc Managing conﬂicts SQL Remote installation at the consolidated database Must be excluded from the installation by proper designHow SQL Remote handles conﬂicts Updating Using conﬂict resolution triggers Implementing conﬂict resolution Referencing OLD AS oldval Create Trigger trigger-name Resolving date conﬂicts Conﬂict resolution examples Resolving inventory conﬂicts Reporting conﬂicts Increments from the two updates. For example Includes the salesorder table Designing to avoid referential integrity errorsDesigning triggers to avoid errors Page Ensuring unique primary keys Using global autoincrement default column values Setting the Globaldatabaseid value Declaring default global autoincrement Database will have Globaldatabaseid set to Primary key values for Global Default Autoincrement valuesTo set the global database identiﬁcation number How default values are chosen Using primary key pools To replicate the primary key pool SQL Primary key pool tableReplicating the primary key pool Filling and replenishing the key pool Cursor for 136 Else Adding new customers Insert Primary key pool summary To create and manage subscriptions in Sybase Central Creating subscriptions 140 For more information, see Create Subscription statement on 141 SQL Remote Design for Adaptive Server Enterprise Design overview Creating articles containing some of the columns in a table Creating publicationsCreating whole-table articles Creating articles containing some of the rows in a table Creating an article using a subscription column Creating an article using a Where clauseTo create an article using a Where clause Spaddarticle publicationname, tablename NULL, columnname To create an article using a subscription columnSpaddremotetable tablename Publication design for Adaptive Server Enterprise Insert Succeeds Contain the subscription column To handle this case, using an exampleTwo tables described earlier in this chapter 149 150 Are realigned Exec spcreatepublication ’SalesRepData’ go Adding a subscription-list column to the Contact table Log entries are values, not subscribers Update on the Customer table Update on the Contact tableMaintaining the subscription-list column For the following operations SQL Remote Design for Adaptive Server Enterprise Tuning extraction performance 156 To create a subscription viewCreate a view that contains this subquery. For example Solving the problem Foreign KEY 158 159 Subscriptions to this publication take the following formThese objects Triggers For Insert AS Begin 161 Ensure the quotedidentiﬁer option is set to on Tuning extraction performance for shared rows 163 Customer Managing conﬂicts Any conﬂict resolution procedures are called Consolidated database, no row will be updatedDatabase objects to handle the resolution When the conﬂicting message arrived Remoterowtable. These three are discussed in turn Names of the conﬂict resolution objectsTable only ever contains a single row This table must have the same column names 168 ﬁrst conﬂict resolution exampleTwo-table database is as follows After the procedure is run, the rows in the OldCustomer Conﬂict-causing UpdateFollows @lostname value is the value that was overridden by ConﬂictLog table has a single row, showing the conﬂict Second conﬂict resolution exampleTo test the example Lost and won names It when the procedure is executedUser ID of the remote user. The table is as follows Replaces it with the value that was previously present There are several points of note here Where conﬂict resolution is carried out by Before triggersUser ID of the remote user is stored by the Message Agent Currentremoteuser column of the temporary table #remote 173 Page Columns of this table have the following meanings 175 Primary key pool 176 To replicate the primary key pool 177 Adding new customers 179 Testing the key poolTo test the primary key pool Primary key pool summary Spsubscription create, puborders SamS ’856’ Page SQL Remote Page 185 SQL Remote replication installationDeploying and Synchronizing Databases Deployment overview Test before deployment Resolve update conﬂicts are all easy to doChanges to avoid on a running system Conﬂicts as they occurPage Mixed operating systems and database extraction Synchronizing databases Example Where path is the path of the reload command ﬁle 191 Using the extraction utilityCreating a database from the reload ﬁles To create a remote database from the reload ﬁle Before extracting a database Using the extraction utility from Sybase Central To extract a database for a remote user Sybase Central Designing an efﬁcient extraction procedure An efﬁcient approach to extracting many databases Extracting groups Limits to using the extraction utility This setup is illustrated in the following diagram Using the extraction utility for Adaptive Server Enterprise Customizing the system tables Synchronizing data over a message system 199 SQL Remote Administration Management overview Granting and revoking Publish permissions Managing SQL Remote permissions To make an existing user the publisher Sybase Central To create a new user as the publisher Sybase CentralTo revoke Publish permissions Sybase Central 202 Exec sppublisher ’SBeaulieu’ go Granting Remote permissions Granting and revoking Remote and Consolidate permissions Following options To make an existing user remote Sybase CentralUse an Smtp e-mail system Send messages to e-mail address sbeaulieu@acme.com Selecting a send frequency Granting Consolidate permissions Permissions to user hquser, using the ﬁle message linkStatement is executed automatically at the remote database Agent is run Permission from user SBeaulieu Assigning permissions in multi-tier installationsTo revoke Remote permissions Sybase Central Publish Working with message types Using message types To alter a message type Sybase Central Using Sybase Central to work with message typesTo add a message type Sybase Central To drop a message type Sybase Central Using commands to work with message typesTo create a message type SQL Create Remote Message Type type-nameADDRESS address-string To drop a message type SQL To alter a message type SQLCreate Remote Message Type statement on 213 To set a message control parameter Adaptive Server Anywhere Setting message type control parameters 215 By using the ﬁle message systemﬁle message system Ftp message system Troubleshooting ftp problems 218 Smtp message systemMote messages SQL Remote Administration Supported operating systems on Mapi message systemSharing SMTP/POP addresses VIM message system VIM message control parameters Message Agent batch and continuous modes Running the Message AgentNames are ssremote and dbremote, respectively Enterprise works on To run the Message Agent in continuous mode Replication system recovery proceduresTo run the Message Agent in batch mode Connections used by the Message Agent See the sections Transaction log and backup management on Ensuring consistent Message Agent settingsAdaptive Server Enterprise transaction log and backup Management on Message Agent and replication security Troubleshooting errors at remote sites To conﬁgure a consolidated site to receive log information Execute the following command against the remote databaseUse either the -roor the -rtMessage Agent option For more information, see The Message Agent on Tuning throughput by controlling Message Agent threading Tuning Message Agent performance Tuning throughput by caching messages Polling interval Tuning incoming message polling 231 Requesting resendsExample Tuning the message sending process Your choices dictate how often updates are sent to To those when tuning the incoming-message polling frequencyWhen a remote user requests that a message be Resending messages Dbremote -ru 1h Encoding scheme Encoding and compressing messages Creating custom encoding schemes Tracking messages by transaction log offsets Message tracking systemStatus information in the remoteuser table Are offsets in the local database transaction log, Agent -loption to change this settingCannot be a later offset than logsent Transaction log Its resendcount Handling of lost or corrupt messagesPage 241 Administering SQL Remote for Adaptive Server Anywhere Starting the Message Agent Running the Message Agent as a service Full DBA permissions on the database Full DBA permissions from the Message Agent When connectingSuggested practice is to grant Remote DBA authority at DBA authority grants them no extra permissionsPage Default error handling Error reporting and handlingIgnoring errors Agent output Example e-mailing notiﬁcation of errors Implementing error handling proceduresFollowing stored procedure implements this notiﬁcation 246 Commit Work Columns have the following meaning 247 248 Here is a sample insert into the table from the above error Setting the transaction log directory Transaction log and backup management Such a directory is kept in proper shape Backup utility optionsUsing the live directory as the transaction log directory Using the backup directory as the transaction log directory Following command line 252 Managing old transaction logs 254 Recovery with a single transaction logTo recover the database Backup the mirrored transaction log d\mirdir\consol.mlg To recover from media failure on the C driveStart the database using the following command line Recovery with multiple transaction logs Command line would be Backups daily using the following commandBackup the mirrored transaction log d\mirdir\cons.mlg Make a copy of the database and log ﬁle Restart your system as normal Backup procedures at remote databasesApply the renamed transaction logs in order, as follows 258 Upgrading consolidated databases To unload and reload a consolidated database manual Using passthrough mode Uses and limitations of passthrough mode On a running SQL Remote setup Operations not replicated in passthrough modeProcedure on the replicate side has the correct effect However, the following dynamic SQL statement is replicated 263 Administering SQL Remote for Adaptive Server Enterprise Message Agent on How the Message Agent for Adaptive Server Enterprise worksScanning the transaction log Stable queue Administering SQL Remote for Adaptive Server Enterprise 266 Message Agent operation phases 267 TransactionStable Logqueue Message Agent 268 Running multiple Message Agents For setup instructions, see Setting Up SQL Remote on To execute Agent output Protecting against media failure on the transaction log Transaction log management Stable queue recovery issues Truncation point can be reset with the following command With the following commandFreed by Dump Transaction 274 Making schema changes Schema modiﬁcations 277 Using SQL Remote with Replication Server When you need to use the SQL Remote Open Server How the pieces ﬁt together Architecture for Replication Server/SQL Remote installations Message system Stable Queue Agent Remote Open Server on Information to retrieve it when it startsSQL Remote Open Server is the following executable On Windows operating systems, the SQL Remote Open Server is Add the SQL Remote Open Server to the setup This stage is Setting up SQL Remote Open ServerSame regardless of previous installations To set up the SQL Remote Open Server Page Set the dsixactgroupsize parameter Conﬁguring Replication ServerSet the dsinumthreads parameter Create replication deﬁnitions for SQL Remote data Suspend and restart the connection Other issues 288 This part presents reference material for SQL Remote Page 291 Utilities and Options Reference Dbremote ssremote options directory Message AgentTracking system to ensure message delivery Utilities and Options Reference Replication security on Listing, see Connections used by the Message Agent on 295 Languagename,charsetname,sortorder For more information, see Running multiple Message Agents on Page Dbremote -rd 30s Page You should create a ﬁle named dbremote.ini Registry, at the following locationWindows NetWare Extracting a remote database in Sybase Central Database Extraction utility 303 Extraction utility 304 Remote Adaptive Server Anywhere database Extraction utility options Utilities and Options Reference Optional Database name. If this parameter is not sup 309 Page Input SQL Remote Open Server Server Enterprise and Adaptive Server Anywhere databases Gain access to the SQL Remote system tables Default value for the open server name is SSQueueValues default to the -cvalues Name of the executable is as followsPage SQL Remote options Page Yy yyyy mm mmm dd Hh nn mm Ss.s 318 Relationships on Sphookdbremoteend and sphookssrmtend SQL Remote event-hook proceduresSphookdbremotebegin and sphookssrmtbegin Sphookdbremotereceiveend and sphookssrmtreceiveend Sphookdbremoteshutdown and sphookssrmtshutdownSphookdbremotereceivebegin and sphookssrmtreceivebegin Sphookdbremotesendend and sphookssrmtsendend Sphookdbremotesendbegin and sphookssrmtsendbeginSphookdbremotemessagesent and sphookssrmtmessagesent Sphookdbremotemessagemissing and sphookssrmtmessagemissing 323 Has applied a set of messages from a userPage 325 System Objects for Adaptive Server Anywhere Each row describes an article in a SQL Remote publication SQL Remote system tablesSysarticle table 327 Sysarticlecol tableIt is in, and the publication it is part Sysremoteoption table Syspublication tableSysremoteoptiontype table Each row describes a SQL Remote publication Sysremoteuser table Sysremotetype tablePublisher address 329 Consolidate 330 331 Syssubscription tableRemote permissions to one publication 332 Sysarticles view SQL Remote system viewsSysarticlecols view Syspublications view Sysremoteusers view Sysremoteoptions view 335 Syssubscriptions viewEach row lists information about a subscription 336 337 System Objects for Adaptive Server Enterprise Srarticle table #remote tableManage SQL Remote information Table has a single row Srmarker table Srarticlecol tableDatabases in the same session 339 Sroption table Srobject tableSrpassthrough table Each row describes a replication option used by SQL Remote Srremoteoption table Srpublication tableSrpublisher table Srremotetable table Srremoteoptiontype tableSrremotetype table 342 343 Srremoteuser table Received 344 345 Srsubscription table Srpublications view Srarticles viewSrarticlecols view Srremotetypes view Srremoteoptions viewSrremotetables view 348 Srremoteusers view 349 Srsubscriptions view Srqueuestate table Stable Queue tablesStable queue 350 351 Srtransaction table Srqueuecoordinate table Srconﬁrmedtransaction tableEach row marks the corresponding row in srtransaction 352 353 Command Reference for Adaptive Server Anywhere 354 Been created Alter Remote Message Type statementMust have DBA authority Automatic commit Subscribe by expression Create Publication statementCreate Publication statement ASA SQL Reference, Messages from a database Create Remote Message Type statementCreate Remote Message Type statement SQL Remote ASA SQL Create Remote Message Type message-system Create Subscription statement SQL Remote ASA SQL Reference Create Subscription statement Create Trigger statement Must have Resource authority and have Alter permissions on Update statement onTable and thus requires exclusive use of the table Create Trigger statement SQL Remote ASA SQL Reference Drop Publication statement ASA SQL Reference, Drop Publication statement Granted Remote or Consolidate permissions with this type Drop Remote Message Type statementDrop Remote Message Type statement SQL Remote ASA SQL Drop Remote Message Type message-system Drop Subscription statement SQL Remote ASA SQL Reference Drop Subscription statement Current database Grant Consolidate statementGrant Consolidate statement SQL Remote ASA SQL Reference Send Every AT ’hhmm ss’ Grant Publish statement Grant Remote statement Grant Remote DBA statement Identified by passwordConnected from the Message Agent Grant Remote DBA statement SQL Remote ASA SQL Reference Sppassthrough procedure on Passthrough modePassthrough statement Passthrough statement SQL Remote ASA SQL Reference, Remote Reset statement SQL Remote ASA SQL Reference, Remote Reset statementSubscriptions for a remote user in a single transaction No automatic commit is done by this statement Revoke Consolidate statement Sprevokeconsolidate procedure onSQL Remote messages from this database Automatic commit. Drops all subscriptions for the user Current publisher Revoke Publish statementRevoke Publish statement SQL Remote ASA SQL Reference Revoke Publish from userid Revoke Remote statement Sprevokeremote procedure onMessages from this database Revoke Remote statement SQL Remote ASA SQL Reference Revoke Remote DBA statement SQL Remote ASA SQL Reference Revoke Remote DBA statement Message link Splinkoption procedure onSET Remote Option statement Start Subscription Start Subscription statement Stop Subscription Stop Subscription statement Synchronize Subscription Synchronize Subscription statement Create Trigger statement on Update statementUpdate statement SQL Remote ASA SQL Reference, 379 Command Reference for Adaptive Server Enterprise 380 Spaddarticle procedure 382 Making schema changes onSalesRepData Spaddarticlecol publicationname Spaddarticlecol procedureTo add a column to an article in a publication To mark a table for SQL Remote replication Spaddremotetable procedure Remote rows are stored in tables named oldCustomer Default conﬂict resolutionRemoteCustomer, respectively 385 Spdroppublication procedure on Spcreatepublication procedureTo create a publication Spcreatepublication publicationname Spcreatepublication procedure on Spdroppublication procedureTo drop a publication from the database Spdroppublication publicationname Spdropremotetype typename Spdropremotetype procedure Procedure on Spdropsqlremote procedureSpqueuedrop procedure on Databases acting as remote databases Spgrantconsolidate procedure See also Description Example 392 Spgrantremote procedure Single quotes Current database must be granted Remote permissions usingOne of the publications in the current database Speciﬁed 395 Databases Splinkoption procedureSET Remote Option statement on Ftp message system on ﬁle message system onMapi message system on Smtp message system on To change the description of an article in a procedure Spmodifyarticle procedure 399 Subscription expressionExpression that is the repkey column Spaddremotetable procedure on Spmodifyremotetable procedureSpremoveremotetable procedure on Managing conﬂicts on 401 Sppassthrough procedure Sppassthroughpiece string Sppassthroughpiece procedureTo build a long SQL statement for passthrough 404 Resents passthrough mode Sppassthroughstop procedureSppassthroughstop procedure resents the list of recipients Currently being built Sppassthroughsubscription publicationname Sppassthroughsubscription procedureStatements Sppassthroughuser username Sppassthroughuser procedure Consolidated database Sppopulatesqlanywhere procedureCalled directly Sppopulatesqlanywhere Sppublisher username Sppublisher procedure Spqueueclean Spqueueclean procedureBe called directly Log was scanned Spqueueconﬁrmeddeleteold Spqueueconﬁrmeddeleteold procedure Spqueueconﬁrmedtransaction offset Spqueueconﬁrmedtransaction procedureMatch this offset Spqueuedeleteold Spqueuedeleteold procedureBeen conﬁrmed by all remote databases Spdropsqlremote procedure on Spqueuedrop procedure Stable queue recovery issues on Spqueuedumpdatabase procedureSpqueuedumptransaction procedure on Spqueuedumptransaction Spqueuedumptransaction procedureSpqueuedumpdatabase procedure on Spqueuegetstate Spqueuegetstate procedureQueue Spqueuelogtransferreset Spqueuelogtransferreset procedureBe called directly. It resets the page and row IDs to zero Spqueueread startoffset Spqueueread procedureUse by the Message Agent Spqueuereset Spqueuereset procedureReady for a new SQL Remote setup Spqueuesetconﬁrm conﬁrmoffset Spqueuesetconﬁrm procedureUsers in the srqueuestate table Spqueuesetprogress pageid Spqueuesetprogress procedure Spqueuetransaction offset Spqueuetransaction procedure Spremote operation Spremote procedureMessage tracking information in the srremoteuser table To set a SQL Remote option Spremoteoption procedure 426 Alter Remote Message Type statement on Spremotetype procedureSpdropremotetype procedure on Spaddarticle procedure on Spremovearticle procedureTo remove an article from a publication Spremovearticle publicationname Spremovearticlecol publicationname Spremovearticlecol procedureTo remove a column from an article in a publication Spmodifyremotetable procedure on Spremoveremotetable procedureTo mark a table as unavailable for SQL Remote replication Spremoveremotetable tablename Sprevokeconsolidate username Sprevokeconsolidate procedureSpgrantconsolidate procedure on Sprevokeremote username Sprevokeremote procedure Spsubscription procedure To reset all SQL Remote information for all remote users Spsubscriptionreset procedureSpsubscriptionreset Srsubscription tables to zero or Null Appendices Page 437 Types of difference Differences in functionality Differences in approach 441 Limitations for Enterprise to Enterprise replication Page Page 445 Supported Platforms and Message Links Supported message systems Windows NT/2000/XP All message links Supported operating systems 448 Index Symbols 450 Index Current Remote User 451 Sqlremote 452 453 LTM 454 455 Subscribebyremote 456 Remote 457 458 459 Send Every 460 461 462 Tempdb 463 464 465 466 Unix 467

Creating whole-table articles

sp_add_article procedure:

Creating articles containing some of the columns in a table