• About

sqlerrors

~ From the errors of others, a wise man corrects his own

sqlerrors

Category Archives: MS SQL Tips

What’s wrong with this query?

30 Thursday Jul 2015

Posted by Praveen D'sa in MS SQL Tips, Uncategorized

≈ Leave a comment

Tags

query, sub-query, table alias

Recently there was a question on one of the MS SQL forum, the query which user had posted was pretty simple but was behaving strangely.
Here I’m demonstrating you all the scenario which user had posted.

Create two tables and inserted few records in both the tables.

DECLARE @Header Table (EmpId Int , FirstName Varchar(50) , LastName Varchar(50))
DECLARE @Detail Table (id Int , Department Varchar(50))

INSERT INTO @Header Values(1 , 'ABC' , 'CBA'), (2 , 'BCD' , 'DCB'),(3 , 'CDE' , 'EDC')
INSERT INTO @Detail Values(7 , 'IT'), (8 , 'HR'),(9 , 'Admin')

Now execute below query, which pulls records from Header table for matching EmpId exists in Detail table.

SELECT * FROM @Header WHERE EmpId IN (SELECT EMPID From @Detail)

Following is the result set generated by above query.
Results

Is this query pulling out right data?

Apparently, yes it does, but wait a minute and carefully observe the sub-query.

SELECT EMPID From @Detail

If you carefully check the Detail table definition it doesn’t have any EMPID column defined, infact it has column name ID, but still entire query runs successfully and produces the result.

The EMPID in the sub-query does refer to the outer-query (Header) EMPID column and we are getting the result set.

Alter the above query introduce table alias and run it again.

SELECT * FROM @Header H WHERE H.EmpId IN (SELECT D.EMPID From @Detail D)

Now, the query fails with the error message.

Msg 207, Level 16, State 1, Line 7
Invalid column name ‘EMPID’.

Many a times we do commit this kind of coding errors in our queries without realising it, so it’s always recommended to use table alias’s in your queries to overcome this.

Hope this helps.!!!

Advertisement

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

SQL Server – Finding Last Backup & Restore Date for All Database

10 Monday Mar 2014

Posted by Praveen D'sa in MS SQL Tips, SQL Script

≈ Leave a comment

Tags

last backup, last restore, SQL Server

Many a times we need to find when my database last backed up or when it was last restored.
Below is the quick script i use to find last backup and restore dates.

Script to find last backup date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = db.name
GROUP BY
db.Name

Script to find last restored date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(rh.restore_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = db.name
GROUP BY db.Name

Hope this helps…!

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Magical Object Explorer Details pane

17 Monday Feb 2014

Posted by Praveen D'sa in MS SQL Tips

≈ Leave a comment

Tags

SQL Server Object Explorer

In this blog I just show you how effectively we can make use of SSMS object Explorer Detail pane.

Once you are in SSMS, go to View-> Object Explorer Details alternatively you can use shortcut key F7 to access Object Explorer window as shown below.
Object Explorer Detail
This brings you the Object Explorer Detail window in the same location where we normally have the Query window.

Object Explorer Detail Pane

Now you can navigate to any node in object explorer or click any node in object explorer and it will give you the related detail on Object Explorer Detail pane as shown in the above screen. Here I have selected SQL Server Agent node on Object Explorer and in Object Explorer Detail pane we can see all the objects related to SQL Server Agent.
Now I will show you how can we make best you of Object Explorer Detail pane to script out all the Jobs in a single click.
Navigate to SQL Server Agent -> Jobs, Object Explorer Detail pane will display all the jobs, now select any job and say Ctrl+A all jobs will be selected, now right click then say Script Job As -> New Query Editor Window as shown below.

export sql agent jobs

Now you have script out all your jobs in a single query window, no need to go to each job to generate script. Same technique can be used to generate script of various other objects like tables, views, procedures etc.

Will explore some more features of Object Explorer Detail pane in coming days.

Hope this helps.!!!

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Recent Posts

  • What’s wrong with this query? July 30, 2015

Categories

  • Learning (2)
  • MS SQL Tips (3)
  • Performance Tuning (1)
  • SQL Script (5)
  • SQL Server (14)
    • Index (1)
    • Log Shipping (1)
  • SQL Tips & Tricks (1)
  • Uncategorized (5)

Archives

  • July 2015 (2)
  • February 2015 (1)
  • October 2014 (2)
  • September 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (2)
  • February 2014 (2)
  • January 2014 (1)
  • December 2013 (3)
  • November 2013 (9)

sqlerrors

sqlerrors

Disclaimer:

This is my personal blog. The opinions & suggestions expressed here are
my own. For accuracy and official references please refer to MSDN,Microsoft TechNet,
Books Online. I do not endorse any of the tools / applications / books / concepts
mentioned here on my blog. I have simply documented my personal experiences on this
blog.

Create a free website or blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • sqlerrors
    • Already have a WordPress.com account? Log in now.
    • sqlerrors
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

    %d bloggers like this: