Manual conversion from Access query to SQL Server Stored Procedure easier

[Rx_]

Comments Welcome. Are there other tools that would help?

Background:
There do not seem to be a plug-and-play solution for upsizing Access queries. If there were, it would have been included in the upsize wizard. No one can create a utility to convert your years of hard work writing queries into SQL Server with the click of a button. It's gonna take analysis, meditation, and a whole lot of typing. Maybe you are accustomed to using the Query-By-Example grid in Access, but if it's your ambition to upsize your app to SQL Server, get familiar with SQL Server Management Studio, and what used to be called Query Analyzer, because you're going to be writing a lot of SQL. But before you start, download the DataFast Utility and give it a whirl. It just might simplify your analysis, aid your meditation, and save you some typing.

Instructions to install from 2007
http://www.databasejournal.com/feat...97256/Migrating-Access-Apps-to-SQL-Server.htm

Rx: This company was in Aurora, Colorado. Could not find any trace of them. It is an interesting Access tool to help manage the manual conversion.

 

[Rx_]

Are there any other tools or tips to convert Access Query to SQL Server?

Here is another one:
Tool: Microsoft SQL Server migration assistant for access v5.2 query
Convert MS Access Queries to SQL Server Queries
Must modify: Access SQL to SQL TSQL by changing the IIF to Case Statements.
How to convert IIF to CASE

From Access:

 IIf(IsNull([mstPatient].[PPrefix]),'',[mstPatient].[PPrefix]+' ') 
    + 'My postfix string'  AS MyIIFStatement

Becomes in SQL Server (using IS NULL check):

CASE 
    WHEN [mstPatient].[PPrefix] IS NULL THEN ''
    ELSE [mstPatient].[PPrefix] + ' '
END + 'My postfix string'  AS MyCaseStatement

also:
Change CHR(13) to CHAR(13)

also:
Change the colums referenced by !
i.e. mstPatient!Add1 becomes mstPatient.Add1