All posts by andre

Using EXISTS with variable tables

I ran into a need to use EXISTS with a variable table yesterday and thought it might be nice to make it available to others.

As you can see all you need to do is enclose the variable table in [ ].

TSQL Pull file into binary field

Ok so i needed a fast way to import a file from the filesystem straight into a binary database field
It couldnt be easier with OPENROWSET.
Here is how

simple as that
Ok so I have an update to make here. In most cases the file your trying to pull up from the filesystem does exist, but wouldnt it be nice to make sure it does before you go ahead, well here’s how do it using xp_fileexist.
I’ve also left the snippet that will tidy up filename UNC’s of the ‘ character. If you tidy it before the file exist test, the test will fail since it already does this. Be sure to leave it between the exist test and the openrowset call.

GridView with Nested GridView in

Have you ever needed to nest controls within gridviews
Its actually pretty simple these days. At first i was a bit stuck but having now successfully nested many types of controls within a gridview im fairly confident its possible to nest anything, and likely down to an infinite nest tree.
So lets keep it simple
What we are going to do is have a primary gridview (grid1), with a few button controls on each row (btnexpand btnselect btndelete), a nested gridview with a couple of buttons (btnselect btndelete) and a nested control within it.
I will show you how to wire up events to handle the nested controls.
You are most likely better served by copying this code out into your favorite editor, visual studio – scite whichever your preference.

Firstly Lets look at the button controls in Grid1. The grid routine rowdatabound wires up the button control command argument to hold the ID of the record like so, this gives us a hook back to the data.

The grid rountine rowcommand is used to handle the button events.

This covers the primary grid button routines, now lets look at the nested grid buttons, how do we handle those. In the same fashion, the nested grid data would likely have been bound when we had clicked on the primary grids select button. We are only concerned at handling the nested grid button wireup and click event handler.

Simply set the “OnCommand” property of the button to our custom subroutine

Note the routine required parameters sender and e.

And that as they say is that. You have now learnt how to wireup control events through nested gridviews.

bb handheld update error

If you have an enterprise activated device and you want to update the handheld software, you might bump into this little error

You cannot update your software using this system as your device is currently activated on BlackBerry Enterprise Server or BlackBerry Professional Software. Please contact your administrator for updates. Or check for updates from your carrier service provider

you have two choices
1. remove the it policy on the handheld
2. edit the it policy on the BES to allow non enterprise upgrade (this does lead to reactivation)

I opted for option 2 since i manage our BES server. those of you who opt for option 1 need to continue googling

So for option two.
1. we edit the policy on bes Refer to instructions
2. Roll the IT policy back out to the device from the bes manager
3. Reboot the device
4. download the latest handheld firmware to your pc one size fits all
5. install the latest firmware package on your pc
6. remove the vendor.xml (C:\Program Files\Common Files\Research In Motion\AppLoader)
7. run the loader.exe and upgrade the devices firmware (C:\Program Files\Common Files\Research In Motion\AppLoader)

updating firmware on bb device
updating firmware on bb device

8. reactivate the device to rejoin it to the bes

and your done

sql job prevent concurrency between jobs

Have you ever needed to make absolutely sure two jobs dont run at the same time in, just schedule to run at different times i hear you say. well in some cases its not that easy. Sometimes you have jobs that run every 20 minutes and others of a similar fashion, there is a chance one job could extend longer for what ever reason, most of the time its unforseen, as in the case i had recently.
So i came up with a solution. A simple Stored Procedure i wrote can, it will live in your master db, why the master i hear you ask. well in order to prevent collasions between databases it is best to live in the master.
1st write a list of the job names you want to prevent from running concurrently, and then follow the instructions here
2nd place a code snippet only once, in the first step of each job, so it runs prior to anything in the job

BES Pro Install

Im about to configure Blackberry Enterprise Server Professional edition (BESPro) in our work place.
We use MDA’s paired up with OWA to our exchange but several people have been complaining about their devices being very slow and combersome.
I decided to imlement BESPro and enable the devices for email and calendar sync, the mobile provider can provide the remaining services such as web browsing and BB messenger.

Anyway i was most of the way into the installation when i bumped into a problem with MSDE edition of the install. If you don’t have a default DB with MSDE installed, the BB installer doesnt prompt to create a DB, infact it doesnt do anything it fails to connect. So firstly you have to download MSDE from microsoft and go through the install sequence, at some point it will ask you if you would like to create a default db, select yes and complete the install.

Next up make sure that all the connection methods are enabled in the db for that default db, namely TCPIP and named pipes (use the SQL Server Configuration Manageer console to do this, dont forget to restart the instance of MSSQL in the services console).

Now in your bb installer you can give the default db instance name, if it asks you if you want to create a new DB “BESMgmt” say yes, once its created the new DB you must grant either the local admin group or the bb account relevant access permissions under the security node of the DB, otherwise when you come to activate your installation by filling in the license details it will fail to connect because the installer does not create the security permission. You will see this error in the log file [PIPTOOL]: Failed to update the license keys as no database connections.

Run 32 bit Apps in IIS on 64 bit OS

So you want to run your web Apps in 32 bit mode on your 64 bit windows operating system
Ive never needed to do this until now, Cybersouce, the online financial gateway we have recently integrated with dont support 64 bit when you use their .NET API.

OK all you need to do is
1. IIS management >> Local computer node >> Web Server Extensions >> set the “ASP.NET vr xxxx (32 bit)” to Allow
2. Run the following batch command (changing the folder targets if you need to)
cscript c:inetpubadminscriptsadsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727aspnet_regiis.exe -i


If things go terribly wrong and you need to revert to 64bit then run the following batch
cscript c:inetpubadminscriptsadsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0
C:WINDOWSMicrosoft.NETFramework64v2.0.50727aspnet_regiis.exe -i


Display your latest twitter on your WP Blog

Copy and paste this code anywhere in your website
originally posted by

SQL UPDATE() updating millions of records nicely

Ever needed to update millions of records before ?
New to tackling this ?
I had this experience when our database contained 17 million records when 15.5 million were junk and could be disgarded. My mission was flag the unwanted records and remove them.
This blog is simply about how to go about updating a table with millions of records. Fear not there is an approach that wont lock your database for hours, or expontentially increase the size of your transaction log file. Its known as Chunked updates.

original source explained here

In my case I needed to update 17 million records based on a cross reference match of each record against another table containing 5.5K records, a single update statement would have takens hours and hours.
The chunked approach on an optimised indexed set of tables took just 35 minutes on a single license quad core proc SQL server 2005 standard edition.
Heres is the SQL statement

So you can see from the code i was chunking in 500000 rows segments, tune this to your requirements I guessed and struck it lucky.

For those interested, yes i did take steps to prevent this from occuring again. I created an insert trigger that cross references the User Agent table flagging the record. A Job then does the clean up of periodically archiving the unwanted data into a redundant database for historical reference.

HTML embed an audio clip and repeat / loop it

I needed to loop an audio clip for a friends static web page. I found out that there are so many idosyncracies to take into account with various browsers.
Here’s how to do it with an mp3

ok so

1. The header contains the tag

enclosed within a tag

This is used in older browsers that do not support the tag

2. The tag

is used with the correct parameters to call the sound autoplay it and loop it
3. The

supported by pretty much anything is there just in case the browser is not IE and does not support the tag

The only other alternative is to do the following