解开SQL Server单数据库死锁

今天误触了使某个数据库离线的操作,导致事务在该数据库死锁。该数据库既不能返回在线状态,又无法访问。不想重启整个数据库引擎,于是搜得以下解决方法:

1、查看哪个进程SPID在连接改数据库
EXEC sp_who2

2、杀掉该SPID
Kill

3、将该数据库恢复在线状态

Advertisements

SQL Server导入导出

SQL Server导数据到SQL Server比较头疼,特别是降级处理,没有好的方案。官方的导入导出工具有以下问题:

1、如果导到一个空数据库,则主键、外键、存储过程、默认值等所有约束全部丢掉。等于废品。
2、如果有系统字段(如时间戳Timestamp类型,是系统自带的,以下以UpdateVer命名),则该字段是只读的不能导入,在导入导出工具中需要逐个点击打开表再选择忽略该字段(想象一下200个表的情况)。
3、如果先导出数据库的结构描述,依据脚本创建一个含有结构描述的空数据库,再导数据,则官方的导入导出工具竟然会不考虑约束条件直接按照表名顺序导数据,则外键关系在验证准备阶段就报错了。等于没品。

头疼。还是自己动手丰衣足食。花了两天时间,温习了一下SQL语法,写了以下这段脚本。该脚本用来复制源数据库的所有数据到空数据库,空数据库必须和源数据库结构一致。

首先依据外键关系,得到一个表名序列,按表的独立性由高到低排列,强制在输出一个表前,先输出其依赖的表。然后从序列中依次输出每个表的数据插入脚本,如果是空表则跳过该表,如果该表含有UpdateVer列则忽略该列,如果该表含有标志属性(Identity Property),则强制设IDENTITY_INSERT为ON再插入再设OFF。

DECLARE @strTempTablename VARCHAR(128)
SET @strTempTablename = '##Tmp20140619'
DECLARE @strTypeTempTablename NVARCHAR(128)
SET @strTypeTempTablename = N'@strTempTablename VARCHAR(128)'
DECLARE @strSql NVARCHAR(1024)
SET @strSql = N'DROP TABLE ' + @strTempTablename
DECLARE @nTableCount INT
SET @nTableCount = (SELECT COUNT(1) FROM SysObjects Where XType='U')

IF EXISTS(SELECT * FROM tempdb.dbo.SysObjects Where XType='U' AND id=object_id(N'tempdb..'+@strTempTablename))
    EXEC sp_executesql @strSql, @strTypeTempTablename, @strTempTablename
SET @strSql = N'CREATE TABLE '+ @strTempTablename + '(TID INT IDENTITY(1,1) PRIMARY KEY, TNAME VARCHAR(128))'
EXEC sp_executesql @strSql, @strTypeTempTablename, @strTempTablename
SET @strSql =
    N'INSERT INTO ' + @strTempTablename + N'(TNAME) 
    SELECT name FROM SysObjects Where XType=''U'' AND name NOT IN (
        SELECT DISTINCT ftable.name FROM Sysforeignkeys
            JOIN Sysobjects fk ON Sysforeignkeys.constid=fk.id
            JOIN Sysobjects ftable ON Sysforeignkeys.fkeyid=ftable.id
            JOIN Sysobjects rtable ON Sysforeignkeys.rkeyid=rtable.id
            WHERE fk.xtype=''F'')
        ORDER BY name'
EXEC sp_executesql @strSql, @strTypeTempTablename, @strTempTablename

DECLARE @nTmpTableCount INT
SET @strSql = N'SELECT @nTmpTableCount=COUNT(1) FROM '+ @strTempTablename
EXEC sp_executesql @strSql, N'@nTmpTableCount INT OUTPUT, @strTempTablename VARCHAR(128)', @nTmpTableCount OUT, @strTempTablename
PRINT @nTmpTableCount

WHILE (@nTmpTableCount < @nTableCount)
BEGIN
    SET @strSql =
        N'INSERT INTO ' + @strTempTablename + N'(TNAME)
        SELECT DISTINCT ftable.name FROM Sysforeignkeys
            JOIN Sysobjects fk ON Sysforeignkeys.constid=fk.id
            JOIN Sysobjects ftable ON Sysforeignkeys.fkeyid=ftable.id
            WHERE fk.xtype=''F''
                AND ftable.name NOT IN (SELECT TNAME FROM ' + @strTempTablename + ')
                AND ftable.name NOT IN (SELECT ftable.name FROM Sysforeignkeys
                    JOIN Sysobjects fk ON Sysforeignkeys.constid=fk.id
                    JOIN Sysobjects ftable ON Sysforeignkeys.fkeyid=ftable.id
                    JOIN Sysobjects rtable ON Sysforeignkeys.rkeyid=rtable.id
                    WHERE fk.xtype=''F''
                        AND rtable.name NOT IN (SELECT TNAME FROM ' + @strTempTablename + ')
                        AND Sysforeignkeys.rkeyid != Sysforeignkeys.fkeyid)
            ORDER BY ftable.name'
    EXEC sp_executesql @strSql, @strTypeTempTablename, @strTempTablename
    SET @strSql = N'SELECT @nTmpTableCount=COUNT(1) FROM '+ @strTempTablename
    EXEC sp_executesql @strSql, N'@nTmpTableCount INT OUTPUT, @strTempTablename VARCHAR(128)', @nTmpTableCount OUT, @strTempTablename
    PRINT @nTmpTableCount
END

/*
Get All Foreign Keys
SELECT fk.name fkname, ftable.name ftablename, rtable.name rtablename, fcol.name fcolname, rcol.name rcolname FROM Sysforeignkeys
  JOIN Sysobjects fk ON Sysforeignkeys.constid=fk.id
  JOIN Sysobjects ftable ON Sysforeignkeys.fkeyid=ftable.id
  JOIN Sysobjects rtable ON Sysforeignkeys.rkeyid=rtable.id
  JOIN Syscolumns fcol ON Sysforeignkeys.fkeyid=fcol.id AND Sysforeignkeys.fkey=fcol.colid
  JOIN Syscolumns rcol ON Sysforeignkeys.rkeyid=rcol.id AND Sysforeignkeys.rkey=rcol.colid
WHERE fk.xtype='F'
ORDER BY ftablename
*/

PRINT CHAR(13)
EXEC('DECLARE c1 CURSOR FOR SELECT TNAME FROM ' + @strTempTablename + ' ORDER BY TID');
OPEN c1
DECLARE @strTablename VARCHAR(128)
FETCH NEXT FROM c1 INTO @strTablename
WHILE (@@FETCH_STATUS=0)
BEGIN
    DECLARE @nRecordCount INT
    SET @strSql = 'SELECT @nRecordCount=COUNT(1) FROM ' + @strTablename 
    EXEC sp_executesql @strSql, N'@nRecordCount INT OUTPUT, @strTablename VARCHAR(128)', @nRecordCount OUT, @strTablename
    IF @nRecordCount > 0
    BEGIN
        DECLARE @nTypeUpdateVer INT
        SET @nTypeUpdateVer = (SELECT xtype FROM SysColumns WHERE id=Object_Id(@strTablename) AND name='UpdateVer')
        DECLARE @bWithUpdateVer BIT
        SET @bWithUpdateVer = 0
        
        DECLARE c2 CURSOR FOR
            SELECT name FROM SysColumns WHERE id=Object_Id(@strTablename) 
        OPEN c2
        DECLARE @strAllColumnname VARCHAR(8000)
        SET @strAllColumnname = ''
        DECLARE @strColumnname VARCHAR(128)
        FETCH NEXT FROM c2 INTO @strColumnname
        WHILE (@@FETCH_STATUS=0)
        BEGIN
            IF @strColumnname = 'UpdateVer' AND @nTypeUpdateVer != 189
                SET @bWithUpdateVer = 1
            IF @strColumnname != 'UpdateVer' OR @bWithUpdateVer = 1
                IF @strAllColumnname = ''
                    SET @strAllColumnname = '[' + @strColumnname + ']'
                ELSE
                    SET @strAllColumnname = @strAllColumnname + ',' + '[' + @strColumnname + ']'
            FETCH NEXT FROM c2 INTO @strColumnname
        END
        CLOSE c2
        DEALLOCATE c2

        DECLARE @nIdentityCount INT
        SET @nIdentityCount = (SELECT COUNT(1) FROM SysColumns WHERE id=Object_Id(@strTablename) AND colstat=1)
       
        IF @nIdentityCount > 0
            PRINT 'SET IDENTITY_INSERT DbTo.dbo.' + @strTablename + ' ON'
        PRINT 'INSERT INTO DbTo.dbo.' + @strTablename + '(' + @strAllColumnname +
            ') SELECT '  + @strAllColumnname + ' FROM DbFrom.dbo.' + @strTablename
        IF @nIdentityCount > 0
            PRINT 'SET IDENTITY_INSERT DbTo.dbo.' + @strTablename + ' OFF'
        PRINT ''
    END
    FETCH NEXT FROM c1 INTO @strTablename
END
CLOSE c1
DEALLOCATE c1

--SET @strSql = N'SELECT * FROM '+ @strTempTablename
--EXEC sp_executesql @strSql, @strTypeTempTablename, @strTempTablename
SET @strSql = N'DROP TABLE ' + @strTempTablename
EXEC sp_executesql @strSql, @strTypeTempTablename, @strTempTablename

SQLite读写二进制数据


