July 5, 2011 at 2:03 pm
I've migrated MS Access db to SQL server 2008 but unfortunately two queries could not be converted ..Received below error
Error :-
Following SQL statement is not supported and cannot be converted:
I have fixed the syntax error and when i run the modified query in the migrated DB in sql I get an error ...that
table does not exist....Now my question is when we migrate from Access to SQL what happen to MS Access queries...
do they become SQL view or Do they become sql tables. if yes …If they become Table or View I think I know the problem why I am getting an error that table does not exist....Please help
July 5, 2011 at 2:48 pm
Post the queries that could not be converted. Maybe we can figure out why from the syntax.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 5, 2011 at 4:23 pm
below are the queries which errored out--- I changed the distinctrow to distinct and as function to Function_1 and that corrected the syntax and after these changes the query parsed out successfully but got a new error that the table does not exist
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;
-----------
SELECT DISTINCTROW qry_PI_Reports.Func_Name AS Function,
qry_PI_Reports.MgrName AS Manager, qry_PI_Reports.Assoc_Name AS Associate,
Sum(qry_PI_Reports.Total_Proc) AS Processed, Sum(qry_PI_Reports.Prod_Hrs) AS [Productive Hrs],
Sum(qry_PI_Reports.Paid_Hrs) AS [Paid Hrs]
FROM qry_PI_Reports
GROUP BY qry_PI_Reports.Func_Name, qry_PI_Reports.MgrName, qry_PI_Reports.Assoc_Name;
July 5, 2011 at 4:26 pm
In 99% of cases, an Access query becomes a SQL Server view when upgraded. I can't remember if the exception still exists, been a long while.
sqlquest2575 (7/5/2011)
I changed the distinctrow to distinct and as function to Function_1 and that corrected the syntax and after these changes the query parsed out successfully but got a new error that the table does not exist
Check your views for qry_Reports and qry_PI_Reports. My guess is they're not in the dbo schema, which means you're going to have to fully qualify them.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 6, 2011 at 8:30 am
I checked the view in sql server but i don’t see any user created view i can only see system views. There were 8 queries and 2 of them were not converted and now the questions is if the queries become view then why i cant see other 6 view for the successful queries? Please Help
July 6, 2011 at 12:10 pm
The converstion may have made qry_Reports and qry_PI_Reports into tables. Check that. Also check that qry_Reports and qry_PI_Reports were converted at all.
Run this in a SSMS query window:
select * from information_schema.tables where table_name = 'qry_Reports'
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 6, 2011 at 1:25 pm
sqlquest2575 (7/5/2011)
below are the queries which errored out--- I changed the distinctrow to distinct and as function to Function_1 and that corrected the syntax and after these changes the query parsed out successfully but got a new error that the table does not existSELECT 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;
-----------
SELECT DISTINCTROW qry_PI_Reports.Func_Name AS Function,
qry_PI_Reports.MgrName AS Manager, qry_PI_Reports.Assoc_Name AS Associate,
Sum(qry_PI_Reports.Total_Proc) AS Processed, Sum(qry_PI_Reports.Prod_Hrs) AS [Productive Hrs],
Sum(qry_PI_Reports.Paid_Hrs) AS [Paid Hrs]
FROM qry_PI_Reports
GROUP BY qry_PI_Reports.Func_Name, qry_PI_Reports.MgrName, qry_PI_Reports.Assoc_Name;
This is a duplicate post!
You are wasting peoples time. :w00t: People that are trying to help you.
Did I not already answer your question?
Do not reply to this post.
The original post is at the following URL:
http://www.sqlservercentral.com/Forums/Topic1135045-391-1.aspx
Remove DISTINCTROW for it is not valid in SQL Server.
Use the Group By on columns in which you are not performing an aggregate.
-- 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;
Did you even think to try this?
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:13 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
July 6, 2011 at 3:27 pm
Hi Todd..As suggested, I ran below statement in the database but the result came out empty
July 6, 2011 at 3:33 pm
sqlquest2575 (7/6/2011)
Hi Todd..As suggested, I ran below statement in the database but the result came out empty
What are you talking about? :hehe:
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:49 pm
sqlquest2575 (7/6/2011)
Hi Todd..As suggested, I ran below statement in the database but the result came out empty
Is there a different schema?
What happens if you query on the table directly?
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 4:18 pm
sqlquest2575 (7/6/2011)
Hi Todd..As suggested, I ran below statement in the database but the result came out empty
So, if this returned nothing
select * from information_schema.tables where table_name = 'qry_Reports'
then there is no view or table called qry_Reports. Your original query is not working because it depends on this object. You need to figure out why qry_reports didn't convert. Is it in the original Access database?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 7, 2011 at 7:22 am
I see that you are still trying to use a "SELECT DISTINCTROW".
Did you every try what I suggested?
Edit then copy & paste.
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 9:56 am
toddasd (7/6/2011)
sqlquest2575 (7/6/2011)
Hi Todd..As suggested, I ran below statement in the database but the result came out emptySo, if this returned nothing
select * from information_schema.tables where table_name = 'qry_Reports'
then there is no view or table called qry_Reports. Your original query is not working because it depends on this object. You need to figure out why qry_reports didn't convert. Is it in the original Access database?
What are you using to convert you queries to Stored Procedures?
If you are you performing it manually you have better control? You can change the schema. You can see the message that the SP was created successfully and if not you get an intuitive error.
I converted Several Access Database with Several Hundred queries in each one.
Converting a query to a SP is easy You should be able to knock out 20 or so once you get the process down
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 11:19 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 those 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 ??? I am using SSMA for migrating and i am assuming that SSMA takes care of coverting the queries. Please let me know if i need to convert it manually
Modified Queries
SELECT DISTINCT qry_Reports.Func_Name AS Function_1, 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;
-----------
SELECT DISTINCT qry_PI_Reports.Func_Name AS Function_1,
qry_PI_Reports.MgrName AS Manager, qry_PI_Reports.Assoc_Name AS Associate,
Sum(qry_PI_Reports.Total_Proc) AS Processed, Sum(qry_PI_Reports.Prod_Hrs) AS [Productive Hrs],
Sum(qry_PI_Reports.Paid_Hrs) AS [Paid Hrs]
FROM qry_PI_Reports
GROUP BY qry_PI_Reports.Func_Name, qry_PI_Reports.MgrName, qry_PI_Reports.Assoc_Name;
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply