Access SQL Query -> T-SQL Query Conversion ?

[Minty]

Hi All,

Before I reinvent the wheel, has anyone written a text convertor that would take most of the pain out of moving a massive bunch of Access queries (25 databases with probably at least 500 queries!) into MS SQL Server T-Sql ?

I use Textpad and think I can create a dictionary and macro's to do some nifty global replacement work, I don't expect 100% compatibility but just simple things like DATE() change to GETDATE() , "String" replaced with 'String' etc.

I wouldn't bother with trying to automate if the number of queries wasn't so large.

Or I could use Access to create some query strings, and jigger about with them in that ? I'm not fussy about the how.

 

[NauticalGent]

I imagine you have already found this but just in case you haven't OR you choose to write your own conversion utility: https://weblogs.sqlteam.com/jeffs/2007/03/30/quick-access-jet-sql-to-t-sql-cheatsheet/

 

[isladogs]

I was just about to post the same link but NG was faster to it.
Here's another one by Danny Lesandrini https://www.databasejournal.com/features/msaccess/article.php/3705151/Converting-Access-Queries-to-SQL-Server.htm

 

[Minty]

@NG I hadn't actually - in fact I was very lazy and didn't think to Google for a convertor for some reason.

Give me a slap...

 

[NauticalGent]

Well, it isn't often I get to help someone with a higher rep count then me, so I consider this day a success!

 

[Minty]

Actually this might help ; https://www.microsoft.com/en-us/download/details.aspx?id=54255
Microsoft SQL Server Migration Assistant 8.2 for Access

Going to have a play.

 

[Galaxiom]

If you have complex joins in Access they are horrible to convert to clean TSQL because Access nests the joins. Then all those horrid parentheses to separate from the ones you actually need.

I have made some easily overlooked errors restructuring joins from Access to TSQL where tables had very similar names. The Server will run queries with joins having conditions that don't even make sense and you can end up with worse than Cartesian products that run the server out of memory.

Probably a completely silly thought but I'll throw it in.

If your tables are already ODBC connected to SQL Server you could run the queries and copy what was executed from the query cache on the server. I've never looked that closely at the cache but it should be possible in theory at least for some queries.

I've seen translated Access queries in the Recent Expensive Queries in the Performance Analyser.

 

[Minty]

@Galaxiom - I have seen that in the performance analyser, good shout.

The only fly in the ointment is that the server concerned is the main data-warehouse for the business, and finding one query might prove interesting...

 

[Galaxiom]

The only fly in the ointment is that the server concerned is the main data-warehouse for the business, and finding one query might prove interesting...

Pretty much everything in SQL Server can be queried.

 

[Minty]

But won't the query about the query, need another query, about the first query.......

 

[isladogs]

Was the article by Danny Lesandrini any use? See link in post #3

 

[NauticalGent]

Was the article by Danny Lesandrini any use? See link in post #3

I couldn’t get the page to load...error message attached.

 

[isladogs]

I couldn’t get the page to load...error message attached.

Just rechecked. It works for me. I'll try posting the URL again like this Converting-Access-Queries-to-SQL-Server

 

[Minty]

I can't get it to open from work, same error for me from either link.

The Migration service is annoying, I only have SQL Server 2008 available locally, and the version you can download an run only works > 2012 versions.

I can't get a local SQl 2016 dev version to load on my machine for some unknown reason...

Argghhhh !

 

[Gasman]

Minty,

The link works for me, and I've printed the page to pdf, but to big to upload here.?
PM me if you want me to email it to you. It is 2.25MB

I can't get it to open from work, same error for me from either link.

The Migration service is annoying, I only have SQL Server 2008 available locally, and the version you can download an run only works > 2012 versions.

I can't get a local SQl 2016 dev version to load on my machine for some unknown reason...

Argghhhh !

 

[sonic8]

Just rechecked. It works for me. I'll try posting the URL again like this Converting-Access-Queries-to-SQL-Server

The linked article should be read with a grain of salt!
Some of the issues elaborated in the text were real at the time of writing but are no more today. Some other other details are described simplified to the point of inaccuracy.

 

[isladogs]

The link to Danny's article does seem to be intermittent. Sometimes works but not always.

Other articles by the same author have been reliable so I assumed that would be without checking it carefully.

 

[Vassago]

From my experience and my experience only, I would consider rewriting the queries in SQL rather than trying to convert. There are FAR too many differences between the two and SQL has a lot of functionality that just isn't possible in Access that would allow you to write them BETTER. I'm usually all for automation when it comes to coding, but sometimes the "fix" is harder than actually looking at it from scratch and reconsidering how it's coded to incorporate this better functionality in SQL.

 

[Minty]

@Vassago - I'm in agreement, it's simply the quantity involved that steered me in this direction.

If they want it updated, I would probably re-write it as a set of stored procedures. Most of them are gathering data into temporary local tables to finally be turned into the end result which is excel spreadsheets.

It's easy to do this in one procedure to directly provide the output, without needing the temp tables and I suspect there would be a considerable improvement in performance as well. I'm waiting to hear what direction they want me to take.

My initial investigation has extracted over 2500 queries... I'm hoping they only want a small number of the end results!