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

October 16, 2012

SQL SERVER: T-SQL to find out when database was restored

Recently, we have found that our testing database has been restored on the server and we lost our recent work. We have to identify that at what time the database has been restored, so we can know that what are the changes need to be restored again on the database.

We have found that MSDB database keeps information for each restore we have made on the server and luckily we have found the time and user we had restored the database. Let me share the simple script that I have found:

FROM    msdb.dbo.RestoreHistory WITH (nolock)
WHERE    destination_database_name = '<Database Name>'
ORDER BY restore_date DESC

SQL Yoga T-SQL to find out when database was restored

Reference: Tejas Shah (

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 (

December 3, 2009

SQL SERVER: How to Read Excel file by TSQL

Many times developers asked that, they want to import data from Excel file.

We can do this by many ways with SQL SERVER.

1. We can use SSIS package
2. Import/Export Wizard
3. T-SQL

Today, I am going to explain, How to import data from Excel file by TSQL.

To import Excel file by TSQL, we need to do following:

1. Put Excel file on server, means we need to put files on server, if we are accessing it from local.

2. Write following TSQL, to read data from excel file

SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLYoga.xls', [SQL$])


NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file

If you want to insert excel data into table,

SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$])

That's it.

December 2, 2009

SQL SERVER: How to remove cursor

Many times developer ask me that How can they remove Cursor?

They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same.
Please find this code to remove cursor with Table variable:
--declare table to keep records to be processed
--populate table variable with data that we want to process
INSERT INTO @Table(Column1, Column2)
SELECT    Column1, Column2
FROM    <Table>
WHERE    <Conditions>
--declare variables to process each record
DECLARE @inc INT, @cnt INT
--Assign increment counter
SELECT @inc = 1
--Get Number of records to be processed
SELECT @cnt = COUNT(*)
FROM @Table
WHILE @inc <= @cnt BEGIN
    --As we have AutoID declared as IDENTITY, it always get only one record.
    --Get values in Variable and process it as you want.
    SELECT    @Column1 = Column1,
            @Column2 = Column2
    FROM    @Table
    WHERE    AutoID = @inc        
    --do your calculation here
     --Select next record
     SET @inc = @inc = 1 

By this way, we can remove CURSOR by Table variable. 
It is quite easy to implement.
One more benefit is: It will process one record at a time, so it locks only that record at a time.
Let me know if you have any questions.