• About

sqlerrors

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

sqlerrors

Monthly Archives: July 2015

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...

List permissions on any object – SQL Server

22 Wednesday Jul 2015

Posted by Praveen D'sa in SQL Server

≈ Leave a comment

Tags

object permission, permission, sp_helprotect

Sometimes we need to quickly check the permissions on any database object, below script will help you to extract this information.

Method 1.

USE <DB_Name , Sysname , Database Name>
Go
sp_helprotect '<object name , sysname , object name>'

Note: use Ctrl-Shift-M to replace template parameters.

Though the above method displays the desired result we can not filter the result, to filter the result based on different parameters there is an alternative method as shown below.

Method 2.

USE <DB_Name , Sysname , Database Name>
Go
SELECT
    dp.NAME AS principal_name
    ,dp.type_desc AS principal_type_desc
    ,o.NAME AS object_name
    ,o.type_desc
    ,p.permission_name
    ,p.state_desc AS permission_state_desc
    FROM sys.all_objects o
    INNER JOIN sys.database_permissions p ON o.OBJECT_ID=p.major_id
    LEFT OUTER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
    WHERE o.NAME = '<object name , sysname ,object name>' 
    --AND dp.name = '<principal name , sysname ,login name>'    

Note: use Ctrl-Shift-M to replace template parameters.

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: