A2SS0030: SQL Server Migration Assistant for Access Warning messag

[Jean-Marc]

For the query below SSMAA tell me Identifier name might cause problems
at 2 places.
Can you tell me where I can find guidelline to eliminate these warning.

or
Can you tell me what are the warning?

Here access the query

SELECT DISTINCTROW WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER, WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status, WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl, WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code, WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No, WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id, WIP_HIERARCHY_TABLE.user_id
FROM WIP_ASSY_TABLE INNER JOIN WIP_HIERARCHY_TABLE ON
WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (((WIP_HIERARCHY_TABLE.CommonKey)=[Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type)="Assy" Or
(WIP_HIERARCHY_TABLE.Entity_Type)="ASSY") AND
((WIP_ASSY_TABLE.user_id)=[User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id)=[User_Id_Parameter]))
ORDER BY WIP_HIERARCHY_TABLE.CommonKey, WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER, WIP_HIERARCHY_TABLE.Item_Auto_No;


Here the SQL2008 query.

CREATE VIEW dbo.[Fill_Data_Assy]
AS

/*
* Generated by SQL Server Migration Assistant for Access.
* Contact ***@microsoft.com or visit
http://www.microsoft.com/sql/migration for more information.
*/
SELECT TOP 9223372036854775807 WITH TIES
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status,
WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl,
WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code,
WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No,
WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id AS [WIP_ASSY_TABLE_user_id],
WIP_HIERARCHY_TABLE.user_id AS [WIP_HIERARCHY_TABLE_user_id]
FROM
(
SELECT DISTINCT *
FROM WIP_ASSY_TABLE
) AS WIP_ASSY_TABLE
INNER JOIN
(
SELECT DISTINCT *
FROM WIP_HIERARCHY_TABLE
) AS WIP_HIERARCHY_TABLE
ON WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (
((WIP_HIERARCHY_TABLE.CommonKey) = [Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type) = 'Assy' OR
(WIP_HIERARCHY_TABLE.Entity_Type) = 'ASSY') AND
((WIP_ASSY_TABLE.user_id) = [User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id) = [User_Id_Parameter]))
ORDER BY
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.Item_Auto_No
GO

[unknown]

On SQL-Server, you cannot have variable or parameters enclosed between [];
so the two parameters [Assy_Number_Parameter]) and [User_Id_Parameter] are
invalid.

DISTINCTROW is also an invalid keyword. In your case, this is probably
already a bad choice of using this keyword even without the upsizing
process. You should use Distinct instead for both Access & SQL-Server.

The massive use of uppercase and of the underline symbol for the name of
your tables makes your queries very hard to read. You should use the same
Camel notation (the combination of Upper and Lower case with no _ ) for
your tables's names as you are already doing with your columns's names.

Using aliases instead of repeating the name of the tables will also help
making your code easier to read.

Finally, it's not clear if you are upsizing this application to a MDB or
ACCDB file with ODBC linked tables or to an ADP project.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Jean-Marc
For the query below SSMAA tell me Identifier name might cause problems
at 2 places.
Can you tell me where I can find guidelline to eliminate these warning.
or
Can you tell me what are the warning?
Here access the query
SELECT DISTINCTROW WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER, WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status, WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl, WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code, WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No, WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id, WIP_HIERARCHY_TABLE.user_id
FROM WIP_ASSY_TABLE INNER JOIN WIP_HIERARCHY_TABLE ON
WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (((WIP_HIERARCHY_TABLE.CommonKey)=[Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type)="Assy" Or
(WIP_HIERARCHY_TABLE.Entity_Type)="ASSY") AND
((WIP_ASSY_TABLE.user_id)=[User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id)=[User_Id_Parameter]))
ORDER BY WIP_HIERARCHY_TABLE.CommonKey, WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER, WIP_HIERARCHY_TABLE.Item_Auto_No;
Here the SQL2008 query.
CREATE VIEW dbo.[Fill_Data_Assy]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
http://www.microsoft.com/sql/migration for more information.
*/
SELECT TOP 9223372036854775807 WITH TIES
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status,
WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl,
WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code,
WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No,
WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id AS [WIP_ASSY_TABLE_user_id],
WIP_HIERARCHY_TABLE.user_id AS [WIP_HIERARCHY_TABLE_user_id]
FROM
(
SELECT DISTINCT *
FROM WIP_ASSY_TABLE
) AS WIP_ASSY_TABLE
INNER JOIN
(
SELECT DISTINCT *
FROM WIP_HIERARCHY_TABLE
) AS WIP_HIERARCHY_TABLE
ON WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (
((WIP_HIERARCHY_TABLE.CommonKey) = [Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type) = 'Assy' OR
(WIP_HIERARCHY_TABLE.Entity_Type) = 'ASSY') AND
((WIP_ASSY_TABLE.user_id) = [User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id) = [User_Id_Parameter]))
ORDER BY
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.Item_Auto_No
GO

