How to fix the users after SQL migration?

msSQL has system stored procedure called sp_change_users_login which can be used to report about broken user accounts and auto fix them.

After restoring a msSQL backup from one server on another server all user accounts will not be properly linked to the database. The sp_change_users_login stored procedure can help you find all users that are not linked and need to be fixed before they van be used again.

The command to use is: sp_change_users_login ‘report’
Executing this sp in ms SQL server management studio (Query Analyzer) will list all users that are not properly linked to the current database.

To fix these users run the same Stored Procedure with the ‘Auto_Fix’ parameter once for every user.

The command to use is: sp_change_users_login ‘Auto_Fix’, ‘[USERNAME]’
(replace [USERNAME] with the appropriate username)

To check if all users are properly linked after “auto_fixing” them, run the “report” again.

For more details about the sp_change_users_login see Microsoft Developer Network

Using EAN barcodes in Excel

VB code for generating the check digit for EAN8 and EAN13 barcodes
This code generates a checksum needed for EAN-8 and EAN-13 borcodes. The input (Base) is either a 7 or a 12 digit string. The number generated by the EAN function should be added to the end of the Base to make a valid barcode number.

Function EAN(Base As Variant) As Integer
    Z = 0: Odd = True
    For x = Len(Base) To 1 Step -1
        If Odd Then
            Z = Z + Mid(Base, x, 1) * 3
            Odd = False
        Else
            Z = Z + Mid(Base, x, 1)
            Odd = True
        End If
    Next
    EAN = 10 - Z Mod 10
    If EAN = 10 Then EAN = 0
End Function

Excel VBA conditional formating with more than 3 conditions

conditional formating in Excel on the basis of more than 3 conditions
How to change the font color and weight and shading of the first [X] cells in all rows in a given excel sheet? Most of the times the conditional formatting option in Excel is enough, but not when you have more than 3 conditions. Here is a simple piece of VB code to help you make it possible to use as many conditions as you can think of. It gives you the ability to change the font color, the background color and the font weight (bold or not) based on all the cases (conditions) you define. In this case it the formatting is changed when the first collumn (A:A) cantains an A, B, C, D, E or F.

 

