TryCatchFinally.net Some SQL, some .NET, and whatever else

2Mar/160

SQL Agent job fails with QUOTED_INDENTIFIER error

If you have a SQL Agent job that's failing, but the query succeeds when you run it manually in SQL Management Studio, it's possibly a difference between the SQL Server default connection settings and those SSMS uses as the default. In my case, I was inserting from a stored procedure and received the following error:

[SQLSTATE 42000] (Error 1934) INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

To view the default connection options for your SQL Server instance, right-click on your instance in SSMS and select "Properties", and then browse to "Connections" - you'll see something like the following list (on my server, none of these are enabled by default for new connections):

Server Defaults

To compare it to the default settings for your installation of SSMS, click on "Tools" and then "Options...", and then browse to "Query Execution" -> "SQL Server" and view the settings under both "Advanced" and ANSI" (In my case, SSMS was enabling a number of settings by default for my connections that SQL Agent jobs wouldn't have enabled):

SSMS Results ANSI

SSMS Results Advanced

In my case, I just added an explicit "SET QUOTED_IDENTIFIER ON" to the beginning of the script in my SQL Agent job and it resolved the error message. Hope this helps you too!

22May/150

Correcting OGG-01733 in Goldengate – Trail file header file size mismatch

Warning - Oracle/Goldengate support will probably get mad at you if you try this. It worked great for me, but they recommended we reload the data from scratch, so that's probably what they'd recommend for you too. Just know that this is 100% unofficial and unsupported 🙂

Quick summary:

We have Goldengate replication from Oracle 11.2 on Lunux to MSSQL 2012 on Windows, and we ran into an OGG-01733 error "Trail file header file size value {X} for trail file {Y} differs from actual size of the file ({Z})", which caused an ABEND where we were stuck. We opened a ticket with Oracle support and after a week with very little response, the concluded that I should just perform a new initial load on the destination - since the trail files had already been pumped to the destination server and removed from the extract server, they were unable to troubleshoot further.

It turns out the work-around was to open the trail file in a hex editor and manually update the trail file header to make it think it was supposed to be the size it actually was. After saving the file again and resuming replication, it continued on its merry way and applied the transactions without another complaint.

