Archive for the ‘Database’ Category

The reason could be as simple as that you are opening the table designer. The table schema is locked.

Advertisements

For the purpose of remembering

CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON `mydb`.* TO `username`@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%the text to search for%’
AND ROUTINE_TYPE=’PROCEDURE’

Credit: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

I received this error when I tried to change the sa password through SQL Server Management Studio.

Again, googling

Microsoft support site has the detailed reason and resolution, but again, Microsoft failed and Google did it.

This problem occurs if the Map To Credential check box is not selected on the General tab of the Login Properties – sa dialog box. If this check box is not selected, SQL Server 2008 will try to drop any credential that is mapped to your login credentials. Because your credentials cannot be set to the “sa” login, you receive the exception error message that is mentioned in the “Symptoms” section when you click OK to close the dialog box.

Resolution:

To resolve this issue, make sure that you select the Map To Credential check box on the General tab of theLogin Properties – sa dialog box before you change the “sa” login properties and then click OK to close the dialog box.

When I tried to config my website to connect to a newly-setup Microsoft SQL Server, I received the following error: Login failed for user ‘username’.  The user is not associated with a trusted SQL Server connection.

As it always was, Microsoft error message doesn’t give any clear clue what the reason is. After googled a while, I found the reason because I forgot to enable sql authentication in sql server settings.

So the resolution is very simple, just open the Server Properties in SQL Server Management Studio, under Security, set the server authentication mode to SQL Server and Windows Authentication mode.

When updating millions of records in SQL Server, do it batch by batch, otherwise, it may take hours or days.

declare @startId int
declare @endId int
set @endId = 0

while( 1 = 1)
begin
     set @startId = 0

     select top 1 @startId = p.ProductID
     from Product p with(nolock)
     where p.ProductID > @endId
     order by p.ProductID asc

     if @startId  = 0
     begin
          break
     end

-- print @startId

     set rowcount 100000

     select @endId = p.ProductID
     from Product p with(nolock)
     where p.ProductID >= @startId
     order by p.ProductID asc

     set rowcount 0

-- print @endId

     BEGIN TRANSACTION

     update top (100000) p
     set p.dateUpdated = GetDate()
     from Product p with(rowlock)
     where p.ProductID >= @startId and p.ProductID <= @endId

     if @@ROWCOUNT = 0
     BEGIN
          COMMIT TRANSACTION
          BREAK
     END
     COMMIT TRANSACTION
-- 1 second delay

     WAITFOR DELAY '00:00:01';
end

I tried to execute this update statement on a table with 7 millions records, it takes less than 10 mins.

Language: C#; SQL Server: Microsoft SQL Server 2008

I am working on a online store with 6M+ products. The search engine is powered by fulltext, which is good enough for normal scenario; however, a search engine without search suggestion is only partially functioned.

Levenshtein distance is a very useful and powerful algorithm for string difference measuring. I found the implementation in this page. http://levenshtein.blogspot.com/

To implement it for search suggestion, the prerequisite is that you already have a pool of valid keywords, which is true in my case.

First, create a class library in visual studio.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]
    public static SqlDouble Levenshtein(SqlString S1, SqlString S2)
    {
        if (S1.IsNull)
            S1 = new SqlString("");

        if (S2.IsNull)
            S2 = new SqlString("");

        String SC1 = S1.Value.ToUpper();
        String SC2 = S2.Value.ToUpper();

        int n = SC1.Length;
        int m = SC2.Length;

        int[,] d = new int[n + 1, m + 1];
        int cost = 0;

        if (n + m == 0)
        {
            return 100;
        }
        else if (n == 0)
        {
            return 0;
        }
        else if (m == 0)
        {
            return 0;
        }

        for (int i = 0; i <= n; i++)
            d[i, 0] = i;

        for (int j = 0; j <= m; j++)
            d[0, j] = j;

        for (int i = 1; i <= n; i++)
        {
            for (int j = 1; j <= m; j++)
            {
                if (SC1[i - 1] == SC2[j - 1])
                    cost = 0;
                else
                    cost = 1;

                d[i, j] = System.Math.Min(System.Math.Min(d[i - 1, j] + 1, d[i, j - 1] + 1), d[i - 1, j - 1] + cost);
            }
        }

        double percentage = System.Math.Round((1.0 - ((double)d[n, m] / (double)System.Math.Max(n, m))) * 100.0, 2);
        return percentage;
    }
}

Next install the compiled dll to database, under Programmability->Assembly. After that, we will see the newly installed assembly UserFunctions.

We need enable CLR before calling function in the dll.

sp_configure 'clr enabled', 1
GO
reconfigure
GO

Create a sql function to use the function in our dll.

CREATE Function fn_Levenshtein(@S1 nvarchar(4000), @S2 nvarchar(4000))
    RETURNS float as EXTERNAL NAME UserFunctions.StoredFunctions.Levenshtein
GO

Test: select fn_Levenshtein(‘steve jobs’, ‘steve jbs’)