Monday, September 9, 2013

Oracle PL/SQL search for repeated substring

Short Description:  Query to find instances of a substring immediately repeated in a single string.

Description:  the query below finds a substring repeated immediately after the first.  My current example is searching username data for records for which the first and last name are the same.

SELECT USERNAME FROM SCHEMA.TABLE
where
trim(SUBSTR(USERNAME, INSTR(USERNAME, ' ', 1, 1)+1))= trim(substr(USERNAME, 1, instr(USERNAME,' ')))

SSIS 2012 Transfer Database task failure

 

Quick Description:  The SSIS 2012 Transfer Database task fails with error code 0x80131500 (An error occurred while transferring data)

Full Error:

Error: The Execute method on the task returned error code 0x80131500 (An error occurred while transferring data. See the inner exception for details.). The Execute method must succeed, and indicate the result using an "out" parameter.

There are multiple things to check here:

Check all the paths are correct in the destination database section of the task.  SSIS auto-populates this, but it isn't necessarily correct, and appears to just be the file locations of the source database.  If those path don't exist on the target server, it will fail.  See this thread for more information.

Check permissions to the data/log directories on the destination server for the machine account of the source server.

image

Verify you are not attempting to transfer from 2012 *to* an older version

Wednesday, August 7, 2013

Lotus SameTime doesn't start when launched

Short Description:  Attempting to launch Lotus Sametime results in the application being highlighted in the taskbar but not getting to the splash screen or subsequently launching.

Problem:  IM is way off topic for this blog, but this occasionally happens to me and is maddening.  In my case, TaskManager will show multiple copies of "rcplauncher.exe" are running but Sametime.exe is not.   It appears to be a copy of the file %APPDATA%\Lotus\Sametime\.rcp.lock is...well...locked and preventing the application from launching.

To fix step by step

  • Kill all instances of rcplauncher.exe
  • Remove the hidden attribute from %APPDATA%\Lotus\Sametime\.rcp.lock
  • delete or rename .rcp.lock (I'm not sure there are a lot of situations in which you would need to back up this file, but if you're concerned, back it up).

Or run this from command line/as a cmd file:

  • Taskkill /F /IM rcplauncher.exe (for earlier versions of windows kill.exe may be necessary instead of taskkill)
  • attrib -a -h %APPDATA%\Lotus\Sametime\.rcp.lock
  • del /q %APPDATA%\Lotus\Sametime\.rcp.lock
    • if the file isn't found and shows in explorer, check that the APPDATA environment variable is set correctly
      • "set |findstr APPDATA" should return
        • <users>\<your username>\AppData\Roaming

Tuesday, July 9, 2013

T-SQL: Underscores must be escaped in a like statement

Quick description:  a like statement with a string containing an underscore will return strange results.

Description:

This is just a quick note on an issue that caused me some pain today, when looking for a string not like '%string_%' , I was excluding everything containing the string plus subsequent characters.

This means that while I want to see Not like '%string_%' return values like 'string2' but not 'string_2', what I actually get is nothing.

Solution:

Underscores have to be escaped in like statements.  The correct syntax is Not like '%string[_]%', this will return values like 'string2' but not 'string_2' 

For more information see Using Wildcard Characters As Literals (like)

Wednesday, May 22, 2013

Using VMware PowerCLI to get LLDP switch information

This script is based on a CDP version on VMware’s site. CDP is used by Cisco, but most other vendors use LLDP.  The LLDPinfo object is setup differently from the CDP object.  Hopefully this will save a lot of time clicking thru the GUI.

$vmh = Get-VMHost | sort
If ($vmh.State -eq "Connected")
{
  Get-View $vmh.ID | `
  % { $esxname = $_.Name; Get-View $_.ConfigManager.NetworkSystem} | `
  % { foreach ($physnic in $_.NetworkInfo.Pnic) {
    $pnicInfo = $_.QueryNetworkHint($physnic.Device)
    foreach( $hint in $pnicInfo ){
      # Write-Host $esxname $physnic.Device
      if ( $hint.LLDPInfo ) {
        $results = New-Object –TypeName PSObject
        $results | Add-Member –MemberType NoteProperty –Name ESXHostName –Value $esxname
        $results | Add-Member –MemberType NoteProperty –Name DeviceName –Value $physnic.Device
        $results | Add-Member –MemberType NoteProperty –Name PortName –Value $hint.LldpInfo.Parameter[4].value
        $results | Add-Member –MemberType NoteProperty –Name SwitchName –Value $hint.LldpInfo.Parameter[6].value
        Write-Output $results
        }
       }
    }
  }
}

