Whilst MUST+SQL will achieve an exceptionally high level of translation, there are certain types of queries
where you may need manual intervention.
MUST 7.7 achieved a conversion rate of 94.6% on our test environment which contains around 5400 queries.
Below we list error messages that you may see when a query fails to execute against SQL Server,
and we provide recommendations on how to resolve the issues.
Resolving Errors
If MUST displays a translation error, then the full SQL before and after translation together
with the error message can be displayed by pressing the Errors/Details button next to
the query that has failed. The SQL should then be copied and pasted into the SQL Server Management Studio
and executed; this will highlight syntax errors and is also able to identify the position
within a block of SQL where the failure has occured.
Invalid object name 'AnObjectName'
Problem
This error message means that your SQL is referring to another view that does not yet exist.
Solution
Look at fixing the problem in the view called ‘AnObjectName’ before returning to see if this will work.
Top of Page
Invalid column name 'ColumnX'
Problem
The column name ColumnX no longer exists in one of the tables or views (queries) referred to in the SQL.
Solution
This is what we would call a broken query, one that never worked correctly in Access, so you can either ignore the query, or fix the
SQL by editing out the broken column name.
Top of Page
Each GROUP BY expression must contain at least one column that is not an outer reference.
Problem
Access Developers will on the odd occasion add a literal value for display in a summary query when grouping information.
This is a quick way to project a static value onto a grouping.
Some examples are shown below:-
SELECT COUNT(*),GETDATE()
FROM[Orders]
GROUP BY CustomerID,GETDATE()
SELECT COUNT(*), MONTH(GETDATE())
FROM [Orders]
GROUP BY CustomerID,MONTH(GETDATE())
SELECT COUNT(*),1
FROM [Orders]
GROUP BY CustomerID, 1
SELECT COUNT(*),'Test'
FROM [Orders]
GROUP BY CustomerID, 'Test'
|
If you try and do this in SQL Server, then you will receive the above error message.
In this situation what it means is that you are not allowed to just insert a literal value,
or a function like MONTH(GETDATE()) which again gives a constant value in a GROUP BY.
You are however allowed something like the following:-
SELECT COUNT(*), MONTH([Orderdate])
FROM [Orders]
GROUP BY CustomerID, MONTH([Orderdate])
|
Which because it uses the function over a field which is not static will work.
Solution
The simplest solution is to remove the literal value from the grouping
(MUST will automatically do this for you in situations with simple literal values (like 1 or a string) and log the change,
However, if the expression for the grouping involves functions we can not run the risk of
automatically fixing the problem for you).
Once removed, you can always add the extra field back into a report, or using another view that
combines the data:-
CREATE VIEW View1
AS
SELECT COUNT(*)As Total
FROM [Orders]
GROUP BY CustomerID
GO
CREATE VIEW View2
AS
SELECT *, MONTH(GETDATE()) As TheMonth
FROM View1
GO
|
An alternative approach is to use a nested query, moving the static value outside the inner query:-
SELECT *, MONTH(GETDATE()) As TheMonth
FROM
(SELECT COUNT(*) As Total
FROM [Orders]
GROUP BY CustomerID) As Nested1
|
A final option is to use a SQL server function and pass in a dummy field:-
CREATE FUNCTION dbo.GetDateMonth(@CustId VARCHAR)
RETURNS INT
AS
BEGIN
RETURN MONTH(GETDATE())
END
GO
SELECT COUNT(*), dbo.GetDateMonth([CustomerID])
FROM [Orders]
GROUP BY CustomerID, dbo.GetDateMonth([CustomerID])
|
Top of Page
Conversion failed when converting the varchar value 'The Month is ' to data type int.
Problem
In Access you are allowed to add together in strings virtually any type of data you like,
and Access will automatically convert the data to a string. SQL Server does not allow this implicit
data type conversion.
There are a number of slightly different error messages that you may get here for exmaple:-
Implicit conversion from data type datetime to float is not allowed
Use the CONVERT function to run this query
Operand data type varchar is invalid for sum operator
Solution
The solution is very simple, you need to add an explicit CAST or CONVERT function to change the data type.
SELECT 'The Month is ' +
MONTH([OrderDate]) As TheMonth
FROM Orders
becomes
SELECT 'The Month is ' +
CONVERT(VARCHAR,MONTH([OrderDate])) As TheMonth
FROM Orders
or
SELECT 'The Month is ' +
CAST(MONTH([OrderDate])AS VARCHAR) As TheMonth
FROM Orders
|
Top of Page
Case expressions may only be nested to level 10
Problem
In SQL Server CASE statements can only nest to 10 levels (translated from the nested IIF),
so for example the following will not work:-
SELECT 'The Month is ' +
CASE WHEN MONTH([OrderDate]) = 1 THEN 'January' ELSE
CASE WHEN MONTH([OrderDate]) = 2 THEN 'February' ELSE
CASE WHEN MONTH([OrderDate]) = 3 THEN 'March' ELSE
CASE WHEN MONTH([OrderDate]) = 4 THEN 'April' ELSE
CASE WHEN MONTH([OrderDate]) = 5 THEN 'May' ELSE
CASE WHEN MONTH([OrderDate]) = 6 THEN 'June' ELSE
CASE WHEN MONTH([OrderDate]) = 7 THEN 'July' ELSE
CASE WHEN MONTH([OrderDate]) = 8 THEN 'August' ELSE
CASE WHEN MONTH([OrderDate]) = 9 THEN 'September' ELSE
CASE WHEN MONTH([OrderDate]) = 10 THEN 'October' ELSE
CASE WHEN MONTH([OrderDate]) = 11 THEN 'November'
ELSE 'December'
END END END END END END END END END END END
FROM Orders
|
Solution
If the expression being tested is always the same then this can be re-written as follows :-
SELECT 'The Month is ' +
CASE MONTH([OrderDate])
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
ELSE 'December'
END
FROM Orders
|
Problem Status: Resolved
As of MUST+SQL Version 7.7, MUST will automatically do this for you, but there maybe circumstances when
automatic correction is not possible, so it is worth explaining why this problem can occur.
Top of Page
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction",
or the name is ambiguous.
Problem
When MUST+SQL identifies VBA functions in your application it attempts to locate your function
in the VBA code, this error indicates that it was unable to locate the function body,
but found references in your SQL to the function.
Solution
Use the Mange Functions menu to create a body for your function,
or remove the reference if no longer used.
ORDER BY Items must appear in the select list if SELECT DISTINCT is specified.
Problem
When you have a query which contains an ORDER BY, this can only be preserved in a SQL Server VIEW
by adding a SELECT DISTINCT TOP 9223372036854775807 WITH TIES, the problem is that when this
is used, any field in the ORDER BY must also appear in the SELECT, and sometimes the field is missing.
Solution
Add any fields in the ORDER BY which are not in the SELECT into the SELECT
SELECT Field1
FROM Table1
ORDER BY Field2
translates to
SELECT DISTINCT TOP 9223372036854775807 WITH TIES
Field1
FROM Table1
ORDER BY Field2
needs to be changed to
SELECT DISTINCT TOP 9223372036854775807 WITH TIES
Field1, Field2
FROM Table1
ORDER BY Field2
|
Problem Status: On-Going
This is a known issue and we are looking towards improving on translation of this issue,
we already look for and correct many occurances of this problem.
Top of Page
Incorrect syntax near ...Overstated boolean.
Problem
Access allows for statements in a where clause like the following:-
WHERE ... AND (([OrderDate]-[XXXDate]<10)=True)
or when translated
WHERE ... AND (([OrderDate]-[XXXDate]<10)=1)
This is what we call an overstated boolean. SQL Server the and implies a logical test, and the = 1
is over-stating the test, the AND ( ) is a logical test, and AND ( ) =1 is over-stated.
Solution
The additional boolean test needs to be removed.
WHERE ... AND (([OrderDate]-[XXXDate]<10))
Problem Status: On-Going
This is a known issue and we are looking towards improving on translation of this issue,
we already look for and correct many occurances of this problem.
Top of Page
Incorrect syntax near ...Understated boolean.
Problem
Access IIF statements can imply a boolean evaluation, where
SQL Server demands an explicit evaluation test. This can occur in IIF statements which
use functions that result in a boolean test.
IIF(IsNumeric([Fieldname]),....
or when translated
CASE WHEN (IsNumeric([Fieldname]) THEN...
This is what we call an understated boolean. SQL Server requires an explicit logical test.
Solution
An additional boolean test needs to be added.
CASE WHEN (IsNumeric([Fieldname])= 1 THEN...
Problem Status: On-Going
This is a known issue and we are looking towards improving on translation of this issue,
we already look for and correct many occurances of this problem.
Top of Page