July 7, 2011 at 1:10 pm
sqlquest2575 (7/7/2011)
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 ???
Yes, entirely possible. If qry_Reports and qry_PI_Reports do not exist in the Access file (as either queries or tables) then your queries below have no basis and do not need to be converted, apparently. I've seen it before. An object in a database that references other objects that no longer exist. Ask the business user about that functionality. You will usually get that they haven't used it in years or it was promised and never finished.
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
;
It should convert them all, but it seems this is the part you have to figure out. :blink:
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 7, 2011 at 1:46 pm
Given the naming convention, the qry_Reports and qry_PI_Reports Tables seem like ad-hoc temporary tables, a.k.a. garbage. 😉
If you can't find them, ask as was sugested and if nobody ones what they are move on.
It is kinda like the stack of reports that get placed on a upper manages desk and they do not know what they are for and they never read them.
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 2:17 pm
sqlquest2575 (7/7/2011)
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 manuallyModified 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;
You need to find your tables but even if you had them, have you considered the use of DISTINCT as opposed to GROUP By?
http://msmvps.com/blogs/robfarley/archive/2007/03/24/group-by-v-distinct-group-by-wins.aspx
I have migrated several Access Databases which included several hundred Access Databases.
I would have thought that we would be further along in this process by now.
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 8, 2011 at 10:19 am
Hello Friends, Thank you so much for all you replies... I've a meeting next week with the Dev team and then i'll find out about these two queries and surly i'll update you guys here. Thank you so much once again
July 8, 2011 at 12:37 pm
Are you sure that qry_Reports and qry_PI_Reports referenced are not queries?
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 10, 2011 at 12:34 pm
When i open MS access file and i see there is a tab says queries and when click on that i see 8 queries and two of them are(qry_Reports and qry_PI_Reports ) which are not working. So that means they are the queries.
July 10, 2011 at 12:58 pm
sqlquest2575 (7/10/2011)
When i open MS access file and i see there is a tab says queries and when click on that i see 8 queries and two of them are(qry_Reports and qry_PI_Reports ) which are not working. So that means they are the queries.
Exactly! I forgot to mention a very important concept when migrating Access queries and unfortunately I made the assumption that this was common knowledge, my bad. For future reference if you do not see a table that is referenced in the query look for a query.
In SQL Server you create subqueries, inLine views, etc.
In Access most users often create queries and then join them in a subsequent queries.
Try opening up the queries in design mode. You will probably find a few tables.
Have you migrated your tables yet and are they populated.
If you were to provide the scripts to create and load the data and the queries then you would probably get this resolved quite quickly.
Would you like to report a solution as opposed to presenting a problem? 😎
Help us help you. Give it a shot by providing the scripts. If you don't know how or need help ask.;-)
Regards,
WC
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 10, 2011 at 4:29 pm
Thanks Corgi for your post. As suggested, I opened the query in the design mode and found 3 tables and yes those tables are already migrated.
July 10, 2011 at 7:39 pm
sqlquest2575 (7/10/2011)
Thanks Corgi for your post. As suggested, I opened the query in the design mode and found 3 tables and yes those tables are already migrated.
So the next step is to modify your Stored Procedures the include the other tables.
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 11, 2011 at 10:00 am
Thanks Corgi. I am just trying to understand your question here.. I have not created any proc to do this migration. I used SQL Server Migration Assistance for this task. In what proc do I need to add tables and also which tables need to be added?
July 11, 2011 at 10:18 am
sqlquest2575 (7/11/2011)
Thanks Corgi. I am just trying to understand your question here.. I have not created any proc to do this migration. I used SQL Server Migration Assistance for this task. In what proc do I need to add tables and also which tables need to be added?
You need to create a Stored procedure for each of the original Access queries that you listed, THey need to include the tables and any logic listed in the qry_Reports and qry_PI_Reports queries.
It would be faster to explain if I saw what you have.
Script each table that is being referenced by the queries (Right click on the table and select script as
Copy and past the Access queries that you found
Create a quick script to load some data in the tables.
For additional information click on Jeff's link listed below on how to post.
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 11, 2011 at 1:57 pm
Thank you Corgi. As suggested below please find the table script and the Access Query .
Table Create Script and Insert Data Script--
CREATE TABLE [dbo].[tbl_PI_Associates](
[Assoc_Name] [nvarchar](50) NOT NULL,
[MgrName] [nvarchar](50) NULL,
[IsActive] [bit] NULL)
insert into tbl_PI_Associates
values('Test','test2',1)
insert into tbl_PI_Associates
values('again','again',2)
------------------
CREATE TABLE [dbo].[tbl_PI_Functions](
[Func_Name] [nvarchar](50) NOT NULL,
[PPH_Goal] [float] NULL,
[PTP_Goal] [float] NULL,
[IsActive] [bit] NULL)
insert into [tbl_PI_Functions]
values('fatal',8,5,1)
insert into [tbl_PI_Functions]
values('Danger',3,2,1)
-----------------------------
CREATE TABLE [dbo].[tbl_PI_Data](
[ID] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Assoc_Name] [nvarchar](50) NOT NULL,
[Func_Name] [nvarchar](50) NOT NULL,
[Total_Proc] [int] NULL,
[Prod_Hrs] [float] NOT NULL,
[Paid_Hrs] [float] NULL,
[IsActive] [bit] NULL)
insert into [tbl_PI_Data]
values (4,'2008-03-06 00:00:00','Lisa Taylor','Prep',0,3.9,9,1)
insert into [tbl_PI_Data]
values (8,'2008-09-09 00:00:00','Rony','Test',4,7.9,2,1)
-----------------
Access Query :-
SELECT tbl_PI_Data.Date, tbl_PI_Data.Assoc_Name, tbl_PI_Associates.MgrName, tbl_PI_Data.Func_Name, tbl_PI_Data.Total_Proc, tbl_PI_Data.Prod_Hrs, tbl_PI_Data.Paid_Hrs, [Total_Proc]/[Prod_Hrs] AS PPH, tbl_PI_Functions.PPH_Goal, [Prod_Hrs]/[Paid_Hrs] AS PTP, tbl_PI_Functions.PTP_Goal
FROM tbl_PI_Functions INNER JOIN (tbl_PI_Associates INNER JOIN tbl_PI_Data ON tbl_PI_Associates.Assoc_Name = tbl_PI_Data.Assoc_Name) ON tbl_PI_Functions.Func_Name = tbl_PI_Data.Func_Name
WHERE (((tbl_PI_Data.Date) Between [Start Date] And [End Date]));
----------
July 11, 2011 at 9:55 pm
sqlquest2575 (7/11/2011)
Thank you Corgi. As suggested below please find the table script and the Access Query .Table Create Script and Insert Data Script--
CREATE TABLE [dbo].[tbl_PI_Associates](
[Assoc_Name] [nvarchar](50) NOT NULL,
[MgrName] [nvarchar](50) NULL,
[IsActive] [bit] NULL)
insert into tbl_PI_Associates
values('Test','test2',1)
insert into tbl_PI_Associates
values('again','again',2)
------------------
CREATE TABLE [dbo].[tbl_PI_Functions](
[Func_Name] [nvarchar](50) NOT NULL,
[PPH_Goal] [float] NULL,
[PTP_Goal] [float] NULL,
[IsActive] [bit] NULL)
insert into [tbl_PI_Functions]
values('fatal',8,5,1)
insert into [tbl_PI_Functions]
values('Danger',3,2,1)
-----------------------------
CREATE TABLE [dbo].[tbl_PI_Data](
[ID] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Assoc_Name] [nvarchar](50) NOT NULL,
[Func_Name] [nvarchar](50) NOT NULL,
[Total_Proc] [int] NULL,
[Prod_Hrs] [float] NOT NULL,
[Paid_Hrs] [float] NULL,
[IsActive] [bit] NULL)
insert into [tbl_PI_Data]
values (4,'2008-03-06 00:00:00','Lisa Taylor','Prep',0,3.9,9,1)
insert into [tbl_PI_Data]
values (8,'2008-09-09 00:00:00','Rony','Test',4,7.9,2,1)
-----------------
Access Query :-
SELECT tbl_PI_Data.Date, tbl_PI_Data.Assoc_Name, tbl_PI_Associates.MgrName, tbl_PI_Data.Func_Name, tbl_PI_Data.Total_Proc, tbl_PI_Data.Prod_Hrs, tbl_PI_Data.Paid_Hrs, [Total_Proc]/[Prod_Hrs] AS PPH, tbl_PI_Functions.PPH_Goal, [Prod_Hrs]/[Paid_Hrs] AS PTP, tbl_PI_Functions.PTP_Goal
FROM tbl_PI_Functions INNER JOIN (tbl_PI_Associates INNER JOIN tbl_PI_Data ON tbl_PI_Associates.Assoc_Name = tbl_PI_Data.Assoc_Name) ON tbl_PI_Functions.Func_Name = tbl_PI_Data.Func_Name
WHERE (((tbl_PI_Data.Date) Between [Start Date] And [End Date]));
----------
I'm a little tired but did you incude the tables and the queiers for qry_Reports and qry_PI_Reports?
Thank you.
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 12, 2011 at 8:34 am
Thanks Corgi. Yes I had attached one of the 2 queries and its related tables in my last post.
Query:- qry_PI_reports
Errored out Query ( Table does not exist)
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;
July 12, 2011 at 12:10 pm
Hi…. now I got into another error while migrating another db from access to sql. When I open access file I see only 4 tables but when I use ssma to migrate my db to sql it generates almost 25 tables Starting with the following name ~TMPCLP287001 .. I am not sure why I am getting this type of names also all of the queries failed to migrate. Received the error -- Aggregate function First is not supported and cannot be converted. But when I use upsizing wizard in MS Access I don’t get any error? Does upsizing wizard convert queries like SSMA does ? Do we have to manually convert queries if we use upsizing wizard in Access
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply