Hello everybody, this is my first post on this network.. . . I am sure that I will get some help from genuise poeple. Basically, it is the first time I am trying to migrate my database to SQL server 2005. The process seems to work well. . . it does create my access project and create all the links to the SQL Server. . . However, out 15 queries, only 2 queries are copied to my access project file. . all the rest of the queries are deleted. . . given that the control source for my reports and forms are queries. . . no of them works. . as the control source doesn't exist in the access database. . . as an example: this message is shown duration the migration process:Query Representationbynationality Failed to upsize. Attempted to use SQL: CREATE VIEW Representationbynationality AS SELECT ContinentChart.Continent, CountryNationalityChart.Country, count(qrygenderbyage.Status) AS CountOfStatus FROM ContinentChart INNER JOIN (CountryNationalityChart INNER JOIN qrygenderbyage ON (CountryNationalityChart.Nationality=qrygenderbyag e.nationality)) ON (ContinentChart.ID=CountryNationalityChart.Contine nt) GROUP BY ContinentChart.Continent, CountryNationalityChart.Country-------------your help will be really appreciated. . . . thanks.

Hello everybody, this is my first post on this network.. . . I am sure that I will get some help from genuise poeple. Basically, it is the first time I am trying to migrate my database to SQL server 2005. The process seems to work well. . . it does create my access project and create all the links to the SQL Server. . . However, out 15 queries, only 2 queries are copied to my access project file. . all the rest of the queries are deleted. . . given that the control source for my reports and forms are queries. . . no of them works. . as the control source doesn't exist in the access database. . . as an example: this message is shown duration the migration process:Query Representationbynationality Failed to upsize. Attempted to use SQL: CREATE VIEW Representationbynationality AS SELECT ContinentChart.Continent, CountryNationalityChart.Country, count(qrygenderbyage.Status) AS CountOfStatus FROM ContinentChart INNER JOIN (CountryNationalityChart INNER JOIN qrygenderbyage ON (CountryNationalityChart.Nationality=qrygenderbyag e.nationality)) ON (ContinentChart.ID=CountryNationalityChart.Contine nt) GROUP BY ContinentChart.Continent, CountryNationalityChart.Country-------------your help will be really appreciated. . . . thanks.


SELECT ContinentChart.Continent,
CountryNationalityChart.Country,
count(qrygenderbyage.Status) AS CountOfStatus
FROM ContinentChart
INNER JOIN (CountryNationalityChart
INNER JOIN qrygenderbyage
ON (CountryNationalityChart.Nationality=qrygenderbyag e.nationality))
ON (ContinentChart.ID=CountryNationalityChart.Contine nt)
GROUP BY ContinentChart.Continent, CountryNationalityChart.Country


I reformatted your post to make it easier for me to read. If I reformatted it properly, then as far as I can tell, your query should have converted properly. Your table and column names are standard (no special characters or spaces), and there are no obvious syntax errors. There query also does not user ORDER BY, DISTINCTROW, or other non-Server Compatible instructions.

My projects are presently a mix of Access queries and SQL Server Views. I have converted the queries that were possible to convert, and the remainder are still in the Access Front End. Did you leave the ones that did not convert on the Front End?

hi dear,

Sorry for the formating issue, I guess it explains that I am new here. . .

Thanks for the response. If you see well, my query was created from another query. . and as a matter of fact most of my queries all from another query. Do you think that it is a problem. . . Do you recommend re-doing my queries from a table source rather then a query?

Cheers,
Shaheen
:)

hi dear,

Sorry for the formating issue, I guess it explains that I am new here. . .

Thanks for the response. If you see well, my query was created from another query. . and as a matter of fact most of my queries all from another query. Do you think that it is a problem. . . Do you recommend re-doing my queries from a table source rather then a query?

Cheers,
Shaheen
:)


You bring up an interesting point that I overlooked, and as a result I have a question for you. Did the query qrygenderbyage (as well as any subqueries it might refer to ) convert successfully? All of the code used by a query must be on the Server, or the SQL Server will not allow it to run. If any part of it is NOT there, we can evaluate it one subquery at a time, and see if it can be made to work there.

In advance, I recommend you familiarize yourself with the list of non convertible commands (like DISTINCTROW, and (in some cases) ORDER BY).

