I'm finding the SSMA Tool of little use. All this tool seems to accomplish
is
converting Tables and not queries. In continuing to work with this tool, I
clicked a couple of my queries that have no PARAMETER's setup and are just
basic Queries that sort a list of Customer Names in order by Customer
Name.
In fact, one of them just displays the entire contents of a table.
Probably
the most simple queries I've got in my entire database.
When I click on these queries in the "Access Metadata Explorer" window the
"Convert Schema" button does not become enabled. Even when I click on the
"Load Database" nothing happens. I've included the Select statement below:
--
SELECT tblCustomer.CustomerID, tblCustomer.CustomerName,
tblCustomer.CustomerNum
FROM tblCustomer
ORDER BY tblCustomer.CustomerName;
--
SELECT tblCustomer.*
FROM tblCustomer;
--
These is by far the most plain and simple queries one could have and even
these do not convert to SQL. This amazes me. At this point, I don't
understand why the SSMA tool even lists the queries, because not on single
query out of 300+ in my application convert. Having to write 300+ to
stored
procedures is a daunting task. I certainly understand how to create them,
but
a task I though would just take a few hours will now take all week, if not
longer.
If the SSMA tool cannot covert basic Queries, is there another tool out
there, whether Microsoft or not that can handle this.
Thanks for the help.
Greg
Charles Wang said:
Hi,
My understanding of your issue is:
You want to use SSMA to migrate your Access database to SQL Server 2005
and
you also want to migrate those queries with parameters into SQL Server
stored procedures.
However you found that none of your Access parameter queries are
converted
to stored procedures and that an undefined error came out.
If I have misunderstood, please let me know.
Unfortunately SSMA hasn't provided this power function to convert such
parameter queries to stored procedures or valid views. I recommend that
you
mannualy rewrite such parameters queries in stored procedures of SQL
Server
2005.
From the Help of SQL Server Migration Assistant for Access, we can find
the
descriptions in the chapter "Converting Access Database objects":
Not all Access queries are equivalent to views. Queries that cannot be
converted to views will not be converted.
Also, from the conversion objects list we can see that Access queries can
only be converted to SQL views automatically.
To confirm this, I also performed a test migrating an Access 2003
database
to a SQL Server 2005 database.According to my research, Access queries
with
parameters can only be converted to Views. However, the parameters
statements like "PARAMETERS varName Text (255)" are not supported for
schema conversion. When I made comments on those parameters statements,
the
"Convert Schema" operation was executed successfully and a View was
produced. But when I load those conversions to database, I encountered
the
issue "Invalid column [varName]". I think this error message is
reasonable,
because the varName is not declared first and parameters are not
supported
in View of SQL Server.
If you need help on writing a stored procedure, you can refer to SQL
Server
Books Online.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
======================================================
