I am wondering if MySQL's stored procedures can work with two different databases on the same machine? How about if they are on different servers?
Compare Two Database and find differences First, open the SQL Server Database project with visual studio, right-click on it, and choose compare schema as depicted. Then, we will select the source and target databases and provide a connection to those.
You can work around this in SQL Server by creating a stored procedure that accesses data in another database and signing the procedure with a certificate that exists in both databases. This gives users access to the database resources used by the procedure without granting them database access or permissions.
There are three types of consolidation: Host multiple databases on a single SQL Server instance. Host multiple SQL Server instances on a single machine. Host multiple virtual SQL Server machines on a single physical machine.
Even if you execute two stored procedures exactly at the same time, It is not guaranteed that they will go parallel. Parallelism of executions is dependent on other factors like Query Cost, MXDOP(Maximum Degree of Parallelism), Threshold for Parallelism etc.
If we're talking about two databases on the same server: yes, a stored procedure can access another database. You have to make sure that the user under whose privileges the procedure is being run has the necessary privileges on each database.
For example, suppose you have two databases on the same server, mydb1
and mydb2
, and that each contains a table named messages
with the same structure. Suppose you want to add a stored procedure to mydb2
that empties the messages
table in mydb2
and copies the contents of the messages
table in mydb1
. You could do this:
CREATE PROCEDURE `SynchroniseMessages` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN
DELETE FROM `mydb2`.`messages`;
INSERT INTO
`mydb2`.`messages`
SELECT * FROM `mydb1`.`messages`;
END
See how I've fully qualified the tables with the databases to which they belong. In fact you could argue that I'm being over-zealous here, because we specified that this stored procedure will belong in mydb2
. I don't need to add the
qualifier. If the stored procedure were in the mydb2
.mydb1
database, I would need those qualifiers, but conversely I wouldn't need the
where it appears.mydb1
.
In order to be able to run this procedure (possibly in order to be able to define it?), I'd need to make sure my user has DELETE
and INSERT
privileges on mydb2
, and also SELECT
privileges on mydb1
.
Databases on different servers sounds rather more complicated.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With