Resolve SQL translation issues

support@ascassociates.biz , Sales Enquiries

Resolve SQL translation issues

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.

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.

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])

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

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:-

Solution

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

Status

Resolved, MUST+SQL Version 7.7 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.

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

Status

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.

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))

Status

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.

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

Status

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.

There is already an object named xxx in the database.

Problem

This error message is in fact a warning, and should only ever occur when you have existing pass-through queries called xxx with a corresponding view or stored procedure in SQL Server called xxx which you have decided to convert to SQL Server; the pass-through query name is the same as an existing view or stored procedure on SQL Server. If this is the case then the warning can be ignored.

Explanation

This can occur when you already have an Access database which has been converted to SQL Server, and you have started to convert existing Access queries by hand to SQL Server views. So you purchase MUST+SQL to save you time and money converting the bulk of your queries to SQL Server.

You had an existing query in Access called qryOrders, and you created a view in SQL Server called qryOrders, and changed the old Access query to a pass-through query. Your pass-through query contains the following SQL:-

SELECT * FROM [qryOrders]

This is a very sensible convention to adopt.

You also have a number of Access queries which use the pass-through query.

When MUST+SQL analyses your Access queries it will detect that you have existing pass-through queries and ask you if you want to convert these, if as described above you have other existing Access queries which use these pass-through queries, you should answer YES.

MUST+SQL will then add these pass-through queries to the data dictionary when validating your SQL, it will also make sure that when all the SQL is converted to SQL Server views and stored procedures such that if a pass-thought query already has a view or stored procedure with the same name that it DOES NOT replace the existing view or stored procedure.

If you did not select to convert the existing pass-through queries to SQL Server and you had existing Access queries which used your pass-through queries, then because the pass-through queries would not get loaded into the data dictionary the other existing Access queries that used them would not get converted; because they would have missing objects that they depended on (ie. the pass-through queries).

Status

This problem is only fully resolved in MUST 7.8. Contact support for details of how to download a beta version containing this fix.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz