Using 2 differecnt DB's in same SP in SQL Azure

Question

Hi,

I am using an SP which will insert data in 2 tables in 2 different DB's. To mainitain the transaction, the SP has been designed like that. Its working fine in SQL Server environment.

Like Insert into AdminDB.EmpSiteConfig values(,,,) Insert into MainDB.EmpDetails values(,,,)

where AdminDB and MainDB are the database names. But when I migrate it to SQL Azure, I am getting an error as follows. 'Reference to database and/or server name in MainDB.dbo.EmpDetails' is not supported in this version of SQL Server.'

Can somebody tell me how to get rid of this error? Or is there any workaround for this?

Thanks in advance.

Answer 1

SQL Azure does not currently support linking to another server. As to workarounds, you could create a queue message requesting a specific action for data insertion. In your worker role, consume the queue message and call a separate stored procedure on each database.