[Jean-Marc]

Thanks Sylvain for the info and your suggestion for the convention name.

Here some clarification

I'm upgrading the database from and access 2000/2003 database (.mdb) to SQL
2008. There is no front end in access. Only tables and Queries.

The project I'm working is a conversion of an application.

The front was developed with Visual studio 2005. We use the ODBC driver to
connect the application to the access 2000/2003. We run the query from a
store procedure in the application. Is there an option in SSMA to advise that
I’m using the ODBC driver.


I will try to upsize again from access 2000 to acces 2007 than use the SSMA
tool to see the difference but as far I remember the conversion result was
the same in term of number of error, warning... But at did not look in
details.

picasso

Post by unknown
On SQL-Server, you cannot have variable or parameters enclosed between [];
so the two parameters [Assy_Number_Parameter]) and [User_Id_Parameter] are
invalid.
DISTINCTROW is also an invalid keyword. In your case, this is probably
already a bad choice of using this keyword even without the upsizing
process. You should use Distinct instead for both Access & SQL-Server.
The massive use of uppercase and of the underline symbol for the name of
your tables makes your queries very hard to read. You should use the same
Camel notation (the combination of Upper and Lower case with no _ ) for
your tables's names as you are already doing with your columns's names.
Using aliases instead of repeating the name of the tables will also help
making your code easier to read.
Finally, it's not clear if you are upsizing this application to a MDB or
ACCDB file with ODBC linked tables or to an ADP project.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Jean-Marc
For the query below SSMAA tell me Identifier name might cause problems
at 2 places.
Can you tell me where I can find guidelline to eliminate these warning.
or
Can you tell me what are the warning?
Here access the query
SELECT DISTINCTROW WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER, WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status, WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl, WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code, WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No, WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id, WIP_HIERARCHY_TABLE.user_id
FROM WIP_ASSY_TABLE INNER JOIN WIP_HIERARCHY_TABLE ON
WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (((WIP_HIERARCHY_TABLE.CommonKey)=[Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type)="Assy" Or
(WIP_HIERARCHY_TABLE.Entity_Type)="ASSY") AND
((WIP_ASSY_TABLE.user_id)=[User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id)=[User_Id_Parameter]))
ORDER BY WIP_HIERARCHY_TABLE.CommonKey, WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER, WIP_HIERARCHY_TABLE.Item_Auto_No;
Here the SQL2008 query.
CREATE VIEW dbo.[Fill_Data_Assy]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
http://www.microsoft.com/sql/migration for more information.
*/
SELECT TOP 9223372036854775807 WITH TIES
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status,
WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl,
WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code,
WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No,
WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id AS [WIP_ASSY_TABLE_user_id],
WIP_HIERARCHY_TABLE.user_id AS [WIP_HIERARCHY_TABLE_user_id]
FROM
(
SELECT DISTINCT *
FROM WIP_ASSY_TABLE
) AS WIP_ASSY_TABLE
INNER JOIN
(
SELECT DISTINCT *
FROM WIP_HIERARCHY_TABLE
) AS WIP_HIERARCHY_TABLE
ON WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (
((WIP_HIERARCHY_TABLE.CommonKey) = [Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type) = 'Assy' OR
(WIP_HIERARCHY_TABLE.Entity_Type) = 'ASSY') AND
((WIP_ASSY_TABLE.user_id) = [User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id) = [User_Id_Parameter]))
ORDER BY
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.Item_Auto_No
GO

[unknown]

If this the only query that is giving you trouble? If not, then you must
see the upsizing process with the SSMA only as the first step of your
migration process and probably/possibly you'll have to finish the process
yourself by manually converting many of the queries from JET-SQL to T-SQL.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Jean-Marc
Thanks Sylvain for the info and your suggestion for the convention name.
Here some clarification
I'm upgrading the database from and access 2000/2003 database (.mdb) to SQL
2008. There is no front end in access. Only tables and Queries.
The project I'm working is a conversion of an application.
The front was developed with Visual studio 2005. We use the ODBC driver to
connect the application to the access 2000/2003. We run the query from a
store procedure in the application. Is there an option in SSMA to advise that
I'm using the ODBC driver.
I will try to upsize again from access 2000 to acces 2007 than use the SSMA
tool to see the difference but as far I remember the conversion result was
the same in term of number of error, warning... But at did not look in
details.
picasso

Post by unknown
On SQL-Server, you cannot have variable or parameters enclosed between [];
so the two parameters [Assy_Number_Parameter]) and [User_Id_Parameter] are
invalid.
DISTINCTROW is also an invalid keyword. In your case, this is probably
already a bad choice of using this keyword even without the upsizing
process. You should use Distinct instead for both Access & SQL-Server.
The massive use of uppercase and of the underline symbol for the name of
your tables makes your queries very hard to read. You should use the same
Camel notation (the combination of Upper and Lower case with no _ ) for
your tables's names as you are already doing with your columns's names.
Using aliases instead of repeating the name of the tables will also help
making your code easier to read.
Finally, it's not clear if you are upsizing this application to a MDB or
ACCDB file with ODBC linked tables or to an ADP project.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Jean-Marc
For the query below SSMAA tell me Identifier name might cause problems
at 2 places.
Can you tell me where I can find guidelline to eliminate these warning.
or
Can you tell me what are the warning?
Here access the query
SELECT DISTINCTROW WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER, WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status, WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl, WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code,
WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No, WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id, WIP_HIERARCHY_TABLE.user_id
FROM WIP_ASSY_TABLE INNER JOIN WIP_HIERARCHY_TABLE ON
WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (((WIP_HIERARCHY_TABLE.CommonKey)=[Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type)="Assy" Or
(WIP_HIERARCHY_TABLE.Entity_Type)="ASSY") AND
((WIP_ASSY_TABLE.user_id)=[User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id)=[User_Id_Parameter]))
ORDER BY WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER, WIP_HIERARCHY_TABLE.Item_Auto_No;
Here the SQL2008 query.
CREATE VIEW dbo.[Fill_Data_Assy]
AS
/*
* Generated by SQL Server Migration Assistant for Access.
http://www.microsoft.com/sql/migration for more information.
*/
SELECT TOP 9223372036854775807 WITH TIES
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.part_cg_status,
WIP_HIERARCHY_TABLE.part_an_grp,
WIP_HIERARCHY_TABLE.part_an_dtl,
WIP_HIERARCHY_TABLE.part_weight_code,
WIP_HIERARCHY_TABLE.part_pkg_code,
WIP_HIERARCHY_TABLE.bay_number_entered,
WIP_HIERARCHY_TABLE.Item_Auto_No,
WIP_HIERARCHY_TABLE.Entity_Type,
WIP_ASSY_TABLE.user_id AS [WIP_ASSY_TABLE_user_id],
WIP_HIERARCHY_TABLE.user_id AS [WIP_HIERARCHY_TABLE_user_id]
FROM
(
SELECT DISTINCT *
FROM WIP_ASSY_TABLE
) AS WIP_ASSY_TABLE
INNER JOIN
(
SELECT DISTINCT *
FROM WIP_HIERARCHY_TABLE
) AS WIP_HIERARCHY_TABLE
ON WIP_ASSY_TABLE.Commonkey = WIP_HIERARCHY_TABLE.CommonKey
WHERE (
((WIP_HIERARCHY_TABLE.CommonKey) = [Assy_Number_Parameter]) AND
((WIP_HIERARCHY_TABLE.Entity_Type) = 'Assy' OR
(WIP_HIERARCHY_TABLE.Entity_Type) = 'ASSY') AND
((WIP_ASSY_TABLE.user_id) = [User_Id_Parameter]) AND
((WIP_HIERARCHY_TABLE.user_id) = [User_Id_Parameter]))
ORDER BY
WIP_HIERARCHY_TABLE.CommonKey,
WIP_HIERARCHY_TABLE.ITEM_NUMBER,
WIP_HIERARCHY_TABLE.PART_NUMBER,
WIP_HIERARCHY_TABLE.Item_Auto_No
GO