Drop all temp tables(local and global) of a session in SQL Server.

Drop all temp tables(local and global) of a session in SQL Server.

Below code is helpful wherein you are not aware of the temp tables created in your current session.
When we debug the procedure,if that procedure doesn't have construct of DROP and CREATE.



DECLARE @DropGlobal bit=0 --Default dont drop global temp table
DECLARE @DROP_STATEMENT nvarchar(1000)
DECLARE cursorDEL CURSOR FOR
SELECT 'DROP TABLE '
   + case
           when name like '##%' then name
           when name like '#%' then SUBSTRING(name, 1, CHARINDEX( '____', name)-1)
    end as DropSQL
from tempdb..sysobjects
WHERE name LIKE '#%'
   AND OBJECT_ID('tempdb..' + name) IS NOT NULL
   AND name not like case
                       when @DropGlobal=0 then '##%' --//Exclude global temp
                       else '#######%'    --//some fack expression so we can
                                           --//select global temp for delete
                    end

   --//eventhough we have selected all records from sysobjects
   --//but one can access only temp table created by same connection
   --//executing this procedure

OPEN cursorDEL
FETCH NEXT FROM cursorDEL INTO @DROP_STATEMENT
WHILE @@FETCH_STATUS = 0
BEGIN
--EXEC (@DROP_STATEMENT)
print @DROP_STATEMENT
FETCH NEXT FROM cursorDEL INTO @DROP_STATEMENT
END
CLOSE cursorDEL
DEALLOCATE cursorDEL

Find Dependent objects related to TableName in SQL Server.

Below query will fetch you the list of dependent objects(SP or Views or Functions) for the given tablename:


SELECT B.NAME as OBJECT_NAME,A.[DEFINITION],B.TYPE_DESC
FROM SYS.SQL_MODULES A
JOIN SYS.OBJECTS B
ON A.OBJECT_ID = B.OBJECT_ID
WHERE A.DEFINITION LIKE '%F_PARTY_TRANCHE_ROLE%'

Note: sys.syscomments comes under deprecated system table, so in the above query i used sys.sql_modules which Microsoft recommends for future development.

Parent Package Variable in SSIS

Parent Package Variable in SSIS

There are situations wherein you need value which is assigned to variable in one package to be used in other package.

There exist a way in SSIS to access a variable defined in one(Parent) package and utilized in other(Child) package.

Just visualize a scenario where you have a Parent Package and "n" number of child packages.Through Execute Package task you are calling all child's from Parent.

All child packages should get values from Parent package.(Values can be "Server name or DB_Name")

When we want to make use of variable values which is defined and assigned in other package(let's say Parent), the place we need to target is Child Package.

Configuration Steps as follows:

1) Go to the package(child),right click anywhere in the Control Flow Designer Area and choose Package Configuration.

2) "Package Configurations Organizer" window will pop up, click on Check-box "Enable Package Configuration" which enables "ADD" button.Click Add.

3) Package Configuration Wizard(welcome page) will launch, just Click Next. (You may or may not come across this Welcome page).

4) Now when you click Next, Select Configuration Type wizard appears.

5) From the Configuration Type drop down(defaults to XML configuration file), choose Parent Package Variable.

6) Now we have reached the most important step of this article, which is nothing but defining the Parent Variable.

7) In this place, we need to give Variable Name from which we are going to pull out values. (Parent package Variable Name).
   Note: Variable Name is case sensitive and hence you need to use the same name defined in Parent Package. We all set, to assign value to the variable in the Child package.

8) Click Next, Select Properties to Export window will appear.
9) Click on Variable Name,expand Properties and click on "Value" property.
10)Click Next, Completion wizard will summarize the configuration. Click on Finish.

Now whatever value assigned to Parent Package Variable will be assigned to Child Package during run time.

Best example, i can think of is, let's say you developed package in dev environment(with OLEDB in Parent and all Child's). Time has come to move to UAT or SIT then instead of changing OLEDB connection in each and every child package if we change in Parent Package, that will be reflected in Child Package(provided your servername and dbname are configurable through expressions with variables).

How to rename files in SSIS using Script task?

Input Filenames(under my root path folder): 