Private Sub Worksheet_Change(ByVal Target As Range)
'fc = font color
'bc = background color
'bf = bold or not
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
'set d if Target is intersecting with column A
Set d = Intersect(Range("A:A"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
    'c = the value of the cell that just changed value
    'MsgBox UCase(c)
    Select Case UCase(c)
        Case "A"
            fc = 5: fb = True: bc = 10
        Case "B"
            fc = 6: fb = True: bc = 9
        Case "C"
            fc = 7: fb = True: bc = 8
        Case "D"
            fc = 8: fb = True: bc = 7
        Case "E"
            fc = 9: fb = True: bc = 6
        Case "F"
            fc = 10: fb = True: bc = 5
        Case Else
            fc = 1: fb = False: bc = xlNone
    End Select
'here 20 is the number of cells you want to change the format for each row

    With Cells(c.Row, 1).Resize(, 20)
'for each cell in the row change the font color, font weight and background color
        .Font.ColorIndex = fc
        .Font.Bold = fb
        .Interior.ColorIndex = bc
    End With
Next
End Sub

Cross-browser testing

Websites are viewed with different browsers on different operating system. These browser/OS  combinations should all deal with HTML, CSS and JavaScript in the same way. Unfortunately they do not. That is why every website owner should test if his site works and renders properly in at least the most common browser/OS combinations. In the Google Analytics screen dumps below, you can see that Microsoft Internet Explorer (IE), Mozilla Firefox, Apple Safari, Google Chrome and  Opera are the most common browsers. Depending on the time and budget available you can test on all or just a few browser/OS combo’s. Do not forget that of all browsers there are several versions available. As most browsers offer a semi automatic update you will find that most people use one of the latest versions available.

For proper testing I prefer to testing machines available of which I know exactly what is and what is not installed on it. Depending on the fonts that are installed and if ClearType (Windows only) switched on or of a site can look very different. There are a number of browser plugins that are not installed by default in most browsers like Java, Flash or SilverLight. To do a proper test on a website that uses these techniques you need machines where these plugins are not installed to see what is left of your site.  It can even be usefull to test a website with default functionality like JavaScript or CSS switched of. But for most test where you just want to know if your HTML, CSS and JavaScript will act and look like you intended, the browser plug-in available from spoon.net is a great solution. They offer a true cloud computing solution where you can run applications (including the most common browsers) on one of their servers and view the screen  get it pushed to your pc.  It is like Microsoft’s Remote Desktop or Citrix.

If you do not have the resources to get a testing room furnished with at least 10 computers  http://www.spoon.net/Browsers is the best alternative!

For people who just want to see screenshots of their site in different browsers for a cross-browser test, there are several multi-browser screenshot services available. Smashingmagazine.com tested most multi-browser screenshot services and wrote a in depth report about it. The table below is the outcome of their test.

Browsers supported Price (1 year)
BrowserShots IE, Firefox, Chrome, Opera, Safari, Dilo, SeaMonkey, Minefield, Epiphany, Flock, Galeon, Konqueror, K-Meleon, Avant, Netscape, Shireteko, Kazehakase, Iceweasel Free
BrowserCam IE, Firefox, Chrome, Opera, Safari, Konqueror, Camino, Netscape, AOL $999.95
BrowserLab IE, Firefox, Chrome and Safari Free (till end of 2010)
SuperPreview IE, Firefox and Safari $149
BrowserSeal IE, Firefox, Chrome, Opera and Safari $49
Litmus IE, Firefox, Chrome, Opera, Safari, Flock, Camino, SeaMonkey, Netscape $588
Multi Browser Viewer IE, Firefox, Chrome, Opera, Safari, Flock, SeaMonkey, Netscape, K-Meleon, Camino, Konqueror, Epiphany, Kazehakase $129.95
Browsera IE, Firefox, Safari $588

Search API’s: Yahoo, Goolge & Bing

The 3 major search engines all offer the possibility to use their search technology in your on-line application. You can offer Google, Bing and/or Yahoo search results to your visitors on your site in your own layout and filters. The most common use is probably of Search APIs is probably website owners offering site search to their visitors. Joomla, WordPress, Drupal and most other CMSs come with build in site search, but for larger websites this produces irrelevant results. The top search companies are just better in sorting by relevance. If you have a relatively large website and relevance in site search is important to you the Yahoo, Goolge or Bing Search API can help you greatly. For all three you need to register for a API key. It is free to get a key, but there are restrictions on the amount of searches you may preform.

These are the URLs you can use to register for an API key.

Documentation can be found here

Google & Microsoft also offers  paid services under the names Search Solutions for business & SharePoint 2010 Search.
Next to that Google also offer Custom Search which allows only very minor adjustments in layout, but a lot af filter options. The fact that you can not remove the ads from the SERPs is compensated by the fact that you can make some money from them with your AdSense account.

How to make an ‘advanced segment’ in Google Analytics for long tail organic traffic?

How to find out if  “Google MayDay” affected your rankings?

There have been some algorithmic changes to Google Search the around the 1st of May, hence the name ‘Google MayDay’. These changes could have effected your long tail positions in Google. Matt Cutts reacts to questions about Google MayDay in the video below. If you run Google analytics on your website the easiest way to find out if the changes affected the ranking of your site is to create a ‘advanced segment’. With advanced segments you could make analytics display only data for traffic generated by long tail search phrases (ie. search phrases containing 4 or more words).

How to make an ‘advanced segment’ for long tail organic results?

  1. Log in to your google analytics account
  2. If you have multiple websites configured select the website profile you want to work with
  3. In the left menu choose ‘Advanced segments’ under ‘My customizations’
  4. Click  ‘+  Create new custom segment’ in the top right corner of the content area
  5. Add the ‘dimension’ ‘Medium’ from ‘Traffic Sources’ by dragging it from the left menu. (see: figure 1)
  6. Set the condition to: ‘matches exactly’ ‘organic’ to make sure only organic search results will show up
  7. Add “and” statement
  8. In the  and statement add the ‘dimension’ ‘Keyword’ from ‘Traffic Sources’ by dragging it from the left menu.
  9. Set the ‘condition’ to ‘matches regular expression’
  10. Set the ‘value’ to: ^([a-z]+)([\s+]+)([a-z]+)([\s+]+)([a-z]+)([\s+]+)([a-z]+) This regular expression will match strings that start with 4 words (containing only chatracters a to z) which are seperated by one or more spaces (which can also be a + sign).
  11. Give the segment a name in the field indicated with ‘name segment’ and save it. (Your screen should now look like figure 2)
  12. To start using the segment go to the dashboard and click ‘Advanced Segments: All Visits’ in the top right corner of the content area. Check the new segment and uncheck ‘all visits’ and apply the changes. (see: figure 3)
  13. You are now ready to view your stats with only long tail data.

further reading:

Blogvertising

There are many ways in which advertising is offered on blogs. Buying a Pay per Click (PPC) or Pay per View (PPV) campaign is something that can be done on any website including blogs. Paying a blogger to write post about your brand or product can only be done on a blog. The paid review or advertorial which is paid per post is the only form of advertising that truly is blogvertising. There are many platforms that help advertisers to find bloggers.

Some of the most popular of these pay per post platforms according to Google trends:blogvertising platforms popularity according to google trends

Continue reading Blogvertising

Website feedback tools

If you want to know what the visitors of your site think of your site you have a few options. You could ask visitors you know personally for their opinion. You could publish an email address where they could send their comments. You could have a response form build that that send the comments to a database or email address. These methods might give you some insight into the user experience of your website, but there are some free tools out there that will provide you with much more in depth analysis of your visitors’ comments. Why re-invent the wheel if it is freely available?

Some of the most popular website feedback tools according to Google Trends

website feedback tools

Continue reading Website feedback tools