Query XML Data with T-SQL


CREATE TABLE dbo.T( X int, Y int)

INSERT INTO T VALUES (1, 20)
INSERT INTO T VALUES (3, 40)
INSERT INTO T VALUES (2, 30)
GO
  

declare @xml_table as table (col_xml xml );
insert into @xml_table(col_xml) values( (SELECT X, Y FROM T  
ORDER BY X  
--ORDER BY X DESC
FOR XML AUTO, Elements, Root('Root') ))
 
SELECT nums.num.value('(./X)[1]', 'int') AS X
,nums.num.value('(./Y)[1]','int') AS Y
FROM   @xml_table nt
CROSS apply nt.col_xml.nodes('/Root/T') AS nums(num) 
--Order By X DESC


drop table t

There is another easy to understand sample from Jon Tavernier:
http://blog.jontav.com/post/6811942997/querying-xml-in-sql-server


Query Conversion Tool Between Oracle And SQL Server

You can use a free tool from Oracle SQL Developer under>>Tools>>Migration>>Translation Scratch Editor
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

(You may need to register for a free account first to download the software).


Assign System Administrator (Windows 2012) as SysAdmin Fixed Role in SQL Server 2012

AFter installation user( an admin) was removed from SQL Server logins, all of my existing users (Windows System Administrators) do not have sysadmin role.
You cannot make changes or excute restore script but stuck.
Here is a quick fix from microsoft:
Connect to SQL Server When System Administrators Are Locked Out
http://msdn.microsoft.com/en-us/library/dd207004.aspx

1. From SQL Server Configuration Manager,  in the left pane, select SQL Server Services >>
In the right-pane, find your instance of SQL Server>> Right-click the instance of SQL Server
>> and then click Properties.
2. On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add.

3. Click Apply and click OK, >>  right-click your server name, and then click Restart.
4. Right-click the icon for Management Studio and select Run as administrator.
5.Connect with Object Explorer using Windows Authentication (your are one of System Administrators). Expand Security, expand Logins,
and double-click your own login. On the Server Roles page, select sysadmin, and then click OK.

6.Change back to multi-user mode.In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane,
right-click the instance of SQL Server, and then click Properties.
On the Startup Parameters tab, in the Existing parameters box, select -m and then click Remove.
7.Right-click your server name, and then click Restart.
8.Now you are a member of the sysadmin fixed server role.


Check All Databases’ Compatibility Level and Modify Them

You can use this query to find out what your databases’ compatibility levels are:
select name, compatibility_level from sys.databases
You can update the compatibility level of these databases in one shot either through a cursor like this:
http://blogs.msdn.com/b/ai/archive/2012/06/12/sql-server-set-comparability-level-for-all-server-databases.aspx

You can still use the unsupport sp_msforeachdb to make the change:



sp_msforeachdb ' USE [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''distribution''
 BEGIN  ALTER  DATABASE [?] SET COMPATIBILITY_LEVEL = 100; END '

-- 80 = SQL Server 2000
--90 = SQL Server 2005
--100 = SQL Server 2008/R2
--110 = SQL Server 2012
--120 = SQL Server 2014 --not tested


 --Check all databases' compatibility level for your instance
 select name, compatibility_level from sys.databases



Links to Official and Unofficial SQL Server Builds (SP, Cumulative updates)

Official:
http://support.microsoft.com/kb/321185

Unofficial:
http://sqlserverbuilds.blogspot.com/


UPDATE …. FROM JOIN —(Why Merge…) T-SQL

You can find a quick sample case to see that sometimes when JOIN generates duplicates rows, your UPDATE will create wrong results without notice. But you can use Merge to avoid this loophole. You will catch the error:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

http://www.sqlservercentral.com/articles/T-SQL/101464/


How to Add New Column to Existing Flat File in SSIS

When you have a need to add new column to your existing text file through your SSIS package, here is how:
Open the Flat File Connection Manager and click Advanced tab >> click New and give a name on the right panel
and it will refresh left list. You can make changes to data type property from the right panel.
If you want to add new column to the middle of the column, you need to click on the double-arrow next to New to choose insertion position.