Tuesday, June 18, 2013

Convert Number to Roman Numerals in Sql Serve

CREATE FUNCTION fnConvertIntToRoman(@i INT)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN    Replicate('M', @i/1000)
            + REPLACE(REPLACE(REPLACE(
                  Replicate('C', @i%1000/100),
                  Replicate('C', 9), 'CM'),
                  Replicate('C', 5), 'D'),
                  Replicate('C', 4), 'CD')
             + REPLACE(REPLACE(REPLACE(
                  Replicate('X', @i%100 / 10),
                  Replicate('X', 9),'XC'),
                  Replicate('X', 5), 'L'),
                  Replicate('X', 4), 'XL')
             + REPLACE(REPLACE(REPLACE(
                  Replicate('I', @i%10),
                  Replicate('I', 9),'IX'),
                  Replicate('I', 5), 'V'),
                  Replicate('I', 4),'IV')

END

How to recover corrupted database file( .mdf .ldf).

EXEC sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

UPDATE sysdatabases SET status=1073741840 WHRE name='DatabaseName'

CHECKPOINT--shutdown with nowait

DBCC traceon(3604)

DBCC rebuild_log ('DatabaseName', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DatabaseName_Log.LDF')


USE DatabaseName
sp_dboption 'DatabaseName', 'single_user', 'true'

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

USE MASTER

UPDATE sysdatabases SET status = 0 WHERE name = 'DatabaseName'

DBCC CHECKALLOC ('DatabaseName')

DBCC CHECKDB ('DatabaseName')

sp_dboption 'DatabaseName', 'single_user', 'false'

USE MASTER

sp_configure 'allow updates', 0

EXEC sp_resetstatus DatabaseName