Posts

Showing posts from June, 2012

Microsoft SQL Server 2000/2005:Change Database compatibility Level When Restore Database Backup from lower version to higher version.

1.Check Database Compatibility Level. select name,compatibility_level from sys.databases where name like ' %DBNAME% ' Current version is sql server 2000 where compatibility_level=80   EXEC sp_dbcmptlevel DBNAME , 90;--Set SQL Version 2000 to 2005. GO Check Changes compatibility_level  About Me

Microsoft SQL Server 2005:Change string value into multiple rows

Sample Table based on select query. select 1 r,'abcd' String union all select 2,'efgh' union all select 3,'ijkl' union all select 4,'mnop' union all select 5,'qrst' union all select 6,'uvwx' union all select 7,'yz' Create #temp table. select * into #temp from ( select 1 r,'abcd' String union all select 2,'efgh' union all select 3,'ijkl' union all select 4,'mnop' union all select 5,'qrst' union all select 6,'uvwx' union all select 7,'yz') a select *from #temp Output r    String 1    abcd 2    efgh 3    ijkl 4    mnop 5    qrst 6    uvwx 7    yz ;WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B), L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B), L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B), L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B), Nums

Microsoft SQL Server 2005 :Grant Execute Stored Procedure to USER

/* Create a new role for executing stored    procedures */ CREATE ROLE db_executor /* Grant stored procedure execute rights    to the role */ GRANT EXECUTE TO db_executor /* Add a user to the db_executor role */ EXEC sp_addrolemember 'db_executor', ' Username '

Microsoft SQL Server:Resize Database Transaction Log by Detach/Atta

Image
Use Detach/Attach method to re size/re create Transaction Log. 1.Detach Database.   2.Rename old Transaction log. 3.Attach Database.   4.Remove transaction log to attach.    5.Click Ok to Attach.It will create MODEL Database Sized Transaction Log.