July 9, 2019 at 8:27 am
A Customer has recently migrated to Microsoft Access from On-Prem, They wish to run a script that exports data from MS Access to the Azure SQL Database but are having no luck.
They have a script that works on the On0-Prem but they cant get it to work for SQL Azure Database.
My Questions are
July 9, 2019 at 11:35 am
It's possible to write directly to Azure, yes. However, it all depends on how you do it. SSIS is one mechanism. You can also use Powershell or sqlcmd.exe. Make sure your firewall settings in Azure allow the client to connect.
Other than that, it's hard to tell you what the solution is because you've provided no details on the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2019 at 12:59 pm
Not really a problem but there are 5 access databases and one SQL Server on prem. A job has been created on the SQL Server to export data from these Access databases to the on prem SQL. This is all in place and working.
Now they have the SQL in Microsoft Azure SQL Database (Not a managed Instance). The issue is that internal developers can seem to get the script to work and there investigations point to the OPENROWSET Function part of the script as after some investigation it appears as this will not work in Azure SQL.
Others have written that you can get around this with using Linked servers however Linked servers are only available using a SQL managed instance and only to On Prem SQL.
So my question is Simply. I have an access database, I want to export data from it to an Azure Sql Database and automate the process as it needs to happen daily. How Can i do this and if you can provide more details that would be great some pointers to some scripts etc but it is important that it works with Azure SQL Database not Azure Managed instance or On Prem.
Many Thanks.
July 9, 2019 at 1:13 pm
So my question is Simply. I have an access database, I want to export data from it to an Azure Sql Database and automate the process as it needs to happen daily. How Can i do this and if you can provide more details that would be great some pointers to some scripts etc but it is important that it works with Azure SQL Database not Azure Managed instance or On Prem. Many Thanks.
If you want to push the data from on-prem to azure sql db, then it should not be a problem - just treat azure sql db as a linked server or remote SQL instance.
For example, try to connect to Azure DB using sql account first and be sure that firewall on Azure DB is configured properly (allows connections from your subnet).
July 9, 2019 at 4:13 pm
If you still have an on-premise SQL Server, then a linked server to the Azure database can help with your existing job. If you are trying to get rid of on premises SQL Server, this is harder. Access wants to export entire objects, not a query. What you can do is write a script in PoSh that queries Access. At the same time, you can write the script to insert into the SQL Server.
A couple examples:
Query access to export data - https://devblogs.microsoft.com/scripting/hey-scripting-guy-can-i-query-a-microsoft-access-database-with-a-windows-powershell-script/
Write data to SQL Server - https://www.sqlshack.com/6-methods-write-powershell-output-sql-server-table/
July 9, 2019 at 5:11 pm
The issue is that internal developers can seem to get the script to work and there investigations point to the OPENROWSET Function part of the script as after some investigation it appears as this will not work in Azure SQL.
If you're trying to do cross database queries as part of the migration, yeah, OPENROWSET will absolutely fail. I'd suggest first, not doing that at all. However, failing that, there is a way to do cross database queries, but it's kind of ugly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply