Ashish Agarwal

DECLARE @commaSeperatedCols VARCHAR ( MAX ) DECLARE @TableName VARCHAR ( 100 ) SET @TableName = 'Your_Table_Name' SELECT @commaSeperatedCols = COALESCE ( @commaSeperatedCols + ',' , '' ) + COLUMN_NAME FROM INFORMATION_SCHEMA . COLUMNS WHERE TABLE_NAME = @TableName SELECT @commaSeperatedCols

Declare @i int , @loop int , @str varchar ( 100 ) = 'TEST_lOwerCase' IF EXISTS ( SELECT * FROM tempdb . dbo . sysobjects WHERE ID = OBJECT_ID ( N'tempdb..#Temp' ) ) BEGIN DROP TABLE #Temp END create table #Temp ( id int ) select @i = LEN ( @str ) print @i set @loop = 1 while ( @loop <= @i ) BEGIN       if (( ascii ( SUBSTRING ( @str , @loop , 1 ))>= 97 ) and ( ascii ( SUBSTRING ( @str , @loop , 1 ))<= 122 ))       insert into #Temp values ( @loop )       set @loop = @loop + 1 END select MIN ( id ) from #Temp GO

Hello Friends !! Today I am writing my first C#.Net Article going a bit out of my way from SQL and MSBI. This is something related to a requirement which comes my way when in a windows Application i need to give a drop down for all like customer names when typing the names in the Text Box as a very basic user friendly option. Here is how we can do this - Let's take as assumption as we have a table in SQL as CustomerTable and we want to take the FirstName of the Customer as our search result. we will write this code in the Form load event of the form which contains the Text box. The connectionStringName will be configured and can be get from the App.Config table. Also you need to change the below 2 properties of the Text Box on which you want to implement the Auto Complete functionality. SqlConnection  con = new  SqlConnection ( ConfigurationManager .ConnectionStrings[ "connectionStringName" ].ConnectionString); SqlCommand  cmd =  new   Sq

Sometimes the requirement comes when we need to pump the data from one server to another server. Though we have SQL Import\Export wizard which can do this quiet easily, but in that way we will loose some important features of the table --      1. The Identity property of a column in case we have one in the table.      2. All the indexes. To avoid this we can go with a simple SSIS package to which we just need to pass the table name as a parameter and it will do the rest, taking care of the above constraints. This is how the Package looks - Steps - 1. To make the executable dynamic we will drive the package from a table, where we will    store the names of all the tables which we want to refresh from the source server and we will store it in a result set variable (Table_List) Refer the variables list in the below figure used in the package. 2. Create a For each loop container which will iterate throu

DECLARE IndexCursor CURSOR FOR SELECT OBJECT_NAME ( SI . Object_ID ), SI . Object_ID , SI . Name , SI . Index_ID FROM sys.indexes SI LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI . Name = TC . CONSTRAINT_NAME AND OBJECT_NAME ( SI . Object_ID ) = TC . TABLE_NAME WHERE TC . CONSTRAINT_NAME IS NULL AND OBJECTPROPERTY ( SI . Object_ID , 'IsUserTable' ) = 1 ORDER BY OBJECT_NAME ( SI . Object_ID ), SI . Index_ID DECLARE @IxTable varchar ( 50 ) DECLARE @IxTableID INT DECLARE @IxName varchar ( 50 ) DECLARE @IxID INT -- Loop through all indexes OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @IxTable , @IxTableID , @IxName , @IxID WHILE ( @@FETCH_STATUS = 0 ) BEGIN    DECLARE @IXSQL NVARCHAR ( 4000 )    SET @IXSQL = 'CREATE '    -- Check if the index is unique    IF ( INDEXPROPERTY ( @IxTableID , @IxName , 'IsUnique' ) = 1 )       SET @IXSQL = @IXSQL + &#

SELECT     T . EMP_ID , RIGHT(LEFT( dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' , Number - 1 ),     CHARINDEX ( ';' , REVERSE (LEFT( ';' + dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' , Number - 1 )))) as MGR_Hierarchy FROM     master .. spt_values ,     dbo . EMP_MGMT_CHAIN T WHERE     Type = 'P' AND Number BETWEEN 1 AND LEN ( dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' )+ 1     AND     ( SUBSTRING ( dbo . MGR_HIERARCHY ( T . ALL_MGR_IN_MGMT_CHAIN )+ ';' , Number , 1 ) = ';' )

CREATE PROCEDURE MultipLeDataset AS BEGIN DECLARE @IntVariable int ; DECLARE @SQLString1 nvarchar ( 500 ); DECLARE @SQLString2 nvarchar ( 500 ); DECLARE @ParmDefinition nvarchar ( 500 ); /* Build the SQL string one time.*/ SET @SQLString1 =      N'SELECT AccountKey, ParentAccountKey, AccountCodeAlternateKey, ParentAccountCodeAlternateKey        FROM AdventureWorksDW.dbo.DimAccount' ;        SET @SQLString2 =      N'SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName        FROM AdventureWorksDW.dbo.DimCurrency' ;       /* Execute the string with the first parameter value. */ EXECUTE sp_executesql @SQLString1 ; EXECUTE sp_executesql @SQLString2 ; END

EXECUTE msdb.. sp_help_job @job_name = @job_name EXECUTE msdb.. sp_help_job @job_id = @job_id

CREATE PROCEDURE Myproc @parm varchar ( 10 ), @parm1OUT varchar ( 30 ) OUTPUT , @parm2OUT varchar ( 30 ) OUTPUT AS   SELECT @parm1OUT = 'parm 1' + @parm SELECT @parm2OUT = 'parm 2' + @parm GO DECLARE @SQLString NVARCHAR ( 500 ) DECLARE @ParmDefinition NVARCHAR ( 500 ) DECLARE @parmIN VARCHAR ( 10 ) DECLARE @parmRET1 VARCHAR ( 30 ) DECLARE @parmRET2 VARCHAR ( 30 ) SET @parmIN = ' returned' SET @SQLString = N'EXEC Myproc @parm,                          @parm1OUT OUTPUT, @parm2OUT OUTPUT' SET @ParmDefinition = N'@parm varchar(10),                   @parm1OUT varchar(30) OUTPUT,                   @parm2OUT varchar(30) OUTPUT' EXECUTE sp_executesql @SQLString , @ParmDefinition , @parm = @parmIN , @parm1OUT = @parmRET1 OUTPUT , @parm2OUT = @parmRET2 OUTPUT SELECT @parmRET1 AS "parameter 1" , @parmRET2 AS "parameter 2" GO DROP PROCEDURE Myproc