sql server 2008 -运行存储过程从另一个数据库在当前数据库

Question

我有一个shared数据库和多个client数据库。数据存储在client数据库中。我们希望在shared数据库中创建存储过程的主集，并从client数据库执行它们。

use shared;
go
create procedure GetInvoices as
  print db_name() ' <- current database'
  select * from invoices
go
use client1;
create table invoices(...columns...)
exec shared.dbo.GetInvoices

返回以下错误:

shared <- current database
Msg 208, Level 16, State 1, Procedure GetInvoices, Line 3
Invalid object name 'invoices'.

如果不使用动态SQL ，我如何从client1运行shared中的存储过程，使其在client1中执行，从而访问client1中的所有表?

Answer 1

您可以在client1数据库上下文中运行master数据库中定义的存储过程，并且查看所有client1数据库表， 而不使用动态SQL，但它使用未记录的存储过程sp_ms_marksystemobject。

存储过程名称必须以sp_开头，例如sp_GetInvoices。在master数据库中创建它，然后调用exec sp_ms_marksystemobject sp_GetInvoices使其看到当前数据库的表。

USE master
GO
CREATE OR ALTER PROCEDURE sp_GetInvoices  
AS
BEGIN
    SELECT ClientName from Invoice
END
GO
exec sp_ms_marksystemobject sp_GetInvoices
USE client1
GO
create table Invoice (ClientName varchar(100))
insert Invoice select 'Acme Client'
exec sp_GetInvoices 

Result(运行在SQL Server version 13.0.5081.1):

ClientName
------------ 
Acme Client

Answer 2

在你的"Master"数据库上试试:

CREATE PROCEDURE [dbo].[GetDataFromClient]
    @DB VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @STMT VARCHAR( 300 );
DECLARE @SP VARCHAR( 500 );

SET @SP = 'dbo.GetData';

SET @STMT = 'EXEC(''' +  @SP + ''')';   

EXEC('USE '+ @db + ';' + @STMT)
END

现在在"Client"数据库上:

CREATE TABLE [dbo].[TestClient](
    [ID] [int] NOT NULL,
    [Description] [varchar](10) NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)   WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY].

创建从表TestClient

检索数据的存储过程

CREATE PROCEDURE [dbo].[GetData]
AS
BEGIN
    SELECT *
    FROM TestClient;
END

现在您可以使用以下命令从TestClient数据库检索列:

USE [TestMaster]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[GetDataFromClient]
     @DB = N'TESTCLIENT'
SELECT 'Return Value' = @return_value
GO  

Answer 3

创建链接服务器后，可以使用四部分名称调用存储过程。也可以通过openquery选项调用。

LinkSerevr:

EXEC [ServerName] .dbname.scheme.StoredProcedureName

openquery: SELECT * FROMOPENQUERY([ServerName] .dbname.scheme.StoredProcedureName)