SQLSERVER server connection (cross-database connection of sqlserver) bitsCN.com
1. if the database you want to access is not on the same server, you need to use the server link:
EXEC sp_addmediaserver @ server = 'dblink name', @ srvproduct = '', @ provider = 'sqlodb', @ datasrc = 'Destination server address 'goexec sp_addmediasrvlogin 'dblink name ', 'false', NULL, 'username', 'password' GO
Note:
1. the 'dblink name' of the upper and lower parts should be the same.
2. the following username and password should be the username and password on the target server.
3. during use, you only need to modify the 'dblink name', target server address, user name, and password, and do not need to modify the rest.
4. log on to the local database, that is, the sa account of the source database)
Establish a connection in a graphical manner, such:
2. two ways to test whether the server connection is successful
1. SQL statements
-- Query the remote table select * from [dblink name]. remote database name. dbo. table
2. assume that the new dblink is named test, for example:
After creation, you can right-click the location to test connectivity.
3. copy data from a local table to a remote table
Insert into [Link]. remote database name. dbo. table (column list) select column from local table
BitsCN.com