SQL Server 2008 Log File Shrink for all databases on a server

After my previous post (24 june 2009), in which I proposed a script to Shrink log Files for all DB in a SQL Server 2005 machine, now you've got a script to do the same thing on SQL Server 2008 servera:

EXECUTE sp_msforeachdb

'USE ?;

Alter Database ? Set Recovery Simple;

Alter Database ? Set Recovery Full;

DECLARE @LogLogicalName nvarchar(100);

SELECT @LogLogicalName = file_name(2);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);'

Compare string with Aspnet_Membership password

Small time ago I had a particular problem, I need to compare a password written by a user in a client WinForms software with an Asp.net managed password (stored in Aspnet_Membership table).

The main problem is that the Asp.net password is encrypted in db and it's not possible to decrypt. The only way to solve the problem is to encrypt given password in the same way as Asp.net and then compare password hashes. To do it, "Salt" is needed: the value of PasswordSalt column in Aspnet_Membership.

Here the code (I'm sorry but i haven't enought time to colorize the code :D ):

VB.Net

Public Function Compare(ByVal password As String, ByVal aspnetSalt As String, ByVal aspnetPassword As String) As Boolean
    Dim isEqual As Boolean = False
    If aspnetPassword = GenerateHash(password, aspnetSalt) Then
        isEqual = True
    End If
    Return isEqual
End Function

Private Function GenerateHash(ByVal pwd As String, ByVal salt As String) As String
    Dim p1 As Byte() = Convert.FromBase64String(salt)
    Return GenerateHash(pwd, p1)
End Function

Private Function GenerateHash(ByVal pwd As String, ByVal saltAsByteArray As Byte()) As String
    Dim sha As New System.Security.Cryptography.SHA1CryptoServiceProvider()
    Dim p1 As Byte() = saltAsByteArray
    Dim p2 As Byte() = System.Text.Encoding.Unicode.GetBytes(pwd)
    Dim data() As Byte = New Byte(((p1.Length + p2.Length)) - 1) {}
    p1.CopyTo(data, 0)
    p2.CopyTo(data, p1.Length)
    Dim result As Byte() = sha.ComputeHash(data)
    Dim rtn As String = Convert.ToBase64String(result)
    Return rtn
End Function

C#

public boolean Compare (string password, string aspnetSalt, string aspnetPassword)
{
    boolean isEqual = False
    If (aspnetPassword = GenerateHash(password, aspnetSalt))
    {
        isEqual = True
    }
    return isEqual
}

private string GenerateHash(string pwd, string saltAsBase64)
{
    byte[] p1 = Convert.FromBase64String(saltAsBase64);
    return GenerateHash(pwd, p1);
}

private string GenerateHash(string pwd, byte[] saltAsByteArray)
{
    System.Security.Cryptography.SHA1 sha = new System.Security.Cryptography.SHA1CryptoServiceProvider();

    byte[] p1 = saltAsByteArray;
    byte[] p2 = System.Text.Encoding.Unicode.GetBytes(pwd);

    byte[] data = new byte[p1.Length + p2.Length];

    p1.CopyTo(data, 0);
    p2.CopyTo(data, p1.Length);

    byte[] result = sha.ComputeHash(data);

    string res = Convert.ToBase64String(result);
    return res;
}

Capitalize string in Sql Server

There is a very simple way to capitalize a string in Sql Server (T-SQL)

If your need is just in output, use this:

SELECT UPPER(LEFT(ColumnName,1)) + LOWER(SUBSTRING(ColumnName,2,LEN(ColumnName))) FROM TableName


If instead you need to modify and save values into db:

UPDATE ColumnName SET ColumnName=UPPER(LEFT(ColumnName,1)) + LOWER(SUBSTRING(ColumnName,2,LEN(ColumnName)))

Asp.Net Menu. deleting an Item in Runtime

Sometimes we need to delete (hide) a menu item from an aspnet web application menu (or submenu) when a particolar condition occurs. The problem is that the menu component doesn't have a method to hide a menuitem during runtime. Well, here you have the code to do it:

Protected Sub Menu1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Menu1.PreRender
    If CONDITION Then
        'delete the unnecessary menu item
        Dim tot As Integer = Menu1.Items.Count - 1
        Dim i As Integer = 0
            While i <= tot
            Dim ss As MenuItem = Menu1.Items(i)
            If ss.Text.ToLower = "menu name" Then
                'delete sub menus
                Dim subtot As Integer = Menu1.Items(i).ChildItems.Count - 1
                Dim j As Integer = 0
                While j <= subtot
                    Menu1.Items(i).ChildItems.RemoveAt(0)
                    j += 1
                End While
                'delete menu
                Menu1.Items.Remove(ss)
                i = i - 1
                tot = tot - 1
            End If
            i += 1
        End While
    End If
