The difference between a fast running script and a slow one

When logging errors is not a good idea

I like to log errors and debug messages when running scripts as they help diagnose problems not immediately apparent when the script is started. There is nothing worse than starting a script running and then coming into work on a Monday morning to find that it hasn't done what it was supposed to and not know why. A helpful log file with details of the operation that failed and the input parameter values can be worth its weight in gold.

However logging is an overhead and can literally be the difference between a script that takes days to run and minutes. I recently had a script to run on a number of webservers that had to segment out hundreds of thousands of files (documents in the < 500KB range) into a new folder structure.

My ideal solution was to collate a list of physical files in the folder I wanted to segment and pass that into my SELECT statement so that I could return a recordset containing only the files that actually existed. However my ideal solution was thwarted due to an error message I have never come across before which claimed the DB server didn't have the resources available to compile the necessary query plan. Apparently this was due to the complexity of my query and it recommended to reduce the amount of joins. As I only had one join this was not a solution and after a few more attempts with some covering indexes that also failed I tried another solution.

The solution was just to try to move the file and catch any error that was raised and then log it to a file. The script was set running and a day later it was still running along with a large file containing lots of "File not found errors".

The script was eventually killed for another unrelated reason which pissed me off as I had to get the segmented folder structure up running sharpish. I modified the script so that before each move it checked whether the File Existed before the move. I initially had thought that this in itself may have been an overhead as the actual move method would be doing the same thing only at a lower level therefore I was duplicating two searches for a file in a large folder. I reckoned that just trying to do the move and then catching any error would speed things up.

However because I was logging the error to a text file this was causing a bottleneck in I/O and slowing down the procedure immensely. Adding the FileExists check round each move ensured that only files that definitely existed were attempted to be moved and no errors were raised which meant no logging and no I/O overhead.

I had forgone the nicety of knowing which files were not on the server any more but I had also reduced the scripts running time down to a mere 25 minutes. Therefore the lesson to be learned is that although logging is useful it can also be a major overhead and if you can do without it you may just speed up your scripts.

Handling YouTube Videos in Feeds

Reformatting HTML to include YouTube video links

If you use XML feeds to import content into your site or blog you may have experienced the problem that your blogging software or add-on tries to err on the side of caution for security reasons or just messes up the import and in doing so any EMBED or OBJECT tags are stripped in the process.

This is especially annoying when the content is for a blog or news site and the imported content was linking to YouTube videos as it means you have to go through all the posts by hand and reformat the HTML so that the original video is shown.

Luckily even though the actual OBJECT tag gets removed you are usually left with a link to the video on YouTube's site. This can be utilised in a simple regular expression so that you can reformat your content and replace this link with the actual OBJECT HTML.

The regular expression is pretty simple and looks for any anchors pointing to YouTube's site and captures the video ID in a sub group. This sub group is then used in the replace statement as the value for the PARAM and EMBED values that require it.

// use preg_replace to replace anchors with OBJECT/EMBED
// $input is the source HTML

$html = preg_replace("/<a href=\"http:\/\/www\.youtube\.com\/watch\?v=([A-Z1-9]+)\">[\s\S]+?<\/a>/i","<object width=\"425\" height=\"344\"><param name=\"movie\" value=\"http://www.youtube.com/v/$1&hl=en_GB&fs=1&\"></param><param name=\"allowFullScreen\" value=\"true\"></param><param name=\"allowscriptaccess\" value=\"always\"></param><embed src=\"http://www.youtube.com/v/$1&hl=en_GB&fs=1&\" type=\"application/x-shockwave-flash\" allowscriptaccess=\"always\" allowfullscreen=\"true\" width=\"425\" height=\"344\"></embed></object>",$input);

I am using PHP as an example here but the regular expression would be the same across all languages.

Either wrap a call to this function in your own import procedure or if you are using an add-on like Wordpresses WP-o-Matic then you should utilise it in the rewrite section when setting up a new feed import.

Strictly Software's Anonymizing Super Search Tool

Introducing the Anonymous Super Search Tool

The following free online tool which I have created follows in the same vein as the wonderful anonymizing search tools such as Scroogle which give you the searching power of Google but without all the privacy issues.

If you don't know what the privacy issues are when you carry out a Google search then you should be aware of the following:

1. A search on Google, Yahoo and Bing is carried out with an HTTP GET request. This means that your search terms are visible in the address bar e.g:

http://www.google.co.uk/search?q=strictly+sofware

this means that inside the log files on the web server that carried out the search is a record of your search request as well as your IP address and browser details.

