Sybase Offline Capture Scripts on Windows
**Update: This is now fixed in SQL Developer 1.5.1**
Sybase Offline Capture Scripts on Windows are incorrect. Some files needs to replaced for them to work, the text of which can be found below.
You should then be able to perform an Offline Capture successfully.
SYBASE 15
SYB15_OFFLINE_CAPTURE.BAT username password databasename servername
For Example
SYBASE 12
SYB12_OFFLINE_CAPTURE.BAT username password databasename servername
For Example
Some Tips
SYBASE 15 (ASE15)
Replace the following files
SYB15_OFFLINE_CAPTURE.BAT
SYB15_BCP_SCRIPT.BAT
For SYBASE 12
Only 1 SYB12_BCP_SCRIPT.BAT needs to be updated with the following
SYB12_BCP_SCRIPT.BAT
Sybase Offline Capture Scripts on Windows are incorrect. Some files needs to replaced for them to work, the text of which can be found below.
You should then be able to perform an Offline Capture successfully.
SYBASE 15
SYB15_OFFLINE_CAPTURE.BAT username password databasename servername
For Example
SYB15_OFFLINE_CAPTURE.BAT sa "" pubs2 the4400.ie.oracle.com
SYBASE 12
SYB12_OFFLINE_CAPTURE.BAT username password databasename servername
For Example
SYB12_OFFLINE_CAPTURE.BAT sa "" pubs2 the4401.ie.oracle.com
Some Tips
- "" represents an empty string for no password.
- Sybase BCP, ISQL should be first on your PATH if you have SQL Server installed as well.
- Example: SET PATH=D:\SybaseClient\OCS-15_0\bin;%PATH%
- The Servername must be defined in your sql.ini file for BCP to work.
SYBASE 15 (ASE15)
Replace the following files
- SYB15_OFFLINE_CAPTURE.BAT
- SYB15_BCP_SCRIPT.BAT
SYB15_OFFLINE_CAPTURE.BAT
@echo off
rem ** SET THE VALUE FOR THE OFFLINE_CAPTURE_COLUMN_DELIMITER
rem ** echoed through bcp to COLUMN.TXT
set OFFLINE_CAPTURE_COLUMN_DELIMITER="<EOC>"
rem ** SET THE VALUE FOR THE OFFLINE_CAPTURE_ROW_DELIMITER
rem ** echoed through bcp to ROW.TXT
set OFFLINE_CAPTURE_ROW_DELIMITER="<EOR>"
rem ** SET THE SCRIPT VERSION ENVIRONMENT VARIABLE
set OMWB_SCRIPT_VERSION=15
rem ** SET THE VALUE FOR THE OMWB FILE ENVIRONMENT VARIABLE
set OMWB_SCRIPT_FILE=%3\%3_INFO.TXT
rem ** DISPLAY THE HELP PAGE IF THE USER REQUESTS IT
if "%1"=="-h" goto help
if "%1"=="help" goto help
if "%1"=="?" goto help
if "%1"=="-?" goto help
rem *** DISPLAY THE SCRIPT VERSION IF THE USER REQUESTS IT
if "%1"=="-ver" goto version
if "%1"=="version" goto version
rem *** CHECK THAT THREE PARAMETERS HAVE BEEN ENTERED
rem *** THE PASSWORD CAN BE "", SO WE DON't DO THE SAME CHECK FOR THAT
if "%1"=="" goto input_error
if "%3"=="" goto input_error
if "%4"=="" goto input_error
rem *** START THE EXECUTION OF THE SCRIPT INSTRUCTIONS
goto start
:start
rem ** CREATE THE OUTPUT DIRECTORIES
mkdir master
mkdir %3
rem *** CALL THE BCP SCRIPT TO CREATE THE METADATA FILES
call SYB15_BCP_SCRIPT.BAT %1 %2 %3 %4 %OFFLINE_CAPTURE_COLUMN_DELIMITER% %OFFLINE_CAPTURE_ROW_DELIMITER%
rem *** CHECK THAT ALL OF THE OUTPUT FILES HAVE BEEN CREATED
goto checkoutput
:help
echo -----------------------------------------------------------------------
echo --------------------- Oracle Migration Workbench ---------------------
echo --------------------- Release %OMWB_SCRIPT_VERSION% ---------------------
echo -----------------------------------------------------------------------
echo ---- This script will generate delimited flat files containing ----
echo ---- schema metadata from the database you wish to migrate. This ----
echo ---- script will envoke the Bulk Copy Program (BCP) that should be ----
echo ---- part of your Sybase install base. ----
echo ---- Please ensure that your path points to the version of BCP ----
echo ---- that is installed with the Sybase from which you wish ----
echo ---- to migrate. Your current path setting is listed below: ----
echo ----
PATH
echo ----
echo ----
echo ---- To run this script, enter the following command at the prompt ----
echo ----
echo ---- OMWB_OFFLINE_CAPTURE login_id password database_name server_name
echo ---- where,
echo ---- dba_login_id is a login id which has been granted db_datareader
echo ---- and view definition on database_name
echo ---- password is the password for the login id
echo ---- database_name is the name of the database you wish to capture
echo ---- server_name is the name of the server on which the database resides
echo ---- For example,
echo ---- OMWB_OFFLINE_CAPTURE sa sapwd employeeDB DEPT1_SERVER
echo ----
echo -----------------------------------------------------------------------
goto exit
:input_error
echo ---- ** Error executing the script
echo ----
echo ---- To run this script, enter the following command at the prompt ----
echo ----
echo ---- OMWB_OFFLINE_CAPTURE login_id password database_name server_name
echo ---- where,
echo ---- login_id is a login id which has been granted db_datareader
echo ---- and view definition on database_name
echo ---- password is the password for the login id
echo ---- database_name is the name of the database you wish to capture
echo ---- server_name is the name of the server on which the database resides
echo ---- For example,
echo ---- OMWB_OFFLINE_CAPTURE sa sapwd employeeDB DEPT1_SERVER
echo ----
echo -----------------------------------------------------------------------
goto exit
:version
echo -----------------------------------------------------------------------
echo ---- This is the Oracle Migration Workbench offline capture script
echo ---- version %OMWB_SCRIPT_VERSION% for Sybase 15
echo -----------------------------------------------------------------------
goto exit
:omwbfile
echo OMWB REPORT FOR %3 > %OMWB_SCRIPT_FILE%
echo ____________________________________________ >> %OMWB_SCRIPT_FILE%
echo * SCRIPT EXECUTION DATE AND TIME: >> %OMWB_SCRIPT_FILE%
date /t >> %OMWB_SCRIPT_FILE%
time /t >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo SYSTEM PROPERTIES >> %OMWB_SCRIPT_FILE%
echo _______________________ >> %OMWB_SCRIPT_FILE%
echo * PLATFORM VERSION: >> %OMWB_SCRIPT_FILE%
ver >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo * PLATFORM CODEPAGE: >> %OMWB_SCRIPT_FILE%
mode con codepage /sta >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo SOURCE DATABASE SERVER PROPERTIES >> %OMWB_SCRIPT_FILE%
echo __________________________________ >> %OMWB_SCRIPT_FILE%
echo * BCP VERSION: >> %OMWB_SCRIPT_FILE%
bcp -v >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo * DATABSE SERVER COLLATION AND VERSION: >> %OMWB_SCRIPT_FILE%
isql -U%1 -P%2 -S%4 -i%3 -i properties.sql >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo DIRECTORY LISTING for %3 >> %OMWB_SCRIPT_FILE%
echo __________________________________ >> %OMWB_SCRIPT_FILE%
dir %3 >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo DIRECTORY LISTING FOR MASTER >> %OMWB_SCRIPT_FILE%
echo _______________________________ >> %OMWB_SCRIPT_FILE%
dir master >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo ATTRIBUTES OF SCRIPT: >> %OMWB_SCRIPT_FILE%
echo _______________________ >> %OMWB_SCRIPT_FILE%
attrib SYB15_OFFLINE_CAPTURE.bat >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo CONTENTS OF EXECUTED SCRIPT >> %OMWB_SCRIPT_FILE%
echo ____________________________________ >> %OMWB_SCRIPT_FILE%
type SYB15_OFFLINE_CAPTURE.BAT >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo CONTENTS OF BCP SCRIPT >> %OMWB_SCRIPT_FILE%
echo ____________________________________ >> %OMWB_SCRIPT_FILE%
type SYB15_BCP_SCRIPT.BAT >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo - >> %OMWB_SCRIPT_FILE%
echo ** END REPORT FOR %3 >> %OMWB_SCRIPT_FILE%
rem *** DISPLAY THE FINAL INSTRUCTIONS TO THE USER
goto finalinstructions
:checkoutput
rem *** CHECK THAT ALL DATABASE META FILES HAVE BEEN CREATED
if not exist %3\SYB12_SYSUSERS.dat echo ** ERROR %3\SYB12_SYSUSERS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSOBJECTS.dat echo ** ERROR %3\SYB12_SYSOBJECTS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSTYPES.dat echo ** ERROR %3\SYB12_SYSTYPES.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSCOLUMNS.dat echo ** ERROR %3\SYB12_SYSCOLUMNS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSCOMMENTS.dat echo ** ERROR %3\SYB12_SYSCOMMENTS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist master\SYB12_SYSDATABASES.dat echo ** ERROR master\SYB12_SYSDATABASES.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
rem *** CHECK THAT ALL MASTER META FILES HAVE BEEN CREATED
rem ** UPDATE THE OMWB FILE - THIS HOLDS SCRIPT AND PERTINENT SOURCE DATABASE SERVER INFO
goto omwbfile
:finalinstructions
echo **************************************************************************
echo ** The offline capture script has completed execution.
echo ** Please review the screen output and .err files (if any) in the output
echo ** directories for any irregularities. You may need to execute the
echo ** script again to resolve any irregularities.
echo **
echo ** Finally, please archive the directory containing the sybase15.ocp file.
echo ** This contains master and %3 directories (preserve the
echo ** directory structure in the archive) the ROW.TXT, the COLUMN.TXT, and the
echo ** sybase15.ocp file. Return the archive file to
echo ** your Oracle representative.
echo **************************************************************************
goto exit
:exit
rem ** REMOVE THE SCRIPT VERSION ENVIRONMENT VARIABLE
set OMWB_SCRIPT_VERSION=
rem ** REMOVE THE VALUE FOR THE OMWB FILE ENVIRONMENT VARIABLE
set OMWB_SCRIPT_FILE=
rem ** REMOVE THE VALUE FOR THE END OF ROW DELIMITER ENVIRONMENT VARIABLE
set OFFLINE_CAPTURE_ROW_DELIMITER=
rem ** REMOVE THE VALUE FOR THE END OF COLUMN ENVIRONMENT VARIABLE
set OFFLINE_CAPTURE_COLUMN_DELIMITER=
SYB15_BCP_SCRIPT.BAT
rem %1 DBA login id
rem %2 password
rem %3 database name
rem %4 database server name
rem %5 end or column delimiter
rem %6 end of row delimiter
rem export the required system tables in the master database
bcp sysdatabases out master\SYB12_SYSDATABASES.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
rem export the required system tables in the database to be migrated
bcp %3.dbo.sysusers out %3\SYB12_SYSUSERS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.sysobjects out %3\SYB12_SYSOBJECTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.systypes out %3\SYB12_SYSTYPES.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.syscolumns out %3\SYB12_SYSCOLUMNS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.syscomments out %3\SYB12_SYSCOMMENTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
isql -U%1 -P%2 -D%3 -S%4 -b -i get_indexes.sql -o %3\SYB12_SYSINDEXES.dat
isql -U%1 -P%2 -D%3 -S%4 -b -w200 -i get_constraints.sql -o %3\SYB12_SYSCONSTRAINTS.dat
For SYBASE 12
Only 1 SYB12_BCP_SCRIPT.BAT needs to be updated with the following
SYB12_BCP_SCRIPT.BAT
rem %1 DBA login id
rem %2 password
rem %3 database name
rem %4 database server name
rem %5 end or column delimiter
rem %6 end of row delimiter
rem export the required system tables in the master database
bcp sysdatabases out master\SYB12_SYSDATABASES.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
rem export the required system tables in the database to be migrated
bcp %3.dbo.sysusers out %3\SYB12_SYSUSERS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.sysobjects out %3\SYB12_SYSOBJECTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.systypes out %3\SYB12_SYSTYPES.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.syscolumns out %3\SYB12_SYSCOLUMNS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.syscomments out %3\SYB12_SYSCOMMENTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
isql -U%1 -P%2 -D%3 -S%4 -b -i get_indexes.sql -o %3\SYB12_SYSINDEXES.dat
isql -U%1 -P%2 -D%3 -S%4 -b -w200 -i get_constraints.sql -o %3\SYB12_SYSCONSTRAINTS.dat