SonarQube MSSQL backend setup

1. Create Database
• CREATE DATABASE "sonar" COLLATE Latin1_General_CS_AS 
• It needs to be case and accent sensitive
• Create user and add permissions to user so tables can be created with SonarQube start
2. Modify SonarQube configuration to point out to database created
• Go to SonarQube\conf\sonar.properties and follow instructions in the file
• Uncomment and change connection string e.g. sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=sonar;integratedSecurity=true
• In case there is not used integrated also specify sonar.jdbc.username and sonar.jdbc.password
3. In case there is used integrated connection to database used then download Microsoft JDBC driver, unzip and somewhere to the system PATH place sqljdbc_auth.dll (either 32 or 64 bit based on your operating system)
4. Restart SonarQube instance.

SonarQube installation quide 

Removing duplicate records from database table

If there is need to select or remove data from table which are duplicate by multiple fields (field1 and field2) in the below example, there can be used query like

WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY field1, field2
                                       ORDER BY ( SELECT 0)) RN
         FROM   mst_table)
DELETE FROM cte
WHERE  RN > 1;

MSSQL server - How to return values from multiple nodes in XML in single field

DECLARE @xml xml
SET @Xml = '<FIELD NAME="NAME" BASE="5179827" COUNT="4">
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="c">...</TOKEN>
</FIELD>'

SELECT
  x.query('data(TOKEN/@TEXT)') AS List
 ,x.query('distinct-values(TOKEN/@TEXT)') AS DistinctList
 , x.query('<data>
    {
     for $x in distinct-values(TOKEN/@TEXT)
     return 
      (concat($x, ","))
    }
   </data>
 ').query('data/text()') AS CommaSeparatedList
FROM @xml.nodes('/FIELD') AS d(x)

References

http://www.olcot.co.uk/sql-blogs/using-xquery-to-remove-duplicate-values-or-duplicate-nodes-from-an-xml-instance

MSSQL server - how to read XML data - XPATH, sql:variable and local-name()

Example xml used for below queries

declare @xml xml
set @xml = '<Document>
 <Main>
  <Field_01>Field_01Value</Field_01>
  <Field_05>Field_05Value</Field_05>
  <TableFields>
   <Table_02>
    <Row>
     <Column_01>Table_02Row_01Col_01Value</Column_01>
     <Column_02>Table_02Row_01Col_01Value</Column_02>
    </Row>
    <Row>
     <Column_01>Table_02Row_02Col_01Value</Column_01>
     <Column_02>Table_02Row_02Col_02Value</Column_02>
    </Row>
   </Table_02>
  </TableFields>
 </Main>
</Document>'

Get the value of Field_01 tag

SELECT @xml.value('(//Field_01/text())[1]', 'nvarchar(max)')

Get the value of field in case you have the field name defined dynamically

declare @field varchar(10)
set @field = 'Field_05'
SELECT @xml.value('(//*[local-name()=sql:variable("@field")]/text())[1]', 'nvarchar(max)')

Similar in case you need value of value of second row of column_01 in table defined dynamically

set @field = 'Table_02'
SELECT @xml.value('(//*[local-name()=sql:variable("@field")]/*/Column_01/text())[2]', 'nvarchar(max)')

MSSQL server - how to read XML data and join with another table data

This code snippet shows how to work with data stored in xml variable (or column of type xml). The xml data are joined with table and there are joined some data to output.

-- input
set @xml = '<docs>
<doc> <DocId>1000052</DocId><InvoiceNumber>S37</InvoiceNumber></doc>
<doc> <DocId>1000053</DocId><InvoiceNumber>S74</InvoiceNumber></doc>
<doc> <DocId>1000054</DocId><InvoiceNumber>E85</InvoiceNumber></doc>
</docs>'


SELECT
   p.value('(DocId)[1]', 'VARCHAR(10)') AS DocId,
   p.value('(InvoiceNumber)[1]', 'VARCHAR(100)') AS InvoiceNumber,
   ISNULL(d.SupplierID, '') AS Supplier
FROM @xml.nodes('/docs/doc') doc(p)
INNER JOIN Documents d ON d.DocId = p.value('(DocId)[1]', 'VARCHAR(10)')
FOR XML AUTO, ROOT ('docs')

Output:

<docs>
  <doc DocId="1000052" InvoiceNumber="S37" Supplier="Microsoft" />
  <doc DocId="1000053" InvoiceNumber="S74" Supplier="Amazon" />
  <doc DocId="1000054" InvoiceNumber="E85" Supplier="Sony" />
</docs>

Resources:

http://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008

https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://msdn.microsoft.com/en-us/library/ms178107.aspx

MSSQL server - how to generate XML 1

Xml can be generated in SQL server many different ways and it is not always straightforward

Requirement:

Generate elements with same names and some data as attributes and some as element inner text. 

Example:

<document>
  <fields>
    <field name="Field_1" type="Field1">Field1 Value</field>
    <field name="Field_2" type="Field2">Field2 Value</field>
  </fields>
</document>

Query:

select TOP 1
 'Field1' 'field/@type',
 'Field_1' 'field/@name',
 Field1Value AS 'field',
 '',
 'Field2' 'field/@type',
 'Field_2' 'field/@name',
 Field2Value AS 'field',
 ''
from Invoice as tbl1
for xml path('fields'), type, Root('Document')

Resources: 
http://stackoverflow.com/questions/25412429/sql-server-generating-xml-with-generic-field-elements

MSSQL trace file analysis

Today I was looking for a way how to access MSSQL server trace data from TSQL. The solution is to import data to database via function fn_trace_gettable.

SELECT * INTO temp_trc
FROM fn_trace_gettable('c:\temp\trace.trc', default);

Issue to restore of replicated database solved

Restore of backuped database which used replications can be a problem. Database was restored successfully but it was still marked as part of publication (see is_published in sys.databases). There was not possible to truncate, alter structure of tables etc.

This issue can be resolved by running this command on database:

exec sp_removedbreplication 'database_name'

How to Export Image column to file on MSSQL II

Another way how to export binary data from database. It is possible to use bcp utility.

bcp "SELECT img FROM db_name.dbo.Images WHERE ID = 1" queryout "c:\001.jpg" -T -S server

Once you run it you will be asked for the file storage type (no change required), prefix-length (needs to be 0), length of field and field terminator (no change)

Update of XML data

Code snippet for updating specific data in xml column in mssql

declare @date datetime
set @date = GETDATE()

UPDATE XmlDocuments
SET    xmlmetadata.modify ('replace value of (/Header/Date/text())[1] with sql:variable("@date")') 
WHERE ID = '1000001'

Bulk update of Image data in MSSQL

This is just simple example how to do update of image data in Microsoft SQL database.

UPDATE Images SET Img = (SELECT BulkColumn AS Img FROM OPENROWSET(BULK N'C:\NoImage.TIF', SINGLE_BLOB) AS [Document])

How to Export Image column to file on MSSQL

It is not that easy how it looks like:) Used the same approach as it is in the answer below but completed the example.

