Manuals / Sybase / Personal Care / Microscope & Magnifier

Sybase DC38133-01-0902-01 manual SQL Remote options

Models: DC38133-01-0902-01

1 485

Page 333

		Chapter 14. Utilities and Options Reference

SQL Remote options
Function	Replication options are database options included to provide control over
	replication behavior.
Adaptive Server	SET [ TEMPORARY ] OPTION
Anywhere Syntax	[ userid. PUBLIC. ]option_name = [ option_value ]
Adaptive Server	exec sp_remote_option option-name,option-value
Enterprise syntax:
Parameters

	Argument	Description

	option_name	The name of the option being changed.
	option-value	A string containing the setting for the option.
Description	The following options are available.
	OPTION		VALUES	DEFAULT
	OPTION		VALUES	DEFAULT

	Blob_threshold		integer, in kb	256
	Compression		–1 to 9	6
	Delete_old_logs		ON, OFF	OFF
	External_remote_options		ON, OFF	OFF
	Qualify_owners		ON, OFF	ON
	Quote_all_identiﬁers		ON, OFF	OFF
	Replication_error		procedure-name	NULL
	Save_remote_passwords		ON, OFF	ON
	SR_Date_Format		date-string	yyyy/mm/dd
	SR_Time_Format		time-string	hh:nn:ss.Ssssss
	SR_Timestamp_Format		timestamp-string	yyyy/mm/dd
				hh:nn:ss.Ssssss
	Subscribe_by_remote		ON,OFF	ON
	Verify_threshold		integer	256
	Verify_all_columns		ON,OFF	OFF

These options are used by the Message Agent, and should be set for the user

315

Page 333

Image 333

Sybase DC38133-01-0902-01 manual SQL Remote options

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 141Using SQL Remote with Replication Server 277 Administering SQL Remote for Adaptive Server Anywhere241System Objects for Adaptive Server Anywhere 325 Utilities and Options Reference 291Command Reference for Adaptive Server Enterprise 379 Command Reference for Adaptive Server Anywhere 353SQL Remote for Adaptive Server Enterprise and Adaptive Server Anywhere Differences 437Supported Platforms and Message Links 445 Index 449Viii About This Manual Mail or ﬁle transferSystems Adaptive Server Anywhere Database Administration Guide This SQL Anywhere Studio documentationPage Page Release Savepoint savepoint-name Documentation conventionsClient application Following icons are used in this documentationDatabase server, such as Sybase Adaptive Server Anywhere XivAsademo.db Adaptive Server Anywhere sample databaseFinding out more and providing feedback Page Xviii Introduction to SQL Remote Page This chapter introduces SQL Remote and the documentation Welcome to SQL RemoteAbout SQL Remote SQL Remote for Adaptive Server Anywhere Enables replicationTo install the SQL Remote software Windows Product installationTo install the SQL Remote software Unix About this manualPage SQL Remote SQL Remote ConceptsSQL Remote components Data server Client applicationsMessage Agent Message system client Two-table synchronization definition Publications and subscriptionsAt the remote database ReplicationSQL Remote features Page Server-to-laptop replication for mobile workforces Some sample installationsServer-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 toTo install the SQL Remote system objects Installing the SQL Remote system objectsPreparing your Adaptive Server Enterprise server Ensuring Tempdb is large enoughSQL 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 RemoteUninstalling SQL Remote To uninstall the SQL Remote objects from a databaseTo uninstall the stable queue from a database Tutorials for Adaptive Server Anywhere Users Introduction Just on those issues important for replicationGoals DatabaseTables are described in more detail as follows Tutorials for Adaptive Server Anywhere UsersRepkey Replication goalsName CustkeySybase Central or command-line utilities To prepare for the tutorial Preparing for the Sybase Central replication tutorialTo add tables to the consolidated database Add a SQL Remote message type Setting up a consolidated databaseAdd the publisher and remote user to the database To add an address to a message typeTo create a new user as the publisher To add a remote user Add publications and subscriptionsTo add a publication Enter the expression repkey Set up the remote database in Sybase CentralClick Finish to create the publication Publication for the remote database user ﬁelduserTo extract a database To create the databases and directories for the tutorial Preparing for the replication tutorialSet up the consolidated database To add the tables to the consolidated databaseCreate a SQL Remote message type To create the message type Grant Publish and Remote at the consolidated databaseTo create a publisher for the database Create publications and subscriptions SQL, to create a publicationTable SalesRep, and some of the rows of the table Customer Set up the remote database To create the publicationTo create the subscription Extract the remote database information Load the remote database informationTo load the 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 databaseReceive data at the remote database To send the data to the remote databaseTo receive data at the remote database Commit the insertion by executing the following statement To verify that the data has arrivedCustkey 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 replicationTo install SQL Remote into the hq database Setting up the consolidated database Create the SalesRep table with the following statementCreate the Customer table with the following statement Create the necessary users and permissions Create the message links and addressesTo create the subscriber To create the publisherCreate an empty publication Create the publication and subscriptionCreate a subscription to SalesRepData for ﬁelduser , with a To create a subscriptionSubscription value of rep1 Extract the remote databaseTo load the data into the database using Interactive SQL Utility onTo create a database ﬁle named ﬁeld.db Connect to the server using the Interactive SQL utility To load the data into the database as a batch processRun the script from Interactive SQL Connect to the Adaptive Server Enterprise server from isql To enter data at the Adaptive Server Enterprise databaseTo receive the data at Adaptive Server Anywhere To replicate the data from Adaptive Server EnterpriseCommit 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 dataSQL Remote Message Agent does not perform any character set Using compatible sort orders and character setsConversions Between Ssremote and Adaptive Server Enterprise whenever819LATIN1 Eucjapan Replication of updates How statements are replicatedReplication of inserts and deletes User, the row to be updated is not found Clause of the Update statementStatement RepresentativesThis case, the Update does not update anything Replication of proceduresProcedure call is not replicated Replication of triggersBody of your triggers Message causing the conﬂictActions carried out by conﬂict Some Before triggers can produceReplication of data deﬁnition statements Replication of blobs How data types are replicatedReplication of dates and times Select Who gets what? Principles of SQL Remote Design Replication errors and conﬂicts Replication errorsReplication conﬂicts 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 receivesPublishing whole tables Publishing dataTo publish one or more entire tables Sybase Central To publish one or more entire tables SQLPublishing only some columns in a table For more information, see the Create Publication statementTo publish only some columns in a table Sybase Central To publish only some columns in a table SQL Publishing only some rows in a tablePublishing only some rows using a Where clause To create a publication using a Where clause Sybase CentralTo create a publication using a Where clause SQL Publishing only some rows using a subscription expression Column To create an article using a subscription expression SQLTo add articles Sybase Central Altering existing publicationsDropping publications To remove articles Sybase CentralTo modify an existing publication SQL To delete a publication Sybase Central To delete a publication SQLSee also the Drop Publication statement ASA SQL Reference, Publication design for Adaptive Server Anywhere Design issues overviewDesign tips for performance Conditions for valid articlesPay attention to Update Publication Triggers In particular Here is a simple database that illustrates the problem Contact exampleThree tables are described in more detail as follows Following information Those customers assigned to them, from the Customer table106 Partitioning the Customer table in the Contact example Partitioning the Contact table in the Contact exampleTerritory realignment in the Contact example Properly transferred to the new sales representative RepsAlong with the Customer For which there is no longer a CustomerSpecial Update statement for publications Assume the following data Understanding this helps in designing efﬁcient publications∙ SalesRep table ∙ Customer tableSalesRepData Publication Name rep1 Before list Sharing rows among several subscriptions Policy exampleMany-to-many relationship in the database Entire SalesRep table With the following informationInvolving the sales rep subscribed to the data 113Publication Contact example on Territory realignment with a many-to-many relationship116 Remote databases contain the proper dataThat this subquery can be multi-valued Customer being transferred Where custkey = OldRow.custkeyCan be multi-valued Dealing with the customer, taken from the Policy tableCustomer SQL Remote Design for Adaptive Server Anywhere ID Rep Dept Ann Marc Managing conﬂictsSQL Remote installation at the consolidated database Must be excluded from the installation by proper designHow SQL Remote handles conﬂicts UpdatingUsing conﬂict resolution triggers Implementing conﬂict resolutionReferencing OLD AS oldval Create Trigger trigger-nameResolving date conﬂicts Conﬂict resolution examplesResolving inventory conﬂicts Reporting conﬂicts Increments from the two updates. For exampleDesigning to avoid referential integrity errors Designing triggers to avoid errorsIncludes the salesorder table Page Ensuring unique primary keys Using global autoincrement default column valuesSetting the Globaldatabaseid value Declaring default global autoincrementPrimary key values for Global Default Autoincrement values To set the global database identiﬁcation numberDatabase will have Globaldatabaseid set to How default values are chosen Using primary key pools Primary key pool table Replicating the primary key poolTo replicate the primary key pool SQL Filling and replenishing the key pool Cursor for 136Else Adding new customersInsert Primary key pool summaryTo create and manage subscriptions in Sybase Central Creating subscriptions140 For more information, see Create Subscription statement on141 SQL Remote Design for Adaptive Server EnterpriseDesign overview Creating publications Creating whole-table articlesCreating articles containing some of the columns in a table Creating articles containing some of the rows in a table Creating an article using a Where clause To create an article using a Where clauseCreating an article using a subscription column To create an article using a subscription column Spaddremotetable tablenameSpaddarticle publicationname, tablename NULL, columnname 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 149150 Are realigned Exec spcreatepublication ’SalesRepData’ go Adding a subscription-list column to the Contact tableLog entries are values, not subscribers Update on the Customer table Update on the Contact tableMaintaining the subscription-list column For the following operationsSQL Remote Design for Adaptive Server Enterprise Tuning extraction performanceTo create a subscription view Create a view that contains this subquery. For example156 Solving the problem Foreign KEY 158Subscriptions to this publication take the following form These objects159 Triggers For Insert AS Begin 161Ensure the quotedidentiﬁer option is set to on Tuning extraction performance for shared rows163 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 arrivedRemoterowtable. 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ﬁrst conﬂict resolution example Two-table database is as follows168 After the procedure is run, the rows in the OldCustomer Conﬂict-causing UpdateFollows @lostname value is the value that was overridden bySecond conﬂict resolution example To test the exampleConﬂictLog table has a single row, showing the conﬂict 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 presentThere 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 #remote173 Page Columns of this table have the following meanings 175 Primary key pool176 To replicate the primary key pool177 Adding new customers Testing the key pool To test the primary key pool179 Primary key pool summary Spsubscription create, puborders SamS ’856’ Page SQL Remote Page SQL Remote replication installation Deploying and Synchronizing Databases185 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 databasesExample 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 ﬁleBefore extracting a database Using the extraction utility from Sybase CentralTo extract a database for a remote user Sybase Central Designing an efﬁcient extraction procedureAn efﬁcient approach to extracting many databases Extracting groups Limits to using the extraction utilityThis setup is illustrated in the following diagram Using the extraction utility for Adaptive Server EnterpriseCustomizing the system tables Synchronizing data over a message system 199 SQL Remote AdministrationManagement overview Granting and revoking Publish permissions Managing SQL Remote permissionsTo make an existing user the publisher Sybase Central To create a new user as the publisher Sybase CentralTo revoke Publish permissions Sybase Central 202Exec sppublisher ’SBeaulieu’ go Granting Remote permissions Granting and revoking Remote and Consolidate permissionsFollowing options To make an existing user remote Sybase CentralUse an Smtp e-mail system Send messages to e-mail address sbeaulieu@acme.comSelecting a send frequency Granting Consolidate permissions Permissions to user hquser, using the ﬁle message linkStatement is executed automatically at the remote database Agent is runAssigning permissions in multi-tier installations To revoke Remote permissions Sybase CentralPermission from user SBeaulieu Publish Working with message types Using message typesUsing Sybase Central to work with message types To add a message type Sybase CentralTo alter 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-stringTo drop a message type SQL To alter a message type SQLCreate Remote Message Type statement on 213To set a message control parameter Adaptive Server Anywhere Setting message type control parametersBy using the ﬁle message system ﬁle message system215 Ftp message system Troubleshooting ftp problems Smtp message system Mote messages218 SQL Remote Administration Mapi message system Sharing SMTP/POP addressesSupported operating systems on VIM message system VIM message control parameters Message Agent batch and continuous modes Running the Message AgentNames are ssremote and dbremote, respectively Enterprise works onTo run the Message Agent in continuous mode Replication system recovery proceduresTo run the Message Agent in batch mode Connections used by the Message AgentSee the sections Transaction log and backup management on Ensuring consistent Message Agent settingsAdaptive Server Enterprise transaction log and backup Management onMessage Agent and replication security Troubleshooting errors at remote sitesTo 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 onTuning throughput by controlling Message Agent threading Tuning Message Agent performanceTuning throughput by caching messages Polling interval Tuning incoming message pollingRequesting resends Example231 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 messagesDbremote -ru 1h Encoding scheme Encoding and compressing messagesCreating custom encoding schemes Message tracking system Status information in the remoteuser tableTracking messages by transaction log offsets Are offsets in the local database transaction log, Agent -loption to change this settingCannot be a later offset than logsent Transaction logIts resendcount Handling of lost or corrupt messagesPage 241 Administering SQL Remote for Adaptive Server AnywhereStarting the Message Agent Running the Message Agent as a serviceFull 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 outputExample e-mailing notiﬁcation of errors Implementing error handling proceduresFollowing stored procedure implements this notiﬁcation 246Commit Work Columns have the following meaning 247248 Here is a sample insert into the table from the above errorSetting the transaction log directory Transaction log and backup managementBackup utility options Using the live directory as the transaction log directorySuch a directory is kept in proper shape Using the backup directory as the transaction log directory Following command line252 Managing old transaction logs Recovery with a single transaction log To recover the database254 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 logsCommand 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 ﬁleBackup procedures at remote databases Apply the renamed transaction logs in order, as followsRestart your system as normal 258 Upgrading consolidated databasesTo 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 replicated263 Administering SQL Remote for Adaptive Server EnterpriseHow the Message Agent for Adaptive Server Enterprise works Scanning the transaction logMessage Agent on Stable queue Administering SQL Remote for Adaptive Server Enterprise266 Message Agent operation phases267 TransactionStable Logqueue Message Agent268 Running multiple Message Agents For setup instructions, see Setting Up SQL Remote onTo execute Agent output Protecting against media failure on the transaction log Transaction log management Stable queue recovery issuesTruncation point can be reset with the following command With the following commandFreed by Dump Transaction 274Making schema changes Schema modiﬁcations 277 Using SQL Remote with Replication ServerWhen you need to use the SQL Remote Open Server How the pieces ﬁt together Architecture for Replication Server/SQL Remote installationsMessage 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 isSetting up SQL Remote Open Server Same regardless of previous installationsAdd the SQL Remote Open Server to the setup This stage is 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 dataSuspend and restart the connection Other issues 288 This part presents reference material for SQL Remote Page 291 Utilities and Options ReferenceMessage Agent Tracking system to ensure message deliveryDbremote ssremote options directory Utilities and Options Reference Replication security on Listing, see Connections used by the Message Agent on295 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 NetWareExtracting a remote database in Sybase Central Database Extraction utility303 Extraction utility304 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 databasesGain 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 318Relationships on SQL Remote event-hook procedures Sphookdbremotebegin and sphookssrmtbeginSphookdbremoteend and sphookssrmtend Sphookdbremoteshutdown and sphookssrmtshutdown Sphookdbremotereceivebegin and sphookssrmtreceivebeginSphookdbremotereceiveend and sphookssrmtreceiveend Sphookdbremotesendend and sphookssrmtsendend Sphookdbremotesendbegin and sphookssrmtsendbeginSphookdbremotemessagesent and sphookssrmtmessagesent Sphookdbremotemessagemissing and sphookssrmtmessagemissing323 Has applied a set of messages from a userPage 325 System Objects for Adaptive Server AnywhereSQL Remote system tables Sysarticle tableEach row describes an article in a SQL Remote publication Sysarticlecol table It is in, and the publication it is part327 Sysremoteoption table Syspublication tableSysremoteoptiontype table Each row describes a SQL Remote publicationSysremoteuser table Sysremotetype tablePublisher address 329Consolidate 330Syssubscription table Remote permissions to one publication331 332 Sysarticles view SQL Remote system viewsSysarticlecols view Syspublications viewSysremoteusers view Sysremoteoptions viewSyssubscriptions view Each row lists information about a subscription335 336 337 System Objects for Adaptive Server EnterpriseSrarticle table #remote tableManage SQL Remote information Table has a single rowSrmarker table Srarticlecol tableDatabases in the same session 339Sroption table Srobject tableSrpassthrough table Each row describes a replication option used by SQL RemoteSrpublication table Srpublisher tableSrremoteoption table Srremotetable table Srremoteoptiontype tableSrremotetype table 342343 Srremoteuser tableReceived 344345 Srsubscription tableSrarticles view Srarticlecols viewSrpublications view Srremoteoptions view Srremotetables viewSrremotetypes view 348 Srremoteusers view349 Srsubscriptions viewSrqueuestate table Stable Queue tablesStable queue 350351 Srtransaction tableSrqueuecoordinate table Srconﬁrmedtransaction tableEach row marks the corresponding row in srtransaction 352353 Command Reference for Adaptive Server Anywhere354 Been created Alter Remote Message Type statementMust have DBA authority Automatic commitCreate Publication statement Create Publication statement ASA SQL Reference,Subscribe by expression Messages from a database Create Remote Message Type statementCreate Remote Message Type statement SQL Remote ASA SQL Create Remote Message Type message-systemCreate Subscription statement SQL Remote ASA SQL Reference Create Subscription statementCreate 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 ReferenceDrop Publication statement ASA SQL Reference, Drop Publication statementGranted 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-systemDrop Subscription statement SQL Remote ASA SQL Reference Drop Subscription statementCurrent 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 ReferenceSppassthrough 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 statementRevoke Consolidate statement Sprevokeconsolidate procedure onSQL Remote messages from this database Automatic commit. Drops all subscriptions for the userCurrent publisher Revoke Publish statementRevoke Publish statement SQL Remote ASA SQL Reference Revoke Publish from useridRevoke Remote statement Sprevokeremote procedure onMessages from this database Revoke Remote statement SQL Remote ASA SQL ReferenceRevoke Remote DBA statement SQL Remote ASA SQL Reference Revoke Remote DBA statementSplinkoption procedure on SET Remote Option statementMessage link Start Subscription Start Subscription statementStop Subscription Stop Subscription statementSynchronize Subscription Synchronize Subscription statementUpdate statement Update statement SQL Remote ASA SQL Reference,Create Trigger statement on 379 Command Reference for Adaptive Server Enterprise380 Spaddarticle procedure Making schema changes on SalesRepData382 Spaddarticlecol procedure To add a column to an article in a publicationSpaddarticlecol publicationname To mark a table for SQL Remote replication Spaddremotetable procedureRemote rows are stored in tables named oldCustomer Default conﬂict resolutionRemoteCustomer, respectively 385Spdroppublication procedure on Spcreatepublication procedureTo create a publication Spcreatepublication publicationnameSpcreatepublication procedure on Spdroppublication procedureTo drop a publication from the database Spdroppublication publicationnameSpdropremotetype typename Spdropremotetype procedureSpdropsqlremote procedure Spqueuedrop procedure onProcedure on Databases acting as remote databases Spgrantconsolidate procedureSee also Description Example 392 Spgrantremote procedure Single quotes Current database must be granted Remote permissions usingOne of the publications in the current database Speciﬁed395 Splinkoption procedure SET Remote Option statement onDatabases Ftp message system on ﬁle message system onMapi message system on Smtp message system onTo change the description of an article in a procedure Spmodifyarticle procedureSubscription expression Expression that is the repkey column399 Spaddremotetable procedure on Spmodifyremotetable procedureSpremoveremotetable procedure on Managing conﬂicts on401 Sppassthrough procedure Sppassthroughpiece procedure To build a long SQL statement for passthroughSppassthroughpiece string 404 Resents passthrough mode Sppassthroughstop procedureSppassthroughstop procedure resents the list of recipients Currently being builtSppassthroughsubscription procedure StatementsSppassthroughsubscription publicationname Sppassthroughuser username Sppassthroughuser procedureConsolidated database Sppopulatesqlanywhere procedureCalled directly SppopulatesqlanywhereSppublisher username Sppublisher procedureSpqueueclean Spqueueclean procedureBe called directly Log was scannedSpqueueconﬁrmeddeleteold Spqueueconﬁrmeddeleteold procedureSpqueueconﬁrmedtransaction procedure Match this offsetSpqueueconﬁrmedtransaction offset Spqueuedeleteold procedure Been conﬁrmed by all remote databasesSpqueuedeleteold Spdropsqlremote procedure on Spqueuedrop procedureSpqueuedumpdatabase procedure Spqueuedumptransaction procedure onStable queue recovery issues on Spqueuedumptransaction procedure Spqueuedumpdatabase procedure onSpqueuedumptransaction Spqueuegetstate procedure QueueSpqueuegetstate Spqueuelogtransferreset procedure Be called directly. It resets the page and row IDs to zeroSpqueuelogtransferreset Spqueueread procedure Use by the Message AgentSpqueueread startoffset Spqueuereset procedure Ready for a new SQL Remote setupSpqueuereset Spqueuesetconﬁrm procedure Users in the srqueuestate tableSpqueuesetconﬁrm conﬁrmoffset Spqueuesetprogress pageid Spqueuesetprogress procedureSpqueuetransaction offset Spqueuetransaction procedureSpremote procedure Message tracking information in the srremoteuser tableSpremote operation To set a SQL Remote option Spremoteoption procedure426 Spremotetype procedure Spdropremotetype procedure onAlter Remote Message Type statement on Spaddarticle procedure on Spremovearticle procedureTo remove an article from a publication Spremovearticle publicationnameSpremovearticlecol procedure To remove a column from an article in a publicationSpremovearticlecol publicationname Spmodifyremotetable procedure on Spremoveremotetable procedureTo mark a table as unavailable for SQL Remote replication Spremoveremotetable tablenameSprevokeconsolidate procedure Spgrantconsolidate procedure onSprevokeconsolidate username Sprevokeremote username Sprevokeremote procedureSpsubscription procedure To reset all SQL Remote information for all remote users Spsubscriptionreset procedureSpsubscriptionreset Srsubscription tables to zero or NullAppendices 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 LinksSupported message systems Windows NT/2000/XP All message links Supported operating systems448 Index Symbols450 IndexCurrent Remote User 451Sqlremote 452453 LTM 454455 Subscribebyremote 456Remote 457458 459 Send Every 460461 462 Tempdb 463464 465 466 Unix 467

SQL Remote options

replication behavior.

exec sp_remote_option option-name,option-value

Enterprise syntax:

Parameters

The following options are available.