Query XML Data with T-SQL

Posted: September 24, 2013 | Author: | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment 

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

Posted: September 16, 2013 | Author: | Filed under: My IT tools | Leave a comment

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

Posted: September 13, 2013 | Author: | Filed under: SQL Server 2012, Uncategorized | Leave a comment

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

Posted: September 10, 2013 | Author: | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

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)

Posted: September 10, 2013 | Author: | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

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

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

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

Posted: September 9, 2013 | Author: | Filed under: SQL Server 2008 | Leave a comment

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

Posted: September 9, 2013 | Author: | Filed under: SQL SSIS | Leave a comment

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.

Concatenate Different Rows For Two Columns (T-SQL

Posted: September 6, 2013 | Author: | Filed under: SQL Server 2008 | Leave a comment 

  
CREATE TABLE #SantasHelpers (work_dt datetime, fname varchar(20), lname varchar(25), title tinyint)

INSERT INTO #SantasHelpers VALUES ('4/1/2013', 'Tiny', 'Tim', 0)
INSERT INTO #SantasHelpers VALUES ('4/1/2013', 'Mad', 'Rapper', 0)
INSERT INTO #SantasHelpers VALUES ('4/2/2013', 'Little', 'Joe', 0)
INSERT INTO #SantasHelpers VALUES ('4/2/2013', 'Pretty', 'Missy', 0)
INSERT INTO #SantasHelpers VALUES ('4/3/2013', 'Rough', 'Ralph', 1)
INSERT INTO #SantasHelpers VALUES ('4/3/2013', 'Big', 'Barry', 0)
INSERT INTO #SantasHelpers VALUES ('4/4/2013', 'Fancy', 'Dancy', 0)
INSERT INTO #SantasHelpers VALUES ('4/5/2013', 'Wild', 'Willie', 0)
INSERT INTO #SantasHelpers VALUES ('4/5/2013', 'Silly', 'Sonia', 1)
INSERT INTO #SantasHelpers VALUES ('4/8/2013', 'Tiny', 'Tim', 0)
INSERT INTO #SantasHelpers VALUES ('4/8/2013', 'Mad', 'Rapper', 0)
INSERT INTO #SantasHelpers VALUES ('4/9/2013', 'Little', 'Joe', 0)
INSERT INTO #SantasHelpers VALUES ('4/9/2013', 'Pretty', 'Missy', 0)
INSERT INTO #SantasHelpers VALUES ('4/10/2013', 'Rough', 'Ralph', 1)
INSERT INTO #SantasHelpers VALUES ('4/10/2013', 'Big', 'Barry', 0)
INSERT INTO #SantasHelpers VALUES ('4/11/2013', 'Fancy', 'Dancy', 0)
INSERT INTO #SantasHelpers VALUES ('4/12/2013', 'Wild', 'Willie', 0)
INSERT INTO #SantasHelpers VALUES ('4/12/2013', 'Silly', 'Sonia', 1)

;with mycte as (
Select  work_dt
, col1 = Case When title = 0 Then fname + ' ' + lname End
, col2 = Case When title = 1 Then fname + ' ' + lname End
From #SantasHelpers
Group By work_dt, fname, lname, title)
 


SELECT   work_dt, stuff((select ',' + m1.col1
from mycte m1 WHERE m.work_dt=m1.work_dt
 for XML PATH('')),1,1,'') as [Santas Helpers]
 ,stuff((select ',' + m1.col2
from mycte m1 WHERE m.work_dt=m1.work_dt
 for XML PATH('')),1,1,'')  as [Elves]
from mycte m 
Group By work_dt



drop table #SantasHelpers

--http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1c135570-49e2-42fd-982f-c208acf6ac9f/grouping-values-from-multiples-rows-into-single-column

Hide Column With Null Values using SQL server

Posted: September 5, 2013 | Author: | Filed under: SQL Server 2005 Express, SQL Server 2008 | Leave a comment

I don’t think this requirement should be implemented in SQL end.By using dynamic SQL with UNIPIVOT to get the result:


  
DECLARE @colsCast NVARCHAR(2000)
, @cols NVARCHAR(2000)
, @sql1 NVARCHAR(4000)
, @sql2 NVARCHAR(4000)
, @sql3 NVARCHAR(4000)
, @Schema_Name NVARCHAR(4000) ='dbo'
, @TABLE_NAME NVARCHAR(4000) ='Source'
 

SELECT @colsCast = COALESCE(@colsCast + ', ', '') + 'CAST('+ Quotename(column_Name)+' AS NVARCHAR(4000)) AS '+ Quotename(column_Name)
, @cols = COALESCE(@cols + ', ', '') + Quotename(column_Name)  
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME ANd Table_Schema=@Schema_Name

--print @cols 

SET @sql1='IF OBJECT_ID(N''dbo.stagingTable'',N''U'') IS NOT NULL
Begin
DROP Table dbo.stagingTable
END
Begin
SELECT  * INTO stagingTable
FROM (SELECT '+ @colsCast+ ' FROM  '+ @Schema_Name+'.'+ Quotename(@TABLE_NAME) + ') src
UNPIVOT (val For col IN ( '+ @cols+ ')) unpvt  
END'
EXEC sp_executesql @sql1


SET  @sql2 = (SELECT distinct  stuff((select ',' + m1.col
from (select distinct col as col from dbo.stagingTable) m1 
 for XML PATH('')),1,1,'') 
from( select distinct col as col from dbo.stagingTable) m Group By col )
 
 
 --print @sql2

 Set @sql3=N'SELECT '+@sql2+ ' FROM  '+ @Schema_Name+'.'+ @TABLE_NAME

 EXEC sp_executesql @sql3

--http://forums.asp.net/p/1931738/5501150.aspx?p=True&t=635139931216804858&pagenum=1