RE: SSMA - Query Conversion to Stored Procedures.
- From: changliw@xxxxxxxxxxxxxxxxxxxx (Charles Wang[MSFT])
- Date: Thu, 31 Aug 2006 07:18:36 GMT
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 thedescriptions 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.
======================================================
.
- Follow-Ups:
- Prev by Date: RE: SSMA Migration to Master
- Next by Date: hhuih
- Previous by thread: Re: SSMA - Query Conversion to Stored Procedures.
- Next by thread: RE: SSMA - Query Conversion to Stored Procedures.
- Index(es):
Relevant Pages
|