Wednesday, March 13, 2013

How to Flash Google Search Appliance to Dell R710 BIOS

You can reuse a decommissioned Google Search Appliance as a decent server.  The newer Google Search Appliances are Dell R710 PowerEdge servers.  You can differentiate it from the older Google Search Appliances by its square LCD.  The older version had an LCD with rounded corners and was based on a Dell 2950.  You can also use the service tag to look up the server on the Dell support web site and ensure you have a Dell R710.

The two Google Search Appliances I have converted have 48GB RAM, 2 Sockets with 4 cores each, and 6 x 1 TB SATA drives.

The conversion process is pretty simple--but this is about as invasive as you can get--so proceed at your own risk.  You may end up with a brick.

  1. Clear the BIOS password.  Unplug the power.  Open the top cover and look just behind the left CPU and memory area.  There is a double jumper.  You will be moving the back jumper to the alternate position. Then plug in the power and start up the server.  You should see a message saying the BIOS password is disabled.  Power the server back off.  Unplug the power.  Move the jumper back.  The jumper location is marked on the schematic on the inside of the cover if you have any problem finding it.
  2. Erase the hard drive setup.  Power on the server.  Hit Ctrl-R when you reach the PERC controller post message.  Up arrow to the controller and hit F2.  Choose clear configuration.  Confirm and all the logical drives should disappear.  You can make a new logical drive at this point if you wish, or do that later.  Hit escape and then CTRL-ALT-DEL to restart when prompted.
  3. Make a bootable USB drive. You will need a small blank USB thumb drive to boot off of temporarily.  Make the thumb drive bootable using Rufus which is widely available.  Here is one location.  Run rufus, set the file system to FAT32 and the bootable OS type to MS-DOS.  Make sure you have selected the correct drive before you hit start!
  4. Download the latest Dell BIOS.  Navigate to the Dell support site.  Change the Operating System in the Refine Results to BIOS.  Wait a second for the page to refresh and open the BIOS item in the list below.  Click download and choose the non-packaged version which will be named like: R710-060300C.exe.  Copy that file onto the root of the USB drive and give it a short name like r710bios.exe.
  5. Put the USB drive in the R710.  Power the server up and after post completes press F11 to enter the boot bios manager.  Down arrow to the hard drive entry and choose your USB drive from the pop-up.  The server should boot from the USB drive and come to a dos C:> prompt.
  6. type r710bios.exe /forcetype.  You need the forcetype switch to override the normal checks to ensure the server is the correct model--which is why this is dangerous but worked fine for me.  The server will apply the bios and when finished prompt you to press a key.  The server will restart and the Google BIOS messages will be replaced with the stock Dell firmware messages.
  7. Congratulations!  Install your OS!
Edit: Here is Google's page on re-purposing.

Wednesday, March 6, 2013

QlikView Query OLEDB error on Query that runs against Oracle in other products

Quick Description:  A query valid in products like Toad/SQLPlus blows up with a non-descriptive OLEDB error in QlikView
Using the fake query below as an example:
QVTable:
Select
table1.field1,
table1.field2,
table1.field3,
table1.field4,
table2.field5,
table2.field6,
table3.field2,
table3.field7
from
table1 join table2 on table1.key1 = table2.key1
join talble3 on table2.key2 = table2.key2;
The culprit is that QlikView appears to ignore the table specification and sees table1.field2 and table3.field2 as ambiguous, causing an OLEDB error with no further information.   It makes sense considering the fact that a load statement doesn't need to qualify tables from a select statement with joins and would therefore not have a way of knowing which field was being loaded.  It still threw me for a loop troubleshooting a generated query with a pretty long list of joined fields.
Aliasing the fields as below fixes the problem.
QVTable:
Select
table1.field1,
table1.field2 as table1_field2,
table1.field3,
table1.field4,
table2.field5,
table2.field6,
table3.field2 as table3_field2,
table3.field7
from
table1 join table2 on table1.key1 = table2.key1
join talble3 on table2.key2 = table2.key2;

One other thing that can cause an uninformative OLEDB error on a query that runs in other products is a date field that Oracle doesn't like by the time QlikView has interpreted it.  I'm not aware of a way to force NLS_LANG from a QlikView script, but forcing it to char then to date has, in my experience, "fixed" this issue.  For example:
SELECT CURRENT_DATE FROM DUAL
could need to be formatted as
SELECT TO_DATE(TO_CHAR(CURRENT_DATE, 'MM-DD-YYYY'), 'MM-DD-YYYY') FROM DUAL
I'm not proud of some of the things I've done to get dates from Oracle to QlikView