Random UX Thought for Phone Tree Creators

How about saying which number to use before you list options for two minutes so if I hear my option I’ll already know which button to push?

Instead of “If you’re calling about service for X, service for Y, service for Z, service for A, etc. push 1” they could do “push 1 for service for X …” and if “X” is what I want I don’t have to listen to the rest of message.

Fixing Oracle to SQL Server Data Transfer Error: Unclosed quotation mark after the character string ”.

I’ve been trying to fix a data conversion error for several hours and finally figured out the issue so I’m going to document it here in case anyone else ever has to deal with it.

First off some background:

  1. I’m migrating data from Oracle 11 to SQL Server 2012
  2. I’m running Oracle in a Virtualbox VM (I’ll cover that in my next post)
  3. I’m using Navicat Premium to do the migration in VMWare on my MacBook Pro. Running it in Windows 10 seems faster and seems also to give better error messages. The data transfer seems to have more options too.
  4. The error message I was getting was:

[Msg] [Dtf] Start transfer to Target Server: [dbo].[ACQUISITIONS] [Err] [Dtf] 42000 – [SQL Server]Unclosed quotation mark after the character string ”. 42000 – [SQL Server]Incorrect syntax near ”.

The bummer is that when running the data transfer with the default settings I’d get the last 5 records that the transfer tried to insert. None of those were the offending record so that information is more or less useless.

My table has 1,500,000+ records in there and the IDs aren’t sequential so I couldn’t try to find a record before or after the ones in the error message.

Eventually I unchecked the “Use Transaction” and “Use Extended Insert Statements” boxes in the “Advanced” tab of the data transfer dialogue box. That along with unchecking the “Continue on error” box made the inserts stop on the problematic record.

But that only helped me find the bad record. Next up was figuring out which field or fields were causing the issue. The table had 38 fields so it took some researching.

After closely inspecting each field I finally realized two fields had some invisible special characters in them. I couldn’t actually SEE the characters in Windows at all.

Here’s what I’d see in OS X Navicat Premium:

navicat specialchar

If I moused into the field value box I’d see this:

navicat focused

And here’s what I’d see in TextMate. Only TextMate truly showed the invisible characters. Sublime and Atom didn’t.

special character fun

So now that I know what’s causing the issue I need to figure out how to fix it. The first thing I wanted to do was be able to query Oracle and find the records with the <NUL> in them. After a lot of Googling I found an explanation of the null character and some information on how to use unicode strings in a query in Oracle.

After some experimenting I finally came up with the following query which works for me:

SELECT * FROM TABLE_NAME WHERE FIRST_NAME LIKE UNISTR(‘\0000%’) ORDER BY TABLE_NAME.ID

After that I was able to run some updates and then finally get my data migrated.

 

 

How I Fixed the Ansible 2.1 Temporary File Permissions Issue

Sadly this solution didn’t work for me since I’m running Ansible on a vagrant provisioned virtualbox VM on my MacBook Pro.

The error message:

fatal: [10.0.0.100]: FAILED! => {"failed": true, "msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user. For information on working around this, see https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user"}

points to some solutions and some careful reading told me I can add

allow_world_readable_tmpfiles

to my

ansible.cfg

file. That’s great but where the fuck do I put the ansible.cfg file?

I tried adding it to the directory of my vagrant provision code per

* ANSIBLE_CONFIG (an environment variable)
* ansible.cfg (in the current directory)
* .ansible.cfg (in the home directory)
* /etc/ansible/ansible.cfg

but that didn’t work for some reason so I created ~/.ansible/ansible.cfg and added the following:

[defaults]
allow_world_readable_tmpfiles=true

I also have my hosts file in there and set the following ENV_VARS in ~/.zprofile:

export ANSIBLE_HOSTS=~/.ansible/hosts
export ANSIBLE_CONFIG=~/.ansible/ansible.cfg

Now the following code works as it should:

- name: Create Database
 become: true
 become_user: postgres
 postgresql_db: name={{ db_name }}

- name: Create User
 become: yes
 become_user: postgres
 postgresql_user: name={{ db_user }} password={{ db_password }} state=present role_attr_flags=NOSUPERUSER,CREATEDB

- name: Provide user with DB permissions
 become: yes
 become_user: postgres
 postgresql_user: user={{ db_user }} db={{ db_name }} priv=ALL