DW_DATA_MAS_UNIT_03112016
DW_DATA_MAS_PARCEL_03112016
DW_DATA_TRAN_ACCOUNT_03112016
DW_DATA_TRAN_BANKDETAILS_03112016
DW_DATA_CH_ACCDETAILS_03112016

Output(in the same root path folder): I just segregated Master,Transaction and Child tables based on the keywords(MAS,TRAN,CH) present in input file.

MASTER_DW_DATA_MAS_UNIT_03112016
MASTER_DW_DATA_MAS_PARCEL_03112016
TRAN_DW_DATA_TRAN_ACCOUNT_03112016
TRAN_DW_DATA_TRAN_BANKDETAILS_03112016
CHILD_DW_DATA_CH_ACCDETAILS_03112016

Code to achive the same :

string rootfolderPath = Dts.Variables["User::SOURCE_PATH"].Value.ToString();

string[] files = Directory.GetFiles(rootfolderPath, "*.xml", SearchOption.AllDirectories);

  foreach (string s in files)
{
string trgt = "";
if(Path.GetFileName(s).contains("MAS_"))
{
targt = Path.GetDirectoryName(s) +"\\MASTER_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("TRAN_"))
{
targt = Path.GetDirectoryName(s) +"\\TRAN_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("CH_"))
{
targt = Path.GetDirectoryName(s) +"\\CHILD_"+ Path.GetFileName(s);
}

}

if (!File.Exists(targt))
{
File.Move(s, targt);
}

Explanation: 

Firstly, add a variable(to store the Root Path of the files location) and a Script Task(to rename files using C#.net).

1)
Change the variable datatype to String and assign it with a RootFolder path value.

For Ex: C:\Users\kn\Documents\NewFolder

2) 
Make the variable as ReadOnly in Script Task.

 string rootfolderPath = Dts.Variables["User::SOURCE_PATH"].Value.ToString();

Above Line copies the root path into a string variable rootfolderPath.(value is being pulled from SSIS variable)

string[] files = Directory.GetFiles(rootfolderPath, "*.xml", SearchOption.AllDirectories);

Above Line reads out all files of specified type(in root folder and sub folder directories) and store it into a array called "files".

3)
Next step is to loop through each file in an array and rename it based on the certain condition.

