TNS service names ODBC dropdown box garbled, messed up, gibberish

Most likely cause is that there are two or more different Oracle clients installed, with two or more home folders and tnsnames.ora files.

For example, an Oracle 10 client is installed or Oracle XE is installed. Both contain a home folder with tnsnames.ora. Later, the Oracle 11g instant client + ODBC compontents is added. Now there are two home folders.

In this case, the TNS_ADMIN environment variable needs to exist:

• Create TNS_ADMIN variable via control panel -> system -> advanced -> environment variables -> system variables
• Set is to one of the admin folders, eg.:
  c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN
  Note: make sure is contains the tnsnames.ora, sqlnet.ora and listener.ora files.
• Create a ODBC connection using the Oracle driver and check if the "tns service name" dropdown box contains readable entries
• Just to be sure, replace the body of all other tnsnames.ora files with:
  IFILE=c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN

CDBurnerXP: drive not recognized

CDBurnerXP is a freeware tool for burning CD's and DVD's, including blueray. However, it sometimes does not recognize the drive to burn with.

First, check the FAQ on the CDBurnerXP website:
http://cdburnerxp.se/help/appendices/troubleshooting

In my specific case, I then did the following:

• Start CDBurnerXP in "audio mode"; the drive was recognized. Only in "data mode" it is not.
• Check the chipset. Download the freeware tool "system info": http://www.gtopala.com/siw-download.html.
• Extract and start it - it does not have to be installed; it is a standalone executable. Check hardware -> motherboard -> southbridge. Note the chipset.
• Download "intel matrix storage manager" for your chipset: IMSM.
• Install IMSM and restart.

Now, CDBurnerXP should work in "data mode".

Note: make sure the drive is on the "compatible drives" list: http://cdburnerxp.se/en/testeddrives

The nearby-future of storage

The ioDrive Duo by FusionIO out-performs even multiple SSD's placed in parallel. The 320GB version of the ioDrive has a read/write speed of ~1.5GB/s:

http://www.fusionio.com/products/iodriveduo

Difference with traditional SSD:

• The flash memory is integrated in the PCI card.
• The flash memory has been optimized using a proprietary technology.

Downside:

• Cannot be used as a boot drive.
• Requires a 64-bit operating system.
• The price is about 15,000 euro for 320GB version (ssdeurope.com). Ofcourse, this has to be compared against the cost of buying and operating a SAN.
  

SQL Server statistics

Statistics are information about the distribution of data in a column, maintained internally by the database engine.

Rules and behaviour with regard to statistics, when automatic creation/updating is turned on:

• Statistics are automatically created when creating an index, but only for the first column of the (composite) index.
• Statistics can be created manually on any column or combination of columns.
• Statistics are automatically created/updated when the query optimizer finds need for them.
• Statistics are not automatically created/updated when the server is too busy, unless the "update statistics asynchronously" option is turned on, because then the are created in parallel.
• Asynchronously created/updated statistics have effect the next time the (causing) query is run.

Links:
http://www.sql-server-performance.com/tips/update_statistics_p1.aspx
http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx
http://technet.microsoft.com/nl-nl/library/cc966419(en-us).aspx

Set cscript as default script engine

To set cscript as the default scripting engine on the system:

cscript //h:cscript //s

This way, the "wscript.echo" method always spools to the command line in stead of to message boxes. And without the need to start each script with the "cscript.exe" application.

Derived table vs subquery

There are several ways to combine datasets:

• Subquery: SELECT clause, correlation
• Join (cross/left/right/inner/full): FROM clause, no correlation
• Cross apply: FROM clause, correlation

Datasets retrieved in the FROM clause are called "derived tables".

The "cross apply" combines the best features of the subquery and join, but is performance-wise not always the best choice.

SQL Developer thick/OCI driver

The following error messages may appear when using SQL Developer to select data from a SQL Server table via a (heterogeneous or DG4ODBC) database link on an Oracle 11g DB:

