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

SQL : Split string into Column

 SELECT Split.a.value('.', 'VARCHAR(24)') AS String  

 FROM (SELECT CAST ('<M>' + REPLACE('331AW150A01F01,331AW250A01F01,331AW350A01F01,331WF450A01F01', ',', '</M><M>') + '</M>' AS XML) AS String ) 

 AS A CROSS APPLY String.nodes ('/M') AS Split(a); 

Sql : Concate columns in group by

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT * FROM #YourTable

SELECT

  [ID],

  STUFF((

    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))

    FROM #YourTable

    WHERE (ID = Results.ID)

    FOR XML PATH (''))

  ,1,2,'') AS NameValues

FROM #YourTable Results

GROUP BY ID

DROP TABLE #YourTable

-- (thank you) Ref:- http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server