foreach (string s in files)
{
string trgt = "";
if(Path.GetFileName(s).contains("MAS_"))
{
targt = Path.GetDirectoryName(s) +"\\MASTER_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("TRAN_"))
{
targt = Path.GetDirectoryName(s) +"\\TRAN_"+ Path.GetFileName(s);
}
else if(Path.GetFileName(s).contains("CH_"))
{
targt = Path.GetDirectoryName(s) +"\\CHILD_"+ Path.GetFileName(s);
}
}

Example : 

Before Change : C:\Users\kn\Documents\NewFolder\DW_DATA_MAS_UNIT_03112016.txt
After  Change : C:\Users\kn\Documents\NewFolder\MASTER_DW_DATA_MAS_UNIT_03112016.txt

Note: 
Path.GetDirectoryName(C:\Users\kn\Documents\NewFolder\DW_DATA_MAS_UNIT_03112016.txt) will return the file Parent Directory i.e C:\Users\kn\Documents\NewFolder.

Path.GetFileName(C:\Users\kn\Documents\NewFolder\DW_DATA_MAS_UNIT_03112016.txt) will return file name from fully qualified path i.e DW_DATA_MAS_UNIT_03112016.txt

Variable "s" holds the existing filename and the variable "trgt" holds the new filename.

Now with the help of methods under File Class we can rename file.

if (!File.Exists(targt))
{
File.Move(s, targt);
}

Above Code will rename file by ensuring the destination location for the target filename.

Note : 
File.Exists(destination_path)
File.Move(source_path,destination_path)

Same can be done in SSIS using FOR EACH LOOP Container, File System Task and Expression Task as well.

Drop Constraint of a column(s) in SQL Server

How to drop particular(FK OR UQ OR DF) constraint based on given COLUMN_NAME in SQL Server?

Key Note: 
Generally you will come across this situation when you don't create CONSTRAINT NAME on your own.
When you asked to drop "only one unique constraints" on a column of a table(assume that table has 'n' unique constraints on other columns as well) for a particular column(or for a set of columns), how do you do it when all the constraint names are system generated?

--Input for a single column: TABLE_NAME & COLUMN_NAME  

DECLARE @TABLE_NAME VARCHAR(100) = 'TRANSACTION_PARCEL' 
DECLARE @COLUMN_NAME VARCHAR(100) = 'fromdate' 
DECLARE @SQL NVARCHAR(max) = '' 

SELECT @SQL += 'ALTER TABLE ' + @TABLE_NAME 
               + ' DROP CONSTRAINT ' + constraint_name + ';' 
FROM   information_schema.constraint_column_usage A 
       JOIN sys.objects B 
         ON A.constraint_name = B.NAME 
WHERE  --B.TYPE = 'UQ' AND  
  A.table_name = @TABLE_NAME 
  AND A.column_name = @COLUMN_NAME 
GROUP  BY A.constraint_name 

PRINT @SQL 

EXEC Sp_executesql 
  @SQL 


----Input for multiple columns: TABLE_NAME & COLUMN_LIST

DECLARE @TABLE_NAME VARCHAR(100) = 'TRANSACTION_PARCEL' 
DECLARE @SQL NVARCHAR(max) = '' 

SELECT @SQL += 'ALTER TABLE ' + @TABLE_NAME 
               + ' DROP CONSTRAINT ' + constraint_name + ';' 
FROM   information_schema.constraint_column_usage A 
       JOIN sys.objects B 
         ON A.constraint_name = B.NAME 
WHERE  --B.TYPE = 'UQ' AND  
  A.table_name = @TABLE_NAME 
  AND A.column_name IN ('PARCEL_BSKEY','TRAN_BSKEY','TRAN_PARCEL_BALANCE_TYPE') 
GROUP  BY A.constraint_name 

PRINT @SQL 

EXEC Sp_executesql 
  @SQL

Note: Code can be customized to pull out particular keys as well (F - foreign key, D - Default constraint, UQ - Unique constraint, PK - Primary Key constraint)

Before you drop constraint of type PK, you need to drop FK's which falls upon this PK.

MODEM Connectivity Problem (Airtel or BSNL)

Have you ever come across a situation wherein ASDL light in your modem goes OFF continuously in a frequency of 5 to 10 minutes which leads to disrupt in Internet Connection?

There is a workaround exist to make it stable and to make use of Internet Connection without any interrupt.

Solution: Keep OFF the receiver from phone. (Take OFF the receiver and keep somewhere)
By placing your Phone Receiver aside, your Phone Line will no more Listen for calls from outside network since it is in Engaged mode. Now your Internet Line will have the stable connectivity since the other line(CALLS) is put OFF.

Note: It is just a workaround, by doing so you may not able to receive calls from outside people but at the same time you can enjoy the uninterrupted Internet Connection.

Will update the permanent solution of the same SHORTLY!!!.

Command Prompt : PAUSE keyword usage in BATCH file and Redirect output to File.

When we create batch file to execute SQL Commands using sqlcmd utility (or) to execute SSIS packages using DTEXEC utility, we knew that once the execution is over, black screen (CMD prompt screen) will flash off within a sec.

We often more interested in seeing the output status of the execution(successful or failed because of any reasons) once it is done.

All we need to achieve the same is by using the PAUSE keyword at last in the batch file.

PAUSE keyword ensures control gets back to the user by prompting a message
"Press any key to continue . . ." .
In this case after user hits any key in the keyboard only CMD prompt will goes off.

1)using SQLCMD

sqlcmd ....................
PAUSE

(or)

2)using DTEXEC

DTEXEC
PAUSE

Note: PAUSE keword is case insensitive.

3) Create or OverWrite

If you want to redirect the output status to any file, you can use > (Greater than) operator followed by filepath with filename.


sqlcmd .................................. > "C:\Users\TEST\output.txt"
PAUSE

Note: File will be created if not exists. File content will be overriden if file with the same name already exists.

4) Append
If you want to append use >> operator.



sqlcmd .................................. >> "C:\Users\TEST\output.txt"
PAUSE