Ashish Agarwal

DECLARE @TABLE_NAME VARCHAR ( 128 ) DECLARE @SCHEMA_NAME VARCHAR ( 128 ) ----------------------------------------------------------------------- -- *** User Customisation -- Set up the name of the table here : SET @TABLE_NAME = 'Address' -- Set up the name of the schema here, or just leave set to 'dbo' : SET @SCHEMA_NAME = 'dbo' ----------------------------------------------------------------------- DECLARE @vvc_ColumnName VARCHAR ( 128 ) DECLARE @vvc_ColumnList VARCHAR ( MAX ) IF @SCHEMA_NAME = ''   BEGIN   PRINT 'Error : No schema defined!'   RETURN   END IF NOT EXISTS ( SELECT * FROM sys . tables T JOIN sys . schemas S           ON T . schema_id = S . schema_id           WHERE T . Name = @TABLE_NAME AND S . name = @SCHEMA_NAME )   BEGIN   PRINT 'Error : The table ''' + @TABLE_NAME + ''' in schema ''' +         @SCHEMA_NAME + &

Exec xp_dirtree  'C:\Windows' Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use. Here is the quick script which will insert the data into the temptable and retrieve from the same. CREATE TABLE  #TempTable  ( Subdirectory   VARCHAR ( 512 ),  Depth  INT ); INSERT INTO  #TempTable  ( Subdirectory ,   Depth ) EXEC  xp_dirtree  'C:\Windows' SELECT  Subdirectory ,  Depth FROM  #TempTable ; DROP TABLE  #TempTable ;

Here is a example code showing how to list directory files in a file folder using sql xp_cmdshell procedure. CREATE TABLE dirList (   id int identity(1,1),   line nvarchar(1000) ) GO; INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\SQLDatabases' SELECT * FROM dirList; WITH CTE AS (   SELECT     id,     SUBSTRING(line,1,17) [date],     SUBSTRING(line,18,19) sizeordir,     SUBSTRING(line,37,100) name   FROM dirList   WHERE id > (     SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'   ) AND id < (SELECT MAX(id) - 2 FROM dirList) ) SELECT   id,   [date],   isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,   isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,   name FROM cte The above t-sql script can be used to list files in directory using sql code. The isDirectory column and isFile column can be used to distinguish files and folders within the target file folder. By using re

Let us begin with the definition of each which will be followed by examples: 1. Additive measures: These are those specific class of fact measures which can be aggreagated across all dimension and their hieracchy 2. Semi-Additive measures: These are those specific class of fact measures which can be aggreagated across all dimension and their hieracchy except the time dimension 3. Non-additive measures: These are those specific class of fact measures which cannot be aggreagated across all/any dimension and their hieracchy For example: A) We have sales figures...one may tend to add sales across all quareters to avail the yearly sales..hence this is an example of Additive measure B) We have stock levels say 1000(qty of Item A) on Monday...I sell 200(qty of Item A) on Tuesday I further sell 300(qty of Item A) on Wednesday...going by basic math On Thursday I should be left with 500(qty of Item A, assuming no inventory has flown in) to obtain current stock level I cannot aggregate the Stock

SQL Server 2008 Integration Services Tasks SQL Server Integration Services (SSIS) is a Business Intelligence tool used by database developers or administrators to perform extract transform & load (ETL) operations. SSIS has come long way since the early days of Data Transformation Services (DTS) which was initially introduced by Microsoft in SQL Server 7.0. Most database developers and administrators had their first interaction with DTS when using the Import and Export Wizard. The primary objective of DTS was to transform data from an OLEDB data sources to another destination. In SQL Server 2008 Integration Services, there are around 28 different control flow tasks and 11 Maintenance Plan Tasks. This article is the first in a series that will cover all the Control Flow Tasks and Maintenance Plan Tasks in SQL Server 2008 Integration Services. Beginning with an overview of the Control Flow Tasks and Maintenance Plans in SSIS 2008. Overview of Control Flow Tasks in SSIS The Contro