Steps to resolve this error message:

  1. Make a backup of your trail file - you know, since you're editing it and might want a second shot.
  2. Open the report file and make a note of the size the file is currently ("Z") and the size it's supposed to be ("X"). I'll refer to those as X and Z further down.
  3. Use a decimal-to-hex converter like this one to convert both of these values to their hex equivalent (now I'll call them "HX" and "HZ")
  4. Load up the trail file in your favorite hex editing tool - I like using Notepad++ in combination with the HEX-editor plug-in (once the file is loaded, select "HEX-Editor" from the plug-ins menu, and then select "View in Hex")
  5. Perform a search (if you're using Notepad++, ensure the data type is set to "Hexadecimal") for your "HX" value - the size the file thinks it should be. However, you need to search for an even number of digits - if your hex value is an odd number of digits, either drop the leftmost (largest) one or add a zero to the left (I dropped a digit):
  6. Goldengate - Hex Editor

    • Side note: You can see that my trail file size isn't too far into the file - under 300 bytes from the beginning. However, since it's stored in hex, it's not something that's easily viewable in the file (where you will see some file path and server version information if you look to the right where the ASCII is displayed. Also, in my image, the file size is preceded by a quite a few zeroes - my trail files are set to 100MB, but it appears Goldengate supports up to 4GB trail files using the 32 bytes in the header file. Back to fixing this...
  7. CAREFULLY edit the HX value you've found to be the new HZ value - the actual size of the file. In particular, don't move any of the bytes around or add/remove anything, just fix the values you need to change so that the file size is stored in the same location.
  8. Save the file and close it.
  9. Resume replication right where you left off (assuming you made a backup and the edited the original trail file) - it should check the new file size, see the transaction that was previously beyond the file size limit, and then apply it and move on!

Conclusion?

What causes this behavior? I can't find any clear documentation or explanation at all - when searching for this error, the only meaningful links I can find at all are either in an Oriental language and have basic details as well as a dire warning to call Oracle support immediately or a case where somebody receives it on an initial load and the forum's advice is "your table is too small to mess with this - just export it to CSV and reload it that way".

When we looked at the list of trail files, we noticed something particularly odd - the trail files near the offending file all had ascending "last modified" timestamps, as you'd expect, but this file was actually out of order:

05/01/2015  03:45 AM        99,999,462 SV002351
05/01/2015  04:38 AM        99,999,802 SV002352
05/01/2015  08:13 AM        99,999,367 SV002353
05/01/2015  10:09 AM        99,999,936 SV002354
05/01/2015  11:05 AM        99,999,630 SV002355
                                                 <-- File should be right here
05/01/2015  11:41 AM               891 SV002357
05/01/2015  11:47 AM        99,999,462 SV002358
05/01/2015  11:50 AM        99,999,280 SV002359
05/01/2015  11:58 AM        99,999,314 SV002360
05/01/2015  12:09 PM        99,999,910 SV002361
05/01/2015  12:40 PM        99,998,043 SV002362
05/01/2015  01:16 PM        99,999,754 SV002363
05/01/2015  01:34 PM        72,017,446 SV002356  <-- But it's down here
05/01/2015  02:05 PM        99,999,516 SV002364
05/01/2015  02:40 PM        99,999,966 SV002365

The file contained two additional transactions beyond the stated header size and the actual end of the file, and they were both time-stamped correctly to have been located in that file (they were both stamped 10:34AM, along with the transactions that were earlier in the file, and since the server is an hour off because of time zone, they were in the right file).

The fact that it's smaller than the others, and that it's followed by a file containing no transactions (just a header) led me to believe the file was cut short by a network interruption of some kind. We're using a local extract and a separate pump, as we're advised to do, but the connection still drops from time to time. In this case, I can only imagine it was in the middle of committing something, was interrupted, and then somehow these transactions were suspended for some reason and then added to the file later. I can't imagine why, but when they're added, the file header isn't updated.

Hopefully this explanation and work-around have helped somebody else - we pulled our hair out for a week going back and forth with Oracle support and scouring the internet (unsuccessfully) for any relevant information - in the end, going rogue and editing the file was the only way (short of a complete reload) to get things moving again!

22Apr/091

Resolving “STOP 0x0000007B INACCESSIBLE_BOOT_DEVICE” after you make changes to your hard drive configuration

I'd heard that, unlike previous versions of Windows, a Vista installation could be moved to a new computer without any problems, and would just re-detect all the hardware and reconfigure itself. So when I got my new computer, I imaged my boot partition to the new hard drive (it was much bigger than my old one), attempted to boot Vista, and got this error:

STOP: 0x0000007B (0x818B51B0, 0xC00000010, 0x00000000,0x00000000) INACCESSIBLE_BOOT_DEVICE

WTF? After much digging, it turns out that my new motherboard had AHCI enabled by default, whereas my old one didn't. This is a SATA standard that supports Native Command Queuing (NCQ), special power management, and some other features. While Vista supports it out of the box, if no AHCI drives are connected when Vista is installed, it disables the drivers, and any attempt to switch to AHCI mode later will give you a blue screen at boot time. The general consensus online is that you have to reinstall Windows, but you do not!

To resolve the error:

    1. In the system BIOS, switch AHCI mode off. This will probably mean something like "Compatibility mode" for the drive - look for a setting that sounds like it does this, either for the controller or the drive itself. This will allow you to boot into Windows again
      1. a.  If you still can't boot into Windows, you may need to rebuild the boot sector - not as scary as it sounds! Boot the the Vista install DVD, and when prompted, select "Repair installation". After thinking for about 15 seconds, the install should say that it's found the problem and corrected it, and you can reboot - Vista should come right up after that.
    2. Once you're in Windows, load REGEDIT and navigate to [HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Msahci]
    3. Set the value "Start" to "0". This will tell Windows to search for AHCI drives when it boots.

Reboot, and switch your hard drive back into AHCI mode when you do. Windows will boot, detect it, and install drivers. It will probably ask you to reboot again

  1. Resolved! Windows boots on your old Vista installation. Aside from this minor hiccup, Vista did indeed cooperate with my new hardware - it detected everything and came right up. It asked me to reactivate, but since it's a corporate copy, that was no big deal.
16Apr/090

Logon Failure: The user has not been granted the requested logon type at this computer

The printer I use to connected to a domain computer, but the computer I print from is not on the domain, so I ran into this ugly error when I tried to map the printer:

Logon Failure: The user has not been granted the requested logon type at this computer

This occurs because the workgroup computer is trying to pre-connect to the domain computer, and since it's un-authenticated, it connects as "Guest". Under normal circumstances, the guest account is disabled or, at least, it has been denied rights to connect over the network. Here's how you remove this restriction:

  1. Download and install the Windows Resource Kit Tools (http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe). On Vista, you may receive an error that these tools are not compatible, buy you can ignore it - at least in this case, it doesn't cause any problems, and currently. There's no new version of the tools available.
  2. From the start menu, click "All Programs" -> "Windows Resource Kit Tools" -> "Command Shell" Run the following commands, in this order (and they're case sensitive):
    1. net user guest /active:yes
    2. ntrights +r SeNetworkLogonRight -u Guest
    3. ntrights -r SeDenyNetworkLogonRight -u Guest

That's it! You've enabled guest access, so non-domain computers will at least have the ability to connect, though they'll be restricted to the permissions granted to the local "Guest" account, so you're not opening up much of a security risk.

8Apr/094

Join an Active Directory domain and keep your local profile intact

Recently, I had to join a number of computers in a small office to a domain, but the users all had local profiles that they wanted to keep. Things were a mess - some people's usernames were the Last name, first initial (the username format I'd chosen for the domain), some were using their full names, and some were using the local administrator account. When I added these computers to the domain, their "domain user" would log in, and would create a new, empty profile. To avoid this took a few extra domain-joining steps, so I wanted to detail them here.

A note: On the computers that were using the local Administrator account as their main login, I had to create a new user and make them a local admin. I just called this user "Transition", and deleted it once the process was over.

And now for the steps:

  1. Join the computer to your domain, and grant the new domain user local administrator rights before rebooting. Reboot, and log in using the new domain user. This will create a new, empty profile with that user's domain login.
  2. While still logged in as the new domain user, take ownership of the old, local profile folder. To do this, right-click on the folder, select "Properties", go to the "Security" tab, click "Advanced", and then the "Owner" tab. You can set the owner to either the local admins group or the current user - set it to the current user. You must be a local administrator to take ownership (from step 1).
  3. Log out, and log in using either the local administrator account or the transition account you created.
  4. At this point, you can revoke local admin rights from the domain user if they won't need them. They were only needed to take ownership in step 2.
  5. Open REGEDIT, select the "HKEY_Users" branch, and select "Load Hive..." from the file menu. In the user's profile folder, there's a hidden file called "NTUSER.DAT" - that's the one you want to load. Make sure you're loading the file from the old profile, not the one that was created in step 1. You can call it whatever you want when you load it - it doesn't matter. Also, make sure you've made a backup of this file before you edit it.
  6. Right-click the user branch you just loaded, and click "Export...". Export it as a REG file on your desktop.
  7. Open the registry file you just created in either Notepad or Wordpad (I find Wordpad faster for the this step, but it doesn't really matter). Search for occurrences of "\OLDPROFILEFOLDERNAME\" and replace them with "\NEWPROFILEFOLDERNAME\". I've generally found about 100 references, but it depends on the size of your registry. Also, make sure you convert 8.3 folder names as well - "\OLDPRO~1\" should become "\NEWPRO~1\"!
  8. Save the file after your found/replaced all the occurrences. Double-click the REG file to load it back into your registry (into the user's hive). You'll get a warning that not all data was loaded because some keys were in use - that's fine.
  9. Since not all keys were imported, we'll need to fix a few folders by hand. Select the user's hive, and "Find" any occurrences of the old profile path, replacing them with the new path.
  10. With the main user hive folder selected, go to the "File" menu and select "Unload Hive". The changes are saved automatically, which is why it's important that you made a backup in step 5. Close REGEDIT.
  11. Rename the domain user's profile folder to "Username.Empty" (since it's essentially a blank profile), and rename the user's local profile to "Username", which matches the folder name of the profile that was created in step 1.
  12. Log out, and log in as your domain user, enjoying your old profile just as you left it!

This process can be repeated for as many users as you'd like to transition to the new profiles, and you should maintain every one of the settings for your programs. In fact, I've never had a program even realize something is afoot, though I've only done this on a half-dozen computers.

Please let me know if you have any feedback, and I'd be interested to know of any experiences you have trying this out!