- (MRecTemplate*)GetDataRecTemplate
{
    MRecTemplate* data = [[[MRecTemplate alloc] init] autorelease];

    sqlite3_stmt* stmt;
    NSString* str = @"SELECT * FROM RecTemplates";
    int nResult = sqlite3_prepare_v2(m_db, [str UTF8String], -1, &stmt, NULL);
    if (nResult == SQLITE_OK)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
        {
            int i = 0;
            char* Disclaimer = (char*)sqlite3_column_text(stmt, i++);
            data.Disclaimer = Disclaimer ? [NSString stringWithUTF8String:Disclaimer] : @"";
            data.Logo = [[[NSData alloc]
                         initWithBytes:sqlite3_column_blob(stmt,i) length:sqlite3_column_bytes(stmt,i++)]
                         autorelease];
            char* DateModified = (char*)sqlite3_column_text(stmt, i++);
            data.DateModified = [m_dfDateTime dateFromString:[NSString stringWithUTF8String:DateModified]];
            char* ServerTime = (char*)sqlite3_column_text(stmt, i++);
            data.ServerTime = [m_dfDateTime dateFromString:[NSString stringWithUTF8String:ServerTime]];
        }
        sqlite3_finalize(stmt);
    }
    else
    {
        LOG(@"Problem in DB, return=%d, SQL=%@", nResult, str);
    }

    return data;
}

- (BOOL)AddDataRecTemplate:(MRecTemplate*)data
{
    if (data.ServerTime == nil)
        data.ServerTime = [NSDate date];

    sqlite3_stmt* stmt;
    NSString* str = [NSString stringWithFormat:
                     @"INSERT INTO RecTemplates(Disclaimer, Logo, ServerTime) VALUES('%@', ?, '%@')",
                     data.Disclaimer,
                     [m_dfDateTime stringFromDate:data.ServerTime]];
    int nResult = sqlite3_prepare_v2(m_db, [str UTF8String], -1, &stmt, NULL);
    if (nResult == SQLITE_OK)
    {
        if (data.Logo)
            sqlite3_bind_blob(stmt, 1, [data.Logo bytes], [data.Logo length], NULL);
        else
            sqlite3_bind_blob(stmt, 1, nil, -1, NULL);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
    }
    else
    {
        LOG(@"Problem in DB, return=%d, SQL=%@", nResult, str);
        return NO;
    }

    return YES;
}

- (BOOL)UpdateDataRecTemplate:(MRecTemplate*)data
{
    if (data.ServerTime == nil)
        data.ServerTime = [NSDate date];

    sqlite3_stmt* stmt;
    NSString* str = [NSString stringWithFormat:
                     @"UPDATE RecTemplates SET Disclaimer='%@', Logo=?, ServerTime='%@'",
                     data.Disclaimer,
                     [m_dfDateTime stringFromDate:data.ServerTime]];
    int nResult = sqlite3_prepare_v2(m_db, [str UTF8String], -1, &stmt, NULL);
    if (nResult == SQLITE_OK)
    {
        if (data.Logo)
            sqlite3_bind_blob(stmt, 1, [data.Logo bytes], [data.Logo length], NULL);
        else
            sqlite3_bind_blob(stmt, 1, nil, -1, NULL);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
    }
    else
    {
        LOG(@"Problem in DB, return=%d, SQL=%@", nResult, str);
        return NO;
    }

    return YES;
}

Database is in transition. Try the statement later.

–get the dbid which is in Transition mode, check the status is 0 or not.

select * from sys.databases where name =

–if status is not zero reset db status

exec sp_resetstatus

–look for blocking spid or IF WaitRsource column has that DBID

select * from sys.sysprocesses

–Look for waitresource column if that dbid is blocking

select * from sys.dm_exec_requests

–kill the spid or sessionid which is Bloking, OR try to kill session/process which is waiting on resurces.

kill

— immediatly execute put db offline with rolback

ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE

SQL Server Base64编码


CREATE FUNCTION ToBase64 (@Input varchar(6000))
RETURNS varchar(8000)
AS
BEGIN
   DECLARE
      @Output varchar(8000),
      @Bits varbinary(3),
      @Pos int
   SET @Pos = 1
   SET @Output = ''
   WHILE @Pos <= Len(@Input) BEGIN
      SET @Bits = Convert(varbinary(3), Substring(@Input, @Pos, 3))
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 1, 1) / 4 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 1, 1) % 4 * 16 + Substring(@Bits, 2, 1) / 16 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 2, 1) % 16 * 4 + Substring(@Bits, 3, 1) / 64 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 3, 1) % 64 + 1, 1)
      SET @Pos = @Pos + 3
   END
   RETURN (Left(@Output, Len(@Output) - 3 + Len(@Bits)) + Replicate('=', 3 - Len(@Bits)))
