SQLXML Bulk import

Import a single table

Get the XSD schema file and add the following markups to it:

1. Mark the parent  elements which do not have a corresponded database table as sql:is-constant="1"
2. Mark the iterated element which each instance should go to a record as sql:relation="DB_table_name"
3. Mark the elements which do not have a corresponded database field as sql:mapped="false"
4. Assign the field name to the elements which database field name is different with sql:field="DB_field_name"
5. Make sure the field length are roomy enough to fit all the data
6. Automatic convertion of date fields does not work properly, so such DB field should be type of string.

Setup a relationship between tables

1. Add the sql:relationship element to xsd:annotation/xsd:appinfo
2. Add attributes which declare the name="Relationship_annotation_name" (random identifier string) and the related DB tables and PK-FK fields as described here. 
3. To the same element where you declared the child DB table name with sql:relation="DB_table_name" add the attribute which references to the declared relationship: sql:relationship="Relationship_annotation_name"

See more examples here.

Execute the prepared bulk import setup

Download and install the SQLXMLBulkload object and execute a VbScript like the following:

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "Connection String to the database"
objBL.ErrorLogFile = "error.log"
objBL.CheckConstraints=True
objBL.KeepNulls=True
objBL.Execute "annotated_schema.xsd", "input_data.xml"

If the object returns an error, check this site, may be it will give you a clue. The log file sometimes contains a bit more information than in the thrown error description.

Object properties to configure

If the tables already exist the .SchemaGen property of the SQLXMLBulkload object should remain FALSE.
If it is set to TRUE, the tables will be created automatically.
String XSD type is declared as [nvarchar](1000).

.KeepIdentity could be useful when a normalized table structure is created, in other words when an attempt to insert a reference record fails when the record already was created (with the primary key). If the property is set to True the records are inserted one by one which is a lot slower than the default bulk inserting. But the reference records duplicates will not be created.

.KeepNulls is useful to left the fields NULL when there is no actual value in the XML.


Here's the list of all properties of the SQLXMLBulkload object.

Bring Eclipse back on track

All of the sudden, Eclipse stopped working. It started doing something, taking forever for some weird initialization processes like "initializing java tooling" and the similar.

A tip from this site actually  helped to solve the issue:
Kill the stale Eclipse, delete the folder
.metadata/.plugins/org.eclipse.core.resources/.projects
and start the Eclipse again. The folder will be created on the next start.



Don't forget also to delete .metadata/.lock each time Eclipse crashes or being killed.
 

Remotely connect to the windows 2008 console.

Windows server won't let you to connect to the physical console session remotely.
Bot that's still possible.

Connect to a new session, then execute the following command:

tscon.exe 1

replace "1" with the console session if it's differ. To know it, open the task manager's Users tab and look for the console session id number.

To change the network profile

If you wired two PC together using a crossover cable or a hub without an outbound connection, windows makes the network connection type (profile) "Public" and all the public firewall rules will be applied. It's possible to make it "Private" using the script as described in this blog.

MS SQL 2008 rake

If you want to modify a table property through the UI which requires a table recreation, then you need to change the following options in Management Studio:
Tools ->Options-> Designers -> Table and Database Designers -> uncheck the option "Prevent saving changes that require table re-creation"

from here

Restoring an ability to debug a script

After the IE was upgraded to version 10, the VS's debug can't be attached to a script anymore.
This command helps to solve the issue:

regsvr32.exe "%ProgramFiles(x86)%\Common Files\Microsoft Shared\VS7Debug\msdbg2.dll"

Windows on SSD

Made a new computer for my work place. It's not an upgrade, because the old one was not touched and completely replaced. I've got an ASUS motherboard (P8H77V-LE), an IvyBridge i7 CPU, plenty of memory, an Intel 520 series SSD for the system boot and a WD Black HDD for active work data.

To reduce the number of writing operation to the SSD, I moved some folders including my user profile to the HDD, and also instructed the system to create all new profiles there.

The latter is the most easy part, just change the registry value
HKLM\SOFTWARE\Microsoft\Windows NT\Current Version\ProfileList\ProfilesDirectory

To redirect all the other folders you need to create a junction between the expected location and the actual one. The full list of the folders which were moved to be located on the HDD:

C:\Users\%myname%\
C:\ProgramData\
C:\Windows\Logs\
C:\Windows\System32\winevt\
C:\Windows\ServiceProfiles\LocalService
C:\Windows\SoftwareDistribution\DataStore\

To move a folder I need to boot to the recovery console (reboot, F8, repair (if F8 does notwork, try F5))
In the repair console the drive letters are changed. The drive C: becomes E:

Important! If there more than two drives in the system, the target HDD could also change the letter. Make sure it's letter is the same in the recovery mode as in the normal operation mode! Unplug or disable (in the BIOS setup) all other drives.

Use the robust copy command which carefully copies all the directory structure:
robocopy /copyall /mir /xj E:\ProgramData\ D:\ProgramData\

after the command above successfully creates a directory mirror, rename the source directory (delete it if you are brave enough) and then make a junction (a link), using the following command:

mklink /J E:\ProgramData\ D:\ProgramData\

make sure, the ACL remains the same for the original and the junction, use the icacls command as follows (assume the current dir is E:\):


icacls E:\ProgramData\ /save ProgramData.acl
- before you put the original away

icacls . /restore ProgramData.acl
- after you have created the junction

Directories like the following can't be safely moved, windows does not even boot:

C:\Windows\System32\config
C:\Windows\security

 Also do the following:

1. Direct the %TEMP% and %TMP% environment variable to a HDD folder.
2. Make the page file on the HDD as well. Even if you have a lot of RAM, the page file is still required.

Use windows' Resource Monitor to check, that most of writes now go to the D: drive (HDD). Use the Sysinternals' ProcMon to collect a statistic about file operations and make decision what could be also moved to the HDD.