SSMA Query migration

[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

 

[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.

 

[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.