Find Cells containing Formulas in Excel

Method 1: Using ‘Go To Special’ Option:

1. With your excel sheet opened navigate to ‘Home’ tab > ‘Find & Select’ > ‘Go To Special’. Alternatively, you can also press ‘F5’ and then ‘Alt + S’ to open the ‘Go to Special’ dialog.

2. Next, in the ‘Go to Special’ window select the ‘Formulas’ radio button. After checking this radio button you will notice that few checkboxes (like: Numbers, Errors, Logicals and Text) are enabled, these checkboxes signify the return type of the formulas.

So, if you select ‘Formulas’ radio button and only check the ‘Numbers’ checkbox then it will just search the Formulas whose return type is a number. Here in our example we will keep all of these return types checked.

3. After this click the ‘Ok’ button and all the cells that contain formulas get selected.

4. Next, without clicking anywhere on your spreadsheet change the background colour of all the selected cells.
5. Now your formula cells can be easily identified.

Method 2: Using a built-in Excel formula:

Excel has a formula that can find whether a cell contains a formula or not.

=ISFORMULA(reference)

Here ‘reference’ signifies the cell position which you wish to check for the presence of a formula.

For example: If you wish to check the cell ‘A2’ for the existence of a formula then you can use this function as =ISFORMULA(A2)

This function results into a Boolean output i.e. True or False. True signifies that the cell contains a formulas while False tells that cell doesn’t contain any formulas.

Method 3: Using a Macro for identifying the cells that contain formulas:

I have created a VBA Macro that can find and colour any cells that contain formula in the total used range of the Active sheet. To use this macro simply follow the below procedure:

1. Open your spreadsheet and hit the ‘Alt + F11’ keys to open the VBA editor.

2. Next, navigate to ‘Insert‘ > ‘Module‘ and then paste the below macro in the editor.

view plaincopy to clipboardprint?

1. Sub FindFormulaCells()  
2. For Each cl In ActiveSheet.UsedRange  
3. If cl.HasFormula() = True Then  
4. cl.Interior.ColorIndex = 24  
5. End If  
6. Next cl  
7. End Sub  

3. For running this formula press “F5” key.

4. This Macro will change the background colour of all the formula containing cells and thus makes it easier to identify them easily.

What is syspolicy_purge_history

This job is created by default when an instance of SQL Server 2008 is installed or upgraded to SQL Server 2008.

SQL Server 2008 introduced a new feature called Policy Based Management. A Policy can be something like xp_cmdshell should not be enabled on an instance. When a policy runs it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.

In the above screenshot it is set to 0 which means that the Policy Evaluation History will never be deleted. So on this instance the job is running just like that 

If this job is missing for some reason, it can always be recreated using the below query

EXEC msdb.dbo.sp_syspolicy_create_purge_job

Excel : Split text into different cells

You can take the text in one or more cells, and spread it out across multiple cells. This is the opposite of concatenate, where you can combine text from two or more cells into one cell. For example, if you have a column of full names, you can split that column into separate first name and last name columns, like this:

Go to Data > Text to Columns, and the wizard will walk you through the process. Here’s a full breakdown of how it works:

1. Select the cell or column that contains the text you want to split.
   Note    Select as many rows as you want, but no more than one column. Make sure there’s enough empty columns to the right so nothing over there gets overwritten. If you don’t have enough empty columns, add them.
2. Click Data >Text to Columns.
   
3. This starts the Convert Text to Columns Wizard. Click Delimited > Next.
4. Check Space, and clear the rest of the boxes, or check Comma and Space if that is how your text is split (Smith, John, with a comma and space between the names). You can see a preview of your data in the Data preview window.
   
5. Click Next.
6. In this step, you pick the format for your new columns, or you can let Excel do it for you. If you want to pick your own format, select the format you want, such as Text, click the second column of data in the Data preview window, and click the same format again. Repeat for all the columns in the preview window.
   
7. Click the button to the right of the Destination box to collapse the dialog box.
8. Select the cells in your workbook where you want to paste your split data. For example, if you are dividing a full name into a first name column and a last name column, select the appropriate number of cells in two adjacent columns.
   
9. Click the button to expand the dialog box, and then click Finish.
   

How To Resolve Database Blocking In Oracle

Database blocking is a situation where the statement run by one user locks a record or set of records and another statement run by the same user or different user requires a conflicting lock type on the record or records, locked by the first user.

Database blocking issue is a very common scenario in any application.

How to Identify the blocking session
1. DBA_BLOCKERS  : Gives information only about the blocking session.

SQL> select * from dba_blockers;
HOLDING_SESSION
—————
252

 

2. v$LOCK  : Gives details of blocking and waiting session.

SQL> select l1.sid, ‘ IS BLOCKING ‘, l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

SID ‘ISBLOCKING’         SID
———- ————- ———-
244  IS BLOCKING         252

 

To get more specific details use the below query:

 

SQL> select s1.username || ‘@’ || s1.machine
|| ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
|| s2.username || ‘@’|| s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

BLOCKING_STATUS
——————————————————————————–
MECK@machine1 ( SID=244 )  is blocking TAMY@machine2 ( SID=252 )

 

How to Identify the locked object

SQL> select * from v$lock ;

ADDR             KADDR                          SID TY        ID1        ID2             LMODE   REQUEST   CTIME     BLOCK
—————- —————-                    ———- –       ———- ———- ——-    ———-       ———- ———-
0000000451723DE8 0000000451723E20        244 TX    1310745    3139497          6          0         166           1

000000046032AFE0 000000046032B000        252 TX    1310745    3139497          0          6          33             0

 

TYPES OF LOCKS -  UL, TX amd TM
1. UL is a user-defined lock This is a lock defined with the DBMS_LOCK package.
2. TX lock is a row transaction lock; it’s acquired once for every transaction that changes data.Number of objects are being changed does not matter. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
3. TM lock is a DML lock. It’s acquired once for each object that’s being changed. The ID1 column identifies the object being modified.

So to find the object that is being blocked we can use ID1 from the v$lock.

SQL> select object_name from dba_objects where object_id=307193;

OBJECT_NAME
————–
OBJ1

How to Identify the locked row ?

SQL> select do.object_name,
row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=252
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME
——————————————————————————–
ROW_WAIT_OBJ# DATA_OBJECT_ID ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————- ————– ————– ————— ————-
DBMS_ROWID.ROWID_C
——————
OBJ1
307193         307193              5             455             0
AABK/5AAFAAAAHHAAA

From this, we get the row directly:
SQL> select * from obj1 where rowid=’ AABK/5AAFAAAAHHAAA’ ;

Getting the sql query that is being blocked
If you got the sid it should be easy by using the following sql :
SQL>select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = 252;

SID SQL_TEXT
—– —————————————————————-
252 update obj1 set bar=:”SYS_B_0″ where bar=:”SYS_B_1″

Finding the blocking session SID and Serial#.

SQL> Select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session where blocking_session is not NULL order by blocking_session;

BLOCKING_SESSION     SID    SERIAL#  WAIT_CLASS     SECONDS_IN_WAIT
—————- ———- ———- ————————————————–
244                             252  11049       Application        1634

Solution to resolve locking
Kill the blocking session.
SQL> alter system kill session 244,11049′ immediate;

System altered.

 

Workspace Invisible in Lotus Notes

Problem

In Lotus Notes 8.x Standard client, you customize a toolbar by adding the Workspace button. When you attempt to click the Workspace toolbar icon, nothing happens. Additionally, if you focus the mouse on any of the tabs listed below, clicking the button does nothing:

- Mail
- Calendar
- Contacts
- ToDo

 

Cause

The issue is seen only in the Notes 8 Standard Configuration. The issue occurs when the focus is currently in a Java based database/view/document or other user interface (in your mail file or contacts).

Resolving the problem

This issue is fixed in 8.5.2 Fix Pack 2 and 8.5.3

Workaround
Change focus to a non-Java based database/view (such as the Home page or a Notes database other than mail) and then click the Workspace icon.

Steps to reproduce issue:
1. In Notes select File > Preferences. Expand the Notes Toolbar section and select Customize.
2. Add the Workspace button/icon to one of your toolbars, such as the Universal toolbar.
3. Select OK to save the changes.
4. Switch to your mail file.
5. Click the Workspace icon on the toolbar. The Workspace does not launch.

ClickOnce Deployment Application Identity

Scenario supported:  Multiple versions of the same application installed to a single machine/user's account. 
 
For example, say you have a version 2.0.0.0 of an application that is your production version, and you publish a new beta version (3.0.0.0) that you want a limited set of users to access, but those same users need to be able to run both production and beta side by side through ClickOnce on their machines (perhaps for feature comparison testing).

The first step is that you will need to have different deployment manifests for the multiple versions you want a single user to run. You direct the user to launch from each URL to the different deployment manifests and they will get a separate installation on their machine... or will they? The answer depends on a hidden aspect of the ClickOnce runtime regarding what the runtime considers a unique identity for an installed application.

ClickOnce actually install an application on a client machine, it downloads and caches the deployment manifest, the application manifest, and all of the application files. Those manifests have to be signed by a publisher certificate that is cryptographically unique. Additionally, the installed application has a product name that gets embedded in the deployment manifest.

You might be tempted, as I was, to think that a unique product name, combined with a separate deployment manifest would be sufficient to make the client machine treat those installs as separate and distinct (such as setting the product names to "MyApp" and "MyApp - Beta"). Unfortunately you would be wrong, as I was.

There is actually a separate piece of information that the ClickOnce runtime uses to distinguish one application from another - the application identity is set by an identity set for the deployment manifest itself. This identity is normally created by Visual Studio when publishing and is set to the deployment manifest name (i.e. WindowsApplication1.application). You do not have control from Visual Studio to set this to anything else. Through the mageui.exe SDK tool, or better yet my Manifest Manager Utility included with the patterns and practices guidance, you can set this application identity to any string that you like to uniquely identify multiple published versions of a single application.

So to address the scenario presented earlier, you can simply set the application identity to MyApp for one version and MyApp-Beta for the other version, and you will be able to side-by-side install those two copies of the app on the same machine.

I came across this video illustration, really helpful
https://robindotnet.wordpress.com/2009/04/22/clickonce-installing-multiple-versions-concurrently/ 

sp_whoisactive and sp_who3

For looking into some SQL performance problems on systems I normally use these procedures.

sp_whoisactive 
sp_who3 

On running sp_whoisactive, if the output is showing a CXPACKET wait type that’s when sp_who3 comes into play.  Running sp_who3 with a SPID number after the procedure like “exec sp_who3 125″ will give you the wait type for each thread within the process.

When doing this recently on a system sp_whoisactive showed me that CXPACKET was the wait type.  After digging into the process with sp_who3 I saw that one of the threads was waiting on SOS_SCHEDULER_YIELD.  I then used sp_whoisactive to get the execution plan showing me the missing index which needed to be created.  In this case there was a clustered index on the table which was being scanned.  Based on the page count output from SET STATISTICS IO we were scanning the entire table every time the query was run.  This massively expensive query was causing the query to parallelize and the run time to go insanely high.

Hopefully you’ll find these stored procedures to be useful in your performance troubleshooting.  They aren’t hard to use, but they sure are useful.