END
GO
SELECT dbo.ToBase64('Base 64 encoding')
-- QmFzZSA2NCBlbmNvZGluZw==

SQL Server十六进制字符串转二进制


CREATE FUNCTION dbo.HexStrToVarBin(@hexstr VARCHAR(max))
RETURNS varbinary(max)
AS

BEGIN
   DECLARE @hex CHAR(2), @i INT, @count INT, @b varbinary(max), @odd BIT, @start bit
   SET @count = LEN(@hexstr)
   SET @start = 1
   SET @b = CAST('' AS varbinary(1))
   IF SUBSTRING(@hexstr, 1, 2) = '0x'
       SET @i = 3
   ELSE
       SET @i = 1
   SET @odd = CAST(LEN(SUBSTRING(@hexstr, @i, LEN(@hexstr))) % 2 AS BIT)
   WHILE (@i <= @count)
    BEGIN
       IF @start = 1 AND @odd = 1
       BEGIN
           SET @hex = '0' + SUBSTRING(@hexstr, @i, 1)
       END
       ELSE
       BEGIN
           SET @hex = SUBSTRING(@hexstr, @i, 2)
       END
       SET @b = @b +
               CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]'
                   THEN CAST(SUBSTRING(@hex, 1, 1) AS INT)
                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS INT)
               END * 16 +
               CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]'
                   THEN CAST(SUBSTRING(@hex, 2, 1) AS INT)
                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT)
               END AS binary(1))
       SET @i = @i + (2 - (CAST(@start AS INT) * CAST(@odd AS INT)))
       IF @start = 1
       BEGIN
           SET @start = 0
       END
    END
    RETURN @b
END

用VBS将Access数据库表结构转SQL语句


Sub Class1()
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim ndx As DAO.Index
    Dim strSQL As String
    Dim strFlds As String
    Dim strCn As String

    Dim fs, f

    Set db = CurrentDb

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.CreateTextFile("D:Schema.txt")

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4)  "Msys" Then
            strSQL = "CREATE TABLE [" & tdf.Name & "] ("

            strFlds = ""

            For Each fld In tdf.Fields

                strFlds = strFlds & ",[" & fld.Name & "] "

                Select Case fld.Type

                    Case dbText
                        'No look-up fields
                        strFlds = strFlds & "varchar(" & fld.Size & ")"

                    Case dbLong
                        If (fld.Attributes And dbAutoIncrField) = 0& Then
                            strFlds = strFlds & "integer"
                        Else
                            strFlds = strFlds & "integer"
                        End If

                    Case dbBoolean
                        strFlds = strFlds & "bit"

                    Case dbByte
                        strFlds = strFlds & "byte"

                    Case dbInteger
                        strFlds = strFlds & "integer"

                    Case dbCurrency
                        strFlds = strFlds & "decimal"

                    Case dbSingle
                        strFlds = strFlds & "float"

                    Case dbDouble
                        strFlds = strFlds & "float"

                    Case dbDate
                        strFlds = strFlds & "datetime"

                    Case dbBinary
                        strFlds = strFlds & "image"

                    Case dbLongBinary
                        strFlds = strFlds & "image"

                    Case dbMemo
                        If (fld.Attributes And dbHyperlinkField) = 0& Then
                            strFlds = strFlds & "text"
                        Else
                            strFlds = strFlds & "hyperlink"
                        End If

                    Case dbGUID
                        strFlds = strFlds & "uniqueidentifier"

                End Select

            Next

            strSQL = strSQL & Mid(strFlds, 2) & ")"
            f.WriteLine vbCrLf & strSQL

            'Indexes
            For Each ndx In tdf.Indexes
                Exit For

                If ndx.Unique Then
                    strSQL = "strSQL=""CREATE UNIQUE INDEX "
                Else
                    strSQL = "strSQL=""CREATE INDEX "
                End If

                strSQL = strSQL & "[" & ndx.Name & "] ON [" & tdf.Name & "] ("

                strFlds = ""

                For Each fld In tdf.Fields
                    strFlds = ",[" & fld.Name & "]"
                Next

                strSQL = strSQL & Mid(strFlds, 2) & ") "

                strCn = ""

                If ndx.Primary Then
                    strCn = " PRIMARY"
                End If

                If ndx.Required Then
                    strCn = strCn & " DISALLOW NULL"
                End If

                If ndx.IgnoreNulls Then
                    strCn = strCn & " IGNORE NULL"
                End If

                If Trim(strCn)  vbNullString Then
                    strSQL = strSQL & " WITH" & strCn & " "
                End If

                f.WriteLine vbCrLf & strSQL & """" & vbCrLf & "Currentdb.Execute strSQL"
            Next
        End If
    Next

    f.Close
End Sub