Joining on objectSID or using objectSID in a WHERE Clause

This one drove me crazy for the better part of a day.
When SQL Server shows you an objectSID it looks something like this:

0x010500000000000515000000317B347F75771D5BB254B44007080000

If you take that string and type something like:

SELECT * FROM viewADEmployees WHERE objectSID = 0x010500000000000515000000317B347F75771D5BB254B44007080000

It works just fine.

But if you take that string and stuff it in a VARCHAR (or any other type you think might be appropriate) you get an empty result set.

So I thought, duh, I need to cast it to the same type as objectSID. It only took a little while to figure out that objectSID is of type VARBINARY. It turns out, however, that simply casting the string to VARBINARY does NOT work! In fact I could not find any CAST or CONVERT that did the trick. I performed more casts than the entire fishing population on opening day of trout season.

Google was of no help either. I was on my own on this one.

I finally stepped back reasoned it out:

1. objectSID is VARBINARY
2. I need to convert my comparison value to VARBINARY
3. I should create a user function that takes a string representation of a series of hex byte values
4. each two-character pair of hex digits reprsents a single binary byte
5. I need to build up a VARBINARY value by successively converting the pairs to integers and then adding them to my VARBINARY return value

This is my resulting function which took a suprisingly short period of time to conceive, write and debug :

CREATE FUNCTION dbo.fncFormatSID (@vchFromSID VARCHAR(300))
RETURNS VARBINARY(85)
AS
BEGIN
DECLARE @binOut VARBINARY(85);
DECLARE @intLen INT;
DECLARE @intIndex INT;
DECLARE @intLow INT;
DECLARE @intHigh INT;
DECLARE @vchLow VARCHAR(1);
DECLARE @vchHigh VARCHAR(1);

SELECT @intLen = LEN(@vchFromSID)
SELECT @intIndex = 2

WHILE (@intIndex < @intLen)
BEGIN

SELECT @vchHigh = SUBSTRING(@vchFromSID, @intIndex, 1)
SELECT @vchLow = SUBSTRING(@vchFromSID, @intIndex + 1, 1)

IF(@vchLow >= '0' AND @vchLow <= '9')
BEGIN
SELECT @intLow = ASCII(@vchLow) - ASCII("0");
END

IF(@vchHigh >= '0' AND @vchHigh <= '9')
BEGIN
SELECT @intHigh = ASCII(@vchHigh) - ASCII("0");
END

IF(@vchLow >= 'A' AND @vchLow <= 'F')
BEGIN
SELECT @intLow = ASCII(@vchLow) - ASCII("A") + 10;
END

IF(@vchHigh >= 'A' AND @vchHigh <= 'F')
BEGIN
SELECT @intHigh = ASCII(@vchHigh) - ASCII("A") + 10;
END

IF @binOut IS NULL
BEGIN
SELECT @binOut = CONVERT(VARBINARY(1), (@intHigh * 16) + @intLow) ;
END
ELSE BEGIN
SELECT @binOut = @binOut + CONVERT(VARBINARY(1), (@intHigh * 16) + @intLow) ;
END

SELECT @intIndex = @intIndex + 3

END /* While */


RETURN(@binOut);

END

Accessing Active Directory From SQL Server

Figured out how to query active directory from SQL Server. Its pretty well documented but the first step is to create what is know as a 'Linked Server'. This involves executing one line of code in Query Analyzer:

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Then you can execute stuff like this:

SELECT * from openquery(ADSI, 'SELECT cn,objectsid FROM ''LDAP://atlas/CN=Users,DC=doublestarinc,DC=com''')

I'm not sure if its possible to perform a join directly with this. To save time I created a view using something similar to the above SELELCT statement. Then using that view we can all join to our heart's content.

Passed 70-316 Today !

Scored a 920 !

Didn't think I did better than the 70-315 test but somehow I did.

Now on to 70-320.

Whacky, Nutty Keyboard Antics

My keyboard layout got whacked out for Office products only.
a would print q, comma would print : stuff like that.

Solution:

All Programs -> Microsoft Office -> Microsoft Office Tools -> Microsoft Office 2003 Language Tools.

All Programs -> Microsoft Office Tools -> Microsoft Office XP Language Tools.

In both locations found the French to be the Enabled and Active Language.

Deleted it from the the Active Lists.

Viola!

Tools I Used Today

Made my first professional forray into VBA / Access Front end development.

Yuck!

Did a good job though.

Passed 70-315 !

Scored a 905 on the Developing Web Applications with Visual C# .NET.

Tools I've used recently

Wrote my first Fortran Program!
My nephew is taking a course in it and was having trouble.
I found an online Fortran 90 reference and quickly had the program working.
 
I even pulled a Unix command line tool out of my rear-end to kill a couple of processes (ps).
 
When done, he called me a 'Programming God'.
 
Boy, am I now all puffed up.
 
 
Successfully debugged a VB / Windows Scripting Host app.  
 
cscript.exe

How to Debug Classic ASP in VS.NET

Make the following configuration mods:
 
 
In VS.NET:    
 
     project - properties        
            configuration properties            
                    debugging
                         - enable ASP debugging
                         - enable ASP.NET debugging
 
 
 
In the IIS snap-in:    
 
      default website - properties        
             home directory tab            
                  configuration                
                        app debugging                    
                                - enable ASP server-side script debugging    
 
 
Add IIS process account to Debugger Users group:    
 
       In Computer Management snap-in:        
                       System Tools            
                               Local Users and Groups                
                                      Groups                    
                                           Debugger Users - properties                        
                                                  Add user IWAM_machine-name (the "Launch IIS process  account")

Making a Web Apps Browser Window Maximized at Start Up

Finally figured out (first time I tried) how to make the browser maximize upon application start:

in html HEAD add this script/function block:

script lang="javascript"
function doOnLoad()
{
window.resizeTo(window.screen.availWidth, window.screen.availHeight);
window.moveTo(0, 0);
}
/script

Then in the BODY tag add this:

onload="javascript:doOnLoad()"

poof! works.

Hooking up Client Side Jave Script with MyButton.Attributes.Add()

The Session[MadeChangesIndex] stores a boolean indicating if any changes have been made to the lists/data grids.


In the Page_Load() Handler added:


if((bool)Session[MadeChangesIndex])
Cancel_PB.Attributes.Add("onClick", "return(HandleCancel(true));");
else
Cancel_PB.Attributes.Add("onClick", "return(HandleCancel(false));");


In the HEAD tag of the HTML added:

function HandleCancel(ListChanged)
{

if(ListChanged)
return(confirm("Are you sure you want to Discard ALL of your Edits? This includes any changes to the Work Item, Participant and Client Lists."));
else
return(confirm("Are you sure you want to Discard ALL of your Edits?"));

}

Works Great!

What I'd like to do is figure out how to walk the elements of the form and determine if any of the controls have been modified and exit without confirmation if nothing has changed.

ASPNET User Security, NOT!

Migrating laptops and created new virtual directory for existing app coming from old box.

FORGOT TO TURN ANONYMOUS ACCESS OFF! This caused application to execute with inadequate permissions and triggered several exceptions which the exception manager couldn't publish (triggering more exceptions).

I chased my tail trying to give ASPNET adequate permissions for about an hour until I realized I had forgotten to turn off ANONYMOUS access OFF in ISS Mgt. Console.

Idiot!

Tools I Used Today

TOAD - Tool for ORACLE Application Developers
Tweaked Package and Procedure Bodies

Visual Studio .net 2003
Deployed Web Service to test site.
Deployed Application to test site.
Had to reestablish Web Reference from localhost to test.
Had to changes several localhost references to relative paths.

Tools I Used Today

Visio
Did some UML Modeling and Code Generation for my DBProvider Factory Factory.

After building and tweaking the final results in VS .net 2003 I reverse engineered the project. Pretty Cool.

VS .net 2003

MS SQL Server 2000 Enterprise Mgr.
Oracle Enterprise Mgr.
ODBC Administrator

Created test databases and datasources for the Data Provider Factory Testing


NUnit
Kicked some more but with this awsome tool developing tests for my DB Provider Factories which are now working like a charm.

I've implemented factories for the .net SQLServer, Oracle and ODBC data providers.

All my study of design patterns is starting to bear fruit. I'm really excited with the results.

Next I plan to retrofit my DBObjects to use the Provider Factory Factory and then create a Provider Factory Factory for the Compact framwork which will implement the SQL Server CE and SQL Server providers.

Getting SQL Server CE Server Tools to Install

1. Downloaded and installed SQL Server 2000 CE
At this time got error message about version compatibility with SQL Server 2000 SP1 and above.


2. Downloaded and installed SP3 for SQL Server 2000

Ran: D:\My Downloads\SQL Server 2000 SP3\TEMP\x86\setup\setupsql.exe

3. Downloaded and installed SQL Server CE SERVER TOOLS for SQL Server 2000 SP3

Tools I Used Today

.net Compact Framework
SQL Query Analyzer for SQL 2000 CE
The System.Data.SqlServerCe Namespace

My First Foray Into the World of the .NET Compact Framework

Created my first CF .net app and published it to my pda. Voila, it worked!
Visual Studio does EVERYTHING for you, in terms of loading the required files:
The framework!
The apps assemblies etc.

I subsequently downloaded SQLServer 2000 CE and added a reference to it from my app. I added some code to create a DB, create a table, write data to the table, read it back out and display it on the screen. Worked first time!

