Showing posts with label SQL Tips. Show all posts
Showing posts with label SQL Tips. Show all posts

April 4, 2015

SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database

As we are mostly engaged in SQL Assignments, we come to the place where we found that there are lots of ldf files which occupies lots of the space on the DISK. To continue my work, I have to make a room on the same DISK. I found that there are lots of log files (LDF) on the disk which occupies more than 1 TB (1024 GB). This is the development server and I found and confirmed that we can have “Recovery Option” as “SIMPLE” too. The tricky part over here is, these databases are already created and Log file names are not same as database name. So, first of all I need to find out the database name to which I can make Recovery Option as Simple. Finally, I got following query which helps me to continue my assignment as follows:
SELECT As DatabaseName, As FileName,
        m.physical_name As PhysicalFileName,
        (m.size * 8.0) / 1024 / 1024 As SizeInGB
FROM sys.master_files m
INNER JOIN sys.databases d ON d.database_id = m.database_id
WHERE m.Type = 1
It will give us the list as follows:
Reference: Tejas Shah (

September 1, 2014

SQL SERVER: SSIS - Conditional Split Data Flow Transformation Task

Conditional Split transform use split the source row in easy to multiple groups in data flow and which Destination table populated. Lets review the same by reading a Sample text file and separate out the data in two groups.

1. Create sample text file:
This text file is piped delimited and last row  in text Total Row count

242|Lorem ipsum
239|dolor sit
225|amet consectetur
242|adipisicing elit
142|seddo eiusmod
222|tempor incididunt
142|ut labore
143|dolore magna
144|Ut enim
Total Row count|9

2. Create sample table:
Create sample destination table in Test database
  1: CREATE TABLE [dbo].[Employee](
  2:  [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
  3:  [EmployeeNumber] [int] NULL,
  4:  [EmployeeName] [varchar](50) NULL,
  6:  (
  7:   [EmployeeId] ASC
  8:  )
  9: )
 10: GO

3. Add Data Flow Task:
Add data flow task in Package for transfer data source text file “Employee.txt” to sql server table “dbo.Employee” 
4. Add new Flat File Connection:
Right click on Connection Manager and add new Flat File connection 
5. Set Flat file source properties:
Flat file source properties set Connection manager Name, File Name (Source full filepath), column names in the first data row as a true
6. Add Flat file source:
Add “Flat File Source” in “Data flow Task” and set properties Flat file connection manager and retain null values from the source as null values in the data flow as true and click button on preview and see in this file extra row include in text file
7. Add Conditional Split Transformation in Data Flow Task:
Add Conditional Split Transformation in data flow task for split row
8. Set Condition Split Properties:
Set properties Condition split where EmployeeNumber equal to “Total Row count” then this rows not use and other row use to process.
9. Add new OLE DB Connection:
Right click on Connection Managers and add new OLE DB Connection 
10. Set OLE DB Connection properties
OLE DB Connection properties set Server Name, Connection to a database and click on ok button 
11. Add OLE DB Destination:
Add “OLE DB Destination” in “Data flow Task” and set properties OLE DB Connection Manager and Name of the table or the view
12. Set Input output selection :
Set Input output selection between Condition spit and OLE DB Destination
13. Run package and Check :

In this way we get split the information and use as per the requirement,

Reference: Tejas Shah (

August 6, 2014

SQL SERVER: Clone SSIS Package

Recently, I was assigned a job to create many DTSX packages. When I review the packages, I found those packages (Information flow) are moreover same. There is just a difference of Source file connection and destination SQL table which is different in each of the package. It might take couple of hours to create each of the package, but I wanted to get it done in few minutes. To achieve the same, I have looked up the DTSX code (XML) and updated as follows to achieve get it done efficiently and save some time.

Please find following steps to achieve the same:

1. Existing package:

2. Copy Package:
Right click on existing package and click on copy option and after right click on SSIS Packages folder and Paste on the location

3. Rename Package Name for newly Pasted file:
To rename package name as your mind and When message box open then click yes button

4. Open package in Notepad Editor:

Go to folder where package is exist and open with package in notepad editor

5. Replace package Name in Notepad file:

Replace old package name to new package name with Replace option

6. Replace more text

Replace more text if you know to Change (Example: “Activity” text replace to “Job”)

7. Check Replace name

Check all task name, SQL task and Data Flow task in replace with new text and Annotation text will no be changed, so it needs to be change manually

8. Need to change manually

If SSIS package has Package SQL server Configuration then we need to change that manually too.

April 6, 2010

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder.

There is a requirement to save File name along with record, so later on we can identify which record comes from which file.

Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.

1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task.
2. Right click on Flat File Source, and click on Show Advanced Editor:
Derived FileName Show Advanced Editor
3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:
  Derived FileName Component Properties

4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

Let me know your comments/questions.

March 7, 2010

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.

SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.

Let me show you how to use it. Syntax to use SP:

@ProcName = 'SPNAME',
@OptionName = 'startup',
@OptionValue = 'true/false OR on/off'

  • @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
  • @OptionName, should be "startup" always.
  • @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.

That's it, I hope this is very clear to use this feature.

Reference : Tejas Shah (

September 15, 2009

SQL SERVER: Community Tech Days in our City at Ahmedabad on 3rd October 2009

Microsoft Community Tech Days are in 11 cities in INDIA with 19 insightful Technical Sessions.

These insightful Technical sessions are available in our city "Ahmedabad", Gujarat on 3rd October 2009.

So book your calendar for this day and be a part of this TechDays.

Limited seats are available , so please register yourself with this event:

Let me know if you have any problem in registrations.

September 5, 2009

SQL SERVER: Difference between DELETE and TRUNCATE commands

We interviewed many people my company as recruitments are going on for developers. When I asked this SQL SERVER question to person "What is difference between DELETE and TRUNCATE in SQL SERVER?".

I got the following  answers from most of them are, which are incorrect:

1. I can not use WHERE condition with TRUNCATE command
2. I can not use TRUNCATE command if foreign key is there on table.
3. TRUNCATE is faster than the DELETE, as  DELETE write records them in Log file in case it is needed to rollback in future from LOG files. etc..

These answers are correct. I also got this answer, which is Incorrect:

"DELETE can be rolled back while TRUNCATE can not be rolled back"

I asked them what does it mean?, give me an example. See what they say, if I have Transaction and if I have used DELETE then ROLLBACK will let them back to original state. In case of TRUNCATE within Transaction, will not allow me to original state.

This is incorrect. We can ROLLBACK changes made by DELETE and TRUNCATE if the it is used in Transaction. Lets see this in detail by example:

Create one table table and insert some data in it like:

Lets try to use TRUNCATE within Transaction as follows:
BEGIN TRAN --Start Transaction
TRUNCATE TABLE Test --Use this command
--Check the data from table. See transaction is still in progress not commit/Rollback yet
--There is no row in table, as all rows were deleted from above TRUNCATE statement
ROLLBACK --Rollback this Transaction
--Lets see now what values should be there in table after ROLLBACK
So, we found that we can found all values in the table. We can ROLLBACK the TRUNCATE command as same as other commands.
This is place where most of the developers can not give accurate answer. I hope by this article they can have clear idea.

Let me know if there is any confusion.


DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as  DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

Reference: Tejas Shah( )

August 12, 2009

SQL SERVER: Check if Node exists in XML or not

Today, I have one requirement to check dynamically if a node exists in my xml or NOT.

I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic.

I figure it out by using EXISTS.

This is my XML, that I got as parameter.

DECLARE @ExportData  XML
SELECT @ExportData =
'<Data Number="A123">
    <EachData Parts="Test1" />
    <EachData Parts="Test2" />
    <EachData Parts="Test3" />
Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result.
So, I used this

SELECT @ExportData.exist('(//BulkData)')
This will return "1" if node is exists else return "0".

That's it. I can write based on the return result by this statement.

Let me know if it helps you.

Reference : Tejas Shah(

July 31, 2009


Recently I have following situation:
I don't need to execute trigger's code on some condition, like if it is called from particular stored procedure. If data is being updated from any other places, like application, or any other stored procedures, trigger code should be executed, but when one particular Stored Procedure is updating data to that table, it should not allow to do so.
I search and I got the option CONTEXT_INFO with SQL SERVER. Let me share this with all of you.
NOTE: We can use CONTEXT_INFO value in that session only. If there is new session we cannot use that value stored in CONTEXT_INFO.
We can set CONTEXT_INFO up to 128 bytes of binary information with the current session or connection. Reference:
Let see an example, for better understanding:
 ColVal VARCHAR(100)
Let's create two SPs, which will insert a record into tblA, which is just created
    INSERT INTO tblA(ColVal)
    SELECT 'Allow To insert'

    SET CONTEXT_INFO 0x5465737442
    INSERT INTO tblA(ColVal)
    SELECT 'Not Allow To insert'

In this example, when we execute SP: TestA, it should allow to insert record in the table, while we execute SP: TestB, it should restrict. Here I set CONTEXT_INFO with SPName. I can use the same whatever is stored in CONTEXT_INFO during the current session.
To make validation as defined above, lets create one trigger to restrict/allow user to proceed.
   ON  tblA

    DECLARE @Message varbinary(128)
    SELECT @Message = cast('TestB' as varbinary(128))
    IF    @Message = CONTEXT_INFO() BEGIN
        RAISERROR('Not Allowed to Insert/Update/Delete from SP: TestB',15,1)
In Trigger, as you can see, I again converted the SP name and compare it with CONTEXT_INFO.
When Stored Procedure TestA is being executed, CONTEXT_INFO is not set, so trigger will not find comparison and allows user to insert record.
While we execute TestB, as we have set CONTEXT_INFO, system will find comparison in trigger and restrict us to complete transaction. So system will give error message:
So, by this way we can validate value in trigger, to make such decision
Reference : Tejas Shah

May 4, 2009

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.
I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.
Proposed Solution:
Convert VARCHAR variable to XML as follows:
SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
So this statement will generate XML from VARCHAR value as follows:


So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:
SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
This will give me result set as: ( as separate table)
So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.
So my Procedure looks like:
CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)


SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +

UPDATE    TableName
SET        Flag = 1
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
So, it is very easy to read values from Comma separated value.
Let me know if it helps you.

April 15, 2009

SQL SERVER: Difference between OpenQuery and OpenRowSet

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.

Let me share this thing with all of you.

Syntax for both the command:

OPENQUERY ( linked_server ,'query' )

( 'provider_name' , 'datasource' ; 'user_id' ; 'password'
, { [ catalog. ] [ schema. ] object | 'query' }

Difference is:
OpenQuery uses a predefined linked server,
While OpenRowSet has to specify all the connection options. So with OpenRowSet you can query to your remote SQL server from local.

Else it's the same.

Let me know if it helps you in any way.

March 26, 2009

SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

Today, I came across requirement where I need to perform an action on all of the tables within a database.

For example, How much space occupied by each table.

I found undocumented Procedure: sp_MSforEachTable in the master database.

The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

So, We can use sp_MSforeachtable procedure when we need to loop through each table.

Let me know if it helps you in any way.

February 28, 2009

SQL SERVER: Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list.
Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.
I have Table like:
field1 VARCHAR(5), field2 VARCHAR(5)
Lets insert some data in this table:
SELECT '001','AAA'
SELECT '001','BBB'
SELECT '002','CCC'
SELECT '003','DDD'
SELECT '004','EEE'
SELECT '004','FFF'
SELECT '004','GGG'
So now my table has Data like:
Get Comma separated List
Expected Output:
Get Comma separated List
Proposed Solution:
SELECT field1,
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
 ), 3, 1000)
FROM #test t1
GROUP BY field1
Get Comma separated List
My Output will be:
Get Comma separated List
Please make comments, if this helps you in any way