SSMA - Query Conversion to Stored Procedures.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When migrating my Access database to SQL Server none of my parameterized
Queries are converted to stored procedures. SSMA tells me my parameter
variable is undefined. In Access, I used the "Parameters" option to create my
parameter variables.

What must I do to get all fo these parameterized queries to convert to my
SQL Server database as Stored Procedures oir Views.
 
uh crack a book; build some tools and learn the language?

i've done thousands of queries-> sprocs.

in general-- use views if you can.. if you can't make something into a
view; then make it into a sproc.

MSN Instant Messenger (e-mail address removed) if you need more help; i
should be on _SOME_ over the next week or so... (but not every day 24-7
lol)

-Aaron
ADP Nationalist
 
To my knowledge, SSMA won't upsize (or convert) parameterized queries to SP;
only views will get transferred. However, you can ask your question in the
m.p.a.sqlupsizing newsgroup.
 
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.
======================================================
 
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.
======================================================
Click to expand...
 
I'm wondering if you're running into the same bug that hits the older SQL
Upsizing Wizard. It's a pain in the butt, 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 said:
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.
======================================================
Click to expand...
Click to expand...
 
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 said:
I'm wondering if you're running into the same bug that hits the older SQL
Upsizing Wizard. It's a pain in the butt, 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 said:
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.
======================================================
Click to expand...
Click to expand...
Click to expand...
 
Hi Greg,
Please take a seat and have a cup of coffee. I would like to share you my
experiences of using SSMA.
Indeed SSMA has many limitations and apparently it still has a lot of
aspects to be improved. From my research, If your query is plain with no
parameters, it can be converted to View. But before carrying out the
conversion, you need to connect to SQL Server first.
My actions on migrating an Access database to SQL Server 2005 are as
following:
1. Create a new project
2. Add an Access database
3. Connect to SQL Server
4. Select the Access database and its corresponding SQL Server database,
then execute the "Convert schema"
5. Select the SQL Server database and run "Load Database"
I would like your trying the above steps and seeing what is going on.

Also, I would like to let you know that the SSMA is a free tool and it has
no official support now. Appreciate your understanding.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Sincerely,
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.
======================================================
 
yes.. I did something similiar.. but instead of using word.. get this.

a) put the old query defs into a TABLE
b) use the tools you know so well; the update statements that you
already use every day.
c) shape the OldSql field into good candidates of the NewSql
d) filter against Sysobjects to see which objects are already present
e) rinse and repeat.

try to make anything into a view that you can; it simplifies things.
and any super-complex formulas that you do; change those into Scalar
User Defined Functions; instead of having 120 different copies of the
same formula.

-Aaron

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 said:
I'm wondering if you're running into the same bug that hits the older SQL
Upsizing Wizard. It's a pain in the butt, 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 said:
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

:

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.
======================================================
Click to expand...
Click to expand...
Click to expand...
 
After a bit more research, basic queries do convert to Views. Unforutnately
for me, 95% of my queries have PARAMETERS.

I do understand that SSMA is a FREE and unsupported software product and am
just sharing the fact that it is a tool of little use.

In light of that fact then, I persume Microsoft does not offer any Migration
Tools for Access to SQL Server that would inlude most all basic and standard
queries, even those with PARAMTERS? Would this be correct? My research has
also been resulting in no thrid parties that handle this problem either. I
guess I've just come up with a new product for the markiet... LOL.

I'm finally making this Migration at Microsoft's encouragement and yet there
are little to no tools to assist wth the process. Considering how widely
spread Access is, I just figured MS would have had this area covered, as I
think they are very good in that area.

Thanks for you support and help.

Greg
 
Sylvain Lafontaine said:
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.
Click to expand...

You could do something like this in Access ...

Public Sub WriteQueries()

Dim intFile As Integer
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef

intFile = FreeFile
Open CurrentProject.Path & "\queries.text" For Output As intFile
Set db = CurrentDb
Set qdfs = db.QueryDefs
For Each qdf In qdfs
Print #intFile, qdf.Name
Print #intFile, qdf.SQL
Next qdf
Close intFile

End Sub
 
Brenda,

I like the procedure as it will certainly make it much much easier for me to
update all the necessary code as I'm moving them into SQL Server. I've been
opening, copying, modifying, creating, closing, etc each query one at a time.
Atleast this proc will allow me to update common pieces all at once. What a
life save. Thank you very much.
Greg
 
That's nice, Greg, but who is this 'Brenda' person, and how come she keeps
getting the credit for my posts? :-)
 
No problem, Greg! Just remember the next time I make a mistake, it wasn't
me - Brenda did it! :-)
 
Appreciate your understanding!
I'm also very glad to see that the next release will include this power
function.

Have a great day!

Charles Wang
Microsoft Online Community Support
 
Back
Top