Download as PPS, PPTX
![Issue - Reserved WordsIssue - Reserved Words
Table Names, Column Names, Alias referencesTable Names, Column Names, Alias references
Wizard tolerates some, not others when creatingWizard tolerates some, not others when creating
tables and converting queriestables and converting queries
SQL Server tolerates some, not othersSQL Server tolerates some, not others
Fixed in SSMA for CREATE TABLE (ReservedFixed in SSMA for CREATE TABLE (Reserved
Word) Tablename!!!Word) Tablename!!!
Encapsulate references in SQL Statements with [ ]Encapsulate references in SQL Statements with [ ]
where possiblewhere possible
Otherwise Rename and Identify cascading effectOtherwise Rename and Identify cascading effect](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/85/Microsoft-SQL-Server-13-320.jpg)
![Issue - Memo Fields get ConvertedIssue - Memo Fields get Converted
to Ntextto Ntext
Restrictions apply in SQL StatementsRestrictions apply in SQL Statements
Cannot Sort, Compare, or use as part ofCannot Sort, Compare, or use as part of
DISTINCTDISTINCT
Use CONVERTUse CONVERT
Eg SELECT DISTINCT Convert(varchar,[fieldname])Eg SELECT DISTINCT Convert(varchar,[fieldname])
as [fieldname] FROM…as [fieldname] FROM…](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/85/Microsoft-SQL-Server-17-320.jpg)
![Issue - Numeric AliasIssue - Numeric Alias
Requires Encapsulation with [ ]Requires Encapsulation with [ ]
Eg SELECT REF AS [10] FROM…Eg SELECT REF AS [10] FROM…](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/85/Microsoft-SQL-Server-21-320.jpg)
![Issue – VAL FunctionIssue – VAL Function
Requires a UDFRequires a UDF
CREATE FUNCTION VAL(@P1 VARCHAR) ASCREATE FUNCTION VAL(@P1 VARCHAR) AS
BEGIN RETURNBEGIN RETURN
CONVERT(INT,LEFT(@P1,PATINDEX(('%[^0-CONVERT(INT,LEFT(@P1,PATINDEX(('%[^0-
9]%',@P1+' ')-1)) END9]%',@P1+' ')-1)) END](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/85/Microsoft-SQL-Server-33-320.jpg)
![Issue - Reserved WordsIssue - Reserved Words
Table Names, Column Names, Alias referencesTable Names, Column Names, Alias references
Wizard tolerates some, not others when creatingWizard tolerates some, not others when creating
tables and converting queriestables and converting queries
SQL Server tolerates some, not othersSQL Server tolerates some, not others
Fixed in SSMA for CREATE TABLE (ReservedFixed in SSMA for CREATE TABLE (Reserved
Word) Tablename!!!Word) Tablename!!!
Encapsulate references in SQL Statements with [ ]Encapsulate references in SQL Statements with [ ]
where possiblewhere possible
Otherwise Rename and Identify cascading effectOtherwise Rename and Identify cascading effect](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/75/Microsoft-SQL-Server-13-2048.jpg)
![Issue - Memo Fields get ConvertedIssue - Memo Fields get Converted
to Ntextto Ntext
Restrictions apply in SQL StatementsRestrictions apply in SQL Statements
Cannot Sort, Compare, or use as part ofCannot Sort, Compare, or use as part of
DISTINCTDISTINCT
Use CONVERTUse CONVERT
Eg SELECT DISTINCT Convert(varchar,[fieldname])Eg SELECT DISTINCT Convert(varchar,[fieldname])
as [fieldname] FROM…as [fieldname] FROM…](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/75/Microsoft-SQL-Server-17-2048.jpg)
![Issue - Numeric AliasIssue - Numeric Alias
Requires Encapsulation with [ ]Requires Encapsulation with [ ]
Eg SELECT REF AS [10] FROM…Eg SELECT REF AS [10] FROM…](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/75/Microsoft-SQL-Server-21-2048.jpg)
![Issue – VAL FunctionIssue – VAL Function
Requires a UDFRequires a UDF
CREATE FUNCTION VAL(@P1 VARCHAR) ASCREATE FUNCTION VAL(@P1 VARCHAR) AS
BEGIN RETURNBEGIN RETURN
CONVERT(INT,LEFT(@P1,PATINDEX(('%[^0-CONVERT(INT,LEFT(@P1,PATINDEX(('%[^0-
9]%',@P1+' ')-1)) END9]%',@P1+' ')-1)) END](https://image.slidesharecdn.com/microsoftsqlserver-090501211959-phpapp01/75/Microsoft-SQL-Server-33-2048.jpg)
Uploaded byVinayak Hegde
Microsoft (SQL Server)
This document discusses strategies for migrating an Access database to SQL Server. It covers the functionality of the SSMA and Upsizing Wizard tools and compares the ADP and linked table solutions. Key issues that may arise during the migration process are presented along with solutions, such as resolving data type incompatibilities, handling reserved words and wildcards, and converting Access functions to their SQL equivalents. The methodology recommends assessing complexity based on object count and addressing code in forms, reports, modules and queries after migrating the data and schema.
More Related Content
Similar to Microsoft (SQL Server)
Microsoft (SQL Server)
- 3. Migrating, Converting andUpsizingMigrating, Converting and Upsizing to SQL Server from Microsoftto SQL Server from Microsoft AccessAccess
- 4. AgendaAgenda SSMA and UpsizingWizard FunctionalitySSMA and Upsizing Wizard Functionality ComparisonComparison ADP vs Linked Table SolutionADP vs Linked Table Solution Step by Step – Issues and SolutionsStep by Step – Issues and Solutions Methodology and RecommendationsMethodology and Recommendations Q & AQ & A
- 5. SSMA for AccessSSMAfor Access Converts “Simple” SELECT queries onlyConverts “Simple” SELECT queries only Does not support ADP solutions…when ?Does not support ADP solutions…when ? Comprehensive Data/Schema AnalysisComprehensive Data/Schema Analysis Reports on issues to address before upsizingReports on issues to address before upsizing Multi Database upsizingMulti Database upsizing Reports on issues to address post upsizingReports on issues to address post upsizing Several bugs in Wizard fixed – which ones ?Several bugs in Wizard fixed – which ones ? Restricted to SQL Server 2005 and .NET 2.0Restricted to SQL Server 2005 and .NET 2.0
- 6. Access 2003 UpsizingWizardAccess 2003 Upsizing Wizard Supports both ADP and Linked Table SolutionSupports both ADP and Linked Table Solution No pre analysis reportingNo pre analysis reporting Comprehensive Documentation from MicrosoftComprehensive Documentation from Microsoft and Third Partiesand Third Parties ADP Solution creates framework only – noADP Solution creates framework only – no syntax or code fixes in Forms, Reports andsyntax or code fixes in Forms, Reports and ModulesModules
- 7. Access Data ProjectSolutionAccess Data Project Solution True Client ServerTrue Client Server End User TransparencyEnd User Transparency Same Complexity, Different RulesSame Complexity, Different Rules Most significant performance boostMost significant performance boost Gateway to Web Enabled SolutionsGateway to Web Enabled Solutions Leverages SQL Server the mostLeverages SQL Server the most
- 8. Linked Table SolutionLinkedTable Solution Not a Client Server EnvironmentNot a Client Server Environment Slower Performance (sometimes drastically)Slower Performance (sometimes drastically) Much Less VersatilityMuch Less Versatility Design changes may be required to increaseDesign changes may be required to increase performanceperformance Interface changes require end user retrainingInterface changes require end user retraining Not for complex Access DatabasesNot for complex Access Databases Less work, but more compromiseLess work, but more compromise Access 2007 ?Access 2007 ?
- 9. SQL 2000? SQL2005? Wizard ADP? Linked Tables? SSMA Code Modules, Forms, Reports Queries WizardADP? Linked Tables? Code Modules, Forms, Reports Queries Decisions, Decisions…Decisions, Decisions…
- 10. Complexity vs TimeComplexityvs Time Data and Schema is typically 10% of ADP projectData and Schema is typically 10% of ADP project Queries, Forms, Reports and Modules = 90%Queries, Forms, Reports and Modules = 90% 100 Objects or Less – Not complex100 Objects or Less – Not complex 200 Objects – Complex200 Objects – Complex 500 Objects – Very Complex500 Objects – Very Complex 1000 Objects – Extremely Complex1000 Objects – Extremely Complex Reduce by 50%+ with knowledge and strategyReduce by 50%+ with knowledge and strategy Use third party toolsetsUse third party toolsets
- 11. Step by Step– Issues and SolutionsStep by Step – Issues and Solutions Access Data Project Conversion IssuesAccess Data Project Conversion Issues Encompasses Linked Table SolutionEncompasses Linked Table Solution Undocumented Issues straight from the fieldUndocumented Issues straight from the field Expansion on Current DocumentationExpansion on Current Documentation Design Changes to make it all workDesign Changes to make it all work Code SamplesCode Samples Wiser and Smarter to the ChallengeWiser and Smarter to the Challenge Let’s get into itLet’s get into it
- 12. Issue - SQLServer does not knowIssue - SQL Server does not know who you arewho you are But Access did !!But Access did !! @@SPID to the rescue@@SPID to the rescue ADO and Form SPIDS are differentADO and Form SPIDS are different Create Table with SPID and Access UserID columnsCreate Table with SPID and Access UserID columns Initialise at LogonInitialise at Logon Delete previous Login Rows for SPIDDelete previous Login Rows for SPID SELECT COL1 FROM TABLE WHERE SPID =SELECT COL1 FROM TABLE WHERE SPID = @@SPID@@SPID
- 13. Issue - ReservedWordsIssue - Reserved Words Table Names, Column Names, Alias referencesTable Names, Column Names, Alias references Wizard tolerates some, not others when creatingWizard tolerates some, not others when creating tables and converting queriestables and converting queries SQL Server tolerates some, not othersSQL Server tolerates some, not others Fixed in SSMA for CREATE TABLE (ReservedFixed in SSMA for CREATE TABLE (Reserved Word) Tablename!!!Word) Tablename!!! Encapsulate references in SQL Statements with [ ]Encapsulate references in SQL Statements with [ ] where possiblewhere possible Otherwise Rename and Identify cascading effectOtherwise Rename and Identify cascading effect
- 14. Issue - TableRelationship RulesIssue - Table Relationship Rules Fields must be same lengthFields must be same length Wizard demands same field nameWizard demands same field name Part of SSMA Reporting !Part of SSMA Reporting ! Remove RelationshipRemove Relationship Remove IndexRemove Index Change Field Size to Highest LengthChange Field Size to Highest Length Reinstate IndexReinstate Index Reinstate RelationshipReinstate Relationship
- 15. Issue - DateFields can be CorruptIssue - Date Fields can be Corrupt or Invalid in Accessor Invalid in Access Wizard will not upsize tables with column datesWizard will not upsize tables with column dates less than 01/01/1900 or greater than 31/12/2078less than 01/01/1900 or greater than 31/12/2078 Fixed in SSMA !! For dates out of rangeFixed in SSMA !! For dates out of range Compounded if no Format PropertyCompounded if no Format Property Remove Rows with Dates out of RangeRemove Rows with Dates out of Range Or change to 31/12/2078, upsize and review laterOr change to 31/12/2078, upsize and review later
- 16. Issue - Tableswithout Unique IndexIssue - Tables without Unique Index will not Upsizewill not Upsize Simple SolutionSimple Solution Part of SSMA Reporting !!Part of SSMA Reporting !! Create AutoNumber Primary KeyCreate AutoNumber Primary Key Or add Primary or Unique Index to existing field ifOr add Primary or Unique Index to existing field if design is not compromised by doing sodesign is not compromised by doing so
- 17. Issue - MemoFields get ConvertedIssue - Memo Fields get Converted to Ntextto Ntext Restrictions apply in SQL StatementsRestrictions apply in SQL Statements Cannot Sort, Compare, or use as part ofCannot Sort, Compare, or use as part of DISTINCTDISTINCT Use CONVERTUse CONVERT Eg SELECT DISTINCT Convert(varchar,[fieldname])Eg SELECT DISTINCT Convert(varchar,[fieldname]) as [fieldname] FROM…as [fieldname] FROM…
- 18. Issue - AutoNumbersgenerate atIssue - AutoNumbers generate at different update statusdifferent update status Post Update with SQL ServerPost Update with SQL Server Pre Update with Microsoft AccessPre Update with Microsoft Access Use SELECT @@IDENTITY as …Use SELECT @@IDENTITY as …
- 19. Issue - RandomAutoNumbers onIssue - Random AutoNumbers on Primary KeyPrimary Key Wizard Generates Trigger and zero DefaultWizard Generates Trigger and zero Default Value ConstraintValue Constraint Primary Key ViolationPrimary Key Violation Not a common issueNot a common issue Delete the triggerDelete the trigger Remove 0 Default ValueRemove 0 Default Value Create Identity of Increment 1 and Seed 1Create Identity of Increment 1 and Seed 1
- 20. Issue -Parameter Querieswill NotIssue -Parameter Queries will Not UpsizeUpsize Requires Conversion to Parametised StoredRequires Conversion to Parametised Stored Proc or FunctionProc or Function Careful – Views will not recognize stored procsCareful – Views will not recognize stored procs
- 21. Issue - NumericAliasIssue - Numeric Alias Requires Encapsulation with [ ]Requires Encapsulation with [ ] Eg SELECT REF AS [10] FROM…Eg SELECT REF AS [10] FROM…
- 22. Issue - ConcatenationCharacterIssue - Concatenation Character Will only upsize from & to + in simple SQLWill only upsize from & to + in simple SQL StatementsStatements Concatenation of Numeric Fields effect spacingConcatenation of Numeric Fields effect spacing Use LTRIM(CONVERT(varchar(255),fieldname))Use LTRIM(CONVERT(varchar(255),fieldname))
- 23. Issue - DoubleQuote Literal StringIssue - Double Quote Literal String Will only upsize from double quote to single inWill only upsize from double quote to single in simple SQL Statementssimple SQL Statements Difficult to identify in VBA CodeDifficult to identify in VBA Code Replace line of code with double quotes to single andReplace line of code with double quotes to single and force compile errorforce compile error Manually fix until compilation is achievedManually fix until compilation is achieved
- 24. Issue - ORDERBYIssue - ORDER BY Requires Reference in Select ClauseRequires Reference in Select Clause SELECT COL1 FROM TABLE ORDER BY COL2SELECT COL1 FROM TABLE ORDER BY COL2 Converts toConverts to SELECT COL1,COL2 FROM TABLE ORDER BYSELECT COL1,COL2 FROM TABLE ORDER BY COL2COL2 Or refer to subquery containing ORDER BY to hideOr refer to subquery containing ORDER BY to hide COL2COL2
- 25. Issue - UPDATEQueriesIssue - UPDATE Queries Will not upsizeWill not upsize JOINS must occur after FROM ClauseJOINS must occur after FROM Clause UPDATE TABLE INNER JOIN…SET…UPDATE TABLE INNER JOIN…SET… Converts toConverts to UPDATE TABLE FROM TABLE INNER JOIN…SET..UPDATE TABLE FROM TABLE INNER JOIN…SET..
- 26. Issue – Viewswith ORDER BYIssue – Views with ORDER BY Will not upsizeWill not upsize Requires (Superfluous) TOP 100 PERCENTRequires (Superfluous) TOP 100 PERCENT STILL an ISSUE with SQL Server 2005STILL an ISSUE with SQL Server 2005 SELECT TOP 100 PERCENT * FROM… ORDER BYSELECT TOP 100 PERCENT * FROM… ORDER BY
- 27. Issue – UPDATEView instead ofIssue – UPDATE View instead of TableTable Will not function for Views with TOP OR ORDERWill not function for Views with TOP OR ORDER BYBY Create a base view without TOP and ORDER BYCreate a base view without TOP and ORDER BY UPDATE the base viewUPDATE the base view Create new view with TOP and ORDER BY referringCreate new view with TOP and ORDER BY referring to base viewto base view
- 28. Issue – PartialInserts with UniqueIssue – Partial Inserts with Unique ConstraintsConstraints Not Supported in SQL ServerNot Supported in SQL Server Transaction will not occur, ie all or nothingTransaction will not occur, ie all or nothing INSERT INTO TABLE1 (COL1) FROM SELECT COL2INSERT INTO TABLE1 (COL1) FROM SELECT COL2 FROM TABLE2FROM TABLE2 Append the following conditionAppend the following condition WHERE NOT EXISTS(SELECT COL1 FROMWHERE NOT EXISTS(SELECT COL1 FROM TABLE1 WHERE COL1 = COL2)TABLE1 WHERE COL1 = COL2)
- 29. Issue - WILDCARDSIssue- WILDCARDS Will only upsize for Simple SQL StatementsWill only upsize for Simple SQL Statements Requires Ansi89 conversion to Ansi92Requires Ansi89 conversion to Ansi92 Eg LIKE ‘*SMITH’ converts to LIKE ‘%SMITH’Eg LIKE ‘*SMITH’ converts to LIKE ‘%SMITH’ Eg LIKE ‘SMITH’ converts to LIKE ‘%SMITH%’Eg LIKE ‘SMITH’ converts to LIKE ‘%SMITH%’
- 30. Issue – Criteriareferring to DateIssue – Criteria referring to Date LiteralsLiterals Will not upsizeWill not upsize # Symbol requires conversion to single quote# Symbol requires conversion to single quote Month and Day Sequence can conflict withMonth and Day Sequence can conflict with Locale Settings of SQL ServerLocale Settings of SQL Server CLNG(datefield) in VBA requires formattingCLNG(datefield) in VBA requires formatting Use mmm syntax in SQL Statements -Use mmm syntax in SQL Statements - Use Format(xxx,’.. MMM..’) in VBA Code refsUse Format(xxx,’.. MMM..’) in VBA Code refs
- 31. Issue - BooleanReferencesIssue - Boolean References Will only upsize for simple SQL StatementsWill only upsize for simple SQL Statements TRUE, FALSE, YES, NO are not recognizedTRUE, FALSE, YES, NO are not recognized = -1 for True will compile but will malfunction= -1 for True will compile but will malfunction True or -1 converts to =1 or <> 0True or -1 converts to =1 or <> 0 False converts to =0 or <> 1False converts to =0 or <> 1 ADO converts bit fields back to True/False inADO converts bit fields back to True/False in RecordsetsRecordsets
- 32. Issue - AccessFunctions in SQLIssue - Access Functions in SQL Statements and Object propertiesStatements and Object properties Many are not supported – UDF requiredMany are not supported – UDF required Others Require syntax changeOthers Require syntax change In Particular…In Particular… VALVAL FORMATFORMAT TRANSFORMTRANSFORM FIRST & LASTFIRST & LAST
- 33. Issue – VALFunctionIssue – VAL Function Requires a UDFRequires a UDF CREATE FUNCTION VAL(@P1 VARCHAR) ASCREATE FUNCTION VAL(@P1 VARCHAR) AS BEGIN RETURNBEGIN RETURN CONVERT(INT,LEFT(@P1,PATINDEX(('%[^0-CONVERT(INT,LEFT(@P1,PATINDEX(('%[^0- 9]%',@P1+' ')-1)) END9]%',@P1+' ')-1)) END
- 34. Issue – FIRST,LAST andIssue – FIRST ,LAST and DLOOKUP FunctionsDLOOKUP Functions AutoNumber or DateTimeStamp dependancyAutoNumber or DateTimeStamp dependancy Solution Can effect performanceSolution Can effect performance SELECT LAST(COL1) FROM TABLENAMESELECT LAST(COL1) FROM TABLENAME Converts to..Converts to.. SELECT COL1 FROM TABLENAME WHERESELECT COL1 FROM TABLENAME WHERE AUTOID = (SELECT MAX(AUTOID) FROMAUTOID = (SELECT MAX(AUTOID) FROM TABLENAME)TABLENAME) Similar concept for DLOOKUP using COUNT insteadSimilar concept for DLOOKUP using COUNT instead of MIN/MAXof MIN/MAX
- 35. Issue – Propertiesthat can useIssue – Properties that can use Access FunctionsAccess Functions DefaultValueDefaultValue Rowsource when type is Value ListRowsource when type is Value List ControlSource PropertyControlSource Property VBA CodeVBA Code Case by case assessmentCase by case assessment
- 36. Issue - AliasReferencesIssue - Alias References Cannot be referred to elsewhere in SQLCannot be referred to elsewhere in SQL StatementStatement Requires reference to alias definition, not aliasRequires reference to alias definition, not alias SELECT 5 AS A, 10 AS B, CASE WHEN C = 1 THENSELECT 5 AS A, 10 AS B, CASE WHEN C = 1 THEN A ELSE B ENDA ELSE B END Converts toConverts to SELECT 5 AS A, 10 AS B, CASE WHEN C = 1 THENSELECT 5 AS A, 10 AS B, CASE WHEN C = 1 THEN 5 ELSE 10 END5 ELSE 10 END
- 37. Issue – Formand Report ControlsIssue – Form and Report Controls Forms!FormName!ControlName is notForms!FormName!ControlName is not recognized by SQL Serverrecognized by SQL Server Requires conversion to parametised stored procRequires conversion to parametised stored proc or functionor function Pass Forms!Formname!ControlName as parameterPass Forms!Formname!ControlName as parameter
- 38. Issue – RecordSourcerefers toIssue – RecordSource refers to Parameter QueryParameter Query Use InputParameters PropertyUse InputParameters Property Must be Stored Proc, not a User DefinedMust be Stored Proc, not a User Defined FunctionFunction Eg InputParameters = Forms!FormName!Eg InputParameters = Forms!FormName! ControlNameControlName
- 39. Issue – Rowsourcerefers toIssue – Rowsource refers to Parameter QueryParameter Query Must be Stored Proc, not a User DefinedMust be Stored Proc, not a User Defined FunctionFunction But where do the parameters go ?But where do the parameters go ? Use VBA Enter Event Procedure OverrideUse VBA Enter Event Procedure Override ControlName.Rowsource = “Exec ProcName “ &ControlName.Rowsource = “Exec ProcName “ & Forms!FormName!ControlNameForms!FormName!ControlName
- 40. Issue – VBAUser DefinedIssue – VBA User Defined FunctionsFunctions Not recognized by SQL ServerNot recognized by SQL Server Requires VBA UDF conversion to SQL ServerRequires VBA UDF conversion to SQL Server UDFUDF Generate SQL Server UDF Template(s) FirstGenerate SQL Server UDF Template(s) First Upsize QueriesUpsize Queries Develop Functional SQL Server UDFDevelop Functional SQL Server UDF
- 41. Issue – Encapsulatedreferences inIssue – Encapsulated references in WHERE clause that are not an AliasWHERE clause that are not an Alias and not a Columnand not a Column These are undefined input parameter fieldsThese are undefined input parameter fields Convert to Parametised Stored Proc or FunctionConvert to Parametised Stored Proc or Function Don’t forget if RecordSource or RowsourceDon’t forget if RecordSource or Rowsource
- 42. Issue - TheDAO ProgrammingIssue - The DAO Programming LibraryLibrary Is not Supported for Access Data ProjectsIs not Supported for Access Data Projects DAO must be converted to ADODAO must be converted to ADO Completely Documented by Microsoft and ThirdCompletely Documented by Microsoft and Third PartiesParties StraightForward, but not a global search and replaceStraightForward, but not a global search and replace solutionsolution Remove DAO reference and add ADO referenceRemove DAO reference and add ADO reference Fix until code compilesFix until code compiles Won’t Compile ? Won’t Run ? …Won’t Compile ? Won’t Run ? …
- 43. Issue – RecordSetbound FormsIssue – RecordSet bound Forms ADP does not support me.fieldnameADP does not support me.fieldname Compilation ErrorCompilation Error Requires reference to recordsetRequires reference to recordset Me.FieldnameMe.Fieldname Converts to…Converts to… Me.RecordSet(“FieldName”).ValueMe.RecordSet(“FieldName”).Value
- 44. Issue – RecordSetFindIssue – RecordSet Find FIND Property of RecordSet cannot refer to ANDFIND Property of RecordSet cannot refer to AND or ORor OR Requires MOVEFIRST if converted from DAORequires MOVEFIRST if converted from DAO FindFirstFindFirst Use the Filter property for multiple criteria FindsUse the Filter property for multiple criteria Finds Write a VBA Function to encompass both scenariosWrite a VBA Function to encompass both scenarios Sample Code ..Sample Code ..
- 45. Issue - DOCMDIssue- DOCMD Acquery constant no longer appliesAcquery constant no longer applies Requires Conversion to SQL Server EquivalentRequires Conversion to SQL Server Equivalent OpenQuery, SelectObject, DeleteObject,OpenQuery, SelectObject, DeleteObject, ApplyFilterApplyFilter eg OpenQuery converts to OpenServerVieweg OpenQuery converts to OpenServerView Acquery converts to acStoredProcedure,acView orAcquery converts to acStoredProcedure,acView or acFunctionacFunction Solution -Use Type and Name columns of SysObjectsSolution -Use Type and Name columns of SysObjects ApplyFilter requires conversion to ANSI92ApplyFilter requires conversion to ANSI92
- 46. Methodology andMethodology and RecommendationsRecommendations Don’tjump in cold when your application isDon’t jump in cold when your application is complexcomplex Know the traps and pitfallsKnow the traps and pitfalls Learn the tips and tricksLearn the tips and tricks Scan Scan Scan – write (DAO) scripts to find theScan Scan Scan – write (DAO) scripts to find the issuesissues Identify and Track the cascading effectIdentify and Track the cascading effect Management , Knowledge and strategy will ensureManagement , Knowledge and strategy will ensure successful migrationsuccessful migration
- 47. Information collected andSlidesInformation collected and Slides Prepared byPrepared by Vinayak NandikalVinayak Nandikal
Editor's Notes
- #2 &lt;number&gt; © 2006 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION. 03/31/15 01:44
- #3 A presentation at TechEd2006 Sydney in August 2006 by Adelaide Dot Net User Group member Steve Koop on Migrating, Converting and Upsizing to SQL Server from Microsoft Access. Notes have been added to further illustrate and provide reference links to the points outlined on the slides. This has been done in preference to voice-over.. The presentation provides details the many steps required to successfully migrate the Microsoft Access Application to SQL Server Result of Migration is Transparent to Users Users still use the Microsoft Access Application which to all appearances remains unchanged except for improvements (or detriments) in performance.
- #4 Introduction Stephen Koop is an accomplished software developer, with more than 20 years’ experience in developing database conversion technologies that are in use all over the world. In 2004, he played a key role in the successful engagement of commercial arrangements between Microsoft Corporation (USA) and ConverterTechnology (wholly owned subsidiary of Powerlan Ltd), having conceived, designed and written Office Converter, the only automated software utility that converts and repairs Visual Basic Applications written in Microsoft Office products. An IT Specialist with significant startup and merger experience, Stephen formed a partnership with the directors of Tapestry Systems in 2005 to commercialize his new leading edge data conversion tool for the Microsoft SQL Server market and beyond, thereby satisfying worldwide demand for products that can assist companies to migrate from legacy technologies to accommodate the dramatic improvements in flexibility and scalability offered by new technology platforms such as .NET.
- #5 SQL Server Migration Assistant for Access (SSMA Access) http://www.microsoft.com/sql/solutions/migration/access/default.mspx Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005. SSMA for Access converts Access Table schema, indexes and relationships to SQL Server Tables, and then migrates the table data from Access to SQL Server. ADP (Microsoft Access Project) A Microsoft Access project (.adp) is an Access data file that provides efficient, native-mode access to a Microsoft SQL Server database through the **OLE DB component architecture **(OLE DB: A component database architecture that provides efficient network and Internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets.)
- #6 SQL Server Migration Assistant for Access (SSMA) http://www.microsoft.com/sql/solutions/migration/access/default.mspx SSMA for Access supports migrating Access 97 - Access 2003 databases to Microsoft SQL Server 2005 only . The new and improved SSMA still only does 10% of the migration (does tables only which are linked tables back to Access), some bugs have been fixed. The October 2006 release of SSMA for Access includes conversion of SELECT queries only. All other types of queries, eg UPDATE INSERT DELETE etc, are outside the current scope of SSMA, and there are more functionality limitations than the upsizing wizard, in this particular instance. Data must be pre-validated before SSMA as there is no validation of converted data. eg null values in unique index, dates. Invalid data will generate ugly error messages. There is no sense in uploading junk. See later slides on query conversion SSMA migration uses linked SQL tables and takes a very bad performance hit. .. Wizard can crash when getting reports on issues to address before upsizing. Multi database upsizing only available in SSMA NB SSMA will only work for SQL Server 2005 and is dependant on the Net 2.0 framework..
- #7 Access 2003 Upsizing Wizard The Upsizing Wizard is in Database Utilities in the Tools Menu, still with some bugs and omissions. The Upsizing Wizard does not attempt to convert any code in modules or macros to SQL Server. It is strongly recommend that after your database is successfully upsized that you analyze your code and implement optimizations and new database access techniques to get the most performance from SQL Server. Microsoft Access Projects option (preferred option) -Runs 10 times faster than Linked Tables as only query results are sent back to Access. Linked tables option (requires ODBC (old technology)) In an application that uses linked SQL Server tables, two different database engines are at work: the Office Access/Jet database engine that runs on the Office Access client and the SQL Server database engine. The interaction of these two engines can sometimes yield results that are inferior to those obtained by using only the Jet database engine with native Office Access tables. The linked white paper discusses several of these issues and presents strategies for resolving them. Most of these issues relate to performance or updatability. http://msdn2.microsoft.com/en-us/library/bb188204.aspx – another of the bottlenecks of using linked tables is waiting for all the translation to happen. Because of this and for the following reasons, you want to bypass the translation process: - Access SQL might not support some operation that native query language of the back end supports. - Both the Jet Engine and the ODBC driver can produce SQL statement not optimized for the back end. - See also pass-through queries (typically read-only)
- #8 Microsoft Access Project (ADP) Solution A Microsoft Access project (.adp) is an Access data file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB ** ** (OLE DB: A component database architecture that provides efficient network and Internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets.) component architecture -ADP is the preferred solution - Gives 10 fold performance improvement! What are the advantages of using Access as the front end to a SQL Server database? - For starters, it&apos;s likely that your client&apos;s users will be more familiar with it, and it&apos;s relatively easy to put in place. Ideally, no user retraining. See article at http://articles.techrepublic.com.com/5100-6329_11-5065669.html Gateway to web enabled solutions, some examples - http://www.programmingmsaccess.com/ http://www.databasejournal.com/features/msaccess/article.php/3567511 ADP leverages the abilities of SQL Server the most
- #9 For each table in your Access database, the Upsizing Wizard creates a table in the SQL Server database and attempts to copy the schema, relationships, and indexes. The Upsizing Wizard does not upsize any 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. refer http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx User retraining may be required where: Design changes done to increase performance Design changes done for things that don’t work Difficult to convert an access form to web based form
- #10 To migrate an Access Database to SQL Server requires a lot of planning. To migrate to SQL 2000, the Upsizing Wizard will convert the tables to either an ADP or Linked Tables Solution. To migrate to SQL 2005, use the Wizard to migrate the tables for an ADP Solution. Use SSMA for a Linked Table solution. In all cases, the majority of tables will be upsized. Those that do not will need to be tweaked until they do. The wizard will attempt to upsize the queries when the ADP option is chosen, but only the simple less complex queries will get upsized. There are specific issues with field conversions which are discussed in later slides. All other objects (forms, queries, reports, VBA code) have to be done manually. Step One - Clean the Access Data Clean the data. Repair and compact the data. There is no sense in uploading junk. It pays to treat your SQL data well, with the sort of care and consideration as accountants give to financial logs and transactions. Access makes it easy to treat data informally, sloppily, lazily. Use the conversion as an opportunity to get your data into &quot;a good place.&quot; Clean out bad data. Remove test data. Replace Memo fields with long String fields where possible. SQL Server requires that table and field names be 30 characters or less, and begin with a letter.
- #11 In planning the migration, use the following to help scope the project. It is imperative that the Access database is checked for incompatibilities that may arise during upsizing. Migrating Data and Schema is typically 10% of ADP project Migrating Queries, Forms, Reports and Modules = 90% If each query, VBA code item, form and report requiring conversion is regarded as an object then:: -100 Objects or Less – Not complex Possibly use linked tables -200 Objects – Complex -500 Objects – Very Complex -1000 Objects – Extremely Complex By identifying the number and type of objects requiring attention -Reduce by 50%+ with knowledge and strategy Plan, prepare, anticipate Use third party toolsets eg SSW, convert2sql
- #12 The Upsizing Wizard report contains information about the following: Database details, including database size, as well as transaction logs and device names and sizes for a SQL Server database. Upsizing parameters, including what table attributes you chose to upsize and how you upsized. Table information, including a comparison of Access and SQL Server values for names, data types, indexes, validation rules, defaults, triggers, and whether or not time stamps were added. (Sic) Any errors encountered, such as database or transaction log full, inadequate permissions, device or database not created, table, default, or validation rule skipped, relationship not enforced, query skipped (because it cannot be translated to SQL Server syntax), and control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) and record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) conversion errors in forms and reports. For complete article see http://office.microsoft.com/en-us/access/HP052741431033.aspx
- #13 SQL Server needs extra table and function added to identify user Sorts out things like Forms! Me! That can only be referred to in views in SQL Server More suggestions at http://msdn.microsoft.com/msdnmag/issues/0800/sql2000/ (search on spid)
- #14 Reserved Words It is not documented in Wizard where Names do or don’t work. Names such as Table Names, Column Names, Alias references Encapsulate references in SQL Statements with [ ] where possible If any Access tables, queries, or fields contain SQL Server reserved words. Some reserved keywords will not fail, but it is recommended that they be changed to avoid future issues. The following reserved keywords will fail in the Upsizing Wizard: •AUTHORIZATION•CASE•CHECK•COLLATE•COLUMN•CONTAINS•CONTAINSTABLE•ESCAPE•FETCH•FILE•FREETEXT•FREETEXTTABLE•FULL•IDENTITYCOL•INNER•JOIN•KEY•LEFT•NATIONAL•OPENDATASOURCE•OPENQUERY•OPENROWSET•OUTER•RESTRICT•RIGHT•ROWGUIDCOL•SCHEMA•WHEN A complete list of SQL Server reserved keywords is available at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp.
- #15 Some more pre-processing to do. Related Fields in tables must be same length In earlier releases Wizard demands same field name ----- fixed in 2003 Change the linked field lengths and types to be the same in a table relationship.. Make sure to increase the field size of the smaller field, rather than decrease the field size of the larger field, to avoid data loss. Things to do before running SSMA or Wizard where field lengths differ Remove Relationships Remove Indexes Change Field Size to Highest Length Reinstate Index Reinstate Relationship
- #16 Some more pre-processing Write queries in Access that will go through and verify that all dates are within the required date range (01/01/1900 to 31/12/2078) Run a query on every field that has a Date/Time value to check for invalid dates, nulls, etc.. Use format statements to assist in visual review of table data, ie some date fields might be time based only, therefore no attention needed.. .
- #17 Pre-process by checking all tables in Access to ensure they have a unique index column, if not then designate a column that can be a unique index if design is not compromised by this action or add such a column such as an Autonumber primary or Unique Index key The Upsizing Wizard can upsize an existing unique index, but can&apos;t create one where none exists. If you want to be able to update your tables, make sure you add a unique index to each Access table before upsizing.
- #18 The Upsizing Wizard report contains information about the following: Table information, including a comparison of Access and SQL Server values for names, data types, indexes, validation rules, defaults, triggers, and whether or not time stamps were added. For SQL Server version 7.0 or later databases, the Upsizing Wizard converts Access database text to Unicode by adding the Unicode string identifier to all string values and by adding the Unicode n prefix to all data types. All Access database data types are converted to their equivalent in SQL Server. Memo fields are converted to text or ntext (unicode). Field default values are converted during upsizing. Validation text for fields is not converted. Instead, if you attempt to enter invalid text in a field with a validation rule, the insertion or update will fail with a SQL Server error message. The results of upsizing should be checked for continuity of desired functionality
- #19 If you have been working with other databases, the methods for getting SQL identity values may not seem straightforward. In some databases, your auto number field is assigned at the beginning of the insert. . With SQL, the new identity is not known until after the insert completes. This leads to problems when you need that new identity to continue with a different transaction, or return the value to your application for future use. Access provides you with Autonumber prior to update. The function SCOPE_IDENTITY returns the new identity created on any table in this session in this scope, giving us the new value we would expect. @@IDENTITY This global variable is usually the first one suggested in books and news groups for discovering new identities. However, its real purpose is not at first evident and may produce the wrong identity. @@ IDENTITY will return the last identity used in your session, but across all scopes. Meaning if your statement causes a trigger to fire, and that trigger insert into a table with an identity field, the triggers new identity will be returned, not your statements new identity. Using SQL identity fields provides a quick and effective way of generating unique values. If the correct method is used for retrieving new identities, your application will be assured in not getting the Wrong Identity.
- #20 Wizard doesn’t cope with this at all Eli Robillard points out: Random Autonumbers are recommended for Access keys. This makes it harder for a collision to happen when two or more users add new records at the same time. With Incrementals, two users can get the same &quot;next available ID number&quot; and one gets an error (colllision) when the record is saved. Random Autonumbers are also required for Database Replication. In fact making a database replicable automatically converts Incremental Autonumbers to Random. But Randoms present a problem when converting to SQL Server, because when the field is converted to an incremental IDENTITY, the &quot;next available number&quot; might be very high, close to the upper bound of integers, effectively limiting the size of the table. That won&apos;t do. The solution is to convert Random Autonumbers to Incremental Autonumbers.. For the complete article http://www.erobillard.com/LoadPage.aspx?pg=/articles/database/ConvertingAccessToSqlServer.xml
- #21 If a query in Access requires a parameter value that has not been supplied through the process, Access will prompt the operator for a value. There is no such facility in SQL Server. Manual intervention required to add code required to achieve (or avoid) this.
- #22 Pre-process by checking all queries in Access to identify use of numeric alias. Encapsulate occurrences of numeric aliases with [].before migration. These occurrences should be recorded in pre-process documentation Check after migration that correct upsizing has occurred.
- #23 Pre-process by checking and listing locations of all & in Access in pre-process documentation. . & in simple SQL statements will be changed by the wizard to +. Others will require to be changed maually. They will all need to be checked.manually after migration.
- #24 Avoid migration problems by changing all double quotes to single quotes before attempting Migration..
- #25 Access allows naming of fields that aren’t listed in Select statement in the Order By Clause . SELECT fieldlistFROM tableWHERE selectcriteria[ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][, ...]]] SQL Server requires all fields to be listed in Select statement that are listed in the Order By Clause SELECT fieldlist FROM table [ WHERE criteria ] [ ORDER BY field1 [ ASC | DESC ] [ , field2 [ ASC | DESC ] ] [, ...] ]
- #26 Requires manual intervention to Upsize. May require recast of queries to satisfy SQL Server. UPDATE { table_name WITH ( &lt; table_hint_limited &gt; [ ...n ] ) | view_name | rowset_function_limited } SET { column_name = { expression | DEFAULT | NULL } | @variable = expression | @variable = column = expression } [ ,...n ] { { [ FROM { &lt; table_source &gt; } [ ,...n ] ] [ WHERE &lt; search_condition &gt; ] } | [ WHERE CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } [ OPTION ( &lt; query_hint &gt; [ ,...n ] ) ]
- #27 Requires manual intervention to modify query by adding “superfluous” code to make it work in SQL Server.
- #28 Queries need extra code and possible split into separate queries for successful upsizing
- #29 Microsoft Access supports this scenario. Queries need reworking and additional code to work in SQL Server.
- #30 Once again, all the Migration Upsizing conversions need to be checked.
- #31 Include these checks for dates in the pre-processing procedures.
- #32 More pre-processing checks to do. Change references so that they will Upsize correctly. Check after Migration that Upsizing done correctly.
- #33 User Defined Functions need redefining for SQL Server
- #34 Rob Farley MVP, Group Leader Adelaide SQL Server User Group posted an excellent blog on converting the VAL.to SQL http://robfarley.blogspot.com/2006/09/access-val-function.html
- #35 Identify these functions in the pre-process. Discuss modification of process with client to enable definition of suitable SQL process as there is no direct equivalent. Similarly Dlookup not supported in SQL
- #36 These properties need to be evaluated on a case by case basis
- #37 Identify occurrences in pre-process. Access allows use of alias definition. SQL Server doesn’t
- #38 Additional code required for this to work in SQL Server
- #39 See Recordsource and Rowsource (next slide)
- #40 Additional code required for this to work in SQL Server
- #41 These User Defined Functions can be used extensively in Access SQL Statements Additional functions and code required 2sql has templates
- #42 If in Access, need to be redefined and coded for SQL Server
- #43 OLE DB After moving the Access databases to SQL Server 2000, many developers use ODBC data source names (DSN) to maintain the connection between the SQL Server 2000 back end and an Access front-end application installed on the users&apos; computers. This option is a simple way to begin the conversion process to full SQL Server 2000 applications. This setup results in minimal impact on existing internal Access code modules. You may leave this setup in place for months while developers become familiar with the new setup. Other developers move directly to OLE DB because it supports more connections. Moving to OLE DB can require major code modification to reconfigure connection strings within the application. You can anticipate some application rewrite as you move from Data Access Objects (DAO) to ActiveX® Data Objects (ADO). Why does this approach require more work? The DAO language used for Microsoft Visual Basic® for Applications (VBA) modules in older Access applications is optimized for use with the Jet engine. Should you rewrite applications to communicate with SQL Server 2000 through ADO and OLE DB? For large multiuser applications, the answer is yes. Your investment in time and resources is well rewarded with improved performance. You will send less data by using stored procedures to process data on SQL Server 2000. If you make the investment, you could consider using ADO.NET and build an ASP.NET or Microsoft Visual Studio® .NET solution to replace the Access interface. For more information about migrating from Access to .NET, see http://www.ssw.com.au/ssw/standards/DeveloperDotNet/MSUS_02_How_To_Migrate_Access_Forms_To_Dot_Net_Whitepaper_ver15.doc An ADP will not support DAO at all and most of the ADO library will work. There is always a work around when it does not. http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/managesql.mspx#EMC
- #44 Recoding required for SQL Server
- #45 Recoding required for SQL Server
- #46 Recoding required for SQL Server
- #47 Several issues should be considered before you begin migrating your database: •Confirm the integrity of the original database. The best way to protect your database is to create a complete backup and safely store it away from the computers involved in migrating. Consider this step as the single most critical step of the entire operation. •Ensure that you have adequate permissions on both the Access database and the instance of SQL Server to which you are migrating. You will need at least Read Design permission on all objects that will be migrated and on all objects that act as data source for migrated objects. For example, to properly migrate a query you must have Read Design permissions on the query itself as well as the table or tables that the query uses as the data source. •If you are upsizing an MDE file, make sure that you have access to a copy of the original Access database. In an MDE file all source code for Microsoft Visual Basic for Applications is removed and replaced with a compiled, binary format. This creates a more efficient database but also prevents access to the design of forms, reports, and modules, making migration of these objects impossible. Tables, queries, macros, and data access pages are not affected and can be migrated. •You will need a password to any Visual Basic for Applications projects within your database.
- #48 2SQL has been designed to automate the process so that the manual effort is reduced to 10% of the project rather than the current 90%.via the Wizard or SSMA. The major benefit is the significant reduction in time required to complete the project. Where in the past, a Microsoft Access project of any complexity would take months to convert, it is now possible to do this in a matter of weeks reducing the overall time and cost by a typical 75%. Contact Peter Griffith &lt;peter.griffith@bettersoftware.com.au&gt; for a free copy of 2SQL to scan your Microsoft Access Project. Typically, an end user would use 2SQL to scan a database to capture and log all upsizing issues. This will enable the project to be scoped and realistic times and costs estimated. Mention your User Group for extra service and special deals.