Shulou(Shulou.com)05/31 Report--

This article introduces SQL Server to achieve cross-database and cross-server access, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. First create two databases, CrossLibraryTable1,CrossLibraryTable2

-- create a CrossLibraryTable1 script:

Use master-Open the master database, and the general creation statements execute .goif exists (select * from sysdatabases where name='CrossLibraryTable1') drop database CrossLibraryTable1 / * in master to check if there is such a database, and delete it if so. * / gocreate database CrossLibraryTable1on primary (name='CrossLibraryTable1_data',-form a good habit, add _ datafilename='F:\ code to store data files\ database\ CrossLibraryTable1_data.mdf',-be sure to be .MDF files, representing the main data file size=5mb,-- default database size maxsize=100mb,-- maximum capacity filegrowth=1mb-- growth) log on (name='CrossLibraryTable1_log') -get into the habit of adding _ logfilename='F:\ code to store\ database\ CrossLibraryTable1_log.ldf',-make sure that the .ldf file represents the log file size=1mb,-- default database size filegrowth=10%-- growth)

-- create a CrossLibraryTable2 script:

Use master-Open the master database, and the general creation statements execute .goif exists (select * from sysdatabases where name='CrossLibraryTable2') drop database CrossLibraryTable2 / * in master to check if there is such a database, and delete it if so. * / gocreate database CrossLibraryTable2on primary (name='CrossLibraryTable2_data',-form a good habit, add _ datafilename='F:\ code to store data files\ database\ CrossLibraryTable2_data.mdf',-be sure to be .MDF files, representing the main data file size=5mb,-- default database size maxsize=100mb,-- maximum capacity filegrowth=1mb-- growth) log on (name='CrossLibraryTable2_log') -get into the habit of adding _ logfilename='F:\ code to store\ database\ CrossLibraryTable2_log.ldf',-make sure that the .ldf file represents the log file size=1mb,-- default database size filegrowth=10%-- growth)

Then, after executing the script, refresh it and you can see the database you just created:

two。 Next, create a CrossTest1 and a CrossTest2 table in the two databases for cross-database queries.

-- create a CrossTest1 script:

Use CrossLibraryTable1 create table CrossTest1 (Id int primary key identity, Name nvarchar (20)

-- create a CrossTest2 script:

Use CrossLibraryTable2 create table CrossTest2 (Id int primary key identity, Name nvarchar (20)

After the table is created, we add a few more pieces of data to it:

Use CrossLibraryTable1 insert into CrossTest1 values ('cross-database 1 test data 1') insert into CrossTest1 values ('cross-database 1 test data 2') use CrossLibraryTable2 insert into CrossTest2 values ('cross-database 2 test data 1') insert into CrossTest2 values ('cross-database 2 test data 2')

Switch to CrossLibraryTable1 and query the data of CrossLibraryTable2. You can see the following error

Fix the code:

PS: queries can be made as above, but using the above code format in a project will cause some problems, so what's the problem? For example, if the name of another database is changed, we need to change all the places where this is used, which is very troublesome, so is there any solution to make it better? Of course, it can be easily done with database synonyms!

The steps to create synonyms are as follows:

Implementation of cross-database access for different servers

When the database is on different servers, it is not good to use the above method, so how to achieve cross-server access? It's very simple. Take a look at ↓↓↓ below.

On SQL Server to achieve cross-database cross-server access to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.