SSMA Query migration (1 Viewer)

[ions]

Hello MS Access Expert,

I am learning how to migrate MS Access objects using SSMA into SQL Server 2019.

I was not able to Migrate the following query using the SSMA wizard and I am curious why.

SELECT Invoice.InvoiceNo, [Quantity]*[UP] AS Total, Month([Invoice].Date) AS [Month], Invoice.Date
FROM Invoice INNER JOIN InvoiceSub ON Invoice.InvoiceNo = InvoiceSub.InvoiceNo
WHERE (((Invoice.Date)>#12/31/2021# And (Invoice.Date)<=Now()));

I am able to create the same View manually in SQL Server using the below code.

SELECT Invoice.InvoiceNo
    ,[Quantity]*[UP] AS Total
    ,Month([Invoice].[Date]) AS [Month]
    ,Invoice.[Date]
FROM Invoice
    INNER JOIN InvoiceSub ON Invoice.InvoiceNo = InvoiceSub.InvoiceNo
WHERE (((Invoice.[Date])>'12/31/2021'
    AND (Invoice.[Date])<=GETDATE()))

Thank you

 

[Pat Hartman]

I don't migrate my queries. I have not found a reason to. They all work fine as Access querydefs. So, I'm guessing. Now() is VBA and so that may be what is causing the problem but you never told us if you were getting an error or incorrect results so who knows.

Also, Using the names of functions as column names causes problems with your Access FE. Date, Month, Year are ESPECIALLY problematic as are certain properties such as Name. That's as bad as it gets.
You might want to take a look at this sample to see the bad things that are happening that you haven't yet discovered.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[Isaac]

Have no idea what SSMA is, but it sounds like a tool that promises to do something un-doable, as most "auto migrate" tools do.

Like Pat, I'd guess that it didn't know what NOW() was, or else was confused by Access' excessive and unnecessary use of too many parenthesis, like putting a parenthesis around (Invoice.Date) for no particular reason. T-SQL is much better than Access is at evaluating statements on the basis of common sense, although sometimes I still surround things with parenthesis "to be safe" - but that's not really something to be proud of, I know it's just a leftover from my Access indoctrination LOL.
Generally you can just write statements-that-neatly-evaluate-to-a-boolean-result on their own (and each on its own line for readability), like
Where
col1='something'
and col2<getdate()
and datediff(,,,)=something

with no parenthesis at all, unless you have multiple items WITHIN the statement that you want to be evaluated as true or false as a whole, of course.


FYI - I would recommend, all else being equal and I know this topic can be debated and argued ad nauseam, but like I said, "all else being equal" - meaning if you don't know much about date/region settings or T-SQL, and if nothing else unusual or non-default is getting in the way, then I would recommend getting in the habit of using this format when expecting T-SQL to recognize something as a date: YYYY-MM-DD

It is the most likely to work in the most # of scenarios. As a bonus, it is the most recognized format of date around the world, thus gives you the extra bonus of communicating easily in online forums, with many pieces of software, and with various platforms.

 

[AccessBlaster]

Just a guess but maybe SSMA can't handle the time component of Now()

 

[GPGeorge]

Have no idea what SSMA is, but it sounds like a tool that promises to do something un-doable, as most "auto migrate" tools do.

Like Pat, I'd guess that it didn't know what NOW() was, or else was confused by Access' excessive and unnecessary use of too many parenthesis, like putting a parenthesis around (Invoice.Date) for no particular reason. T-SQL is much better than Access is at evaluating statements on the basis of common sense, although sometimes I still surround things with parenthesis "to be safe" - but that's not really something to be proud of, I know it's just a leftover from my Access indoctrination LOL.
Generally you can just write statements-that-neatly-evaluate-to-a-boolean-result on their own (and each on its own line for readability), like
Where
col1='something'
and col2<getdate()
and datediff(,,,)=something

with no parenthesis at all, unless you have multiple items WITHIN the statement that you want to be evaluated as true or false as a whole, of course.


FYI - I would recommend, all else being equal and I know this topic can be debated and argued ad nauseam, but like I said, "all else being equal" - meaning if you don't know much about date/region settings or T-SQL, and if nothing else unusual or non-default is getting in the way, then I would recommend getting in the habit of using this format when expecting T-SQL to recognize something as a date: YYYY-MM-DD

It is the most likely to work in the most # of scenarios. As a bonus, it is the most recognized format of date around the world, thus gives you the extra bonus of communicating easily in online forums, with many pieces of software, and with various platforms.

SSMA, or SQL Server Migration Assistant, is an extremely capable application for migrating data from Access to SQL Server. It's been used by many thousand --if not tens of thousands, or more --Access developers for many years to migrate data from mdbs and accdbs to SQL Server. There are versions for Oracle, MySQL and others as well. No worries on that score.

The main problem is, as Pat pointed out, Now() is an Access function which does not exist in SQL Server! The equivalent, as shown in the view, is GetDate().

I suppose one could ask why the tool couldn't substitute one function for the other, and in a sense, that could happen. However, I'm of the opinion that trusting a piece of software to rewrite code or SQL is probably not a safe approach. I'd rather do it myself most of the time.

If you have non-parameterized queries that don't include Access-specific functions, they should migrate fine. But, as noted, you're much safer to either leave them in Access, or replace them manually with SQL Server views that you control and design yourself.

 

[Pat Hartman]

Unless you are having trouble with a specific query, I would not recommend just willy-nilly converting everything. There is no gain there. Access "passes through" all queries unless you include something in the query that has no direct translation to t-SQL. There is some overhead with running the queries as querydefs because some parts of Access have to "decide" what to send to the server whereas there is no decision when the querydefs are defined as pass-through. They go without thought. What you have to weigh is pain vs gain. Lots of pain vs little to no gain except in special circumstances.

 

[ions]

Thank you for all the responses. They were helpful.

 

[Isaac]

I suppose one could ask why the tool couldn't substitute one function for the other, and in a sense, that could happen. However, I'm of the opinion that trusting a piece of software to rewrite code or SQL is probably not a safe approach. I'd rather do it myself most of the time.

Yeah .. that's kind of what I meant too in my comment about auto migrate tools promising to do un-doable things.
I have tried a few of those things several times and immediately my intuition told me I was better off forcing myself to make each judgment call myself, rather than letting another developer (who created the migration tool) make decisions that I should have made.
Personally, I cannot imagine using a tool like that and saying "Ok, it works! I'm going to leave it like whatever code the tool produced"
and just taking the decision of what was actually best out of my hands - but that's the type of behavior those tools encourage and enable.

I also am in the habit of using Getdate(), although people tell me I really ought to get used to using current_timestamp, as it's much closer to being an ansi standard and more portable compared to the t-sql-only getdate(). There are a few.