2. As well as a history of your search being stored in the web servers log files, your browsers history as well as any tracking cookies used to deliver term specific advertising it can also be stored on any intermediate servers that it passed through on the way. If you are carrying out your search at work or school then it's very likely that all your traffic goes through a proxy or firewall and therefore they will also have a history of your search terms in the log files of those servers.

3. Because your search terms are visible in the address of the URL it is very easy to block certain terms as well as whole sites in real time. It's also easy for your employer, school, ISP or authority with a warrant to find out whether people have been searching for things they shouldn't have been e.g porn or banned sites.

So now you know why searching through the main providers can be a privacy concern. Obviously you might not care who knows about your search for penis pumps, blow up dolls, latex fetish sites, pre-op transsexuals and whatever else you may enjoy looking for but if you do what can you do about it?

Well from now on you can check out my new Super Search tool. Not only does it return the top 10 results from the major three search providers at the same time but it keeps your anonymity by using proxies and other various methods.

Method 1.

All searches are passed through a built in proxy chain containing at least three steps from your PC to the search engines web server. This means that your employer, school or ISP will only ever see your request to my domain and not to the search engine. Also the search engines will only ever see traffic from the last proxy before it arrived at their server. Because the proxies use different IP addresses from your own machine there is no way for them to link a search request with your computer only the proxy server that is used by many users.

Method 2.

The initial request on my search page is made through an HTTP POST not a GET. As it takes a lot of work, disk space and resources to log all POST data it's not done as standard by Apache and IIS when they log HTTP requests. Therefore any search terms are never stored on my web server even if I did enable logging (see next point).

Method 3

The actual logging of HTTP requests has been disabled on my web server for this domain. I keep no history of any searches made through this tool. I also don't use Google Analytics or any other client side urchin tracker tools on this page. There is no way I can tell who came to my tool and what searches they did and if I cannot know then it means no-one else can find out from me...

Method 4

When possible the search requests are passed through proxies that support HTTPS which means they get encrypted on route between my server and the next one.

Method 5

The final results are passed through an anonymizer tool which means if you want to look at any result in more detail you can be assured that the request is not linked back to your IP address as you are viewing the website through a proxy.

The tool is called Super Search and can be found here: Anon Super Search

Also on a side note if you ever want to check out the details a web proxy is actually passing around to ensure its safety then the following info tool details browser related information such as IP, GEO Location, HTTP Headers, Proxy Forwarded values, Agent and Referrer info. Its basic but a good quick way of checking what details you are currently using.

Browser Survey Results

The results of the Strictly Software Browser Survey

If you have visited my site in the last couple of months you may have noticed the survey on Browser usage that popped up. It was geared towards developers to gather information about their favourite browsers in terms of developing, debugging and features.

I have let it run for a couple of months and now I am publishing the results. Thanks to everyone who took the time to answer the questions.

Question 1: Which browser do you use when developing new code

Firefox 56%

Internet Explorer 21%

Chrome 10%

Opera 6%

Safari 5%

Other Option... 2%

Question 2: Which browser has the best inbuilt features for developing

Firefox 44%

Chrome 19%

Internet Explorer 17%

Safari 8%

Opera 8%

Other Option... 3%

Question 3: Which browser do you use for personal web surfing

Firefox 50%

Chrome 22%

Internet Explorer 14%

Opera 8%

Safari 5%

Other Option... 1%

Question 4: Which developer tool do you consider has the best features

Firebug 64%

IE 8's developer toolbar 17%

Webkit's Developer Tools 7%

Other Option... 5% (the majority of which said Web-Developer add-on for FireFox)

DragonFly 4%

Firebug-Lite 2%

Question 5: Which one feature could you not live without

Element inspection 40%

Dynamic DOM manipulation 20%

View generated source 18%

Clear cookies and cache 14%

Disable Javascript 6%

Other Option... 2%

I don't think anything stood out for me as a major surprise in the answers I received. In any survey done over the last 10 years or so over the whole web surfing community Internet Explorer always comes out in top place for browsing but the majority of users questioned are not developers and a large percentage of those are still only using it because they actually believe the little blue e on their desktop IS the Internet.

This is shown by a recent NetApps survey that found IE 8 has just taken the top spot from IE 6 and that Firefox 3.5 has taken 3rd place narrowly just beating IE 7. This is not much of a surprise to me as my own reports from the 200+ sites I run always show IE 6,7,8 taking the top 3 spots with over 75% of all usage between them.

