What’s wrong with this query?

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.

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

List permissions on any object – SQL Server

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…!

I Want to be mentored by Paul Randal

Tags

Paul Randal, SQLSkills

It’s been a long time since I blogged last time, due to hectic work schedule & other commitments, I could not dedicate any time to for writing blog. This post is also my fist post of 2015.
This blog post is in response to Paul’s (b/t) blog: “Want to be mentored by me? “, requesting Paul to consider my candidature to be groomed under his guidance.

About me

Myself is Praveen D’sa, I hold MCA (Master of Computer Application) degree from Mangalore University (same university from where Mr. Satya Nadella, CEO Microsoft attained his bachelor degree 😉 )
Post completion of MCA in 2005 I joined one of the leading media industry in India as a ASSP.Net developer, during that time SQL Server being administrated by a third party vendor in our company.
One fine day boss came upto me and asked me to take over DBA responsibilities from them and he gave me 3 months’ time to learn the skills, I replied, “Boss, I am your servant. Let it be done unto me according to your word”. Like many other DBA’s I too became DBA not by choice but by chance.
Very soon I got attracted towards by new responsibilities and gradually fell in love with SQL, I started to read and read and read, find SQL stuff online do R&D then I found the great site SQLSKILLS, inspired by his writings I took regular subscription to pluralsight.
I consider Paul as my guru, infact my first technical blog on this site is inspired by Pauls blog post (t).
Whenever Paul announces IE/IEPTO classes I madly wanted to attend one of these classes and wanted to be picked for SQLskills community mentoring program. Unfortunately due to my geographic barrier I never able to make to any of his IE/IEPTO classes and without which I cannot make it to SQLskills community mentoring program.
But this opportunity is a blessing in disguise; I desperately want to be mentored by Paul.

Why Paul?

I cannot see any other names but god of SQL to be my mentor, I have acquired lot of knowledge through my reading, research & online classes but right now it’s like a puzzle scattered into bits and pieces, I need mentor like Paul to put together these bits and pieces and make a beautiful picture of SQL. Paul is super dedicated person inspite of his busy schedule he manages to reply to your mails, I never anticipated a reply from Paul when I sent my first mail asking his advice, hats off to you Sir.

How do I benefit?

Grooming under Paul definitely help me to become a Professional DBA from accidental DBA, it also help to contribute to other SQL community members in a much better way (often responds to SQL related queries on MSDN, written few articles on technet wiki and own gold medal for this post)

Conclusion

I wanted to keep this post as short as possible but I am so overwhelmed by the thought of grooming under Paul I could not stop myself writing and pushing hard for my candidature. I apologize for keeping this long.
Desperately waiting for feb 16th to see my name in short listed candidates.
Wishing all best of luck.

Thanks

Find an object name in all databases.

Tags

dm_sql_referenced_entities, sp_MSforeachdb, sql_expression_dependencies

I have one user defined function , but i couldn’t find where the function is and where the function is using.. anyone pls help me to overcome this one.?

The below script will help you find all the references within each database.

DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/object name
Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SELECT DISTINCT
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
 OBJECT_SCHEMA_NAME(sed.referencing_id))
,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ''.'' + OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name like ''' + @ObjectName + ''' AND sre.referenced_entity_name like ''' + @ObjectName + '''';

EXEC sp_MSforeachdb  @MyQuery

References

sys.sql_expression_dependencies
sys.dm_sql_referenced_entities

Note

The system procedure sp_MSforeachdb is neither documented nor officially supported by Microsoft.
Hope this helps…!

SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 have been released

Tags

SQL Server, SQL Server 2008 R2 SP3, SQL Server 2008 SP4, SQL Server Service Pack

Microsoft SQL Server 2008 Service Pack 4 (SP4) and SQL Server 2008 R2 Service Pack 3 (SP3) are now available for download.

Download Service Pack 4 for Microsoft® SQL Server® 2008 

Download Service Pack 3 for Microsoft® SQL Server® 2008 R2

find last day of the month using EOMONTH

Tags

end of the month, EOMONTH, last day of the month

In my previous blog post i have demonstrated how to find first and last date of any month. SQL Server 2012 has introduced a new date function EOMONTH to find the last day of the month.

Syntax
EOMONTH ( start_date [, month_to_add ] )

Arguments
start_date
    Date expression specifying the date for which to return the last day of the month.
month_to_add
    Optional integer expression specifying the number of months to add to start_date.

Examples
EOMONTH without the month_to_add parameter to find last day of current month

EOMONTH with the month_to_add parameter to find last day of last and next month

SQL Server doesn’t provide any function to find start of the month, but we can make use of EOMONTH function to find start date of any month.

EOMONTH function is available in SQL Server 2012 and higher, for SQL Server 2008 R2 and below you can refer to this blog post.

Determining Drive letter database files use

Tags

catalog view, drive letter, sys.master_files

Many a times i need to move database files to new drive letter just to avoid existing drive going out of space, to determine which database files a particular drive host, you can  make use of catalog view sys.master_files as below query.

SELECT  DB_NAME(database_id) [Database Name],  
		Name [Logical File Name] , 
		physical_name [Physical File Name] , 
		Case When type_desc = 'ROWS' Then 'Data' Else 'Log' End [type]  
FROM sys.master_files 
WHERE LEFT(physical_name , 1) = 'C'

You will get result set as below.

Hope this helps…!

Thank you all for 1000+ views!

First off, I’d like to say a big thank you for visiting my blog for more than 1000 times:

I’ve had hits from 65 countries around the world, top three being, India with 347, US with 310, UK 60.

I know that other bloggers have 1,000 views per day but for me, reaching 1,000 has made me happy and at the same time this milestone has motivated me to keep updating my blog.

Here are some blog stats.

Thank you all very much…!.

Quickly search SQL Server Error Log

Tags

sp_cycle_errorlog, sp_readerro

As a Part of my routine DBA activity, I have to go through sp_readerrorlog to locate errors if any. In our production environment we have have Both Failed and Successful logins auditing enabled. Hence if i run sp_readerrorlog without any filters it would return me huge no of rows and go through each and every row is a time consuming and repetitive activity.
Here i would like to share how we can quickly go through Error Log with few filters.

sp_readerrorlog takes 4 parameters to narrow down our search.

1. @p1 – Log file we want to read, 0 = current error log, 1 = Archive #1, 2 = Archive #2 etc..
2. @p2 – 1 or null = SQL Server error log, 2 = SQL Agent error log.
3. @p3 – string which we want to search.
4. @p3 – string to further refine our search.

I basically filter one day error log as below.

EXEC sp_readerrorlog 0 , null , '2014-04-13 10:00' , '2014-04-14 10:00'

You can also refine your search just to check database backups like below.

EXEC sp_readerrorlog 0 , 1 ,   'Database Mirroring' , 'error'

As sp_readerrorlog is undocumented stored procedure to review error log, you will not find any official references in Books online.

Keep your current error log under control just by recycling it, so that you can quickly retrieve its output, refer sp_cycle_errorlog.

Hope this helps…!

Quick start of SSMS

Tags

SQL shortcut, SSMS, ssms.exe

Instead of starting SSMS through Start->Programs-> or through a desktop shortcut, one can start the SSMS in a much faster way by

1. Start->RUN or WINDOWS key+R
2. Type SqlWb.exe for 2005 or SSMS.exe for later versions

3. Press enter and SSMS is opened up for you.

If you want to take this further by making use of more options such as to directly connect to the server by just mentioning server name like

If you like to know what other options are available, you can do that as follows.

this gives you a screen as below.

Hope this helps…!