Procedure table in other database

Question

I have a database for a game server and another one for web. I would like to call a procedure which take a data from a table of the web database.

I have seen many examples like this 'db1.dbo.table_name' but I don't know what is dbo (Data Base Owner ?)

Here is my actual procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `donationCheck`()
BEGIN
  UPDATE `gameserver.players` SET `donatorlvl` = (SELECT `grade` FROM `web.donations` as dona WHERE dona.steamid = player.playerid) where steamid = '76561198125956777';
END

The error is that gameserver.web.donations does'nt exists (normal); Sorry for my bad english.

Firstly, are you using MySQL or SQL Server as you have tagged with both? — Alan Hay, Oct 08 '16 at 09:48
DBO is SQL Server specific http://stackoverflow.com/questions/1062075/why-do-table-names-in-sql-server-start-with-dbo — Alan Hay, Oct 08 '16 at 09:57
Assuming both databases on the same server then as you have done. You will need to check the permissions: does the user running the query have permissions to read from web.donations? — Alan Hay, Oct 08 '16 at 10:01
Yes, it's root user. The error is : 12:29:23 call arma3life.donationCheck() Error Code: 1146. Table 'gameserver.web.donations' doesn't exist 0.000 sec. So i think it's not a permission problem :/ — mike thomson, Oct 08 '16 at 10:29

score 0 · Answer 1 · answered Oct 08 '16 at 13:46

0

CREATE DEFINER=`root`@`localhost` PROCEDURE `donationCheck`()
BEGIN
    UPDATE gameserver.players a 
    INNER JOIN web.donations b ON a.playerid = b.steamid
    SET a.donatorlvl = b.grade;
END

answered Oct 08 '16 at 13:46

mike thomson

57
8

Procedure table in other database

1 Answers1