SQL Query is not enabled for On-Prem SQL Server

One of my current jobs required to pull the latest ID of a SQL table from an on-prem server. I thought it will be an easy one but finally it really wasn’t… let see why:

To get the biggest ID from a SQL table is pretty easy with a SQL script, it is only like SELECT MAX([Id]) as ‘maxid’ FROM schema.table. That’s it. So I set up an action to execute a SQL query against my on-prem SQL server table and run the workflow. Unfortunately I’ve got the error message that states it is not possible to execute a query on an on-prem SQL server… what?? I told it cannot be true, but after reading couple of articles it seems it is. Microsoft was closed the idea to make it work as it would result too complex results and told let’s use the Get Rows action.

Ok, why not. I’ve set up the Get Rows action and selected the necessary id field. The run was successful, however I didn’t receive the expected id, but something much less. Same for the next run. So I found the next issue, that the Get Rows action does have a limitation that only 2048 rows can be received from an on-prem SQL server. Ok, great, so another obstacle to fight.

Worth to mention here that if somebody has a write access to the source database, a view might be a solution for the issue. Just creating that view with the above script and than the Get Rows action should received only 1 row at all.

But now back to reality, fortunately I’ve found an article pretty quickly that explain a pagination method – that actually was familiar for me while working with API calls:

And with that it was finally working and I could get the expected id, but to be honest this workflow is very very complex for that easy purpose:

I also tried to find some alternative options, like if it is possible to query the SQL server with API call. Also find a good article, but it also requires admin right to the server. 😦

So finally using the above very long procedure and hoping Microsoft can release some more convenient solution soon.