1. Enable the extended stored procedures:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

2. Use sp_OA stored procedures

DECLARE @objStream INT
DECLARE @imageBinary VARBINARY(MAX)
DECLARE @filePath VARCHAR(8000)

SELECT @imageBinary = img
FROM Images
WHERE ID = 1

SET @filePath = 'c:\img_1.jpg'

EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT
EXEC sp_OASetProperty @objStream, 'Type', 1
EXEC sp_OAMethod @objStream, 'Open'
EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary
EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2
EXEC sp_OAMethod @objStream, 'Close'
EXEC sp_OADestroy @objStream 

Resources:
How to export a ms sql image column to a file

I didn't have much time to write an article so I decided to post a list of issues I needed to resolve. Hope it can help other people with their issues too.

Reporting services
Report server report viewer 404 - File or directory not found

MSSQL server
Understanding locking in SQL server
Anatomy of deadlock
Deadlock troubleshooting, Part 1
Deadlock troubleshooting, Part 2
Deadlock troubleshooting, Part 3

ASP.NET
Getting :hover to Work in IE7
ASP.NET Menu and IE8 rendering white issue

General
Clipboard is not working

How to get database data file path

Recently I needed to create universal script which added new file group and file to database regardless of where the MSSQL server was installed. The reason for creating new file was to split data which were changing a lot (table with cached documents) and data which were more or less static.

There was necessary to find out where is located primary file for current database and then to get just directory where the file was located.

DECLARE @PATH nvarchar(max)

SELECT @PATH = LEFT(filename, LEN(filename) + 1 - CHARINDEX('\', REVERSE(filename)))
FROM  master.dbo.sysaltfiles WHERE db_name(dbid) = db_name() AND name = 'PrimaryFileName'

Database Project in Visual Studio 2008

There exists database project in Database Developer edition of Visual Studio 2008. It can be very useful but also painful. The original version needs to have installed local database server for this kind of project but a lot of things can go wrong way. There is a lot of troubleshooting articles on Internet so you think it helps you to resolve the problems quickly - unfortunately not in my case. I have spent almost a day without success until I have found Microsoft® Visual Studio Team System 2008 Database Edition GDR R2. It took me less then one hour to install the package, convert old database project and deploy project. Package installs new database project which doesn't need database server on your local. That was really great and I recommend it GDR to everyone struggling with the original database project.

How to install Microsoft SQL Server 2008 Management Studio Express

I had on my computer installed SQL Server 2008 Express edition and I was looking for something which will help me to work with it. I have found there is Microsoft SQL Server 2008 Management Studio Express. You can find it on Microsoft download pages.
As a prerequisite you will need to install PowerShell if you already don't have it on your computer. I had trouble to use link to PowerShell 1.0 on the download page so I recommend to use this download link to PowerShell 2.0 on Windows Management Framework page in Windows Management Framework Core section.
When you have installed PowerShell you can start to install Management Studio. I was a bit surprised to see that what I downloaded contains SQL Server installation package. So for the installation of Management Studio 2008 you will need to go Installation > New SQL Server stand-alone installation or add features to an existing installation then pass installation checks and in Feature Selection check Management Tools - Basic.

How to uninstall MSSQL server 2008

I have had into some troubles with my computer and I wanted to reinstall MSSQL server 2008 but I was not able to find out how to unintall it. Here is the command which will do it for you:

"c:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\x86\SetupARP.exe" /x86