Query - To list the jobs and their steps from where SSIS packages are getting executed- SQL 2008

Recently, I was asked to write a SQL query to fetch the information of the enabled jobs and their steps where SSIS package (stored in SQL server) is getting called directly.

As most of us know information related to jobs and other SQL agent related stuff is stored in the MSDB database in SQL server. There are quite a few tables which comes handy while trying to fetch the job details and honestly, I did not know where do I look for the required information. While browsing for the data I got to know about the following tables which provides all the necessary information 

• sysjobsteps - Stores the job step details 
• sysjobs - Stores the information related to jobs

Note - I noticed that when the package (stored in SQL server) is called from the job step, "command" column in the sysjobsteps starts with "/SQL". Following query is based on this observation only and I could be wrong in assuming that hence please feel to correct me in case I'm wrong. Also if there exists an easier way to find the job-package details please let me know..  

SQL Script

Tip - Query to list the accounts running the SQL services

It will be quick this time around ..

Have you guys ever encountered a situation when you badly had to know the account name under which some remote SQL server and SQL server Agent services are running. 

Ahh you did, great.. So what did you do ? went to SQL box and found out it using services.msc or SQL config manager ?  alright Good. 

Let me make situation a bit more challenging ..

What if you can not remote into SQL BOX because of limited access? Now what will you do ?Pretty frustrating isn't it. Just to know the account name for the SQL service you have to raise a ticket with IT and wait for them to respond. 

But what if I tell you, when you can query that server fine why bother jumping onto the server itself? Yes,it can be achieved using simple SQL statement. Isn't that awesome? if you ask me.. YES, it is.

Starting SQL 2008 R2  MICROSOFT has solved this problem for us and presented 

• dm_server_services - The dynamic management view to report status information about the services

Well how about that.. let us see it in action 

MS-CRM - Query to list the Activity record distribution across Business Units in the CRM database

The MS-CRM contains many entities of type Activity. Few of the most widely used activities are PhoneCall, Email, Appointment etc..

When the activity record is created in MS-CRM, it internally loads 3 tables with the provided information

1. Base table for the Activity. Ex. - AppointmentBase
2. ActivityPointerBase
3. ActivityPartyBase

ActivityPointerBase table contains one record for each activity record that is created in the MS-CRM. While ActivityPartyBase table hols the information of the parties (participants) for particular Activity record.

Following tables are used in the query to find the distribution of the activities across 

Interview Question - What is the difference between Stored Procedures and Functions ?

A very common question, usually asked when the candidate is applying for SQL developers position. 

I have listed few of the differences that I can think of right now. 

Please feel free to let me know 

• In case you know any additional differences
• You think the difference listed below are not correct

Interview Question - How to get the count of rows for each table of the particular database ? How.....

This is simple yet important question which may feature when someone is interviewing for SQL developer position.  

The question goes like this 
"How to get the count of rows for each table of the particular database ? How many ways you can think of to fetch the details ?"

Again second part of the question made it interesting because now interviewer wants to understand your knowledge about different ways of Looping in SQL server ?

One obvious answer for this question would be using CURSOR but I'll leave that to you to write instead I'll try to use in-build looping mechanism that comes handy in this situation.