End Sub

Asp.Net Menu problem with Chome, Safari and Opera

In the past I published another post regarding the malfunction of Asp.Net Menu componente with IE8. Now I write about this component problems with other browsers: Chrome, Safari and Opera.

With these browsers the menu doesn't work properly, it disappears when clicked.

To made it work, add this code snippet in every page that contains a menu definition, into Page_Init event:

[VB.Net]
If Request.UserAgent.Contains("AppleWebKit") Then
    Request.Browser.Adapters.Clear()
End If

[C#]
If Request.UserAgent.Contains("AppleWebKit") Request.Browser.Adapters.Clear() ;

Read and Write with Cassandra in Vb.Net

This is a first example of application in vb.net that write and read datas using Cassandra and HectorSharp interface(http://hectorsharp.com/). Just download HectorSharp and compile it to create libraries for your project. Obviously, we need to create a referenco to the HectorSharp library in our project before we can use it.

In this example we use the default keyspace of Cassandra (Keyspace1) and also a default Cassandta column family (Standard1).

We'll create a column called "MyColumn" in the column family and we'll put in a value.

For this purpose, we'll use a Web Application with label, called “lbltest”, where we'll show data picked from Cassandra.

Imports HectorSharp

[…]

'db connection
Dim cf As New KeyedCassandraClientFactory(New CassandraClientPoolFactory().Create(), _

New KeyedCassandraClientFactory.Config())
Dim client = cf.Make(New Endpoint("localhost", 9160))

'db definition (keyspace)
Dim keyspace = client.GetKeyspace("Keyspace1")

'Dim path = New ColumnPath(columnfamily name, supercolumn name (if present), column name)
Dim path = New ColumnPath("Standard1", "", "MyColumn")

'insert a value in db
keyspace.Insert("0", path, "My new value")

'get the value
Dim column As Column = keyspace.GetColumn("0", path)

lbltest.Text = column.Value

How you can see, working with Cassandra using HectorSharp is really simple.

Installing Cassandra in Windows

Installing Cassandra Project (http://cassandra.apache.org/)on Windows is really simple, maybe more the do it on Linux.

Cassandra is a BD engine written in Java, the only requirements to install it is Java 1.6 (Is better to use the most up-to-date version).

After installing Java, download the tar.gz archive for the official site and then untar it in the chosen folder (For this example we use "C:\Cassandra").

Well, now just do a bit of configuration. Open "C:\Cassandra\conf\storage-conf.xml" and change default value of the nodes below to:

<commitlogdirectory>C:\Cassandra\commitlog</commitlogdirectory>
<datafiledirectories>
<datafiledirectory>C:\Cassandra\data</datafiledirectory>
</datafiledirectories>

Note: normally is better to use two different disks for commit logs and datas.


Finally, we have to set "CASSANDRA_HOME" as system variable with value "C:\Cassandra"

So, now we can launch "C:\Cassandra\bin\cassandra.bat" to start the Cassandra Server.

Ok, at this point the Cassandra server will be "up-n-running".


To test it, we can launch "C:\Cassandra\bin\cassandra-cli.bat" and then give the command:

connect localhost/9160

If all works, we'll have this answer: "Connected to: "Test Cluster" in localhost/9160"

Enjoy :)

Create a link from command prompt

To create a link to a file or a folder we generally use the GUI. For particular issues (script, batch files) is possible to use shell commands.

Commands are:

mklink "Link Name" c:\Folder\SubFolder\filename.txt

or

mklink "Link Name" c:\Folder\SubFolder


These commands create a link called "Link Name" that refers to the specified folder or file.

Warning: the link will be created in the folder where the command runs.

Windows 7 God Mode

Windows 7 (and also Windows Vista) has an hidden mode called "God Mode". With a small trick is possible to have access to an "Extended Control Panel" with a lot of functions.

I'm speaking about all the personalisation that Microsoft use for their Operating sistems. So, no filters!

The procedure is simple: just create a new folder on the desktop and rename it in:

GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}

At this point a shortcut icon should appears. This icon opens a Grafical Interface for the "God Mode". There is an issue: this trick may not work properly with 64 bit os versions.