Execute .NET Code under SQL Server

----------Create one database anmed TestingCLR-----------------------

--ALTER DATABASE TestingCLR SET TRUSTWORTHY ON

--GO

-----Start---Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

------using System;

------using System.Collections.Generic;

------using System.Text;

------using System.IO;

------using System.Data.SqlTypes;

------using System.Data.SqlClient;

------using Microsoft.SqlServer.Server;

------namespace ManagedCodeAndSQLServer

------{

------    public class BaseFunctionClass

------    {

------        #region "Default Constructor"

------        public BaseFunctionClass()

------        {

------        }

------        #endregion

------        #region "Welcome Function"

------        ///

------        /// This function will be called from the SQL Stored Procedure.

------        ///

------        /// Name

------        /// Welcome Message

------        [SqlProcedure]

------        public static void GetMessage(SqlString strName, out SqlString

------        strMessge)

------        {

------            strMessge = "Welcome," + strName + ", " + "your code is executed under CLR !";

------        }

------        #endregion

------    }

------}

-----End------Create one C# class library Project "ManagedCodeAndSQLServer.dll" with Below code ----------------

--sp_configure 'clr enable', 1

--GO

--RECONFIGURE

--GO

--CREATE ASSEMBLY ManagedCodeAndSQLServer

--AUTHORIZATION dbo

--FROM 'C:\Dev\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.dll'

--WITH PERMISSION_SET = UNSAFE

--GO

--CREATE ASSEMBLY Messaging

--AUTHORIZATION dbo

--FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

--WITH PERMISSION_SET = UNSAFE

--GO

--CREATE PROCEDURE usp_UseHelloDotNetAssembly

--@name nvarchar(200),

--@msg nvarchar(MAX)OUTPUT

--AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage

--GO

--DECLARE @msg varchar(MAX)

--EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output

--PRINT @msg

-----Ref : http://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server

SQL Column level Encryption / Decryption

----------- Prerequisites to be done --------------------Start----------

--SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';

--CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';

--CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data';

--CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1;

----------- Prerequisites to be done --------------------End----------

OPEN SYMMETRIC KEY SymmetricKey1

DECRYPTION BY CERTIFICATE Certificate1;

Declare @Card_number as varchar(25)

set @Card_number = '1234'

select @Card_number OrginalVale, EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number) Encryptedvalue, CONVERT(varchar, DecryptByKey(EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number))) Decryptedvalue

CLOSE SYMMETRIC KEY SymmetricKey1;

Make Chart (Spatial results) from SQL Query

--To Draw a line string

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

SELECT geometry::STGeomFromText(

'LINESTRING(0 0,1 1)', 0 );

--To Draw Polygons

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

SELECT geometry::STGeomFromText(

'POLYGON((0 0,0 1,1 1,1 0,0 0))',

0 );

SELECT geometry::STGeomFromText(

'MULTIPOLYGON(

((0 0,0 1,1 1,1 0,0 0)),

((2 0,2 1,3 1,3 0,2 0)))', 0 );

--Fetch Data from Table and Form chart

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

--Create Table

CREATE TABLE [dbo].[Sales](

[FY] [int] NOT NULL,

[Sales] [int] NOT NULL

) ON [PRIMARY]

--Form chart from Results of table

DECLARE @WKT AS VARCHAR(8000);

SET @WKT =

  STUFF(

    (SELECT ',' + CAST( FY AS CHAR(4) ) + ' ' + CAST( Sales AS VARCHAR(30) )

    FROM Sales

    ORDER BY FY

    FOR XML PATH('')), 1, 1, '');

Select @WKT

SELECT geometry::STGeomFromText( 'LINESTRING(' + @WKT + ')', 0 );

--Form Chart with some manipulations in table Result

DECLARE @WKT AS VARCHAR(8000);

SET @WKT =

  STUFF(

    (SELECT ',((' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0,' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' ' +

      CAST( Sales AS VARCHAR(30) ) + ',' +

      CAST( FY + 0.3 AS VARCHAR(30) ) + ' ' +

      CAST( Sales AS VARCHAR(30) ) + ',' +

      CAST( FY + 0.3 AS VARCHAR(30) ) + ' 0,' +

      CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0))'

    FROM Sales

    ORDER BY FY

    FOR XML PATH('')), 1, 1, '');

SELECT geometry::STGeomFromText( 'MULTIPOLYGON(' + @WKT + ')', 0 );

Ref : http://sqlmag.com/t-sql/generating-charts-and-drawings-sql-server-management-studio

TFS Add Work Item Column

1) Get List of WorkItem:

     witadmin listwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName

2) Export Specific WorkItem Type from the List:

     witadmin exportwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName/f:C:\TFS\WorkItem\Product_Backlog_Item.xml /n:"Product Backlog Item"

3) Edit the xml in VS 2015 it will open Eork Item Editor

4) Add Custom Columns ( StartDate -- Custom.StartDate, End Date -- Custom.EndDate)

5) Set the Layout where the colum should display

6) Save the WorkItem Template

7) Validate the WorkItem template

     witadmin importwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName/f:C:\TFS\WorkItem\Product_Backlog_Item.xml /v

8) Import the WorkItem template (by removing /v in previous command)

    witadmin importwitd /collection:http://XXXXXX/tfs/DefaultCollection /p:ProjectName /f:C:\TFS\WorkItem\Product_Backlog_Item.xml

What to DB if DB Goes Suspect Mode ?

--First Check the auto recovery status may be the DB will be in recovery mode 

--xp_readerrorlog

--IF Db not in recovery mode and it is Suspect mode

-- Execute the below query line by line after removing comments
-------------------------------------------------------------------------------------------------------------------

--EXEC sp_resetstatus 'dbname';

--ALTER DATABASE [dbname] SET EMERGENCY

--DBCC checkdb('dbname')

--ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--DBCC CheckDB ('dbname', REPAIR_ALLOW_DATA_LOSS)

--ALTER DATABASE [dbname] SET MULTI_USER

--DBCC CheckDB ('dbname')

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

--Ref1 : http://www.sql-server-performance.com/2015/recovery-sql-server-suspect-mode/2/

--Ref2: http://www.stellarinfo.com/support/kb/index.php/article/procedure-to-recover-sql-database-from-suspect-mode

free hit counter

இருக்கும் இடம் தெரிந்தும் ! உன்னை தேடி உயிர் வாழ்வேன்.

Saving SQL Server backup file in multiple parts

Normal backup

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME
TO DISK = 'D:\DBNAME.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

to split the size into 3 use the following code, it will split the size of 3 gb into 1 GB each file you can add more files or less depending on the size you want

SET STATISTICS IO ON

SET STATISTICS TIME ON


BACKUP DATABASE DBNAME TO

DISK = 'D:\DBBackups\ReportServer\DBNAME _Split1.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split2.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split3.bak'


SET STATISTICS IO OFF

SET STATISTICS TIME OFF