Web Notes

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

In MS SQL Server Managment Studio we can connect to any instance by using TCP/IP protocol. To do this we can use the following syntax: tcp: server_name\instance, port . For example: local instance:   tcp: .\R2,51550 remote instance: tcp: 192.168.11.5\R2,999 local alias: tcp: .\alias,51550

Sometimes solutiona that we create stores data from external suppliers. These data is stored in database and than presents to end-user. The problem occured when our suppliers user XML basen technology to send data package to us.  The  XML standard do not allow using some special characters in text (node attribute or value) so each occurance of special characters encoded. Each of us should know that and try do decode those entities before wtiring it to database and/or presents to user. But in real many thing may go wrong and in some rows of our product database these signs may appear. When You recognize the problem You can do three things but only two of them are correct. The first idea ( wrong ) is the attempt to create a CLR stored procedure or function with the System.Web.HttpUtility.HtmlDecode function. The problem is that You can`t add reference to System.Web in CLR projects! So this idea can not be executed. Second idea is to create a stand alone console application and impl