Hi dear. . . I think the only solution is to redo the my qruies in SQL. . Though it would take lots of time. . . I started to create one of those queries in SQL. . and bang I get a problem while saving my the View. . . . Here is my SQL Code:SELECT dbo.[Employment Information].roster#, dbo.[Employment Information].EmployeeID, dbo.[Personal Information].firstname & [ ] & dbo.[Personal Information].lastname AS [Employee Name], dbo.[Employment Information].dutystationID, dbo.[Employment Information].posttype, dbo.[Employment Information].section, dbo.[Employment Information].subsection, dbo.[Employment Information].unit, dbo.[Employment Information].posttitle, dbo.[Employment Information].budgetcategory, dbo.[Employment Information].adstation, dbo.[Employment Information].aregion, dbo.[Employment Information].acountry, dbo.[Employment Information].eMLA, dbo.[Employment Information].eAA, dbo.[Employment Information].eVLArate, dbo.[Employment Information].Dependents, dbo.[Employment Information].azonestatus, dbo.[Employment Information].EOD, dbo.[Employment Information].EOC, dbo.[Duty Station].dutystation, dbo.[Duty Station].region, dbo.[Duty Station].country, dbo.[Duty Station].hazardouszone, dbo.[Duty Station].MLA, dbo.[Duty Station].AA, ROUND((dbo.[Employment Information].EOC - dbo.[Employment Information].EOD) * 12 / 365 * 2.5, 0) - dbo.[Employment Information].Adjustment AS CAAL, dbo.[Employment Information].Ltaken2007, ROUND((dbo.[Employment Information].EOC - dbo.[Employment Information].EOD) * 12 / 365 * 2.5, 0) - dbo.[Employment Information].Adjustment AS ASL, dbo.[Employment Information].staken2007, dbo.[Employment Information].Adjustment, dbo.[Employment Information].altakentodate, dbo.[Employment Information].CALB, dbo.[Personal Information].firstname, dbo.[Personal Information].lastname, dbo.[Personal Information].Gender, dbo.[Personal Information].DOB, dbo.[Personal Information].titleofcourtesy, dbo.[Personal Information].maritalstatus, dbo.[Personal Information].passport#, dbo.[Personal Information].nationality, dbo.[Personal Information].issueplace, dbo.[Personal Information].issuedate, dbo.[Personal Information].expirydate, dbo.[Personal Information].caddress, dbo.[Personal Information].city, dbo.[Personal Information].homephone, dbo.[Personal Information].postalcode, dbo.[Personal Information].countryofresidence, dbo.[Personal Information].Status, dbo.[Personal Information].photo, dbo.[Employment Information].bf, dbo.[Employment Information].RemarkFROM dbo.[Personal Information] INNER JOIN dbo.[Duty Station] RIGHT OUTER JOIN dbo.[Employment Information] ON dbo.[Duty Station].dutystationid = dbo.[Employment Information].dutystationID ON dbo.[Personal Information].roster# = dbo.[Employment Information].roster#--------------------------------------------------------------------------Here is my the problem:===================================User canceled out of save dialog (MS Visual Database Tools)------------------------------Program Location: at Microsoft.VisualStudio.DataTools.Interop.IDTDocToo l.Save(Object dsRef, String path, Boolean okToOverwrite) at Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.ViewDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)--------------------------------------------------------------------------Your help would be highly appreciated. . . RgrdsShaheen

I am not sure what your problem means yet, but when I reformatted your your query I noticed that it could have problems with SQL Server for a number of reasons. You might want to change some things and try again. Maybe your errors are related to these suggestions.

SEE THE CODE BELOW FOR SUGGESTIONS

Suggestions:




Use aliases to make it easier to read (dbo.[Personal Information] becomes PI
Remove brackets and embedded spaces from field and table names ([Employee Name] becomes EmployeeName),




Replace # with Number or something similar (roster# becomes RosterNumber)

SELECT EI.roster#,
EI.EmployeeID,
PI.firstname & [] & PI.lastname AS [EmployeeName],
EI.dutystationID,
EI.posttype,
EI.section,
EI.subsection,
EI.unit,
EI.posttitle,
EI.budgetcategory,
EI.adstation,
EI.aregion,
EI.acountry,
EI.eMLA,
EI.eAA,
EI.eVLArate,
EI.Dependents,
EI.azonestatus,
EI.EOD,
EI.EOC,
DS.dutystation,
DS.region,
DS.country,
DS.hazardouszone,
DS.MLA,
DS.AA,
ROUND((EI.EOC - EI.EOD) * 12 / 365 * 2.5, 0) - EI.Adjustment AS CAAL,
EI.Ltaken2007,
ROUND((EI.EOC - EI.EOD) * 12 / 365 * 2.5, 0) - EI.Adjustment AS ASL,
EI.staken2007,
EI.Adjustment,
EI.altakentodate,
EI.CALB,
PI.firstname,
PI.lastname,
PI.Gender,
PI.DOB,
PI.titleofcourtesy,
PI.maritalstatus,
PI.passport#,
PI.nationality,
PI.issueplace,
PI.issuedate,
PI.expirydate,
PI.caddress,
PI.city,
PI.homephone,
PI.postalcode,
PI.countryofresidence,
PI.Status,
PI.photo,
EI.bf,
EI.Remark
FROM dbo.[PersonalInformation] As PI
INNER JOIN dbo.[DutyStation] As DS RIGHT
OUTER JOIN dbo.[EmploymentInformation]
ON DS.dutystationid = EI.dutystationID
ON PI.roster# = EI.roster


RED = Name Syntax NOT SUPPORTED by SQL Server
ORANGE = Syntax Not guaranteed to be supported in the future

One more thing I noticed:

SELECT EI.roster,
EI.EmployeeID,
PI.firstname & [] & PI.lastname AS [EmployeeName],

should be:


SELECT EI.roster,
EI.EmployeeID,
PI.firstname + ' ' + PI.lastname AS [EmployeeName],

Don't know if this will help but this is from Microsoft (should still apply to migration to 2005 as well):

Queries
The Upsizing Wizard does not upsize any of your queries (when using linked tables). They continue to use data through the linked tables without requiring any changes to the query. However, some complex queries such as crosstab data and queries with multiple table joins should be rewritten to minimize network traffic and improve system performance.


Have you tried using the SQL Server Migration Assistant (SSMA) for Access?
http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx