How to export data into SQL Azure database

1. From Management Studio right-click on the database you'd like to export data from
2. Go to Tasks -> Export Data
3. Select your source database - regular SQL Server 2008 or 2012
4. For the destination database (your Azure database), select .NET Framework Data Provider for SQL Server
5. Enter your Azure server name [serverName.database.windows.net], also fill in User ID and Password, Set Encrypt property to True and Integrated Security to False
6. Click on Next and select the tables you are looking to import into SQL Azure

ORA-29283: invalid file operation

Error: ORA-29283: invalid file operation

Details: Error occurs when running a full data pump export

Cause:DATA_PUMP_DIR is not defined or defined incorrectly for that database Solution:Login to sqlplus as sysdba and execute:

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR as 'your dir path here';

Using Oracle Data Pump for import/export

In order to get Data Pump directory location, execute

SELECT directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR'; 

Typically it is $ORACLE_BASE/admin/DBName/dpdump/

Then check priviges that are assigned to the directory by executing;

SELECT grantee, privilege FROM dba_tab_privs dtp WHERE table_name = 'DATA_PUMP_DIR';

The grantee could be a user or a role.

Use the following to create a directory:

CREATE OR REPLACE DIRECTORY dpump_dir1 AS '.../admin/DBName/dpdump';

After a directory is created, you need to grant READ and WRITE permission on the directory to other users. For example, to allow the Oracle database to read and to write to files on behalf of user usr1 in the directory named by dpump_dir1, you must execute the following command:

GRANT READ,WRITE ON DIRECTORY dpump_dir1 TO usr1;

Once the directory access is granted, the user can export his database objects with command arguments from a command line:

expdp usr1/pwd DIRECTORY=dpump_dir1 dumpfile=usr1.dmp

The above will produce the following output:

Export: Release 11.2.0.3.0 - Production on Tue Jan 8 12:48:31 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USR1"."SYS_EXPORT_SCHEMA_01":  usr1/******** DIRECTORY=dpump_dir1 dumpfile=usr1.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USR1"."TABLE1"                             0 KB       0 rows
. . exported "USR1"."TABLE2"                             0 KB       0 rows
. . exported "USR1"."TABLE3"                                0 KB       0 rows
Master table "USR1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USR1.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/DBName/dpdump/usr1.dmp
Job "USR1"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:49:08

The above will also be written to a default log file in the same directory - export.log or in the log you specify in the command line in LOGFILE parameter:

expdp usr1/pwd DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log

You could also specify particular tables you want to export in a TABLES parameter of your command, in case you do not need all user tables. You can add more than one table separated by comma:

expdp usr1/pwd TABLES=TABLE1,TABLE2 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log

Import command would look something like this:

impdp usr1/pwd TABLES=TABLE1,TABLE2 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

To import a schema, you will need to add a SCHEMAS parameter to your command:

expdp usr1/pwd SCHEMAS=USR1 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log



impdp usr1/pwd SCHEMAS=USR1 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

And for a full database import/export, you will need to add FULL parameter and set it to y:

expdp usr1/pwd FULL=y DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log



impdp usr1/pwd FULL=y DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

To append data to existing tables, add parameter TABLE_EXISTS_ACTION=APPEND to your import statement

Error when exporting data to Excel

While exporting data from SQL Server 2005 to Excel, I got an error "Columns "Field1" and "F1" cannot convert between unicode and non-unicode string data types". I was exporting data returned by a particular view. In order to fix the issue I had to modify the view, utilizing CAST function to cast the columns to their existing data type. Weird, huh?

Here is my original view. Please note that Field1 and Field2 are of type varchar(5) and varchar(100) respectively:

CREATE VIEW [dbo].[myView]
AS
SELECT Field1,
Field2
FROM Table1

Here is what I modified my view to and what worked for me:

CREATE VIEW [dbo].[myView]
AS
SELECT CAST(Field1 As Varchar(50)) as Field1, 
CAST (Field2 as Varchar(100)) As Field2
FROM Table1