Classic SQL statement Daquan one

<span style= "FONT-SIZE:14PX;" > I. Basic 1, Description: Create DATABASE Database-name 2, Description: Delete database Dbname3, Description: Backup SQL Server---Create backup data deviceuse Masterexec sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '---Start backing up backup DATABASE pubs to Testback 4 , Description: Creating a new Table CREATE table tabname (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..) Create a new table from an existing table: A:create table tab_new like Tab_old (create new table with old table) b:create table tab_new as Select Col1,col2 ... from Tab_old definit Ion Only5, Description: Delete new table TabName 6, Description: Add a column ALTER TABLE tabname add column col Type note: The column will not be deleted after it is added. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type. 7. Description: Add PRIMARY key: ALTER TABLE TabName add primary key (COL) Description: Delete primary key: Alter table tabname drop primary KEY (COL) 8, Description: Create an index: creation [u Nique] Index idxname on tabname (col ...) Delete index: DROP index idxname Note: The index is immutable and you must remove the rebuild if you want to change it.  9. Description: Create a View: Creating Views viewname As SELECT statement Delete view: Drop view Viewname10, Description: Several simple basic SQL statement choices: SELECT * FROM table1 where Range Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2) Delete: Delete from table1 where scope updated: Update table1 set field1=value1 where scope lookup: SELECT * FROM table1 where field1 lik E '%value1% '---like grammar is very subtle, check the information! Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [desc] Total: Select count as TotalCount from tab Le1 summation: Select SUM (field1) as Sumvalue from table1 average: Select AVG (field1) as Avgvalue from table1 max: select Max (field1) as MAXV Alue from table1 Minimum: select min (field1) as MinValue from table111, Description: Several advanced query operators the a:union operator UNION operator by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminate any duplicate rows in the table to derive a result table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2. The b:except operator EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated. The INTERSECT operator of the c:intersect operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated. Note: Several query result rows that use an operation word must be consistent. 12. Description: Use outer connection A, left (outer) Join: Outer join (left connection): The result set includes a matching row for the join table, and also includes all rows of the left join table. Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.CB:Right (outer) join: Left OUTER join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table. C:full/cross (outer) Join: Full outer join: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.    12, grouping: Group by: A table, once the group is complete, after the query can only get group-related information. Group-related information: (statistics) COUNT,SUM,MAX,MIN,AVG grouping criteria) when grouping in SQL Server: You cannot use fields of type Text,ntext,image as a field in the selecte statistic function. Cannot be put together with ordinary fields; 13. Operate on database: separate database: sp_detach_db; Additional database: sp_attach_db The following indicates that the append requires a full pathname of 14. How to modify the name of the database: Sp_renamedb ' old_name ', ' new_name ' II, elevation 1, Description: Copy table (copy structure only, source table name: A new table name: b) ( Access available) method one: SELECT * into B from a where 1<>1 (SQL Server only) Law II: select top 0 * into B from A2, Description: Copy table (copy data, source table name: A target table name: b  ) (Access available) insert into B (A, B, c) Select D,e,f from b;3, Description: Copy of table across databases (exact data using absolute path) (Access available) insert into B (A, B, c) select D,e,f from B in ' specific database ' WHERE condition example:. From B in ' "&server.mappath (". ") & "\data.mdb" & "' Where." 4, Description: Sub-query (table name 1:a table name 2:b) Select A,b,c from a where a in (select D from B) or: Select A,b,c from a Where a in (5), Description: Show article , author and last reply time select a.title,a.username,b.adddate from Table A, (select Max (adddate) aDddate from table where Table.title=a.title) B6, Description: Outer join query (table name 1:a table name 2:b) Select A.a, a.b, A.C, B.C, B.D, B.f from a left out J OIN b on a.a = b.c7, description: Online view query (table name 1:a) SELECT * FROM (select A,b,c from a) T where t.a > 1;8, Description: Between usage, between limit query data The bounding value is included in the perimeter, not between does not include the SELECT * from table1 where time between time1 and Time2select A,b,c, from table1 where A is not betwee N value 1 and value 29, Description: In how to use SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ') 10, Description: Two associated tables, delete information in the primary table that is not already in the secondary table delete F ROM table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1) 11, Description: Four table joint Check problem: SELECT * from a left in NER Join B on a.a=b.b right inner join C in a.a=c.c inner join D on A.A=D.D where ... 12, Description: Schedule Five minutes early reminder Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >513, Description: A SQL statement take care of database paging select top   b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort field specific implementation: About database paging: declare @start int, @end int @sql nvarchar () Set @sql = 'Select Top ' +str (@[email protected]+1) + ' +from t where rid not in (select Top ' +str (@str-1) + ' RIDs from T where rid>-1) ' EXEC sp_executesql @sql Note: It is not possible to follow a variable directly after top, so there is only such special handling in the actual application. The RID is an identity column, which is beneficial if there are specific fields after top. Because this avoids the top field if it is a logical index, the result of the query is inconsistent in the actual table (the data in the logical index is likely to be inconsistent with the data table, and if the query is indexed then the index is queried first) 14, Description: Top 10 Records select top * form table1 where scope 15, Description: Select the same data in each set of B values corresponding to the largest record of all the information (similar to the use of this can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subjects, and so on.) Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where TB.B=TA.B) 16, Description: Includes all in TableA but not in tableb and tab Rows in the LeC and all duplicate rows are derived from a result table (select a from TableA) except (select a from TableB) except (select a from TableC) 17, Description: Randomly remove 10 data  Select Top * FROM tablename ORDER by NEWID () 18, Description: Random selection record select NEWID () 19, Description: Delete duplicate record 1), delete from TableName where ID not In (the Select Max (ID) from tablename GROUP by Col1,col2,...) 2), select DISTINCT * into temp from tablename Delete from tablename insert to tablename SELECT * FROM temp rating: This operation is implicated in a large number of Data movement, which is not suitable for large capacity but data manipulation 3), for example: in an externalImport data in the Department table, for some reason, for the first time only part of the import, but it is difficult to determine the exact location, so that only the next time all imports, so that will produce a lot of duplicate fields, how to remove the duplicate field ALTER TABLE tablename--add a self-added column, adding Column_b int identity (max) Delete from TableName where Column_b not in (select Max (Column_b) from TableName GROUP by Column1,column 2,...) ALTER TABLE tablename DROP column Column_b20, description: Lists all table names in the database select name from sysobjects where type= ' u '//u represents user 21, Description: Lists the Some column names select name from syscolumns where id=object_id (' TableName ') 22, Description: List the type, Vender, and PCs fields, arranged in the Type field, case can easily implement multiple choices, Similar to case in select. Select Type,sum (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then PCs else 0 end), sum (case vender WH En ' B ' then PCs else 0 end) from tablename group By type display result: Type vender pcs Computer a 1 pc a 1 Disc B 2 disc a 2 mobile B 3 Mobile phone C 323, Description: Initialize table Table1truncate TABLE table124, Description: Select a record from 10 to 15 select Top 5 * FROM (select top to TABLE ORDER by ID ASC) Table_ alias Orde R by id Desc III, the use of tricks 1, 1=1,1=2, in the combination of SQL statements with more "where 1=1" is to select All "where 1=2" all not selected, such as: if @strWhere! = "' Beginset @strSQL = ' s Elect Count (*) as Total from [' + @tblName + '] where ' + @strWhere endelse beginset @strSQL = ' SELECT count (*) as total from [' + @tblName + '] ' end we can write the error directly! The catalog entry was not found. Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where 1=1 diazepam ' + @strWhere 2, shrink database-rebuild index DBCC REINDEXDBCC in dexdefrag--shrink data and log DBCC SHRINKDBDBCC SHRINKFILE3, compress database DBCC SHRINKDATABASE (dbname) 4, transfer database to new user with existing user rights exec Sp_change_ Users_login ' Update_One ', ' newname ', ' oldname ' Go5, check backup set restore VERIFYONLY from disk= ' E:\dvbbs.bak ' 6, repair database alter Database [Dvbbs] set SINGLE_USERGODBCC CHECKDB (' Dvbbs ', repair_allow_data_loss) with Tablockgoalter database [Dvbbs] Set Multi_usergo7, log purge set NOCOUNT ondeclare @LogicalFileName sysname, @MaxMinutes INT, @NewSize intuse tablename--database name to manipulate Elect @LogicalFileName = ' Tablename_log ',--log file name @maxminutes = ten,--Limit on time allowed to wrap log. @NewSize = 1-the size of the log file you want to set (M) setup/initializedeclare @OriginalSize intselect @OriginalSize = size from Sysfiles wher E name = @LogicalFileNameSELECT ' OrIginal Size of ' + db_name () + ' LOG is ' + CONVERT (varchar), @OriginalSize) + ' 8K pages or ' + CONVERT (varchar (30), ( @OriginalSize *8/1024)) + ' MB ' from sysfiles WHERE name = @LogicalFileNameCREATE TABLE Dummytrans (dummycolumn char (8000) Not null) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR (255) SELECT @StartTime = GETDATE (), @TruncLog = ' BACKUP LOG ' + db_name () + ' with Truncate_only ' DBCC shrinkfile (@LogicalFileName, @NewSize) EXEC (@TruncLog)-Wrap The L og if necessary. While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())--time have not expired and @OriginalSize = (SELECT size from Sysfiles WHERE name = @LogicalFileName) and (@OriginalSize * 8/1024) > @NewSize BEGIN--Outer loop. SELECT @Counter = 0 while ((@Counter < @OriginalSize/16) and (@Counter < 50000)) BEGIN--Update INSERT Dummytra NS VALUES (' Fill Log ') DELETE dummytrans SELECT @Counter = @Counter + 1 END EXEC (@TruncLog) endselect ' Final Size of ' + db_name () + ' LOG is ' ' + CONVERT (varchar (), size) + ' 8K pages or ' + CONVERT (varchar (+), (size*8/1024)) + ' MB ' from sysfiles WHERE name = @LogicalFileNameDROP table Dummytransset NOCOUNT OFF8, Description: Change a table exec sp_changeobjectowner ' tablename ', ' dbo ' 9, Store change all tables create PROCEDURE dbo. User_changeobjectownerbatch@oldowner as NVARCHAR, @NewOwner as NVARCHAR (+) Asdeclare @Name as NVARCHAR (128) DECLARE @Owner as NVARCHAR (+) DECLARE @OwnerName as NVARCHAR (+) DECLARE curobject CURSOR for select ' Name ' = Name  , ' Owner ' = user_name (UID) from Sysobjectswhere user_name (UID) [Email protected]order by Nameopen Curobjectfetch NEXT from Curobject to @Name, @OwnerWHILE (@ @FETCH_STATUS =0) begin if @[email protected] begin set @OwnerName = @OldOwner + '. ' + RTrim (@Name) exec sp_changeobjectowner @OwnerName, @NewOwnerend--select @name, @NewOwner, @OldOwnerFE  TCH NEXT from Curobject to @Name, @OwnerENDclose curobjectdeallocate curObjectGO10, SQL Server, directly loops write data declare @i intset @i=1while @i<30Begin INSERT INTO Test (userid) VALUES (@i) Set @[email protected]+1end case: There is a table that asks for all of the failed grades in the mounting, on the basis of each increase of 0.1, to make them just fine  Pass: Name score Zhangshan Lishi Wangwu Songquan 69while ((select min (score) from tb_table) <60) beginupdate tb_table set score =score*1.01where score<60if (select min (score) from tb_table) >60 b Reak Else Continueend Data Development-Classic 1. Sort by last name stroke: Select * from TableName ORDER by CustomerName Collate chinese_prc_stroke_ci_as/ /from less than 2. Database encryption: Select Encrypt (' original password ') Select Pwdencrypt (' original password ') Select Pwdcompare (' original password ', ' encrypted after password ') = same; otherwise different encrypt ( ' Original password ') Select Pwdencrypt (' original password ') Select Pwdcompare (' original password ', ' encrypted password ') = same; 3. Retrieve the fields in the table: Declare @list varchar (1000), @sql nvarchar (+) Select @[email protected]+ ', ' +b.name from sysobjects a,syscolumns b where a.id=b.id and A.name= ' Table A ' Set @sql = ' select ' +right (@list, Len (@list)-1) + ' from Table A ' EXEC (@sql) 4. View hard disk partition: exec master. Xp_fixeddrives5. Compare the equality of A/b table: if (select Checksum_agg (Binary_checksum (*) from A) = (select Checksum_agg (binary_checksum (*)) from B) print ' equal ' elseprint ' not equal ' 6. Kill all the Event Explorer processes: DECLARE hcfor Each CURSOR GLOBAL for SELECT ' kill ' +rtrim (spid) from Master.dbo.sysprocessesWHERE program_name in (' SQL Profiler ', N ' sql thing Piece Profiler ') EXEC sp_msforeach_worker '? ' 7. Record Search: Start to n records select top n * FROM table-------------------------------N to M Records (to have primary index ID) select top M-n * FROM table Where ID in (sele CT top M ID from table) Order BY id Desc----------------------------------N to end record Select Top N * FROM table ORDER BY id desc Case Example 1: A table with more than 10,000 records, the first field of the table RecID is the self-growth field, write an SQL statement, find the table of the 31st to 40th Records.    Select Top RecId from a where recid not in (select top of recid from a) analysis: if this writes a certain problem, if RecId has a logical index in the table. Select Top Ten recid from A where ... Is looked up from the index, and the back of select top RecId from A is looked up in the datasheet, so that the order in the index is likely to be inconsistent with the data table, which results in a query that is not the intended data. Solution 1, with order by select top recid from a order by ricid if the field is not self-growing, problem 2 will occur, and in that subquery the condition is also added: Select Top recid from a wher E recid>-1 Example 2: The last record in the query table does not know how much data the table has, as well as the table structure. Set @s = ' selECT top 1 * from T where PID is not in (select top ' + str (@count-1) + ' pid from T ') ' Print @s exec sp_executesql @ S9: Gets all user tables in the current database select name from sysobjects where xtype= ' u ' and status>=010: Gets all the fields of a table select name from syscolumns WH ere id=object_id (' Table name ') select name from syscolumns where ID in (select id from sysobjects where type = ' u ' and name = ' table name ') The effect is the same in both ways 11: View a table-related view, stored procedure, function select A.* from sysobjects A, syscomments b where a.id = b.ID and b.text like '% table name% ' 12: view when All stored procedures in the previous database select name as stored procedure name from sysobjects where xtype= ' P ' 13: Query user created all databases select * from Master. sysdatabases D where Sid not in (select Sid from Master: syslogins where name= ' sa ') or select dbid, name as Db_name from master. sysdatabases where Sid <> 0x0114: Querying a table for fields and data types select Column_name,data_type from Information_ Schema.columnswhere table_name = ' table name ' 15: Data manipulation between different server databases--Create a linked server exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' remote service Name or IP address ' exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username', ' password '--query Example SELECT * from ITSV. Database name. dbo. Table name--import Example select * into table from ITSV. Database name. dbo. Table name--Remove linked server exec sp_dropserver when no longer in use ' ITSV ', ' droplogins '--connect remote/LAN data (Openrowset/openquery/opendatasource)--1, openrowset--query Example SELECT * FROM OPENROWSET ( ' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)--Raw cost surface select * into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name--Import the local table into the remote table Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name) Select *from Local table--Update local table update bset B. Column a=a. Column A from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table bon a.column1=b.column1--openquery usage you need to create a connection--first create a connection to create a linked server exec sp_ Addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '--query SELECT *from openquery (ITSV, ' select * from database. dbo. Table name ') --Import the local table into the remote table Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ') SELECT * from local table-Update local table update bset b. column b=a. Column Bfrom Openquer Y (ITSV, ' SELECT * from database. dbo. Table name ') as a inner join local table B on a. Column a=b. Column A--3, opendatasourCe/openrowsetselect *from opendatasource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). test.dbo.roy_ta--Import the local table into the remote table Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name SELECT * from local table SQL Server basic functions SQL Server Basic functions 1. String function length and parsing with 1,datalength (char_expr) returns a string containing the number of characters , but does not contain the trailing space 2,substring (expression,start,length) substring, the subscript of the string is from "1", start is the start position, length is the string length, the actual application of Len (expression) Gets its length 3,right (char_expr,int_expr) returns the int_expr character to the right of the string, and also uses left to the opposite 4,isnull (check_expression, Replacement_value) If Check_expression is empty, returns the value of Replacement_value, NOT NULL, returns the Check_expression character action class 5,sp_addtype custom type For example: EXEC sp_addtype Birthday, datetime, ' NULL ' 6,set nocount {On|off} causes the returned results to contain no information about the number of rows affected by the Transact-SQL statement. If some of the statements contained in a stored procedure do not return much of the actual data, this setting can significantly improve performance because of the significant reduction in network traffic. Set NOCOUNT settings are set at execution or runtime, not at parse time. When SET NOCOUNT is on, no count is returned (indicating the number of rows affected by the Transact-SQL statement). When SET NOCOUNT is OFF, return count common sense in SQL queries: up to how many tables or views can be followed from: 256 in the SQL statement, the order BY, the query, the first sort, and then in SQL, the maximum capacity of a field is 8000, and for nvarchar (4000), since nvarchar is a Unicode code. SQLSERVER2000 Synchronous replication Technology Implementation Step one, preparatory work 1. Publisher, subscribers create a Windows user with the same name, and set the same password. As a valid access user for the release snapshot folder-Administrative Tools-Computer Management-users and Groups-right-click User-New user-Create a user who is affiliated to the Administrator group (synuser) 2. At the publisher, create a new shared directory , as a storage directory for the snapshot files of the publication, Operation: My Computer--d:\ Create a new directory named: pub--Right click this new directory--Properties--Share--select "Share this Folder"--with "permissions" to set specific user rights by buttons, to ensure that the user (Synuser) created in the first step has all the permissions on the folder--OK 3. Setting up SQL Agent Start User (SQLServerAgent) service starts--Program--management tool--service--right-click sqlserveragent--Properties--Login--Select "This Account"-- Enter or select the Windows login user name (Synuser) created in the first step--Enter the password for the user in the password 4. Set the SQL Server authentication mode to resolve permissions issues when connecting (publish/Subscribe server does this setting) Enterprise Manager--right-click SQL Instance--Properties--security--Authentication--Select SQL Server and Windows--OK 5. Register Enterprise Manager with each other at the Publisher and subscriber-right-click SQL Server Group-new SQL Server Registration ...--next--The available server, enter the name of the remote server you want to register--add--next--Connect using, choose second SQL Server Authentication--Next--Enter username and password (synuser)--Next--Select SQL Server Group , you can also create a new group--The next--complete 6. For IP only, cannot use the computer name, register the server alias for it (this step is not used in the implementation) (on the connection side configuration, for example, at the Subscriber configuration, the server name is entered in the publisher's IP) Start--Program--microsoft SQL server--Client Network Utility--alias--Add--Network library Select "TCP/IP"-- Server alias Enter SQL Server name--Connection parameter--Enter the SQL Server IP address in the server name--If you modify the SQL port, takeSelect "Dynamically Determine Port" and enter the corresponding port number two, formal configuration 1, configure the Publisher to open Enterprise Manager, on the publisher (B, C, D) perform the following steps: (1) from the [Tools] drop-down menu, select [Configure Publishing, Subscribers, and distribution] from the [Copy] submenu] The Configure Publishing and Distribution Wizard appears (2) [Next] Select the Distributor to select the Publisher itself as the Distributor or other SQL Server (select itself) (3) [next] set the snapshot folder with default \\servername\Pub (4) [Next] Custom configuration You can choose: Yes, let me set the distribution database properties to enable the Publisher or set publishing settings No, use the following default settings (recommended) (5) [Next] set the distribution database name and location with default values (6) [next] enable Publisher to select the server to publish as (7) [Next] Select the database and publication type you want to publish (8) [Next] Select Register Subscriber (9) [Next] Complete configuration 2, create publication Publisher B, C, D (1) from the [Tools] menu, select [Create and manage publications] from the Copy submenu (2) Select the database where you want to create the publication. then click [Create Publication] (3) Click [Next] in the prompt dialog box of the Create Publication Wizard, a dialog box will pop up. The contents of the dialog box are the three types of replication. We're going to choose the first one. The default snapshot publication (two other people can see Help) (4) Click Next to specify the type of database server that can subscribe to the publication, and SQL Server allows data replication between different databases, such as Oracle or access. But here we choose the database server running SQL Server 2000 (5) Click [Next] the system pops up a dialog box that defines the article. Note: If a transactional publication is selected earlier, then only the table with the primary key (6) is selected in this step to select the publication name and description (7) The Custom Publication Properties wizard provides choices: Yes I will customize data filtering, enable anonymous subscriptions, or other custom properties to create a publication as specified (recommended for customization) (8) [next] Select how to filter the publication (9) [next] to choose whether to allow anonymous subscriptions 1) If you select a named subscription, you will need to add a Subscriber method at the Publisher: [Tools]->[replication]->[Configuration Publications, subscribers, and Distribution properties]->[subscribers] Add a prompt that would otherwise be present at the Subscriber when requesting a subscription: Change publication does not allow anonymous subscriptions if you still need anonymous subscriptions, use the following workaround [Enterprise Manager]->[Replication]-&GT [Publish content]->[Properties]->[subscription options] Select Allow anonymous pull subscriptions 2) If you select Anonymous subscription, the above hint will not appear when you configure the Subscriber (10) [Next] set the Snapshot Agent schedule (11) [Next] Finish configuring the database that created the publication after the creation of the publication has become a shared database with data Srv1. Library Name: Author has fields: Id,name,phone, srv2. Library name. Author has fields: Id,name,telphone,adress requirements: SRV1. Library name. Author add record Srv1. Library Name: Author records increase SRV1. Library Name: Author The phone field is updated, the SRV1. Library name. Author corresponding field Telphone update--*/--Approximate process steps--1. Create a connection server on SRV1 to operate SRV1 in SRV2, implement synchronous exec sp_addlinkedserver ' srv2 ', ', ' sqloled B ', ' srv2 ' SQL instance name or IP ' exec sp_addlinkedsrvlogin ' srv2 ', ' false ', NULL, ' username ', ' password ' go--2. In both the SRV1 and SRV2 computers, start MSDTC ( Distributed transaction Processing service), and is set to start automatically.  My Computer--control Panel--management tool--service--right distributed Transaction coordinator--Property--Start--and set the startup type to start the go automatically--and then create a job to call the synchronization stored procedure above. Enterprise Manager--Manage--sql Server Agent--right-click Job--New job--Enter job name in "Steps"--new--"Step name" Enter step Name--select "Transact-SQL script (TSQL)" in "type"--"database" Select the database that executes the command--Enter the statement to execute in the command: EXEC p_process--OK--"dispatch" item--New schedule--Enter the schedule name in "name"--Select your job execution schedule in "schedule Type"--If you choose "Recurring"--click "Change" To set your schedule then start the SQL Agent service and set it to start automatically, otherwise your job will not be executed by the Setup method: My Computer--control Panel--management tools--services--right-click SQL Serveragent--Property--Startup Type--select "Auto Start"--OK. --3. Method of implementing synchronous processing 2, timed synchronization--creating the following synchronization stored procedure in SRV1 create proc p_process as--updating modified data Update B set Name=i.name,telphone=i.telpho NE from SRV2. Library name. Dbo.author B,author I where b.id=i.id and (B.name <> i.name or B.telphone <> i.telphone)--Insert new Added data insert Srv2. Library name. Dbo.author (id,name,telphone) Select Id,name,telphone from author I where NOT EXISTS (SELECT * from SRV 2. Library name. Dbo.author where id=i.id)--Delete deleted data (if required) Delete b from srv2. Library name. Dbo.author b where NOT EXISTS (SELECT * from AUT Hor where id=b.id) go</span>