Azure SQL Automation stored procedures

3
votes

I am migrating on premises SQL server database to Azure PaaS SQL database using Data Migration Tool. I am not using Data Migration Service Instance mechanism to migrate the database as it would require VPN on the corporate network and I do not see any benefit doing that way in my case. There is one error which I am stuck on and a bit confused. When migrating a stored procedure which use automation stored procedures, I get the following error: enter image description here

When I run Alter procedure script directly on Azure SQL database , I get this error; Reference to database and/or server name in 'MASTER..sp_OACreate' is not supported in this version of SQL Server.A few answers to similar question on stackoverflow suggest that Azure SQL does not support automation stored procedures but the Microsoft link has a green tick against Azure SQL which suggest it does; https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/ole-automation-sample-script?view=sql-server-2017. A snippet of the sp is:

enter image description here

Is it true that automation stored procedures are not supported in Azure SQL and if thats hte case then what is hte workaround apart from rewriting the sp?

1
sql-serverazurecloudazure-sql-database
if you check this documentation, you can see that sp_OACreate does not exist in Azure SQL: docs.microsoft.com/en-us/sql/relational-databases/…. Don't know what this is for but you will have to do some rewrite – Thomas

1 Answers

3
votes

SQL Azure prevents you from calling server resources. sp_OACreate attempts to create a reference to an OLE object on the server; that's simply not allowed on Azure SQL Database. So you will need to redesign your solution to move this type of logic in a middle-tier of some kind, such as a Web Role in Windows Azure.