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
            Z = Z + Mid(Base, x, 1)
            Odd = True
        End If
    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
End Sub

How to use Rel=”author” and Rel=”publisher” for SEO?

In my post “How to use authorship for SEO?” I poitned out the importance of claiming authorship of your content. In the article ‘Wake Up SEOs, the New Google is Here’ on the same thing is stated. Google’s Matt Cutts (see the video below) also indicates that claiming authorship will help your SEO efforts.

Google introduced the concepts of Author and Publisher and, even more important, Google linked them to the Google Profiles and is pushing Google Plus, which is not just another Social Media, but what Google aims to be in the future: a social search engine.

Rel=”author” and Rel=”publisher” are the solution Google is adopting in order to better control, within other things, the spam pollution of the SERPs.

If you are a blogger, you will be incentivized in marking your content with Author and link it to your G+ Profile, and as a Site, you are incentivized to create your G+ Business page and to promote it with a badge on you site that has the rel=”publisher” in its code.


source: Coding Strategist

SQL funtion to Capitalize a string

If you need a string you select from a microsoft SQL database to be capitalized, you could use the the folowing select statement: SELECT dbo.InitCap([FieldToCapatalize]) FROM TableName

To make this work the function ‘InitCap’ should be made available by adding a new function to your database. To add a function in Microsoft SQL Server Management Studio; In the Object Explorer you navigate to the Databases > [DatabaseName] > Programmability > Functions > Right Click > New. Choose any of the three new function types you could add. Then replace all the the code in the editor window with the code below. Make sure to replace DataBaseName with the name of your database.

USE [DataBaseName]
/****** Object: UserDefinedFunction [dbo].[InitCap] Script Date: 05/07/2010 16:26:51 ******/
ALTER FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
SET @Index = @Index + 1
RETURN @OutputString