Re: SSMA - Query Conversion to Stored Procedures.

Tech-Archive recommends: Fix windows errors by optimizing your registry

The last time that I've performed an upsizing some years ago, the fastest
way that I've found was simply to collect all queries in a big file and then
starting using the usual word processor facilities (cut&paste, global
replaces, etc.) to edit this one big file for transforming all the queries
into SP and Views.

However, I don't remember what I used to collect all the queries into a big
file in a single step.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OY0rDhRzGHA.2640@xxxxxxxxxxxxxxxxxxxxxxx
I'm wondering if you're running into the same bug that hits the older SQL
Upsizing Wizard. It's a pain in the ***, but try adding "SELECT TOP 100
PERCENT " to any queries you have in Access that're using Order By and see
if that works. I ended up doing all mine on SQL's end, so I don't know
for certain that it would even work in the Upsizing Wizard, much less in
SSMA which I've never used.

As for the parameterized queries, you're probably SOL there. While I'm
sure someone's smart enough to write code that would do at least basic
conversions on parameter queries, apparently Microsoft doesn't know
anybody like that. :)


Rob

"Greg" <AccessVBAnet@xxxxxxxxxxxxxxxxx> wrote in message
news:18497241-7640-449D-AF25-34CC21B369D4@xxxxxxxxxxxxxxxx
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[MSFT]" wrote:

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






.

Quantcast