Create Dynamic Get storedprocedure for All the tables

--- This can be customized as per the Requirments ------ -- This Script used to create the Get(select all columns) Procedure for all the database tables. ---------------------------------------------------------------------------------------------------- SELECT ' CREATE PROCEDURE [USP_GET_' + TBL.NAME + ']' + + ' AS BEGIN SELECT ' + LEFT(COLUMNS,LEN(COLUMNS)- 1) + CHAR(10) + ' , GETDATE() AS INSERTED_DATE FROM ' + TBL.NAME + ' END GO ' FROM SYS.TABLES AS TBL CROSS APPLY ( SELECT UPPER('[' + COL.NAME + ']') + ' ,' FROM SYS.COLUMNS AS COL WHERE COL.OBJECT_ID = TBL.OBJECT_ID FOR XML PATH('')) AS TABLE_COLUMN(COLUMNS) ORDER BY TBL.NAME ASC

Sql Table variable Function for Handling Multiple Value Parameter in SSRS

Sql Table variable Function for Handling Multiple Value Parameter in SSRS

http://geekswithblogs.net/HighAltitudeCoder/archive/2010/06/06/sql-server-split-function.aspx

*** Just for self reference in future ****

Parse Hierarchy(TreeView) XML using Outer Apply

/*

This sample will help you to parse Tree View XMl of Explicit format.
If you Apply Cross Apply on for Outer Apply then it will not display the child which does not have 3 Levl

If you store this XML in to temp table then you can update the Parent child clasification in to table.

*/
-- Please Replace [ and ] by < and >
DECLARE @HierarchyDetail XML

SET @HierarchyDetail =
'[treeview]


[treeview-nodes]

[treeview-node node-text="Account -1001" node-value="1001"]

[treeview-nodes]

[treeview-node node-text="Account -1075" node-value="1075"]

[treeview-nodes]

