fullparam

September 4, 2015

New Challenge @ Google

Filed under: Uncategorized — fullparam @ 10:44 am

I took a new role with Google in database analytic.
I have been heavily Microsoft based until now – so this is a big change.
Hopefully I can update this blog with new scripts eventually but many things here are only in-house.

June 2, 2015

On-Demand 1E NightWatchman Actions

Filed under: Uncategorized — fullparam @ 10:18 am

Made a blog post on the 1E website here:
http://www.1e.com/blogs/2015/06/02/on-demand-nightwatchman-actions/

The methods used in the tool I wrote can be used easily to do any WMI action on a set of machines.
I will see if I can release a more open ended version for the ConfigMgr console on here.

March 31, 2015

SQL: Select Nth rank of something. Three approaches.

Filed under: SQL Server — Tags: , , — fullparam @ 5:42 pm

Common Question on Forums, how to get the 3rd highest salary or similar. Also a common job interview question perhaps?
I am using the AdventureWorksDW2012 database. Tests were done with SQL Server 2012 SP2

I run each query twice. Once with the base table and once with a covering Index.
Here is the covering Index:

CREATE NONCLUSTERED INDEX _E_DimEmployee_BaseRate
ON [dbo].[DimEmployee] ([BaseRate])
INCLUDE ([FirstName],[LastName])

Correlated subquery:

SELECT FirstName, LastName, BaseRate
FROM DimEmployee e
WHERE (SELECT COUNT(DISTINCT BaseRate)
    FROM DimEmployee p WHERE e.BaseRate <= p.BaseRate) = 4

Why is this a good answer? It’s not really but this will work on any SQL implementation.
It’s fairly slow, it will do a lot of look ups.  The subquery is evaluated every time a row is processed by the outer query. This query uses dense ranking and can return multiple rows.

Here are the IO and time stats.
Without Index:
Table ‘Worktable’. Scan count 564, logical reads 1337, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DimEmployee’. Scan count 54, logical reads 2646, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 20 ms.

With covering Index:
Table ‘Worktable’. Scan count 349, logical reads 907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DimEmployee’. Scan count 54, logical reads 233, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 13 ms.

Double Order By with TOP statement:

SELECT TOP 1 FirstName, LastName, BaseRate
FROM ( SELECT TOP 4 FirstName, LastName, BaseRate
    FROM DimEmployee ORDER BY BaseRate DESC) AS MyTable
ORDER BY BaseRate ASC;


Why is this a good answer? Because it is an easy syntax to remember.
Let’s look at the subquery, which returns the N highest salaries in the DimEmployee table in descending order. Then, the outer query will re-order those values in ascending (default) order, this means the Nth highest salary will now be the topmost salary. Keep in mind that the TOP statement is MS SQL server specific. MySQL would use LIMIT 1 for instance. In addition this solution cannot do DENSE ranking and only returns one row even if two employees share the same BaseRate.

Edit June 2015: The addition of LIMIT/OFFSET on SQL Server 2012 made answer obsolete. The syntax for LIMIT/OFFSET has been added on the bottom of this post.

Here are the IO and time stats.
Without Index:
Table ‘DimEmployee’. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 5 ms.

With covering Index:
Table ‘DimEmployee’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 0 ms.

Use Windowing function:

SELECT FirstName, LastName, BaseRate
FROM (SELECT FirstName, LastName, BaseRate, DENSE_RANK() OVER (ORDER BY BaseRate DESC) Ranking
FROM DimEmployee) AS MyTable
WHERE Ranking = 4

Why is this a good answer? Because it performs the best – performance is king. The Syntax is also ANSI SQL however of the “Big 3” only Oracle and MS are using it. In addition you can chose to use ROW_NUMBER, DENSE_RANK or regular RANK.

Here are the IO and time stats.
Without Index:
Table ‘DimEmployee’. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 2 ms.

With covering Index:
Table ‘DimEmployee’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
elapsed time = 0 ms.

Edit: June 2015
Use OFFSET (SQL Server 2012):

SELECT FirstName, LastName, BaseRate
FROM DimEmployee e
ORDER BY BaseRate DESC
OFFSET 3 ROWS
FETCH NEXT 1 ROWS ONLY

Why is this a good answer? Because similar Syntax exists on the other platforms.
Performance wise it runs as well as the windowing solution.

January 2, 2013

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

Filed under: Scripting, Troubleshooting, Windows — fullparam @ 9:45 pm

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.

December 19, 2012

Getting the Language pack lp.cab out of the downloadable exe files.

Filed under: Uncategorized — fullparam @ 5:01 pm

You can get them out by running the EXE, as it extracts/creates the cab file. In my case the German package windows6.1-kb2483139-x86-de-de_acb9b88b96d432749ab63bd93423af054d23bf81.exe
However the issue, I had found, is that the EXE quits very fast and deletes the cab file right away.
I considered running PSSuspend against it, but the issue is that this would still be a game, as the cab file gets built slowly, but deleted very quickly if the OS refuses the install.
So the next best solution is NTFS permissions, simply create a temporary DACL for EVERYONE with DENY for “Delete Folders and Files”.
This will leave your lp.cab ready for the grabbin :)

September 7, 2012

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

Filed under: Scripting, SQL Server — fullparam @ 7:21 pm

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

Filed under: General, Scripting, WMI — fullparam @ 6:20 pm

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.

April 10, 2012

Bitlocker refuses access to your disk due to Bios Upgrade.

Filed under: Troubleshooting, Windows — fullparam @ 1:59 pm

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.

March 7, 2012

Remove SQL block comments via regex

Filed under: General, Regex, Scripting — fullparam @ 1:25 pm

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
/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/

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.

November 4, 2011

Side by Side assembly issues. Error 1935.

Filed under: General, Troubleshooting, Windows — fullparam @ 6:21 pm

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.

Older Posts »

Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.