Bigger type length than Maximum
OALL8 is in an inconsistent state

Solution: switch to the thick driver to work around this.

In SQL Developer: tools -> preferences -> database -> advanced -> use oci/thick driver = checked.

If this does not work or when getting the error messages:

no ocijdbc11 in java.library.path
vendor code 0

then:

- Download Oracle Instant Client 11.1.0.7.0 basic lite (easily found with Google, register with Oracle to download)

Note: version 11.2 does not work with SQL Developer!

Note: take the 64-bits version on a 64-bits OS, like Windows7 x64.

- Extract files to c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Add to environment variable PATH: c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Create a batch file called sd.bat in the rootfolder of the SQL Developer installation, with the following body:

set ORACLE_HOME="c:\ora\instantclient_x64_11.1.0.7.0_basiclite"
start sqldeveloper.exe

- Double-click sd.bat to start SQL Developer
- Add a connection with Connection type = basic

To make a connection with Connection type = TNS:

- Create folder c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Put these files in the admin folder:

sqlnet.ora
tnsnames.ora

- In SQL Developer: tools -> preferences -> database -> advanced -> tnsnames folder: c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Add a connection with Connection type = TNS

Note the following:

• The solution above even (or especially) works when another Oracle client is already installed. For example, if the OracleXE DB (which, at the time of writing, is 10g) is installed locally, then the Oracle Client 10 is also installed. But this client might not work when trying to connect to a remote Oracle 11g DB
• There is no need to set the environment variables ORACLE_HOME or TNS_ADMIN variable on the system. It does not matter what their values are
• The order of folders in the variable PATH does not matter
• Most likely Help -> About -> Properties -> oracle.home does not point to the correct Oracle home folder. So not to c:\ora\instantclient_x64_11.1.0.7.0_basiclite. It does not matter what the value is
• Unfortunately, the ORACLE_HOME trick does not work with the PATH variable; it is no use adding that to the batch file
• The error "Bigger type length than Maximum" is related to a bug in the implementation of the thin part of the JDBC driver. Not to heterogeneous services, Oracle DB, SQL Server, SQL Developer or the thick part of the JDBC driver. (Note: the same driver, e.g. ojdbc6.jar, contains the thin and the thick classes)

Oracle JDBC connections

The following JDBC drivers exist for Oracle:

• classes111.jar: Java 1.1
• classes12.jar: 1.2 and 1.3
• ojdbc14.jar: 1.4 and 1.5 (no JDBC3 and 4 features)
• ojdbc5.jar: 1.5
• ojdbc6.jar: 1.6

Java 1.5 = Java JRE/SE 5 and Java 1.6 = Java JRE/SE 6. Additional jar files are required for some features.

All the classes to support basic functionality for the Thin and OCI drivers are inside the same jar. In short, for simplification, the same file contains both the thin and the thick (=OCI) classes.

Joe Weinstein on forums.oracle.com:

The jar is all of the thin driver and the java portion of the OCI driver. In order to
run in OCI mode, you would use this same jar, giving it the OCI URL instead of the thin
URL, and you would have to have the whole Oracle client installed on your machine,
and your OS environment set up to point to the OCI DLLs as well as the standard C
libraries. Note that the version of the OCI libraries that works with one driver jar
may not work with another driver jar, so if you go the OCI route, you may have to
install a whole new OCI client just to get a particular driver fix.

Thick URL: jdbc:oracle:oci8:@<database_name>
The database_name is the Net Service name of a mapping in tnsnames.ora.

Thin URL: jdbc:oracle:thin:@<server>:1521:<database_name>
The database_name is the database name part of the database service on the (remote) server. E.g. Windows service "OracleServiceORCL", then the database_name is "ORCL".

Oracle released different versions for each driver, eg. 11.1.0.6.0 or 11.1.0.7.0.

To get the different versions, download the Oracle Instant Client of choice from the Oracle website and extract the ojdbc.jar files to one folder. Rename them using the version. This makes testing or switching easy:

