MYSQL Beginners (two) commonly used command - from blog Park

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The MySQL SQL statement Daquan
1, Description: create database
CREATE DATABASE database-name
2, Description: delete database
drop database dbname
3, Note: Backup SQL Server
Create a backup of your data -- device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
- begin backup
BACKUP DATABASE pubs TO testBack
4, Description: create a new table
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
Create a new table the table according to the:
A: Create table tab_new like tab_old (using the old table to create a new table)
B: create table tab_new as select col1,col2… from tab_old definition only
5, Description: delete the new table
drop table tabname
6, Description: add a column
Alter table tabname add column col type
Note: column increase will not be deleted. The DB2 column data type does not change, the only change is to increase the length of varchar type.
7, Description: add primary key: Alter table tabname add primary key(col)
Description: the removal of the primary key: Alter table tabname drop primary key(col)
8, Description: create index: create [unique] index idxname on tabname(col….)
Drop index: drop index idxname
Note: the index is not subject to change, want to change must be removed to build.
9, Description: create view: create view viewname as select statement
Deleting a view: drop view viewname
10, Description: basic SQL statements a few simple
Select the select * from table1 where range:
Insert: insert into table1(field1,field2) values(value1,value2)
Delete: delete from table1 where
Update: update table1 set field1=value1 where
Search: select * from table1 where field1 like ’%value1%’ ---like syntax is very exquisite, check the information!
Sort: select * from table1 order by field1,field2 [desc]
Total: select count as totalcount from table1
Summation: select sum(field1) as sumvalue from table1
Average: select avg(field1) as avgvalue from table1
Maximum: select max(field1) as maxvalue from table1
Least: select min(field1) as minvalue from table1
11, Note: some advanced query operations.
A: The UNION operator
The UNION operator by combining the results of the other two tables (for example TABLE1 and TABLE2) and eliminate any duplicate rows in the table to derive a list of results. When ALL is used together with the UNION (UNION ALL), does not remove duplicate rows. The two case, each row from the TABLE1 table is not derived from TABLE2.
B: The EXCEPT operator
The EXCEPT operator by including all in TABLE1 but not in TABLE2 line and remove all duplicate rows to derive a list of results. When ALL is used together with the EXCEPT (EXCEPT ALL), does not remove duplicate rows.
C: The INTERSECT operator
The INTERSECT operator by including only TABLE1 and TABLE2 are some lines and remove all duplicate rows to derive a list of results. When ALL is used together with the INTERSECT (INTERSECT ALL), does not remove duplicate rows.
Note: several query results using arithmetic word line must be the same.
12, Description: the use of external connection
A, left (outer) join:
Left outer join (the left connecting): the result set several including matching rows connection table, including all the left connection table.
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B: right (outer) join:
Right outer join (the right connection): the result set includes matching connection line connection table, also includes all rows from the right connection table.
C: full/cross (outer) join:
Full outer join: includes not only the matching line symbol connection table, also includes a two connecting all records in the table.
12, Grouping: Group by:
A table, once the packet is completed, the query can only be set of related information.
Group of related information: (Statistics) count, sum, Max, min, AVG packet standard)
Packet in SQLServer: not to text, ntext, image fields as the grouping criterion
Fields in the selecte statistical function, not ordinary field together.,
13, The database operation:
Database: sp_detach_db; additional database: sp_attach_db after the show, added to the full path name
14 how to modify the database name:
sp_renamedb 'old_name', 'new_name'

