Rename Database

USE master

GO

EXEC sp_dboption Fox8, 'Single User', True

GO

-- if this command fails, use:

-- alter database Fox8 set single_user with rollback IMMEDIATE

-- see: http://copypastenet.blogspot.com/2011/01/spdboption-single-user-command-failed.html

EXEC sp_renamedb 'Fox8', 'Fox8NotUnicode'

GO

EXEC sp_dboption Fox8NotUnicode, 'Single User', False

GO

sp_dboption 'Single User' command failed

Problem:

USE master

GO

EXEC sp_dboption DBNAME, 'Single User', True

GO

Msg 5070, Level 16, State 2, Line 1

Database state cannot be changed while other users are using the database 'DBNAME'

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

sp_dboption command failed.

Solution:

Use this command:

alter database DBNAME set single_user with rollback IMMEDIATE

GO

Converting int to datetime

Converting int to datetime nean to add days as the int number to 1900-01-01 00:00:00.

What nean:

cast(37947 as datetime) = dateadd(day, 37947, '19000101 00:00:00') 

[=20031124 00:00:00]

and:

select cast(0 as datetime) = 1900-01-01 00:00:00

Converting datetime to ing will return the number of the days since 1900-01-01 00:00:00:

select cast(cast('20031124 00:00:00' as datetime) as int) = 37947

BTW:

select cast(37947.5 as datetime) will return 2003-11-24 12:00:00.000

Time convertion: EST and EDT to GMT

EST: Eastern Time Zone is the Eastern Time Zone of the United States of America (USA) and Canada

GMT = EST+5

EDT: Atlantic Daylight Time is the Atlantic Time Zone of the United States of America (USA) and Canada

GMT = EST+4

http://wwp.greenwichmeantime.com/time-zone/usa/eastern-time/convert/

Mssql 2008 R2 Restore error: File '.ndf' is claimed by (4) and (3). The WITH MOVE clause can be used to relocate one or more files

Mssql 2008 R2 Restore Error: 

File '.ndf' is claimed by (4) and (3). The WITH MOVE clause can be used to relocate one or more files.

Solutions:

1.
Restore behavior for new DB was changed in SQL 2008:

In 2005, we can create new (empty) DB and restore from bak file to the new DB.

In 2008, Restore from bak file to new DB (that was not created yet).
(Right click on Databases-->Restore Database...)

Another possible causes of the problem can be (not only for MSSQL 2008):
2.
 Different number of files between the new DB and the source DB.

3.
Attempting to use a file for more than one purpose.

Find text in columns content

Find text in columns content of the tables in the DB.

If you want to replace the text with another text - remove remarks from the remarked lines.

begin tran

SET NOCOUNT ON 

DECLARE @stringToFind VARCHAR(100) 

--DECLARE @stringToReplace VARCHAR(100) 

DECLARE @schema sysname 

DECLARE @table sysname 

DECLARE @count INT 

DECLARE @sqlCommand VARCHAR(8000) 

DECLARE @where VARCHAR(8000) 

DECLARE @columnName sysname 

DECLARE @object_id INT 

                     

SET @stringToFind = 'admin' 

--SET @stringToReplace = 'Jones' 

                        

DECLARE TAB_CURSOR CURSOR  FOR 

SELECT   B.NAME      AS SCHEMANAME, 

         A.NAME      AS TABLENAME, 

         A.OBJECT_ID 

FROM     sys.objects A 

         INNER JOIN sys.schemas B 

           ON A.SCHEMA_ID = B.SCHEMA_ID 

WHERE    TYPE = 'U' 

ORDER BY 1 

          

OPEN TAB_CURSOR 

FETCH NEXT FROM TAB_CURSOR 

INTO @schema, 

     @table, 

     @object_id 

      

WHILE @@FETCH_STATUS = 0 

  BEGIN 

    DECLARE COL_CURSOR CURSOR FOR 

    SELECT A.NAME 

    FROM   sys.columns A 

           INNER JOIN sys.types B 

             ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID 

    WHERE  OBJECT_ID = @object_id 

           AND IS_COMPUTED = 0 

           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext') 

    OPEN COL_CURSOR 

     

    FETCH NEXT FROM COL_CURSOR 

    INTO @columnName 

     

    WHILE @@FETCH_STATUS = 0 

      BEGIN 

print @schema + '.' + @table + '.' + @columnName

----------------------------------------------------

-- only search:

        EXEC (' if exists (select [' + @columnName + '] from [' + @schema + '].[' + @table + '] where [' + @columnName + '] like ''%' + @stringToFind + '%'') ' +

'select [' + @columnName + '] as ' + @schema + '_' + @table + '_' + @columnName +

' from [' + @schema + '].[' + @table + '] where [' + @columnName + '] like ''%' + @stringToFind + '%''')

----------------------------------------------------

-- search/replace:

        --SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')' 

        --SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 

        --EXEC( @sqlCommand + @where) 

        --SET @count = @@ROWCOUNT 

        --IF @count > 0 

        --BEGIN 

            --PRINT @sqlCommand + @where 

            --PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count) 

--print @schema + '.' + @table + '.' + @columnName

        --END 

----------------------------------------------------

         

        FETCH NEXT FROM COL_CURSOR 

        INTO @columnName 

      END 

     

    CLOSE COL_CURSOR 

    DEALLOCATE COL_CURSOR 

     

    FETCH NEXT FROM TAB_CURSOR 

    INTO @schema, 

         @table, 

         @object_id 

  END 

   

CLOSE TAB_CURSOR 

DEALLOCATE TAB_CURSOR

rollback tran

Allows/Don't allow explicit values to be inserted into identity column

-- allow:

set identity_insert dbo.TableA on

-- don't allow:

set identity_insert dbo.TableA off