ojdbc6_11gR1_11.1.0.6.0.jar
ojdbc6_11gR1_11.1.0.7.0.jar
ojdbc6_11gR1_11.2.0.1.0.jar
etc

Databases

Row-oriented:
Oracle DB
SQL Server
SQL Server CE
MySql: InnoDB
Sybase Advantage Database Server (ADS)
MS Access

Column-oriented:
Extreme-DB
InfiniDB Enterprise Edition
Infobright
MonetDB
ParAccel Analytic Database
Sybase IQ
Sybase Adaptive Server Enterprise (RDBMS & column-oriented)
Vectorwise
Vertica

Appliance:
Aster Data (MPP, MapReduce)
Greenplum (MPP, MapReduce)
Netezza (MPP)
Teradata (MPP)

In-process:
SQLite

No login prompt for website

Suppose you want to login with Internet Explorer to an intranet website, that uses Windows Authentication as the authentication mechanism. When you do this, a prompt is displayed, that requires you to enter the account's username and password.

This prompt can be skipped:

• Note the webserver name in the title bar of the prompt, e.g. wbsvr01.acme.lan
• Start the Credential Manager: Control Panel -> Credential Manager
• Add a windows credential:
  a. internet or network address: wbsvr01.acme.lan
  b. username: domain\usr
  c. password: pwd

Note: if the website is on the internet, the webserver name has to be added to the trusted sites of the intranet zone:

• Internet Explorer -> Tools -> Internet Options -> Security -> Select "Local Intranet" -> Sites -> Advanced
• Add this website to the zone: http://wbsvr01.acme.lan

Run SSMS using different Windows account

You can log on to a SQL Server database using SQL Server Management Studio (SSMS) with a Windows Authentication login / NT account / Windows login.

However, the Active Directory domain account to do this with cannot be entered. It is greyed out and always the account of the user that is currently logged on.

There are several ways to bypass this and make a connection:

1) Run SSMS under different privileges

• Create a shortcut to SSMS with the following command:
  runas /netonly /user:domain\usr "g:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"
• Double-click the shortcut and enter password for the runas account

Note: SSMS can be located somewhere else, depending on the choice made for the location when installing or the operating system. When using Windows 7 x64, then the tool is by default located as specified above.

Note: The greyed-out login screen in SSMS still displays the logged on user, not the runas account. This might be confusing.

1a) Run SSMS under different privileges, without needing to enter a password

• Download "runasspc" (can be easily found with a search engine)
• Extract "runasspc.exe" from the zip file and copy to the windows folder
• Create a shortcut to SSMS with the following command:
  runassprc "g:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" /domain:domain /user:usr /password:pwd

2) Create a network connection to the remote server

• Open a command windows (Win+R, cmd.exe)
• Delete all mappings: net use * /delete /y
• Create mapping to SQL Server server: net use \\servername /user:domain\usr
• Start SQL Server Configuration Manager via Programs (or locate SQLServerManager.msc directly)
• Open SQL Native Client Configuration
• Enable Named Pipes protocol and move to top in the order
• Add a new alias. Use:
  a. "Named Pipes" as network library
  b. Server name: fill in remote server name. The pipe field will display: \\[servername]\pipe\sql\query"
• Start SSMS
  a. Server name: fill in remote server name
  b. Authentication: Windows Authentication
  c. Connection Properties -> Network Protocol, set to Named Pipes

Note: SSMS automatically uses the account name from the connection to the remote server.

3) Set credentials for a remote server using Credential Manager

• Control Panel -> Credential Manager
• Add the domain\account and password for the remote server

Note: use the fully qualified server name and port, e.g. remoteserver.domain.com:1433

4) Set terminal services Active Directory rights
Get the "allow logon to terminal services" right for the remote server for the domain account. Then make a terminal services connection to the server with that account and start SSMS within that session.

Links:
http://www.olegsych.com/2009/05/crossing-domain-boundaries-windows-authentication/

Change column position

In Oracle, it is not straightforward to change the position of a column. Let alone one that contains data.

Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Create a new table called "newtable" with the desired column order, based on "oldcolumn"

Note: generate DDL script for "oldtable" and only change the position of the columns. Do not apply constraints, defaults, primary keys, foreign keys, etc. They are named and the names cannot exists twice in a schema

- Insert all data from "oldtable" into "newtable" using a DML INSERT-statement
- Drop "oldtable"
- Rename "newtable" as "oldtable"
- Apply constraints, defaults, etc to "oldtable"

Note: if columns are deleted or newly added to "newtable", then this is not straightforward and each constraint, default, etc should be inspected before applying

Or you can use the "dbms_redefinition" package (bhaski on www.geekinterview.com):

But you should have these previleges -->
# execute access on the dbms_redefinition package
# create any table # alter any table
# drop any table
# lock any table
# select any table

create table tab1(col1 char,col3 number);

alter table tab1 add(col2 char);

create table tab2 as select col1,col2,col3 from tab1; exec

dbms_redefinition.can_redef_table('schema_name','tab1');

begin
dbms_redefinition.start_redef_table('schema_name','tab1','tab2','col1,col2,col3');
dbms_redefinition.finish_redef_table( 'schema_name', 'tab1', 'tab2' );
end;

select * from tab1;
col1 col2 col3

Change column data type

In Oracle, it is not straightforward to change the data type of a column that contains data. Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Add a new column to the table called "new column" with the new data type
- Copy the values of "old column" to "new column" using a DML UPDATE statement
- Remove the "old column" from the table using a DDL ALTER statement

Most recognizable samples