Two, enhance
1, Description: copy (copy structure, the source table name: a new table name: b) (Access)
Method: select * into B from a where 1<> 1 (SQlServer only)
Method two: select top 0 * into b from a
2, Description: copy table (copy of the data, the source table name: a the destination table name: b) (Access)
insert into b(a, b, c) select d,e,f from b;
3, Description: the copy form between the cross database (specific data using the absolute path) (Access)
insert into b(a, b, c) select d,e,f from b in ‘The database ’ where condition
Example: ..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4, Description: the sub query (table 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 (1,2,3)
5, Note: the person who submitted the display, and the last response time
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6, Description: the outer join (table 1:a table name 2: b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7, Description: online view query (Table 1: a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8, Description: the usage of between, between limit the query data range includes the boundary values, not between does not include
select * from table1 where time between time1 and time2
select a,b,c, From table1 where a not between value of 1 and the value 2
9, Note: use the method of in
select * from table1 where a [not] in (‘The value of 1&rsquo, ’ 2&rsquo, ’ value; value; 4&rsquo, ’ a value of 6;’)
10, Description: two related tables, delete the table has been in the side table contains no information
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11, Note: four table linked to check problem:
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12, Description: five minutes ahead of schedule to remind
SQL: select * from where DateDiff (schedule'minute', f start time,getdate())>5
13, Description: a SQL statement to get database pagination
Select top 10 b.* from (select top 20 primary key field, sequencing field from table name order by desc a sort field), table name b where B. primary key field = A. order by A. sort field for the primary key field
Concrete realization:
On the database pagination:
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql

Note: in top cannot be directly with a variable, so in practical application only such special treatment. Rid is an identity column, if there is a specific field of top, it is very good. Because it can avoid top field if it is logical index, inconsistent query results after the actual list (logical index data in May and the data in the table is not the same, but if at first when the query index query index)
14, Description: the first 10 records
Select top 10 * form table1 where
15, Note: in all the information of each group of B value a the biggest record corresponding to the same data in (such usage can be used for the forum monthly list, the monthly sales product analysis, according to the subjects score ranking, 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 TableC lines and remove all duplicate rows to derive a list of results
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17, Description: random out of 10 data
select top 10 * from tablename order by newid()
18, Description: random selection of records
select newid()
19, Description: delete duplicate records
1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
delete from tablename
insert into tablename select * from temp
Evaluation: this operation involved moving large amounts of data, this approach is not suitable for large capacity but the data operation
3),For example: in an external table data import, for some reason the first import only a part, but it is difficult to determine the specific location, so that only the next time the imported, it produces a lot of repetition of the field, how to remove duplicate fields
alter table tablename
- add a self increasing column
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,...)
alter table tablename drop column column_b
20, Description: list all the database table name
select name from sysobjects where type='U' // U represents the user
21, Description: list all column names.
select name from syscolumns where id=object_id('TableName')
22, Note: listed in type, vender, PCS field, arranged in the type field, case can easily achieve the multiple choice, similar to select case.
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
Display the results:
type vender pcs
Computer A 1
Computer A 1
Disc B 2
Disc A 2
Mobile phone B 3
Mobile phone C 3
23, Description: initialization table table1
TRUNCATE TABLE table1
24, Description: choose from 10 to a record 15
select top 5 * from (select top 15 * from table order by id asc) Table_ alias order by ID desc
Three, skills
1, 1=1, The use of 1=2 in the SQL statement, combined with the more
"where 1=1" It is said "where 1=2" are not the election of all,
Such as:
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
We can directly.
Error! The directory is not found.
set @strSQL = 'select count(*) as Total from [' + @tblName + '] Where 1=1 '+ @strWhere 2, shrinking the database security
-- rebuild index
DBCC REINDEX
DBCC INDEXDEFRAG
Contraction data and log
DBCC SHRINKDB
DBCC SHRINKFILE
3, Compressed database
dbcc shrinkdatabase(dbname)
4, The transfer database to a new user to existing user permissions
exec sp_change_users_login 'update_one','newname','oldname'
go
5, Check the backup set
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6, Repair the database
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7, Log removal
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

USE tablename -- to the operation of the database name
SELECT @LogicalFileName = 'tablename_log', -- log file name
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 log files you want to set the size of(M)
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE 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 log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has 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 DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8, Description: change a table
exec sp_changeobjectowner 'tablename','dbo'
9, Storage change all table
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO

10, Direct cycle write data in SQL SERVER
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
Case:
The following table is mounted, all no grade, based on each increase of 0.1, so they just pass:
Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69
while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if (select min(score) from tb_table)>60
break
else
continue
end

Data development - Classic

1 the surname stroke ordering:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //From less to more
2 database encryption:
select encrypt('The original password')
select pwdencrypt('The original password')
select pwdcompare('The original password ',' the encrypted password ') = 1--; otherwise the same encrypt (' original password')
select pwdencrypt('The original password')
select pwdcompare('The original password ',' the encrypted password ') = 1-- the same or different;
3 back to the fields in the table:
declare @list varchar(1000),
@sql nvarchar(1000)
select @list=@list+','+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)+' The from table A'
exec (@sql)
4 see hard disk partition:
EXEC master..xp_fixeddrives
5 Comparison of A, B tables are equal:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print 'Equal'
else
print 'Not equal'
6 kill all the profiler process:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL profiler')
EXEC sp_msforeach_worker '?'
7 records search:
At the beginning of the N records
Select Top N * From table
-------------------------------
N to M records (to have the main index ID)
Select Top M-N * From ID in (Select Where Top M ID From Order by ID Desc)
----------------------------------
N to the end records
Select Top N From Order by ID Desc *
Case
Example 1: a list of more than ten thousand records, the first field of the RecID table is self growth field, write a SQL statement, find out the table thirty-first to fortieth records.
select top 10 recid from A where recid not in(select top 30 recid from A)
Analysis: if you write will produce some problems, if the recid logical index in the table.
select top 10 recid from A where……Is to find from the index, and the select top 30 recid from A to find the data in tables, such as the index in the order of May and the data in the table is not the same, this caused the query to not originally wanted data.
The solution
1, Order by select top 30 recid from A order by ricid if the field is not self growth, there will be a problem
2, Also in the sub query: select top 30 recid from A where recid>-1
Example 2: the final query table to record, do not know the total number of data table, and the table structure.
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'
print @s exec sp_executesql @s
9: Get all the user tables in the current database
select Name from sysobjects where xtype='u' and status>=0
10: Gets a list of all of the fields
select name from syscolumns where id=object_id('Table name')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = 'Table name')
In two ways the same effect
11: See the function associated with a table, view, stored procedure
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%Table name%'
12: All the stored procedure to check the current database
Select name as from sysobjects where xtype stored procedure name='P'
13: All database query user created
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 <> 0x01
14: Query data fields and a table types
select column_name,data_type from information_schema.columns
where table_name = 'Table name'
15: Between different server database data operation
To create a linked server
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'The remote server name or IP address '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'User name ',' password '
-- Query example
Select * from ITSV. database.Dbo. table name
- Import examples
Select * into from ITSV. database.Dbo. table name
Deleting the linked server - no longer used when
exec sp_dropserver 'ITSV ', 'droplogins '

