Microsoft Access Database Engine: External table is not in the expected format. (when opening an excel file that is read locked)

Another quick and dirty post. Because it took me over an hour to get to the issue.

VBS Error: Microsoft Access Database Engine: External table is not in the expected format.
This error occurs upon opening the ADODB.Connection

Turns out the issue was because I had read some stuff of the excel via OpenTextFile, previously but forgot to close that.
So the error occurred because the file was read locked and not because “External table is not in the expected format.”
Shows just how much time you could save with the “right” error message.

Note1: This is most likely just one possible cause of this generic error message.
Note2: if the file actually doesn’t exist the error message is much better:
Microsoft Access Database Engine: The Microsoft Access database engine could not find the object 'Scope Information$A1:B65535'. Make sure the object exists and that you spell its name and the path name correctly. If 'Scope Information$A1:B65535' is not a local object, check your network connection or contact the server administrator.

Advertisements

Windows bitlocker drive encryption recovery key entry

Seen this discussed, and actually the DOS based bios update tool warns about it, but for whatever reasons the Lenovo team decided not to show that warning on the Windows tool.

As I did not have access to the recovery key due to it being the weekend and the internal IT being unavailable I had to look at alternative recovery solutions. The Windows bootup greets me with the message:
Windows Bitlocker Drive Encryption Recovery Key Entry
Enter the recovery key for this drive

Obviously the big question is: Will restoring to the old Bios version do the trick? The answer is YES.
Now let’s hope your vendor has a DOS/CD-ROM/Floppy disk based alternative to modify the bios, because that is what you will need as you are locked out of your Windows.

Since I do not own a CDR-Drive, I’ve chosen to use a USB stick instead.
I’ve used XBoot for this purpose and found it worked well, here is the process:
1.) Get XBoot and open the program.
2.) Drag and drop your .iso file into it, and in the wizard select: Grub4dos ISO Emulation
Unfortunately I’ve found you can only add one Grub4dos ISO Emulation .iso file successfully.
3.) Boot up the affected machine via this USB key and downgrade the BIOS.
4.) Success? If not you may have chosen the wrong bios version and want to replace the created .iso file on your memory stick with another bios version.
5.) Once you can boot again, Suspend Bitlocker encryption via the Control Panel, re-do the Bios upgrade and after the reboot resume Bitlocker.

Side by Side assembly issues. Error 1935.

Affected OS: Vista, 2008, Win7

Error: When attempting to install redistributable (vcredist_x86.exe) we get:
Error 1935.An Error occurred during the installation of assembly ‘Microsoft.VC90.ATL,version=”9.0.30729.4148″,publicKeyToken=”1fc8b3b9a1e18e3b”,processorArchitecture=”x86″,type=”win32″‘. Please refer to Help and Support for more information. HRESULT: 0x8007054F

When attempting to install 1E Wakeup Server 6 we get:
Microsoft.VC90.CRT,processorArchitecture=”x86″,publicKeyToken=”1fc8b3b9a1e18e3b”,type=”win32″,version=”9.0.30729.4148″ could not be found. Please use sxstrace.exe for detailed diagnosis.

Further Details:
I have established that the actual DDL’s are here:
C:\Windows\winsxs\x86_microsoft.vc90.crt_1fc8b3b9a1e18e3b_9.0.30729.4148_none_5090ab56bcba71c2
C:\Windows\winsxs\x86_microsoft.vc90.atl_1fc8b3b9a1e18e3b_9.0.30729.4148_none_51ca66a2bbe76806

The manifests are also present. I’ve created the sxstrace log file, however these does not indicate any errors.  I am unsure why these trace logs did not show any error – not helpful.

MS has a KB for error 1935: http://support.microsoft.com/kb/970652
This KB did not help: Under HKEY_LOCAL_MACHINE\COMPONENTS, the offending keys do not exist.

The resolution:
Windows has a further log file called CBS.log for the component -based servicing also known as the trusted installer, which managed the installation of SxS. This log file indicated the presence of the pending.xml file, however the CBS.log also complained that the action cannot be found in the registry in the path the above KB discussed. It appears this server had an issue were the registry was rolled back but the pending.xml file remained. Manually renaming/deleting this pending.xml brought the CBS back into action. As with any trusted installer files you first have to wrest control of the file via taking ownership and setting new DACLs.

CBS.log file extract:
2011-11-02 13:57:46, Error                 CSI    0000e320 (F) Could not find pending.xml identifier in registry.

