1 / 17

<Insert Picture Here>

<Insert Picture Here>. SQL Server Migration Hints and Tips. Manual conversion required. Complex triggers References to data dictionary tables (systables,…) Recent extensions to T-SQL are usually not supported (e.g. XML extensions). Triggers.

saber
Download Presentation

<Insert Picture Here>

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. <Insert Picture Here> SQL Server MigrationHints and Tips

  2. Manual conversion required • Complex triggers • References to data dictionary tables (systables,…) • Recent extensions to T-SQL are usually not supported (e.g. XML extensions)

  3. Triggers • SQL Server triggers are statement level but give you access to change rows via the inserted and deleted temp tables. In Oracle exist statement / row level triggers • Because SS Triggers are not part of a transaction it is common to do a rollback in the trigger, will be replaced by a RAISE_APPLICATION_ERROR by OMWB which will cause Oracle to roll the transaction back. The trigger will be terminated at this point rather than continuing as in TSQL.

  4. Error handling • IN TSQL errors can be trapped by the programmer via the @@ERROR variable, do not necessarily stop the execution of the stored module. • In PL/SQL all errors that occur will stop the execution of the stored module UNLESS they are trapped via a BEGIN...EXCEPTION...END block. • In SQL Server RAISERROR does not stop the execution of a stored module. The Oracle equivalent RAISE_APPLICATION_ERROR does.

  5. Error Handling • Raiserror (num) • Manual conversion will be required • Num gives ID in SQL Server SYSCOMMENTS table • This raises an error based on the SYSCOMMENTS table • Replaced by RAISE_APPLICATION_ERROR • Needs re-coding to get error message from a table

  6. Temporary Tables • TSQL has 2 types of Temporary Table, ones that you can create/drop using DDL and ones that are created on the fly using the #TABLE syntax. • Oracle only has 1 type of temporary table, with a global definition (i.e. it is created outside of any stored procedures and is not normally dropped), with temporary data. • Any DDL referencing temporary tables is removed by the workbench. • The CREATE TABLE statements are used to build global temporary tables which are then used by the PL/SQL modules.

  7. Common Workbench Warnings • SET • ROWCOUNT – Recoding needed to implement this • NOCOUNT – Not needed and ignored • DATEFORMAT - Recoding needed if required • Transactions • BEGIN TRANS Statement Ignored • SET TRANSACTION Statement Ignored • Transcount functionality is simulated by Workbench so COMMIT only happens if Transcount = 1

  8. Common Workbench Warnings • Global Variables • @@FETCH_STATUS – Simulated by workbench • @@SQLSTATUS – Simulated by workbench • @@ROWCOUNT – Simulated by workbench • @@ERROR – Simulated by workbench • @@VERSION – Not supported by Oracle • @@SPID – Simulated by workbench as UID • @@IDENTITY – Simulated by workbench • @@SERVERNAME – Not supported by Oracle – Could use V$INSTANCE • @@TRANSTATE – Not supported by Oracle • @@ - Any variable will be treated as a local variable by the workbench – could use package variable.

  9. Common Workbench Warnings • DDL • CREATE TABLE STRING Statement Generated – If temporary converted to GLOBAL Temporary Table • ALTER TABLE STRING • CREATE INDEX STRING Statement Ignored • CREATE VIEW STRING Statement Ignored • DROP PROCEDURE STRING Statement Ignored • DROP TABLE STRING Statement Ignored • DROP VIEW STRING Statement Ignored • GRANT Statement Ignored

  10. Common Workbench Warnings • Cursor Attributes • SCROLL – Not yet supported in Oracle PL/SQL • INSENSITIVE – Not required • FAST_FORWARD – Not required • LOCAL – Not Required • READ_ONLY – Not Required

  11. Common Issues • COLLATE • The workbench does not understand the COLLATE keyword. Each occurrence of the COLLATE keyword must be identified and removed. Once the SP’s have been converted any manual implementation of the COLLATE keyword must be done.

  12. Common Issues • Dynamic Cursors • The workbench cannot handle dynamically defined cursors only static ones • Selecting from user defined functions • The workbench cannot currently handle selecting from user defined functions

  13. Common Issues • Use of XML operators FOR XML and OPENXML • The workbench partially supports this construct by commenting out the FOR XML keywords. • SQL Server XML handling needs manually converting to use Oracle’s XDK/XDB functionality.

  14. Common Issues • Case Insensitivity • SQL Server can be set up so all queries are case insensitive. Oracle 10g includes a number of Linguistic sorts that allow this functionality for individual columns/queries. • < > = queries are easily indexed • REGEXP_LIKE can be used in 10g for CI Like functionality but indexing is difficult. • Oracle TEXT index can be used if extensive indexing is required.

  15. Emulated Functions • Migration workbench provides a set up functions to emulate SQL Server equivalents • ATN DATE_ LCASE SGN • CDATE DAY_ LEFT SHELL • CDBL DBNAME LEN SPACE_ • CEILING DDL_MANAGER MID SQR • CHAR_LENGTH DEBUG_TABLE MONTH_ STR • CSTR FIX NOW STRING • DATEADD GETDATE OBJECT_NAME SUBSTRING • DATEDIFF INSTR_ REPLICATE TIME_ • DATESERIAL INT_ REVERSE_ UCASE • DATETOSTR ISNULL RIGHT VAL • YEAR_

  16. Q & Q U E S T I O N S A N S W E R S A

More Related