- to connect to remote / local area network data(openrowset/openquery/opendatasource)
--1, openrowset
-- Query example
select * from openrowset( 'SQLOLEDB ', 'SQL server name ';' name ';' password ', database.Dbo. table)
- generating local table
Select * into from openrowset ('SQLOLEDB ','sql server name'; 'name'; 'password', database.Dbo. table)

- the local table into the remote table
insert openrowset( 'SQLOLEDB ', 'SQL server name ';' name ';' password ', database.Dbo. table)
Select *from, a local table
Update local table
update b
Set B. row A=a. column A
from openrowset( 'SQLOLEDB ', 'SQL server name ';' name ';' password ', the name of the database table name) as.Dbo. a inner join local B table
on a.column1=b.column1
--openquery usage need to create a connection
First create a connection creates a linked server
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'The remote server name or IP address '
-- Query
select *
FROM openquery(ITSV, 'SELECT * FROM.Dbo. database table name ')
- the local table into the remote table
insert openquery(ITSV, 'SELECT * FROM.Dbo. database table name ')
Select * from local table
Update local table
update b
Set B. row B=a. column B
FROM openquery(ITSV, 'SELECT * FROM.Dbo. database table name ') as a
Inner join b on A. local table column A=b. column A

--3, opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID= Password= password login; ' ).test.dbo.roy_ta
- the local table into the remote table
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID= login password '); Password=.Dbo. database table name.
Select * from local table
SQL Server basic function
SQL Server basic function
The 1 string length and analysis
1,datalength(Char_expr) Returns a string containing the number of characters, but does not contain spaces behind
2,substring(expression,start,length) The sub string, string subscript is from the "1", start as the starting position, length is the length of the string, the actual application in len (expression) made its length
3,right(char_expr,int_expr) Returns a string on the right side of the int_expr characters, left is also used in the opposite
4,isnull( check_expression , replacement_value )If the check_expression is empty, then returns the value of replacement_value, is not empty, it returns the check_expression character classes
5,Sp_addtype custom data types
For example: EXEC sp_addtype birthday, datetime, 'NULL'
6,set nocount {on|off}
The number of rows affected by the Transact-SQL statement does not include return results in information. If some statement contained in the stored procedure does not return a lot of practical data, the setting because of the large number of reducing network traffic, it can significantly improve the performance of. SET NOCOUNT settings are set at execute or run time, and is not set in the analysis.
SET NOCOUNT is ON, the count is not returned (expressed by the Transact-SQL statement affects the number of rows).
SET NOCOUNT is OFF, the count is returned
Common sense

In the SQL query: from can at most how many with table or view: 256
Order by, appear in a SQL statement in the query, sorting, after taking
In SQL, the maximum capacity of a field is 8000, while for nvarchar (4000), because the nvarchar is the Unicode code.

Steps to achieve technical replication SQLServer2000
A, preparatory work
1 publishers, subscribers will create a namesake Windows users, and set the same password, to access the user releases the snapshot folder
- Administrative Tools
- Computer Management
-- users and groups
- right click the user
-- new user
-- establish a belong to the administrator group on the windows user(SynUser)
2 at the publisher, create a shared directory, as storage directory snapshot files released, operation:
My computer --D:\ create a new directory, named: PUB
- right click the new directory
- properties - sharing
- select the "share this folder"
By "authority" to set the specific user access keys, which created in the first step the user (SynUser) with all access to this folder

- OK
3 set SQL proxy (SQLSERVERAGENT) service startup user (publish / subscribe servers are doing this setting)
Start procedure - Administrative Tools - Service
- right click SQLSERVERAGENT
- properties - login - select "this account"
Input or select the first step to create a Windows login name.(SynUser)
--"Password "input the user
4 set SQL Server authentication mode, solve the permissions problems when connecting (publish / subscribe servers are doing this setting)
Enterprise manager
- right click SQL instance - properties
- Safety -- authentication
Select "SQL Server - and Windows"
- OK
5 at the publisher and subscriber mutual registration
Enterprise manager
- right click SQL Server group
The new SQL Server registration...
Server step - available --, enter the remote server name -- you have to register to add
Connections - - the next step, a choice of second "SQL Server authentication"
The next step, enter the user name and password(SynUser)
SQL Server - the next step, or you can create a new group
Finish - - the next step
6 for only with IP, using the computer name cannot be registered server, an alias for it (this step in the implementation of no use)
(in the end connection configuration, for example, the configuration at the subscriber's words, enter the server name is the publisher of IP)
Start procedure --Microsoft SQL Server-- client network utility
-- -- add alias
Select the "tcp/ip" server name - enter a SQL server name -- the network library
Enter the SQL server IP address, connection parameters - server name
If you modify the SQL port, deselect the "dynamic decided to port", and enter the port number corresponding to the
Two, formal configuration
1, Configure publishing server
Open the enterprise manager, at the publisher (B, C, D) performs the following steps:
(1) Copy] sub menu select [configure publishing, subscribers and distribute] appear to configure publishing and distribution wizard from the tools menu [[]
(2) [next] select a distribution server can choose the publisher himself as a distributor or other SQL servers (choose)
(3) The snapshot folder settings [next]
Using the default \servername\Pub
(4) [next] custom configuration
You can choose: Yes, let me set the distribution database properties enable publishers, or set the publishing settings
Otherwise, use the following default settings (recommended)
(5) [next] set the distribution database name and location of the default values
(6) [next] enable publishers to choose as the published server
(7) [next] choose to release the database and publication type
(8) [next] choose to register the subscriber
(9) [next] complete configuration
2, Create your publication
The publisher B, C, D
(1)From the tools menu the [copy] sub menu select [released] order creation and management
(2)Choose to create a publication database, and then click [create release]
(3)Create publication wizard] in [prompt dialog box click [next] the system will pop up a dialog box. The dialog box is the content of the three types of replication. We now choose the first snapshot is the default publication (the other two you can go to have a look.)
(4)Click [next] system requirements specified can subscribe to this publication database server type,
SQLSERVER allows to copy data between different databases such as orACLE or ACCESS.
But we choose the database server is running "SQL SERVER 2000" here.
(5)Click [next] system will pop up a dialog box that is defined in the choice to published tables
Note: if the front chose a transactional publication then this step can only choose a primary key table
(6)Select a publication name and description
(7)Custom publishing attribute Wizard provides a choice:
I will be a custom data screening, enable anonymous subscription or other custom attributes
Not create the publication as specified (recommended by the custom way)
(8)Select [next] released
(9)[next] can choose whether to allow anonymous subscriptions
1)If you choose to sign the subscription, you need to add a subscriber to the publisher
Methods: [tools]-> [copy]-> [configure publishing, subscribers and distribution properties of]-> [add] in the subscriber
Can appear otherwise requested subscription at the subscriber when the cue: change the publication does not allow anonymous subscriptions
If you still need an anonymous subscription with the following solutions
[enterprise manager]-> [copy]-> [publication]-> [attribute]-> [subscription options allow anonymous subscriptions
2)If you choose an anonymous subscription, configure subscriber does not occur when the above tips
(10)[set the snapshot agent schedule next]
(11)[next] complete configuration
When creating the complete publication created after the publication database becomes a shared database
Data
The srv1. library..author field: ID,name,phone,
The srv2. library..author field: ID,name,telphone,adress

Requirement:
The srv1. library..author increased srv1. library..author record increase record
The phone field srv1. library..author updates, srv1. library..author telphone to update the corresponding field
--*/

The processing steps of rough -
--1. created in the srv1 to connect to the server, so that the operation of the srv2 in srv1, to achieve synchronization
exec sp_addlinkedserver 'srv2','','SQLOLEDB','The srv2 SQL instance name or IP'
exec sp_addlinkedsrvlogin 'srv2','false',null,'User name ',' password'
go
--2. srv1 and srv2 in the two computer, start the MSDTC (distributed transaction processing services), and is set to start automatically
. My computer - control panel - Administrative Tools - Services - right click Distributed Transaction Coordinator-- attribute -- start -- and the startup type is set to start automatically
go


Then create a Job Timer above synchronous processing stored procedure on the line

Enterprise manager
- Management
The --SQL Server agent
- right click operation
-- the new job
--"The conventional "item in the input operation name
--""A step
- New
--"Step name "input step name
--"Type "select" Transact-SQL script(TSQL)"
--""Select the database to execute a command database
--""Enter the command: exec p_process statements to be executed
- OK
--"Scheduling "item
-- new scheduling
--"The name "input schedule name
--"Scheduling type "choose your job execution schedule
If choose "recurring"
- "change" to set your schedule


Then the SQL Agent service is started, and is set to start automatically, or your work will not be executed

Setting method:
My computer - control panel - Administrative Tools - Services - right click SQLSERVERAGENT-- properties - start type - select "automatic start" - OK.


--3. method for realizing the synchronization treatment 2, timing synchronization

Synchronous processing and storage process -- created in srv1.
create proc p_process
as
Update the modified data
update b set name=i.name,telphone=i.telphone
From srv2..Dbo.author B Library,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)

Insert new data
Insert srv2. library.Dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
Select * from srv2. library.Dbo.author where id=i.id)

- delete deleted data (if needed)
delete b
From srv2..Dbo.author B Library
where not exists(
select * from author where id=b.id)
go

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Jessie at December 08, 2013 - 9:20 PM