May 23, 2013
April 29, 2013
April 7, 2013
May 16, 2012
April 15, 2012
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
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
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