2011-11-02 13:57:46, Error                 CSI    0000e321@2011/11/2:18:57:46.567 (F) d:\longhorn\base\wcp\componentstore\com\store.cpp(354): Store corruption detected in function `anonymous-namespace’::QueryPendingXmlIdentifier expression: 0
RegistryCorruption on resource [50]”\Registry\Machine\COMPONENTS\\PendingXmlIdentifier”[gle=0x80004005]

Maybe it helps someone, also interesting that the error text from the CBS.log showed no goggle results at the time, looks like this log file is not very well known when it’s clearly very helpful.

Cannot create an instance of ole db provider “oraoledb.oracle” for linked server

You know you’re dealing with something annoying when Google suggestions cannot wait to auto complete your error message!
Yet this is how I spent a good three hours of my life on earth, just chasing forums & blog posts, even translating a Russian one into English.
I needed that Oracle data, dammit. Sufficient to say that none of the suggestions worked, I kept receiving “Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server”

Ok, lets go back to the start and list the environment.
Using my local SQL Server 2008 Express R2 on my Win7 x64 workstation, I had to query a Oracle g10 Database on one of my VMWare box.
Yes, you could use the MSDAORA provider by Microsoft for Oracle DBs, but this provider is deprecating and does not exist in x64 flavor.

It all started when i downloaded the ODAC pack for Oracle g10 (ODAC10203x64.zip). This fails to install: Java(TM) 2 Platform Standard Edition binary has stopped working
At least there are a few confirmations that it’s save to use the OLEDB Oracle g11 driver, which uses an “Xcopy” deployment. So I download ODAC112030Xcopy_x64.zip and extract it. Grab an oracle login here via bugmenot. As of Sep 2012, this is still the latest version.
Fair enough, it contains clear instructions and installs easily. I get the provider in the list of providers on my SQL server management studio – Awesome, i thought I was done!

Not so, not by a longshot, now the pain was just starting! First I went into the properties of the provider set the AllowInProcess to enabled.
Now attempting to link the server failed with the error message that I have decided to best be the title of this post. Now this post is not going to list all the possible causes there are, hell I never really want to touch this subject again after today, but I want to “broadcast” the thing that made it ultimately work for me.

Let me just say that I’ve attempted each suggestion that I found sensible, those that I think everyone else that has this issue should repeat are as below:
1.) Set NTFS permission to the install directly to Authenticated Users with Read/Execute.
2.) Enable AllowInProcess for the Provider as outlined above.
3.) Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI and remove the three values there (create a backup export if you are so inclined). This step I am the least certain about, however why would you want to reference these 3 completely outdated dll’s in the registry?)
4.) Create the Sub folder NETWORK\ADMIN in your install directory and copy a working tnsnames.ora into it(Maybe from another machine that has the full feature Oracle SQL Developer installed)

Nothing helped – so what does one do at such a point? Unleash the furry of Procmon.exe!
This tool from Sysinternals/Mark Russinovich is the best, and my only regret that day was not launching it earlier instead of scouring Google and going insane.
I’ve limited Procmon to just sqlservr.exe, as it’s the SQL Service itself that loads/handles the providers and not the ssms.exe. Also of note is that the sqlservr.exe is a 64bit process while the management studio is still just 32bit. As the server service is loading the provider, and the service process is 64bit, the provider must also be available in 64 bit format.

The ODAC112021Xcopy_x64.zip was installed to C:\Oracle.
What Procmon showed me however is that sqlservr is attempting to find the oci.dll in any folder but his! (It iterates through the %Path% sysvariable). When it finally gives up on find the dll, the SQL Service is in a unstable shape and the only way to stop the service was to kill it via taskmgr/procexp.
Clearly I can see that the “xcopy” deployment – while not giving me any error messages – it also did not set the PATH variable!
And this is what this post is really about… adding C:\Oracle and C:\Oracle\Bin to the Path variable or maybe it’s about
employing investigative tools earlier in the process instead of relying on your search engine skills.

sqlservr.exe can now find the relevant DLL’s. The OCI.DLL in the root and the OraOLEDB11.DLL in the Bin subfolder.
At this point I could query the database! If you did my steps as above and you still get the same error, I strongly suggest using Procmon.exe as I have instead of jumping to the next search result.

I’ve left out the part where it initially could not find the tnsnames.ora file and it attempted to connect via the default oracle port instead of the custom one, and as an additional note if sqlservr cannot locate the tnsnames.ora in the first attempt, it’s not going to retry again until you restart the SQL service. I was actually surprised to even see the port connection attempt in procmon, it seems this tool just keeps getting better.

So this is what I’ve done on my “Day off” from work…. I hope you were reading this while at work yourself and can get back to the real work & fun stuff, writing queries!

Your windows 2003/2008 dhcp server will only listen on one ip per nic

Yes, there are known issues with Firewalls, DHCP Server not authorized  or Scope not activated or simply the broadcast not reaching the server.

A lesser known issue is that per NIC you can effectively only have one Scope and it must be a scope that contains the first IP address of the NIC.
Easy to verify the first IP address, it’s the one listed on top of the IP addresses in the IP Settings Tab.

Another good check is via the DHCP servers bindings:

If your scope does not contain any of these IP addresses and you are not using DHCP Relay Agents, it will never give out an offer for it.
I did not find an easier way to change the order of IP addresses then  re-add all of them.

You can do a quick test with portqry.exe (Windows Support Tools):
portqry -n -e 67 -p UDP

From the IP address example above:
First IP: 172.20.20.20

Attempting to resolve IP address to a name…
IP address resolved to REG-DC2.reg.intra
UDP port 67 (bootps service): LISTENING or FILTERED

Now the Additional IP: 172.80.80.20

Attempting to resolve IP address to a name…
IP address resolved to REG-DC2.reg.intra
UDP port 67 (bootps service): NOT LISTENING

You can see the DHCP server is not listing on this port and IP.
If I was to create only the scope 172.80.80.0/24 it would appear to me like my DHCP server was not working at all, I would be able to capture the traffic using
Wireshark/Netmon and see the DHCP Discover broadcast, but no offer from my Server.
If I now create the additional scope 172.20.20.0/24, all my clients would receive a lease from that scope and 172.80.80.0 would remain dead.

Tested on both Windows 2003 and 2008 but all the screenshot are from 2003.