However when looking at browser usage for people in the industry i.e web developers and designers FireFox seems to always take top spot purely because of the numerous add-ons and built in features. However I also know from personal experience and from colleagues that within the year or so Chrome has been around it has built up quite an on-line following and for pure web surfing I personally don't think it can be beaten for speed, simplicity and usability and this seems to be proven by it taking 2nd place as the browser techies like to use when surfing.

Anyway thanks for taking part.

Update Orphaned SQL Users

SQL Script to relink orphaned users to logins

It's quite a common task to have to move one or more databases from one server to another and this usually causes issues with orphaned users that have no related SQL login due to the SID values mismatching. The following is taken from an MS Knowledge base article about the problem:

When you move a database from one server that is running SQL Server to another server that is running SQL Server, a mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide the sp_change_users_login system stored procedure to map these mismatched users. However, you can only use the sp_change_users_login stored procedure to map standard SQL Server logins and you must perform these mapping for one user at a time.

Therefore the issue becomes how to sync up the logins in the copied database that relate to Login SID's that no longer exist on the server. If you try to manually re-save the login and grant it access to the copied database you are likely to run into problems with an error that prevents you from accomplishing the task such as:

User or role already exists in the current database

Therefore you either have to run a script to delete the users and then re-create them all correctly or do as I did and use some SQL to output the necessary TSQL statements that use the system views sys.sql_logins and sysusers and the stored procedure sp_change_users_login to relink the orphaned user to an SQL login.

-- example output to relink logins to users USE myDB1 GO EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase101', 'mydatabase101' GO USE myDB2 GO EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase_admin_101', 'mydatabase_admin_101' GO

The script has two options which allows you to either just output a list of all the orphaned users and their related databases so that you can run any reconfigure statements yourself. The other option will output all the necessary TSQL statements to relink the user to an SQL login of the same name.

IF @MODE = 'SQL'
BEGIN
 -- loop each orphan and generate auto fix code

 SELECT @MaxRow = COUNT(*), @RowID = 0
 FROM #RESULTS

 IF @MaxRow > 0
   BEGIN
  WHILE @RowID > @MaxRow
    BEGIN
   SELECT @RowID = @RowID + 1

   SELECT @DatabaseName = DatabaseName
    ,@OrphanedUser = OrphanedUser
   FROM #RESULTS
   WHERE RowID = @RowID

   -- output the TSQL to relink the user with the login

   SELECT @SQL =  'USE '   + @DatabaseName + + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)
      +'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + @OrphanedUser + ''', ''' + @OrphanedUser + '''' + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)

   PRINT @SQL
    END
   END

END
ELSE -- output the orphans
BEGIN
 SELECT *
 FROM #Results
 ORDER BY [DatabaseName], [OrphanedUser]
END

Obviously this is no good if you use different names for your logins and their corresponding users but as I don't it was perfect for me and enabled me to quickly resolve all the issues I had today when moving some large and important databases to a new server.

The code specifically ignores certain system users as well as all Windows Logins. It is specifically for SQL 2005 and above and is based on an SQL 2K script I found at the following site:  vyaskn.tripod.com. 

If you require a script for an SQL 2K or SQL 7 server then I suggest checking out that site as this script will not work due to changes in the SQL System Views it uses.

To get the full source code please visit the following link:

Download the SQL

By Srictly-Software

Creating Twitter Mashups

Using Free Online Tools to Create Twitter Mashups

If you are into Twitter then you may have wanted at some time to automate some tweets. There are many reasons you may want to do this such as creating new content quickly or providing an online presence when your not physically around. You may want to follow certain hash tags and retweet the same post with new hash tags or you may want to auto retweet relevant messages from certain friends or lists. Whatever reason you require this feature for you are not alone and there are already many tools out there that you can purchase to provide this functionality.

If however you don't want to spend any money and want to learn the necessary skills to do the job yourself then there are also many ways to accomplish this quickly. I managed to create an automated tweetbot within an hour tonight that follows a series of search terms as well as retweeting certain friends messages. It was very easy to do and I didn't have to write one line of code to get it working.

The aim of the Tweetbot was simple. I wanted content for a new Twitter account and I wanted to drive users to a site without having to manually write lots of Twitter updates. The updates I do write I wanted to retweet with new hash tags as well as retweeting certain relevant posts from friends.

By creating this Tweetbot I am delivering my earlier posts to a new audience who maybe following different hash tags as well as driving new users to my site by retweeting other related tweets automatically. I will see how this method works in terms of generating traffic over time but from a development point of view it was quite simple to set up.

1. Create a Yahoo Account if you haven't already got one.

2. Create a Twitter Account if you haven't already got one.

3. Find your source data which in my case was a mixture of Twitter RSS feeds. If you want to access a feed relevant to search terms then you can use the search.twitter.com interface. For example the following RSS is for the search term Javascript:

4. To combine the input feeds, analyse the content and reformat them into one output feed we are going to a great on-line tool called Yahoo Pipes. This free tool allows you to do lots of cool operations on various forms of input data with a drag n drop interface. You do this by creating a visual flow diagram with multiple steps going from your input source which can be one or more feeds through various filters and then to the pipe output. Each filter can be set to look for terms within the posts e.g you may want to only include posts that mention the word "javascript" or "SQL" in the title or the content. Or you may want to only include the post if it doesn't mention certain words or terms. You can do quite complex operations including using regular expressions to reformat the content, sort operations and clean-ups to remove duplicate content. The following article explains it quite well:

http://ryanpricemedia.com/2009/02/03/howto-yahoo-pipes-auto-retweet-recipe-for-twitter/

4. Once your pipe is saved you will be able to access it in RSS format from the supplied URL.

5. To get this data into your Twitter account you can either use the Twitter API and write your own code to parse the feed on a timed interval and add the posts to your twitter account e.g by using a CRON job that runs a Perl, PHP, VBScript:

http://apiwiki.twitter.com/Open+source

6. Or you could do what I did which is use one of the free Twitter feed applications that are available on the web such as twitterfeed.com. They obviously do something similar to point 5 in that you specify the RSS feed you want to import (e.g your Yahoo Pipe RSS feed) and set some configuration options such as the Twitter account details, number of items to parse, extra content to add to the posts etc.

The only downside with using twitterfeed.com is that the job only runs every half an hour which may not be a small enough interval for you. They obviously have a CRON job that runs every 20 minutes or so and parses all the feeds setup through the application so its not surprising that they cannot offer a smaller timescale however this does prevent you from over tweeting.

There are other apps out there and it would be quite simple to create your own Twitter app if you so wished with very little script, a timed job and the relevant input files.

I managed to create my PIPE within an hour and it had some quite complex content parsing and reformatting functionality so I was surprised at how easily I could do what I wanted without resorting to writing my own code. Try it yourself.

Grant Execute Permission to all Stored Procedures and Functions

How to grant execute permission to all your stored procedures and functions

Another useful stored procedure I have in my toolkit that I find myself using over and over on any system that I work on is the following proc that allows me to grant execute permission to all stored procedures and user defined functions within a database to a particular logon.

Sometimes I find myself creating multiple stored procedures or functions in one go, either through a CRUD proc generator script or just manually but without the necessary execute permissions added to each procedure or function they will fail to execute. Therefore its very handy if you have forgotten to add the GRANT EXECUTE statements as you went along to do it all in one batch.

The procedure makes use of the sysobjects system view to return a list of all the stored procedures and user defined functions within the database the procedure is called from. I then loop through each one and generate the necessary SQL to apply EXECUTE permission to that proc or UDF to the supplied logon.

I have also added a flag which allows you to print all the GRANT EXECUTE statements without running them so that you can check the output before running it.

The code is below.

CREATE PROCEDURE [dbo].[usp_sql_grant_execute_permission] 

 @login VARCHAR(50) = 'mylogon',
 @RunEXEC BIT = 1 --1 will run the statements, 0 = Prints them

AS

SET NOCOUNT ON

DECLARE @procname VARCHAR(50),
  @Rows INT,
  @Count INT

-- Populate the temporary table with user defined stored procs and functions
SELECT [name] INTO #tmp 
FROM sysobjects 
WHERE (xtype = 'P') OR  (xtype = 'FN' OR xtype = 'TF')

SELECT @Rows = @@ROWCOUNT, @Count = 0


-- Iterate through each of the rows on the table
WHILE (@Count < @Rows)
BEGIN
 -- get next item
 SELECT TOP 1 @procname = [name] FROM #tmp

 -- Prepare and execute SQL statement
 DECLARE @sql VARCHAR(255)
 SET @sql = 'GRANT EXEC ON ' + @procname + ' TO ' + @login
 
 -- do we run or print them out
 IF @RunEXEC = 1
   EXEC (@sql)
 ELSE
   PRINT @SQL
 
 -- delete record from temp table 
 DELETE #tmp WHERE [name] = @procname
 
 SELECT @Count = @Count + 1
END

You call it simply like so:

EXEC usp_sql_grant_execute_permission 'mylogin'

It's one of those simple procedures that are very handy to have and save a lot of time. Its very quick to run so if I ever come across any permission denied errors on my development system I just run this procedure first to ensure all my procedures and functions have the necessary execute permissions to run correctly.