Breakbeats
Al Green - I’m Glad You’re Mine (0:00)
[Eric B. & Rakim - Mahogany, The Notorious B.I.G. - I Got a Story to Tell, RZA - My Lovin' Is Digi]

Bad Bascomb – Black Grass (1:41)
[Doug E. Fresh - Everybody Got 2 Get Some, Monie Love - Monie In The Middle]

Billy Squier – Big Beat (0:00)
[Ice Cube – Jackin’ For Beats, Big Daddy Kane – Get Down, A Tribe Called Quest – We Can Get Down, EPMD – Get Wit This]

Booker T. & The M.G.'s - Born Under A Bad Sign
Booker T. Jones & William Bell - Born Under A Bad Sign

Bobby Byrd - Hot Pants (0:00)
[Sir Mix-a-Lot - One Time's Got No Case, Das EFX - Straight Out the Sewer, Ini Kamoze - Here Comes the Hotstepper, Run-DMC - What's It All About]

Chuck Brown and The Soul Searchers - Ashley’s Roach Clip (3:31)
[Eric B. & Rakim – Paid In Full, PM Dawn – Set Adrift On Memory Bliss, Nice & Smooth - Down the Line]

Dexter Wansel – Theme From The Planets (0:00)
[Eric B. & Rakim - I Ain't No Joke, Last Night by Kid 'N Play, Gutfest '89 by Digital Underground]

Donny Hathaway – Magnificent Sanctuary Band (0:00)
[Beastie Boys - Johnny Ryall]

Edwin Birdsong - Rapper Dapper Snapper (0:00)
[De La Soul - Me Myself and I, Gang Starr - Skills]

Fred Wesley and The J.B.'s - More Peas (7:22)
[Organized Konfusion - Fudge Pudge, Cypress Hill - The Phunky Feel One]

Funkadelic – Good Old Music (0:00)
[Young Black Male by 2Pac]

Kool & The Gang – Jungle Jazz (0:13)

Kool & The Gang - N.T.
[Jump by Kris Kross, Fakin' the Funk by Main Source]

Funk Inc. – Kool Is Back (1:48)
[Jeru the Damaja - Come Clean]

Isley Brothers – Footsteps In The Dark

James Brown - Blues and Pants (0:00)
[Ice-T - New Jack Hustler, Cypress Hill - The Phunky Feel One]

James Brown - Funky Drummer (5:35)

James Brown – Funky President (0:00)
Eric B. Is President by Eric B. & Rakim, Hey Ladies by Beastie Boys]

Jeff Beck – Come Dancing (0:00)
[Chino XL - It's All Bad, Gang Starr - New York Strait Talk]

Jimi Hendrix - Flashing (0:00)

Joe Tex – Papa Was Too (0:00)
[EPMD - Headbanger, Wu-Tang Clan - Wu-Tang Clan Ain't Nuthing Ta F*** Wit]

Lafayette Afro Rock Band - Hihache (0:00)

Led Zeppelin - When The Levee Breaks (0:00)
[Ice-T - Midnight, Eminem - Kim]

Little Feat - Fool Yourself (0:00)
[A Tribe Called Quest - Bonita Applebum, Organized Konfusion - Soundman]

Little Richard – The Rill Thing (0:00)

Lowell Fulsom - Tramp (0:00)
[House Of Pain - Jump Around, Cypress Hill - How I Could Just Kill A Man]

Lyn Collins - Think (About It) (1:23)

Manzel - Midnight Theme (0:00)
[Wreckx-N-Effect - Rump Shaker, Cypress Hill - How I Could Just Kill A Man]

Mountain - Long Red (Live) (0:00)

Melvin Bliss - Synthetic Substitution (0:00)
[Naughty By Nature - OPP, Ice-T - Original Gangster, The Pharcyde - Ya Mama]

Orange Crush – Action (0:25)
[Do You Want It... Do You Like It... by Puff Daddy, We Major by Kanye West]

Power of Zeus – Sorcerer of Isis (0:10)
[The Wiseguys - Face the Flames, Eminem - Amityville]

Q65 – Get Out Of My Life Woman
[Jeru the Damaja - Revenge of the Prophet (Part 5), The Wiseguys - We Be the Crew]

Roy Ayers – The Boogie Back (0:00)
[N.W.A - F*** Tha Police]

Silhouettes – Fonky First (0:00)

Skull Snaps – It’s A New Day (0:00)
[The Pharcyde - Passin' Me By, Digable Planets - For Corners]

Sly & the Family Stone - Sing a Simple Song (2:12)

The Headhunters – God Make Me Funky (0:09)
[Eric B. & Rakim - Beats for the Listeners]

The Honey Drippers - Impeach the President (0:00)

The Incredible Bongo Band - Apache (0:00)
[Sugarhill Gang - Apache, LL Cool J - You Can't Dance, Grandmaster Flash - Freelance]

The Meters – Here Comes The Meterman
[Big Daddy Kane - Long Live The Kane, Run-DMC - How'd Ya Do It Dee?]

The Meters - Look-Ka Py Py (0:50)
[Cypress Hill - The Phunky Feel One]

The Winstons - Amen, Brother (1:27)
[N.W.A. - Straight Out Of Compton, 3rd Bass - Portrait Of The Artist As A Hood, Brand Nubian - The Gods, Snow - Informer, Eric B. & Rakim - Casualties of War]

Young Holt Unlimited – Wah Wah Man

Zapp and Roger – More Bounce To The Ounce (0:00)
[EPMD – You Gots To Chill, Ice Cube – Jackin’ For Beats, Public Enemy – One Million Bottlebags, Ice Cube – Look Who’s Burnin’]

The Turtles – I’m Chief Kamanawanalea (0:00)
[Serious by Steady B feat. KRS-One, Say No Go by De La Soul, AmeriKKKa's Most Wanted by Ice Cube]

Grady Tate - Be Black Baby (0:00)
[Set It Off by Big Daddy Kane, Fakin' the Funk by Main Source, Stand Together by Beastie Boys]

Sounds
The J.B.'s - The Grunt (0:00)
Herbie Hancock - Wiggle Waggle (0:00)

Identifying keys

A key that is made up of one field is a simple key. All other keys are combined keys. There are two types of combined keys, compound and composite:

Simple key: key that consists of one field

Combined key: key that consists of more than one field

Compound key: combined key in which each field is itself a simple key (of another entity)

Composite key: combined key, that is not a compound key

A new term I have made up myself:

Concatenated key: a simple key, that is the concatenation of the fields in a combined key

Truncate tables that have a foreign key

A script to truncate tables that have foreign keys referencing them. It also keeps one record in the table, the one with ID=0. In this case, each table has this field as a primary key.

Finally, some tables are not to be truncated. The readible names of these tables are in a metadata table called TDWM_TABLES. The readible name is in the field VIEWNAME.

The process is:

- disable constraints on the selected tables, i.e. with names like 'TDWH_%'
- create a new intermediate table via CTAS that contains the records to keep, i.e. with ID=0
- truncate the table
- copy records back from the intermediate table
- drop intermediate table
- enable constraints on the selected tables, i.e. with names like 'TDWH_%'. Note: enable PK constraints before enabling the FK constraints!

Here is the script:

/* SCRIPT */

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN

-- Disable constraints
DBMS_OUTPUT.PUT_LINE ('-> Disabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' CASCADE' as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%')
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;

-- Truncate tables
DBMS_OUTPUT.PUT_LINE ('-> Truncating tables');
FOR reg IN (
SELECT
'CREATE TABLE Z_' || TABLE_NAME || ' AS SELECT * FROM ' || TABLE_NAME || ' WHERE ID=0' AS ST1,
'TRUNCATE TABLE ' || TABLE_NAME AS ST2,
'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM Z_' || TABLE_NAME AS ST3,
'DROP TABLE Z_' || TABLE_NAME AS ST4
FROM USER_TABLES t
INNER JOIN TDWM_TABLES b ON UPPER(t.TABLE_NAME) = UPPER(b.TABLENAME)
WHERE t.TABLE_NAME LIKE 'TDWH_%' AND
UPPER(b.VIEWNAME) NOT IN ('TDAYOFWEEK','TMONTHOFYEAR','TQUARTEROFYEAR')
)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
DBMS_OUTPUT.PUT_LINE (reg.ST2); EXECUTE IMMEDIATE reg.ST2;
DBMS_OUTPUT.PUT_LINE (reg.ST3); EXECUTE IMMEDIATE reg.ST3;
DBMS_OUTPUT.PUT_LINE (reg.ST4); EXECUTE IMMEDIATE reg.ST4;
END LOOP;

-- Enable constraints
DBMS_OUTPUT.PUT_LINE ('-> Enabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%' ORDER BY CONSTRAINT_NAME DESC)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;
END;

Unlimited login attempts on Oracle 11g

In Oracle 10g, by default, each user has unlimited login attempts before being blocked. In Oracle 11g this behaviour is changed to 10 attempts.

When a user/schema is created, it gets assigned to the default profile. The easiest way for unlimited login attempts for all users is to give the default profile the rights:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Reconfigure OracleXE built-in webserver's port

OracleXE comes with a built-in webserver, running on port 8080. Since this is a wellknown port, chances are that it is already in use on your machine, and therefore conflicting with an already running webserver.

Check this blog to reconfigure it:

http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html

Cannot load package & required privilege is not held by the client

How to start a filesystem dtsx package from sql agent, without a config file or without adding a package password to the command line:

- ssis: make note of the package owner, should be windows (domain) account
- ssis: package: encrypt with userkey
- os: sql agent service should run under LocalSystem account
- os: add package owner to local groups: SQLServer2005DTSUser$SERVER, SQLServer2005SQLAgent$SERVER
- ssms: create credential for package owner
- ssms: create proxy using created credential, add all principals, add all subsystems
- ssms: use the created proxy as "run as" user for the job step that starts the package

There is no easy option to specify the password for a package from sql agent, hence the "encrypt with userkey".

See also: http://support.microsoft.com/kb/918760

Background information
Principals have access to a proxy. A proxy is a mapping to a credential. A credential is a mapping to a Windows account.

1. SSMS / Security / Credential, create a credential. Set identity to an existing account that has the appropriate rights to run the job.
2. SQL Agent / Proxies / SSIS Package Execution, create a new proxy
2a. Proxy / General / Credential name: select the credential from step 1
2b. Proxy / General / Subsystems: select all
2c. Proxy / Principals / MSDB Role, add principals: sqlagentuserrole, sqlagentreaderrole, sqlagentoperatorrole. This are the principals that can access the proxy.
2d. The account must have "logon as a batch job" privileges (check with secpol.msc)
4. Set SQL Server Agent service account to LocalSystem
5. If needed, run a job step under another account. Typically: a SQL Server account based on a Windows account: SQL Server Agent / Jobs / job / Steps / Edit / Advanced / Run as user,
- select proxy
- type SSIS package
- package source File system

If the "A required privilege is not held by the client" error occurs (from Microsoft website):

1. Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account.
2. Stop and then start the SQL Server Agent service.
3. Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.
4.Stop and then start the SQL Server Agent service.

From Marc at Stackoverflow:

So it boils down to really just two accounts:

one account is needed to run the SQL Server Agent Windows service - this is a Windows account on your machine / server which needs to have enough permissions to run the service, start and stop it - either use LocalSystem, Network Service, or whatever other Windows account you have to run services with

The other account would be the account to run your SQL Server Agent steps under - that's typically a SQL Server account (which could be based on a Windows account), and it needs enough privileges inside SQL Server to do its job, e.g. it needs access to the database objects and all. I would strive to have just one account for each app that runs the SQL Server jobs - makes life a whole lot easier!

From Influent at SQLTeam:

1. set ProtectionLevel to DontSaveSensitive in package properties in Visual Studio (using SSPI anyway so not sure it mattered)
2. created configuration file (had to alter path to config file in dtsx file since it's different on servers than in dev environment)
3. instead of importing the package into MSDB, set the job step Package Source to "File system" and pointed to dtsx file on local drive of server
4. set the Configurations tab of the job step to point to the dtsConfig file created in step 2
5. made sure the AD user kicking off the job was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER (I eventually removed Administrator permissions because I only needed it to log to Windows Event Log)
6. made the associated SQL user to the user in step 5 part of the SQLAgentUserRole in the msdb database
7. made sure the local user assigned to the SQL Agent service was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER

From xprog.blogspot.com:

1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314). The step failed.
The error 1314 is "A required privilege is not held by the client".

This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.

To fix it verify:
1. The proxy account have the "Act as part of the operating system" privilege.
2. The SQL Server Account is in the group
SQLServer2005MSSQLUser$$
3. The Proxy account is in the group
SQLServer2005DTSLUser$$

SQL Server Agent Account must have 'Adjust memory quotas for a process' (or 'Increase quotas' in pre-Windows 2003) privilege on SQL Server.

From FriggleFragle at MSDN:

an issue i found was that sql server agent and ssis had been changed to not use the local system account. this caused me to continually get the dreaded --> "A required privilege is not held by the client" error. i fought with it all afternoon and eventually updated the services to use the local system account and it worked. perhaps this will save some other as much time as i wasted.

External tables

Data can be imported into and exported from Oracle 10g in different ways. E.g. by using the SQL*Loader tool for text files (sqlldr) or the Data Pump (impdp/expdp) tools for the propriety Oracle RAW format. For previous Oracle versions, the tools imp and are also available. Those import/export to external locations on the client machine, not the server machine.

Another way to get data in and out of Oracle is to define an external table. To do this you have to create a table in Oracle in the regular way using a CREATE TABLE statement, but with adding an additional ORGANIZATION EXTERNAL clause.

Within this clause the properties of the external (server side!) location can be specified.

EXAMPLE

CREATE TABLE TEST
(
  "CODE" VARCHAR2(17 BYTE)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_PUMP_DIR
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET WE8MSWIN1252
    FIELDS TERMINATED BY 0X'09'
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
  )
  LOCATION ( DATA_PUMP_DIR: 'EXTFILE.TXT' )
)

FIELD LIST

The list of fields following the create table keywords cannot be inferred dynamically, but has to be specified explicitly. So it is not possible to "hook up" a text file and retrieving the list of fields automatically.

Trick: import the data first into a table using SQL Developer, TOAD, etc, and create the DDL script from it. Finally, copy and paste the field list from this script.

It is also possible to specify the "external" list of fields inside the organization external clause. This is, for example, similar to how it is done using Oracle Warehouse Builder (OWB).

TYPE

One of these properties is "TYPE", which specifies the access driver that Oracle uses. There are two values for this property:

• ORACLE_LOADER: only read access, from all kinds of text files (but not, for example, Excel .xls)
• ORACLE_DATAPUMP: read and write access, a propriety RAW format

The access driver is a wrapper Oracle uses to call the previously mentioned SQL*Loader or Data Pump functionality.

An error occurs when an insert statement is performed on a table is created with access driver ORACLE_LOADER:

SQL Error: ORA-30657: operation not supported on external organized table30657.0000 -  "operation not supported on external organized table"
*Cause:    User attempted on operation on an external table which is
           not supported.
*Action:   Don't do that!

The ORACLE_DATAPUMP type has to be used to export data from Oracle using an external table. Unfortunately, this data cannot be humanly read or imported into any other tool then an Oracle database.

DEFAULT DIRECTORY / LOCATION

A directory object has to be available in Oracle. If it is not already present, it has to be created. For example:

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\'

Now the tag DATA_PUMP_DIR can be used in the organization external clause. The DEFAULT DICTIONARY property is mandatory and has to be used when setting the LOCATION.

A path specification is not allowed between the single-quotes.

This is not valid:

• LOCATION ( 'C:\EXTFILE.TXT' ) 

ACCESS PARAMETERS

The options are rather straightforward, except perhaps "FIELDS TERMINATED BY". To set the field delimiter a character can be specified between single-quotes. But it is also possible to specify special characters like tab or linefeed.

To specify a tab:

FIELDS TERMINATED BY 0X'09'

The 0X instructs Oracle that the value between the single-quotes should be interpreted as an ASCII value. In the example, ASCII character 9 is a tab.

OPTIONALLY ENCLOSED BY '"'

It is also possible to specify that some values have double-quotes around them. This however does not work in the following case:

Column 1 value - tab - tab - Column 3 value

Here a record has a value for columns 1 and 3, but not for column 2. It should be read as NULL, but is actually skipped. This results in the value of column 3 being wrongly inserted in column 2.

CHARACTER SET

Explicitly setting the character set of the external location prevents problems with interpreting the data and can improve performance.

From Odi's blog:

If you do not specify the CHARACTERSET parameter, the database character set is used to interpret the input file. As the input file is in WE8MSWIN1252, the ORACLE_LOADER driver gets confused trying to interpret single-byte WE8MSWIN1252 codes as multibyte AL32UTF8 codes.

The character set of the input file depends on the way it was created. Even on US Windows, you can create text files in different encodings. Notepad allows you to save the file in ANSI code page (=WE8MSWIN1252 on US Windows), Unicode (=AL16UTF16LE), Unicode big endian (=AL16UTF16), and UTF-8 (=AL32UTF8). The Command Prompt edit.exe editor saves the files in the OEM code page (=US8PC437 on US Windows).

-- Sergiusz

LOGGING

All errors are logged to a bad file - containing the violating records - and a log file - containing messages about the error that occured.

To disable logging, (some of) the following keywords can be added to the clause:

• NODISCARDFILE
• NOBADFILE
• NOLOGFILE

Oracle specification of the ORACLE_LOADER access driver:

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10825/et_params.htm