Access SQL Query -> T-SQL Query Conversion ? (1 Viewer)

Minty

AWF VIP
Local time
Today, 11:51
Joined
Jul 26, 2013
Messages
10,355
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. :cool:
 

Minty

AWF VIP
Local time
Today, 11:51
Joined
Jul 26, 2013
Messages
10,355
@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

Ignore List Poster Boy
Local time
Today, 07:51
Joined
Apr 27, 2015
Messages
6,286
Well, it isn't often I get to help someone with a higher rep count then me, so I consider this day a success!
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:51
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 11:51
Joined
Jul 26, 2013
Messages
10,355
@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

Super Moderator
Staff member
Local time
Today, 22:51
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 11:51
Joined
Jul 26, 2013
Messages
10,355
But won't the query about the query, need another query, about the first query....... :D
 

isladogs

MVP / VIP
Local time
Today, 11:51
Joined
Jan 14, 2017
Messages
18,186
Was the article by Danny Lesandrini any use? See link in post #3
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:51
Joined
Apr 27, 2015
Messages
6,286
Was the article by Danny Lesandrini any use? See link in post #3

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

Attachments

  • BB89EFE0-5A1A-4B7C-91A6-786538BEC61C.png
    BB89EFE0-5A1A-4B7C-91A6-786538BEC61C.png
    90.8 KB · Views: 113

Minty

AWF VIP
Local time
Today, 11:51
Joined
Jul 26, 2013
Messages
10,355
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

Enthusiastic Amateur
Local time
Today, 11:51
Joined
Sep 21, 2011
Messages
14,051
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 !
 

isladogs

MVP / VIP
Local time
Today, 11:51
Joined
Jan 14, 2017
Messages
18,186
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

Former Staff Turned AWF Retiree
Local time
Today, 07:51
Joined
Dec 26, 2002
Messages
4,751
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

AWF VIP
Local time
Today, 11:51
Joined
Jul 26, 2013
Messages
10,355
@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!
 

Users who are viewing this thread

Top Bottom