July 1, 2011 at 8:10 am
Hello Friends
I am migrating MS access Db to SQL 2008 and I am using SSMA to accomplish this task. But I’ve couple of questions.
1. When I run ssma I got 2 error saying that two of the queries could not be converted
(Is that a problem as I see all table are migrated successfully but 2 queries have issues)
2.What would be the best approach to do this in production? I’ll be doing this in Dev environment for now but this step should be repeatable so that it can be implemented in production. Now the issue is the MDB file doesn’t have current data in it and that is fine for dev environment but when we’ll be doing this in production we’ll have to get the current back up of mdb file ..i am worried if there is an issue while converting the
Access db to sql via SSMA on that Production implementation day as we’ll have only couple of hours to complete this task.. Is there anyone who has done similar kind of task? Please help me here … I would like to know the approach I should take for this task
July 1, 2011 at 12:00 pm
sqlquest2575 (7/1/2011)
Hello FriendsI am migrating MS access Db to SQL 2008 and I am using SSMA to accomplish this task. But I’ve couple of questions.
1. When I run ssma I got 2 error saying that two of the queries could not be converted
(Is that a problem as I see all table are migrated successfully but 2 queries have issues)
2.What would be the best approach to do this in production?
1- Yes, that's a problem. Identify and rewrite the two offending queries.
2- Once you have perfected the process in development just do exactly the same in production (after a full backup) - be sure you have scripted the two queries of the first part of the question and create them on Production too.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2011 at 2:45 pm
sqlquest2575 (7/1/2011)
Hello FriendsI am migrating MS access Db to SQL 2008 and I am using SSMA to accomplish this task. But I’ve couple of questions.
1. When I run ssma I got 2 error saying that two of the queries could not be converted
(Is that a problem as I see all table are migrated successfully but 2 queries have issues)
2.What would be the best approach to do this in production? I’ll be doing this in Dev environment for now but this step should be repeatable so that it can be implemented in production. Now the issue is the MDB file doesn’t have current data in it and that is fine for dev environment but when we’ll be doing this in production we’ll have to get the current back up of mdb file ..i am worried if there is an issue while converting the
Access db to sql via SSMA on that Production implementation day as we’ll have only couple of hours to complete this task.. Is there anyone who has done similar kind of task? Please help me here … I would like to know the approach I should take for this task
1. When I run ssma I got 2 error saying that two of the queries could not be converted
(Is that a problem as I see all table are migrated successfully but 2 queries have issues)
What errors are you getting?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2011 at 7:58 pm
Thank you so much PaulB and Corgi for your quick response. When I try to migrate through SSMA it converts everything except 2 queries and in the reports it shows the ddl of those queries. I’ve pasted the queries below….PaulB helped me a lot so far….if you guys could give me little more guidance on this… would be great
July 3, 2011 at 8:05 am
Unfortunately I can't address your issue for the following query.
SELECT DISTINCTROW qry_Reports.Func_Name AS Function, qry_Reports.MgrName AS Manager, qry_Reports.Assoc_Name AS Associate, Sum(qry_Reports.Total_Proc) AS Processed, Sum(qry_Reports.Prod_Hrs) AS [Productive Hrs], Sum(qry_Reports.Paid_Hrs) AS [Paid Hrs]
FROM qry_Reports
GROUP BY qry_Reports.Func_Name, qry_Reports.MgrName, qry_Reports.Assoc_Name;
But here are some guidelines that I go by that help with readability and IMHO make it easier to spot a problem.
-- I don't have the schema or data so that I can test.
-- I would suggest that you consider the following to make you code easier to read
-- Use Aliases to make you code more readable
-- Don't use reserved words
-- Why use DISTINCTROW?
-- Don't Use Spaces in Column Names or Aliases
SELECT Rpt.Func_Name AS FuncName, qry.MgrName AS MgrName, qry.AssocName AS AssocName,
SUM(qry.Total_Proc) AS Processed, SUM(qry.Prod_Hrs) AS ProdHrs,
SUM(qry.Paid_Hrs) AS PaidHrs
FROM qry_Reports AS qry
GROUP BY qry.FuncName, qry.MgrName, qry.AssocName;
Regards. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 3, 2011 at 3:29 pm
Remove DISTINCTROW for it is not valid in SQL Server.
Use the Group By on columns in which you are not performing an aggregate.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 3, 2011 at 6:39 pm
Thank you so much all for your help and support ...You guys helped me al ot
July 6, 2011 at 2:56 pm
Welsh Corgi (7/1/2011)
sqlquest2575 (7/1/2011)
1. When I run ssma I got 2 error saying that two of the queries could not be converted(Is that a problem as I see all table are migrated successfully but 2 queries have issues)
What errors are you getting?
SQL Server returned an error message stating that two queries could not be converted.
When you are ask to provide the error message, please provided it.
I provided you with a solution to your problem and you create a new post.
http://www.sqlservercentral.com/Forums/Topic1136803-391-1.aspx
Try the following:
Provide scripts to create the schema and populate the tables and I will crewate and test the SP.
I do not use wizards whern converting from Access queries to SP's.
I open up the Access qury in Design Mode. Right Clic on each table and create an alias.
Click on View SQL.
Select all and copy and paste into Query Analyzer.
Then I add the CREATE PROCEDURE ProcedureName (Include Parameters) AS
SELECT * FROM ,,,
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 6, 2011 at 3:18 pm
corgi. thanks for your post. I am not trying to waste anyone's time. I am just trying to get the answer of my sql issue. I had a different question in my last post . If you read my earlier post you'll find out that i had a different MS Access related issue. I am sorry if you feel that i wasted your time. As far as error is concerned that was the only error i received while migrating From MS Access to SQL via SSMA.
July 6, 2011 at 4:25 pm
sqlquest2575 (7/6/2011)
corgi. thanks for your post. I am not trying to waste anyone's time. I am just trying to get the answer of my sql issue. I had a different question in my last post . If you read my earlier post you'll find out that i had a different MS Access related issue. I am sorry if you feel that i wasted your time. As far as error is concerned that was the only error i received while migrating From MS Access to SQL via SSMA.
Has your problem been resolved?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2011 at 10:24 am
Access and SQL Server are very different beasts. SQL Server has no GUI, so any query that points to a control on a form will fail when you try to convert it. Any parameterized queries, e.g., "SELECT ... FROM .... WHERE [SomeField] LIKE [Forms]![frmUnboundForm]![txtSearchCriterion1] & '*'" will fail.
There are a few Access-specific SQL extensions (DISTINCTROW) that SQL Server won't support... I'm sure that if you dig around, you can find them. If that doesn't work for you, post the Access SQL. Open the query in Design view, hit the SQL button, and post the SQL. Then people can see what you're talking about. Then the problem should become obvious.
July 7, 2011 at 11:10 am
Thank you for your reply. I am still trying to figure out the issue here. After Modifying the queries now the error says that table does not exist... I checked the source MS Access file and noticed that all queries have some related tables in the access file except for these two queries. Do you think the application may not be using the query and that’s why I don’t see related tables or views and getting the error? Someone from the application dev team might have removed the tables but did not take out the query…… Do you think it is possible ???
July 7, 2011 at 1:40 pm
If the table is missing then recreate it in SQL Server and the populate it.
You should tweak the indexes and remove reserved words and spaces in column names, etc so that you do not have to use brackets in your code.
It is very easy. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply