Stored Procedure Calls data from different databases

Stored Procedure Calls data from different databases

How to call data from different databases in a stored procedure? For example, in a stored procedure named AAA, how does one call the data in the databases hudu1, hudu2, and hudu3 for statistics?
On the same database server:
Select * From hudu1.dbo. Table Name
Select * From hudu2.dbo. Table Name
Select * From hudu3.dbo. Table Name

On different database servers:
Select * From OpenRowSet ('sqlodb', 'SQL Server 1'; 'username'; 'Password', hudu1.dbo. Table name)
Select * From OpenRowSet ('sqlodb', 'SQL Server 2'; 'username'; 'Password', hudu2.dbo. Table name)
Select * From OpenRowSet ('sqlodb', 'SQL Server 3'; 'username'; 'Password', hudu3.dbo. Table name)

The following method can be referenced:
Method 1: Use the OpenDataSource method to operate a remote database

Declare @ I int
Set @ I = 1
Select * From OpenDataSource ('sqlodb', 'Data source = IP address; user id = sa; Password = password '). remote database name. DBO. table Name a inner join local database name .. table name B
On a. Join field = B. Join field and A. Field name = @ I



-- Method 2: if there is frequent access or a large amount of data, we recommend that you use the linked server

-- Create a linked server
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqlodb', 'remote server name or IP address'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'username', 'Password'
Go

-- Query example
Select * From srv_lnk. Database Name. DBO. Table Name

-- Import example
Select * into table from srv_lnk. Database Name. DBO. Table Name

Go
-- Delete the linked server when it is no longer in use
Exec sp_dropserver 'srv _ lnk ', 'droplogins'


-- For temporary access, you can directly use OpenRowSet
-- Query example
Select * From OpenRowSet ('sqloledb'
, 'SQL Server name'; 'username'; 'Password'
, Database name. DBO. Table name)


-- Import example
Select * into table from OpenRowSet ('sqloledb'
, 'SQL Server name'; 'username'; 'Password'
, Database name. DBO. Table name)

Error 7405: heterogeneous queries require that ANSI-NULL and ANSI-WARNNINGS options be set for the connection, which ensures consistent query semantics, enable these options, and then reissue the query
Solution:
Set ansi_nulls on
Set ansi_warnings on
Go
Create procedure ....

SQL Server access a remote server through a linked server in the Stored Procedure

During SQL Server development, cross-physical Server Cross-database access and operations are often encountered.
The following is a complete example of creating remote access in the stored procedure.
Requirement: There is a card database on server a, and the card database has a stored procedure proc_card;
There is an accountdb database on server B and an up_account stored procedure under accountdb.
Now we need to call the up_account stored procedure at the end of proc_card execution to synchronize data between different servers and databases.
Analysis: 1. Calling stored procedures in stored procedures is not a problem,
Execute 'stored procedure name'
Stored Procedure Parameter 1 = parameter 1 value,
Parameter 2 = value of parameter 2
......
2. How to create access to server B SQL Server on server?
The first thing I have to mention is the link server. in SQL 2 K, Enterprise Manager ==> Security ==> link server.
In sqlserver2005 or 2008, The maragement Studio Server Object ==> linked server.
How does the linked server object come from?
By default, there will be only one local machine, and the linked server name is the name of the local machine. You can view it through the system stored procedure execute sp_helpserver, which is displayed as the name field.

How to add a linked server?
A. Through the wizard,
1. Right-click the server and choose add link server.
2. enter the name of the linked server [the name or IP address of the server to be accessed. If the alias cannot be resolved, use IP address]. Specify the server type as SQL Server,
3. In Security, select 'establish connection using this security context 'to specify the SQL login account and password of the server to be accessed
4. Click OK to refresh the linked server. The link server information is visible.
B. Through system stored procedures,
There are two common scenarios for creating a link to an SQL Server:
1. In the first case, select "SQL Server" for the product"
Exec sp_addmediaserver
@ Server = 'linkservername ',
@ Srvproduct = n' SQL Server'
In this case, @ server (linkservername) is the name or IP address of the sqlserver server to be linked.
2. In the second case, select "Microsoft ole db Provider SQL Server" or "SQL native client" as the access interface"
Exec sp_addmediaserver
@ Server = 'linkservername ',
@ Srvproduct = '',
@ Provider = 'sqlncl ',
@ Datasrc = 'sqlservername'
In this case, @ datasrc (sqlservername) is the actual sqlserver server name or IP address to be linked.
3. the SQL Server database engine accesses the linked server through the server name or IP address set above, and the DTC service also accesses the linked server through the server name or IP address, therefore, make sure that both the database engine and DTC can access the linked server through the server name or IP address.
A connection is established to create the login access to the database:
Exec sp_add1_srvlogin
'Alias or IP address of the linked server created above ', -- alias of the accessed Server
'False ',
Null,
'Account', -- account
'Logon password' -- Password

Next, you can access the up_account stored procedure under the accountdb database of server B by connecting to the server:
Execute [LINK server alias]. Database Name. DBO. Stored Procedure

The work seems to have ended, but at this time, it is found that when you run the stored a server stored procedure, the message "The [alias used] server is not configured as available RPC" is reported. How can this problem be solved?
Check the options of the linked server and change the value of RPC rpc_out from false to true before debugging!
The solution is as follows:
Exec sp_configure 'show advanced options', 1; -- the default value is 0.
Go
Reconfigure with override;
Go

Stored Procedure Calls data from different databases _ SQL server access to remote server SQL during stored procedure _ SQL Server cross-platform Data Access