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.


Screw it, I am going to search all tables, all columns!

Ever had the need to find out where data is stored in your MS SQL Database?
I sometimes do and I find it often faster to simply search every table and every column within these table than to try and hunt down the schema definitions. I get a coffee while the script crunches away.  The @SearchStrColumnName option is there mostly for when you search integers, as you may get too many false positives.

Edit: GitHub Gist works awesome, will have to do that for the previous posts too! Just paste the URL, and wordpress will do the rest, in this case display a fully highlighted T-SQL code.
Edit 4/8/2013 – @SearchStrColumnName wasn’t properly escaped before and this parameter didn’t really work.
Edit 4/9/2013 – Added parameter @FullRowResult, this will cause it to return the full row for each hit. This is usefull when you need to lookup or find related info to the search term.
Edit 4/12/2013 – Added parameter @SearchStrTableName, to limit in what tables we are going to search.
Edit 5/2/2013 – Now also searching in type uniqueidentifier (GUID)
Edit 5/22/2013 – Additional numeric data types will be searched.
Edit 1/7/2014 – Can now also search timestamp value (known as rowversion)
Edit 4/18/2014 – Added a Top parameter that works together with @FullRowResult. This is to help limit the return if a search string is found in a table too often

Run a script when windows resumes from suspend/sleep via WMI Event Consumer

You may want to achieve some power savings, but it turns out some services or programs misbehave.
One way around this issue is to run a script when the machine resumes. We can achieve this via two OS native methods:
Task Scheduler on Vista+
WMI Event consumer on Win2K+

I am going to only show my approach for the WMI Event, but I wanted to preempt the comments about task scheduler, yes – I am aware, but there are still two advantages to running it via WMI.
Works for all Windows flavors & it is embedding the script code within WMI, so no visibility to end users that they can modify or disable.
Both methods will run the script/code as SYSTEM, which can be a problem when your issues are related to GUI apps, such as Outlook/Exchange connectivity. This limitation does not exist with the excellent 1E NightWatchman that can run scripts as the logged in user. Without NightWatchman there is no way around programming WTSEnumerateSessions () -> WTSQueryUserToken() -> CreateEnvironmentBlock() -> CreateProcessAsUser() and actually compiling an executable that our script would have to call. Maybe I will post an actual example if some requests do come in.

The main obstacle I found with WMI scripts is the deployment. Likely you want to deploy this solution on a number of machines. The classical way would be to write a MOF file and compile that via mofcomp. I decided to actually create my instances via a VBS script. Note that no matter which approach, the Instances will have to be created while running in SYSTEM. Any consumers created as a regular user would only execute if that user is specifically logged in and is a local admin.

This script will restart a specified Service on your machine. As an example only I am restarting “Wireless Zero Configuration” or in short WZCSVC which exists on all XP machines.
Each time this service stops/starts it will write into the application event log as source EAPOL. This is how you can verify the process worked.

Interesting bits worth noting from the script:
The script will force to use the 64bit version of Wscript when available, even if this script is launched via 32bit process such as the SCCM client. I do this via the WMI method Win32_Process whilst forcing to connect to the 64 bit WMI.
The following code snippet is relevant: objCtx.Add “__ProviderArchitecture”, 64 ‘Load 64bit version of WMI if available. In fact I should create another blog post just on how to escape the bit-ness via WMI of a process.

Use of vbNewLine for the script text. This is needed as VBScript is line aware and I have to somehow add the new line command into WMI. Note that vbCrLf doesn’t work for this scenario.

Remove SQL block comments via regex

My blog is a bit quiet sometimes, and this is because I usually want to post big projects.
However I think I should also post small code snippets as they may help somebody too.
Trevor just asked about how to detect SQL block statements via regex, and I remember I had to use such a regex in order to trim a massive SQL query into 8000 chars for a classic SCCM report.
Use this Perl regex to detect SQL block comments

This will not detect comments denoted by -- which you should not use anyway :)

My next bigger project is to show WMI Event Consumer driven scripts, run scripts on resume and on PC idle btw.