After some interation of the build, load and run process the deployment to the pda started failing due to a sharing violation. I was unable to delete the .exe and/or its folder but I WAS able to rename the folder and got back on track!

ThreadAbortException rears its head again

Response.Redirect will always throw a
ThreadAbortException that will be silently handled by the ASP.NET runtime.

You can move your Response.Redirect outside the try/catch block or leave it there and rethrow the exception.

Mobile Developement and Sometimes Offline Scenarios

Here's an msdn article regarding synchronizatio strategies for Handheld apps:

http://msdn.microsoft.com/mobility/prodtechinfo/devtools/netcf/default.aspx?pull=/library/en-us/dnppc2k3/html/sometimesoffline2.asp

Tools I used today

VS .net 2003
Oracle Enterprise Mgr.

Tools I Used Today

Oracle Enterprise Mgr.
Tweaked the CareerPathSalesPositionNumber Procedure to return a Number instead of a Cursor Reference so now the declaration looks like this in the Package Declaration:

PROCEDURE CareerPathSalesPositionNumber(InParm IN NUMBER, OutParm OUT NUMBER);

In the package body the OPEN CURSOR FOR SELECT... is replaced with A SELECT INTO which looks something like:

SELECT DISTINCT
columnx INTO outParm
FROM
tablex
WHERE ....


VS .net 2003
Grabbing the return value is slightly different than using the Cursor Reference to fill a DataTable:

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("InParm", OracleType.Number).Value = iInParm;

cmd.Parameters.Add("OutParm", OracleType.Number).Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

iOutParmVal = Convert.ToInt32(cmd.Parameters["OutParm"].Value);



SQL+Plus Worksheet

Finished slogging test data into the DB. What a pita!

Getting Reconnected to Visual Source Safe after Working Disconnected

Ran into a small head scratcher today.

I'm getting ready for a demo which will have to run without an Oracle DB connection. To test that everthing is a go, I undocked my laptop and ran the app. A small tweak was required and when attempting to make the change, VS .net complained about being disconnected from VSS. It asked if I'd like to do a 'virtual' check out and work in disconnected mode. I agreed.

Problem:

When I redocked VS .net was not smart enough to switch modes and most of my Source Control menu options were missing. Specifically, Check-in was gone and a couple of files were flagged as checked out!

Solution:

From VS .net, on the File menu, point to Source Control, and then click Change Source Control. Highlight the VSS Server and click on 'Connect' Icon in upper left of dialog. :-)

Tools and Technologies Catch Up

Just to catch up Here's a List of Tools and Technologies I've Used from Jan 5th 2004 to May 9th 2004:

Microsoft Visual Studio .NET 2003
C#
ASP.NET Mostly
A few Windows Forms Utility Apps

Microsoft SQL Server 2000 Enterprise Mgr.
Almost every day

Microsoft SQL Server 2000 DTS
ETL Assignment

Microsoft SQL T-SQL Stored Procedures

VB 6
Maintain ASP components

XML

XSLT

Maintain / fix rendering of ASP web page(s)

XMLSpy
Great Product!

NUnit Unit Test Facility for .NET
Another Great Product and its FREE!
Testing components is now rapid! Actually, RETesting components is now super rapid!


HTML
JAVA Script

Maintenance of significant amounts of client side validation dynamic
DOM / Control creation, activation

ASP.NET
ASP.NET Data Web Controls
ADO.NET

Execution of stored procedures via ADO.NET Both Oracle and SQL Server

LDAP / Active Directory Access via .NET System.DirectoryServices
DirectorySearcher Class

Softerra's LDAP Browser version 2.1 Christmas Edition
Another excellent, FREE product
Visio
Created UML Static Class Diagrams. Used the diagrams to generate
code.

CSS

Tools I used today

Oracle Enterprise Mgr.
Finished Creating the Stored Procedures in the Package Body

VS .net 2003
Finished switching from 'embedded' SQL to calling the Stored Procs

SQL+Plus Worksheet
Described the tables requiring test data propagation and cut/pasted the results into my data propagation script. Subsequently cut and pasted the data in the script to SQL+Plus Worksheet and executed, fixed errors, more cutting and pasting ...

Test Data Propagation Woes

Have now wasted several hours downloading and evaluating SQL Test Data Generators.
The only one that actually 'worked' doesn't allow you to actually write the results to the DB. The results are held in memory where they can be viewed but you can't commit them to disk. The data LOOKS really good but its difficult to tell if all of the relationships are sound plus you don't know what disasters lurk in the facilities that actually do the writing to the db. It'll take some serious coin to find out, $1,600.

The product is GS Data Generator from Global Software Solutions

Two products that showed promised but ultimately failed miserably:

TurboData from Canam
Advanced Data Generator Pro Edition from Upscene Productions

I've gone back to creating the data by hand.