Archive for the 'Misc' Category

Read IP from TMG Log

2012/04/23

 You can use SQL Server as TMG log destination. You can also use BCP to save data to local csv file then edit with your favorite excel. The only problem here is that columns ClientIP, DestHostIP and NATAddress are uniqueidentifier type. Record is like "C0A84C4A-FFFF-0000-0000-000000000000". The script below is to transfer them to readable IPv4 format. Enjoy it.

PS. C0A84C4A == 192.168.76.74.

SELECT *,
    CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),1,2), 1)))
    +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),3,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),5,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),7,2), 1)))
      as ClientIP_New
        ,
      CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),1,2), 1)))
    +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),3,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),5,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),7,2), 1)))
      as DestHostIP_New
        ,
      CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),1,2), 1)))
    +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),3,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),5,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),7,2), 1)))
      as NATAddress_new
from dbo.WebProxyLog
 

Posted in Misc, TSQL | Leave a Comment »

Chinese Character Unicode Conversion – 1

2011/12/16

There are 2 ways to do the conversion: in-place and side-by-side. In-place means alter the column type directly; side-by-side means create a new database with new schema, and then use bcp to export/import data. It’s hard to say which one is better, it depends. If most of the data in database will be converted, I will use side-by-side. If not, I will use in-place.
 

This is a post from SQLCAT discuss side-by-side conversion http://blogs.msdn.com/b/mssqlisv/archive/2006/07/07/659374.aspx, you can take it as good start of this topic. In my post, I will discuss how to convert char/nvarch/ntext columns to unicode type, which store Chinese charater using in-place method. To narrow the scope, I assume collation of the database is already Unicode ready like Chinese_Hong_Kong_Stroke_90, and the SQL Server is SQL 2005/2008 which have datatype nvarchar(max). And I will cover real-world scenarios: find out columns which store Chinese character, planning of extra storage, validate the converted result and handle constraint.

 

Posted in Install Update Consolidation, Misc | Leave a Comment »