SQL Server Query Output to XML file

Use this to get the sql server query output into an XML file.
DECLARE @SQL VARCHAR(2000)
DECLARE @ExportResult INT

SET @SQL = 'bcp "SELECT * from [DBName].dbo.TableName FOR XML PATH (''OwnerStatus''), ROOT(''Root'')" queryout D:\EMS\Data.XML -c -t, -T -S SERVERNAME'

EXEC master..xp_cmdshell @SQL

EXEC @ExportResult = master..xp_cmdshell @SQL ,NO_OUTPUT

SELECT @ExportResult

The command requires that cmdshell be enabled. In sql server 2005 this is found in the surface area configuration. In 2008 this can be enabled in the SSMS by running the following.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
GO
RECONFIGURE

Reset Identity in SQL Server 2005

Simple, yet i keep forgetting so to keep me reminded here it is. To reset identity to a table, so that everytime the surrogate keys start with 1 use the following command

DBCC CHECKIDENT('DimAssetUser', RESEED, 0)

This resets the identity to where its starting point, for the DimAssetUser table.

Chinese, Japanese, Korean .... alphabets in SQL Server SSIS

Our SSIS source, a SQL Server 2005 table had customer names in chinese, korean, japanese alphabets, the package was performing sort (duplicate removal - set true) and a conversion to upper case.

The packages work well in development but in QA the destination table, which was supposed to have those chinese, korean, japanese alphabets had unreadable characters and question marks.

Checked on the database property and found that the Collation was set to SQL_Latin1_General_CP1_CI_AS. To fix this issue there is a property setting which is part of the Operating system which has to be set. In control panel under Regional and Language Options setting there is a language tab, when we checked the Install file for east asian languages, some OS files get installed and then on restart the chinese and other languages are visbile in the table.

Note: Converting to upper case had no effect on the chinese, korean and japanese letters. But we are still not able to figure out the sort order.

Select statement when table value has square brackets

I had to query a customer table which has customer name enclosed in square brackets.

I tried this first
SELECT * FROM JCustomer WHERE FirstName LIKE '%[%]' -- no rows returned

Then i tried this,
SELECT * FROM JCustomer WHERE FirstName LIKE ' [ [ ]% ' -- this return records which start with square brackets. The trick is to enclose the search string within a square bracket.