How to Connect to an External Database from a SQL Statement or a Stored Procedure

How can I connect to an external database from a sql statement or a stored procedure?

You'll need to setup a Linked Server instance. Then you can reference the external database as though it were a SQL Server database.

Is it Possible to call Functions and Stored Procedures of One database In another database- Azure Sql server

Maybe you can see this documentation:Reporting across scaled-out cloud databases (preview).

Elastic query also introduces a stored procedure that provides direct access to the shards. The stored procedure is called sp_execute _remote and can be used to execute remote stored procedures or T-SQL code on the remote databases. It takes the following parameters:

1. Data source name (nvarchar): The name of the external data source of type RDBMS.
2. Query (nvarchar): The T-SQL query to be executed on each shard.
3. Parameter declaration (nvarchar) - optional: String with data type definitions for the 4. parameters used in the Query parameter (like sp_executesql).
4. Parameter value list - optional: Comma-separated list of parameter values (like sp_executesql).

The sp_execute_remote uses the external data source provided in the invocation parameters to execute the given T-SQL statement on the remote databases. It uses the credential of the external data source to connect to the shardmap manager database and the remote databases.

Example:

 EXEC sp_execute_remote
        N'MyExtSrc',
        N'select count(w_id) as foo from warehouse'

It means that you can call the Functions and Stored Procedures of One database In another database, just need to modify the SQL statement.

Hope this helps.

Use database inside a stored procedure

Dynamic SQL

CREATE PROCEDURE spTestProc
AS

EXEC ('USE [database1]; CREATE USER [userLogin] FOR LOGIN [userLogin]')

EXEC ('USE [database2]; CREATE USER [userLogin] FOR LOGIN [userLogin]')
GO

Access another SQL Server through stored procedure

I found the answer. The problem was an instance, I had created "Amazon RDS for SQL Server" which is currently not supporting to "Linked servers".

So I create a new AWS EC2 instance for Windows Server 2012 R2 and then I create Linked servers in it and tried to assess remotely. Now its working fine as I wanted.

Here is detailed document to Running SQL Server Linked Servers on AWS

Thanks.

How can I get a stored procedure to call an outside database on the same SQL server?

You can fully qualify the table names (I'm assuming the databases are on the same db server)

e.g. from sproc in DB1:

UPDATE DB2.dbo.MyOtherTable
SET Field = 'SomeValue'
WHERE ID = 1

Executing a SQL Server stored procedure in another database server from a stored procedure without having permission to create link server

I want to execute one of these procedures from another and I am not able to create a link server

Without a linked server this is not normally possible.

As @Martin commented, you can try OPENDATASOURCE, but this is unlikely to work as it is disabled by default and is not likely to be enabled by a hosting company.

In these cases, the only option you have is to run each stored procedure separately from your application and process the results in the application.

---

Related Topics