Web Notes

Since SQL SERVER 2005 it is possible to store XML data in database by using dedicated data type - XML. This type solved many programming problems such storing XML data as simple text which was very unpleasant to maintenance  and detecting errors in document structure. Now stroring XML data is very simple and requires only the creation of column in XML data type. But sometimes we may want to do reverse operation. I mean generating XML document or fragment from non XML columns in a single SELECT statement. Of course SQL Server meets our expectations and allow to transform any results of SELECT statement (which always returns table) to XML structure. To start transforming selected results to XML format we must use FOR XML clause right after last statemnt in normal SELECT. For example instead of using: Code Snippet SELECT ProductNumber , Name , ListPrice , Color FROM Production . Product we should use: Code Snippet USE [AdventureWorks] SELECT ProductNumber ,

Many times our application uses data from external suppliers. These data, mostly recived by the Internet, is written in XML format and has different from our data mode structure. But it`s still very important for us so we want to process them by extracting data from. Of course we may procesing XML  documents in CLR or simple .NET project and than pass them to database but we should this about performance of each our solution. For example if we recieved 100MB XML  document from supplier and we processed them we still need to send those data to our database which means  that we have to pass data by the network- it`s very costly... Now assume that the same operation, connected with proccessing XMLdocuemnt, can be done in SQL SERVER side. Looks great don`t You? So let`s begin. First of all we should learn something more about three things: master.dbo.sp_xml_preparedocument : start preparing passed text as XML document with chec

In many application the database structure can be changed by the user ex. CRM or Reporting. In such situation we may want to know such changes was maded. In SQL SERVER 2008 we have a possibility to react when DML (Data Manipulation Language) AND DDL (Data Definition Language) execution occured in out database. So try to log it. Firstly we want to Create a single table named 'DatabaseChangesLog': use model; GO CREATE SCHEMA LOGS CREATE TABLE DatabaseChangeLogs (    EventId int Identity Primary Key,    EventDate datetime2  Constraint DF_DefaultLogDate DEFAULT(sysdatetime()),    EventType nvarchar(100) NOT NULL,    UserName nvarchar(1050) NOT NULL,    Command nvarchar(max) NOT NULL    ) GO Note that we creata this table in model databse. This means that each newly created database will has this table after creation. For existing databases You need to run this script manullay. Now when we have appropriate table we can insert record to. We are able to detect each

SQL Server offers a lot of system views which allow to get information about each instance without interfering in system tables. All those system views are located in hidden system datadabase name 'Resource'. All system views can be divided into at least two groups: Structure views - which storing information about SQLSERVER properties and custom structure, and begins with schema INFORMATIN_SCHEMA.* . (compilant with ISO) Dynamic views - storing information at runtime from last time instance starts; this begins with sys.dm_* When we need to get information about executed connection in the all current opened session we can query to sys.dm_exec_connections. In this view we see two very important columns: session_id: represent the IDs of all current opened session; note that session ID is presented in each query tab in Managment Studio (ex. 52). most_recent_sql_handle: a handle to the most recent executed query for single session ID. Now assume that we want to preview