[treeview-node node-text="Account - 1132" node-value="1132" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1000" node-value="1000"]

[treeview-nodes]

[treeview-node node-text="Account -1077" node-value="1077"]

[treeview-nodes]

[treeview-node node-text="Account -1134" node-value="1134" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1080" node-value="1080"]

[treeview-nodes]

[treeview-node node-text="Account - 1101 " node-value="1101"]

[treeview-nodes]

[treeview-node node-text="Account - 1135" node-value="1135" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1114" node-value="1114"]

[treeview-nodes]

[treeview-node node-text="Account - 1124 " node-value="1124"]

[treeview-nodes]

[treeview-node node-text="Account - 1137" node-value="1137" /]

[treeview-node node-text="Account - 2937 " node-value="2937" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview]'

Select Rows from Table1 NOT IN Table2 (t1.a = t2.a and t1.b = t2.b)

--Source(Table1 & Table2)

--Expected OutPut

DECLARE @T1 TABLE(A INT, B INT,C VARCHAR(10))
DECLARE @T2 TABLE(A INT, B INT,C VARCHAR(10))
INSERT INTO @T1(A,B,C)
VALUES (1,1,'SS1')
,(1,2,'SS2')
,(1,3,'SS3')


INSERT INTO @T2(A,B,C)
VALUES (1,1,'SS1')
,(1,2,'SS2')


--Method1:
SELECT A,B
FROM @T1
EXCEPT
SELECT A,B
FROM @T2
--Method2:
SELECT A.A,A.B
FROM @T1 A
left JOIN @T2 B ON A.A = B.A
AND a.B = b.B
WHERE b.B is null
--Method3:
SELECT * FROM @T1 AS T1
WHERE NOT EXISTS (SELECT 0
FROM @T2 AS T2
WHERE T2.A = T1.A
AND T2.B = T1.B
)
 
/*
 
I hope these will give the expected Result, Now i am looking over this which is best?
 
*/

String Concatenation Using XML Path

/*
Description : String concatenation using XML Path
This will help you to concatenate the reference table column values by using cross apply
*/
DECLARE @Table1 TABLE(CountryID INT,CountryName VARCHAR(50))
DECLARE @Table2 TABLE(StateID INT IDENTITY(1,1)
                                 ,CountryID INT
                                 ,StateName VARCHAR(50))

INSERT INTO @Table1(CountryID,CountryName) VALUES
(1 , 'USA'),
(2 , 'INDIA'),
(3 , 'AUSTRALIA')


INSERT INTO @Table2(CountryID,StateName) VALUES
(1,'Texas'),
(1,'Washington'),
(1,'New Yark'),
(1,'Colorodo'),
(2,'TamilNadu'),
(2,'Mumbai'),
(2,'Delhi'),
(2,'Banglore'),
(3,'Victoria'),
(3,'Tasmania'),
(3,'Queensland'),
(3,'New South Wales'),
(3,'Western Australia')


SELECT Table1.CountryID
,Table1.CountryName
,LEFT(StateNames,LEN(StateNames)- 1) AS StateNames
FROM @Table1 as Table1
CROSS APPLY
( SELECT StateName + ', '
FROM @Table2 AS Table2
WHERE Table2.CountryID = Table1.CountryID
FOR XML PATH('') ) AS State(StateNames)

--Desired Result

--* This Insert Statement will work in 2008 *

Export Table Row in to CSV File

Step1: Create Procedure with Select Statement

Create Procedure pcget_ETLErrorlog
AS
BEGIN
   SELECT * FROM ETLErrorlog
END

Step 2:
DECLARE @SQL VARCHAR(2000)
DECLARE @ExportResult INT


SET @SQL = 'bcp "EXEC [MyDatabase].[dbo].pcget_ETLErrorlog" queryout "C:\Configuration V1\Global Alert\ETLAlert.CSV" -c -t, -T -S ServerName'
EXEC master..xp_cmdshell @SQL

Note: Procedure can be replaced directly by Select Statment "SELECT * FROM ETLErrorlog"
This will return the list of Output rows. If we dont want the out put rows then we can run the above query like this...


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

Note: 
If the OutPut Result is 0 then It means no error. This will help you to validate and proceed the next step.

Log on through windows authentication to the Different Domain SQL Server

1. Connect SQL Server Management Studio from Remote

Run the following script from PowerShell

runas /netonly /user:CIT\Sam “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
Then type the domain account Password.(Password will not be visible while enter) Now you can able to login to the DatabaseServer

2. Connect Microsoft Visual Studio from Remote:

runas /netonly /user:CIT\Sam "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

SQL OVER() - Perform SQl Aggregate With out Group by

Use Aggregate Function With out Group by:
SQL Over() Function:

The real use of this function is
1. Find Sales ratio of each month sales out of the current year
2. Find Sales ratio of each product sales out of all product
3. Find Sales ratio of each division sales out of all division
Example
1. (SUM(Sales) OVER (PARTITION BY Year,Month) / SUM(Sales) OVER (PARTITION BY Year)) *100
2. (SUM(Sales) OVER (PARTITION BY product) / SUM(Sales) OVER ()) *100
3. (SUM(Sales) OVER (PARTITION BY division) / SUM(Sales) OVER ()) *100

--Example1:

DECLARE @FactSale TABLE(ID INT IDENTITY(1,1),ProductName VARCHAR(50),Amount INT)
INSERT INTO @FactSale(ProductName,Amount)
SELECT 'Samsung',40
UNION
SELECT 'LG',60
UNION
SELECT 'Sony',100

SELECT ProductName
,Amount
,SUM(Amount) OVER () TotalSales
,(CAST(SUM(Amount) OVER (PARTITION BY ProductName) AS NUMERIC(10,2)) / CAST(SUM(Amount) OVER () AS NUMERIC(10,2))) * 100 AS SalesRatiobyProduct
FROM @FactSale
--Example2:
DECLARE @FactSale1 TABLE(ID INT IDENTITY(1,1),Year INT,Month VARCHAR(10),Amount INT)
INSERT INTO @FactSale1(Year,Month,Amount)
SELECT 2008,'Jan',600
UNION
SELECT 2008,'Feb',700
UNION
SELECT 2008,'Mar',200
UNION
SELECT 2008,'Apr',500
UNION
SELECT 2009,'Jan',40
UNION
SELECT 2009,'Feb',60
union
SELECT 2009,'Mar',70
UNION
SELECT 2009,'Apr',30


SELECT Year
,Month
,Amount
,SUM(Amount) OVER () AS TotalSales
,SUM(Amount) OVER (PARTITION BY Year) AS TotalSalesbyYear
,(CAST(SUM(Amount) OVER (PARTITION BY Year,Month) AS NUMERIC(10,2)) / CAST(SUM(Amount) OVER (PARTITION BY Year) AS NUMERIC(10,2))) * 100 AS SalesRatiobyMonth
FROM @FactSale1

Please go through this for more information

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/ddcef3a6-0341-43e0-ae73-630484b7b398.htm