Copy-Paste/Renaming the Sheet Name in Microsoft Excel

I thought everything was super easy with Microsoft Products. At least with MS Office. But just realized that MS has long way to go with respect to good UX. All I was trying to do is - to rename a sheet in Excel by pasting a string from clip board. Did you ever try it?

After spending about half-an-hour experimenting and searching through the web, I finally figured out how to do it. Some keys to remember:

[CTRL] + [INS] -> Copy

[SHFT] + [INS] -> Paste

[SHFT] + [DEL] -> Delete

This did the trick for me, but if you try to copy a cell within excel and paste it as a sheet name, that wouldn't work. Because, you will actually be copying the cell and not the content inside it. You can go to the edit box of the cell and copy content and then try pasting it with + .

In my case, I still couldn't do it. The string never gets copied... after a bit of research, figured out that the sheet name has a lenght limitation (31 chars) and I was trying to copy a larger string. Otherwise, the shortcut keys listed above do the job!

I feel like a scientist today.. :)

Accessing Active Directory without C#.Net

Earlier I posted an article which talks about basic C# code to access AD. Recently, I came across a method with which we could access some basic data from AD in windows without any programming.

Create a shortcut on your desktop with the following target:

%windir%\system32\rundll32.exe dsquery.dll,OpenQueryWindow

When you open the shortcut, you would see a window similar to the one:

As you can guess, it is an easy search tool to traverse through the AD for different data. You might say 'What is the big deal in this?'. We already get this info with our windows network or with outlook addressbook. But this can give us access to larger set of data if you observe carefully.

For instance, the description field in the Users would give the employee designation(role) in our organization. What is interesting is that information is confidential and no one is supposed to share it with others. But some one didn't take care while mapping that info to AD. Now I know who is exactly at my level and who is one level ahead.. :)

Have you find any other interesting data with this? Let me know by posting comments..

Accessing Active Directory through C#.Net

If you are reading from an office computer, you might have already been logged in to a domain using your domain user id and password. Most of the organizations use Active Directory (AD) to organize their internal computing assets and also control access to it. Have you ever used an intranet website that greets with your full name (without logging in)? System.DirectoryServices name space in .Net gives access to AD services and allows us to do lot of things.

To see the current logged in account details,

string DomainUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Replace("\\", "/");

To get the full name of the currently logged in user,

DirectoryEntry ADEntry = new DirectoryEntry("WinNT://" + DomainUser);

string FullName = ADEntry.Properties["FullName"].Value.ToString();

And if you want to loop through all the available resources in AD,

foreach (string propName in ADEntry.Properties.PropertyNames)

{

foreach (object value in ADEntry.Properties[propName])

{

Response.Write( propName + " : "+ value.ToString() + " : " + value.GetType() + "\n");

}

}

This introduction will only help have a glance at AD. You could do a lot more if you explore the AD and System.DirectoryService name space.

Ever foxed by Nulls in DB (SQL Server and Oracle)?

Recently, I landed into an issue that gave an opportunity to explore more about null values.

Consider two tables X(Int Column A) and Y(Int Column B) with following values:

A (Table X):

-----------

1

NULL

2

3

4

B (Table Y):

-----------

1

2

4

NULL

1. Run the query 'SELECT B FROM Y WHERE B IS NULL'. You will see one NULL record.
2. Run the query 'SELECT * FROM X WHERE A NOT IN(SELECT B FROM Y WHERE B IS NULL ) '. You will see no results. I was expecting it to return 1,2,3, and 4 values.
3. Run the query 'SELECT * FROM X WHERE A IN(SELECT B FROM Y WHERE B IS NULL ) '. You will see no results.
4. Run the query 'SELECT * FROM X WHERE A NOT IN(SELECT B FROM Y WHERE B IS NOT NULL ) '. Again, you will see no results.

Queries listed at 3 and 4 look quite opposite but both return no result. If you are wondering which database I am talking about, I am using SQL Server 2005 with ANSI_NULLS ON. I see the same behavior with Oracle 11i as well.

Reason for this behavior is simple, DBs treat NULLS as 'Unknown' value. If you are trying to compare something with an 'Unknown' value, that is the reason we get an 'Unknown' result.. :) In SQL Server, if you turn off ANSI_NULLS (using SET ANSI_NULLS OFF statement), you would get different results for the above queries.

This leads us to a question. How should we write safe queries to not get surprises while in production?. This article explains how we can use NOT EXISTS instead of NOT IN. You can read more about NULLs in Oracle in this article. And if you are looking for the SQL SERVER help, check out this article.

How to covert comma separated values into a table in Oracle?

One of my friends was working on production support for a system and was dealing with a tidious process of manually verifying thousands of rows to filter out certain records. The process was error prone and demanded lot of concentration all the time. She was looking for a script to make it better.

The requirement: She would be given a set of data (comma separated values) and she need to verify if that data is present in database or not. If she is given 1000 numbers but only 600 exist in database, she has to figure out the missing 400 and send them back for some action.

She wanted to use a join (left outer) on the table in DB and the given list to get the missing records, but the list she got is a comma delimted list and she was looking for a way to convert it into table. I gave the following script (after googling and some research) which did the trick for her..

Step1: Create type

create or replace type split_tbl as table of varchar2(32767);
/
show errors;

Step2: Create function

create or replace function splitfunc

(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;

l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;

end splitfunc;
/
show errors;

Step2: The Query

select a.column_value from table(SPLITFUNC('143,3455,356')) a
left outer join TBL_MyTable b on a.column_value = b.TBL_pk
where b.TBL_pk is null;

As you can see, this script creates a type and then a user defined function which uses the type. In the query, we use the function to make the list as table and then use the returned table in join to filter the data.

If you are wondering what that type does, check this out. Also you can know more about the Pipelined table functions here.

Let me know if this helped you and also if you have a better method to do this..

How to get table space name in Oracle and file group name in SQL Server?

I was working on an upgrade script which is supposed to create a new table in the database. One catch was it has to be created on an existing table space in the oracle DB and similarly in a file group for SQL Server.

In Oracle, I have an existing table (say TBL_ExistingTable) in the target table space and need to create my new table in the same table space. I used the following script to get the desired table space name and create new table (say TBL_NewTable) in it:

declare

i_count number:=0;

v_sqlstmt VARCHAR2(2000);

v_tablespace VARCHAR2(81);

begin

select tablespace_name into v_tablespace from user_segments where upper(segment_name) = 'TBL_ExistingTable';

select count(*) into i_count

from user_tables where table_name = upper('TBL_NewTable');

if i_count <>

v_sqlstmt := 'create table TBL_NewTable ('||

'Tbl_Pk number( 10, 0 ) not null ,' ||

'.... full table definition.....................' ||

')ROWDEPENDENCIES tablespace '|| v_tablespace ;

execute immediate v_sqlstmt;

end if;

end;

/

EXIT

Similarly for SQL Server, I used the following script:

declare @vc_filegroup varchar(250)

declare v_sqlstmt VARCHAR2(2000)

SELECT @vc_filegroup = coalesce(GroupName,'')

FROM SysFileGroups

WHERE GroupID IN

(

SELECT GroupID FROM SysIndexes

WHERE UPPER(SysIndexes.ID) IN (OBJECT_ID('TBL_ExistingTable'))

)

SET @v_sqlstmt = ' create table TBL_NewTable

(

Tbl_Pk int identity(1, 1)not null ,

................ full definition.....................................................

)

ON ' + @vc_filegroup;

IF (object_id('TBL_NewTable') IS NULL AND @vc_filegroup != '')

EXEC (@v_sqlstmt);

go

Do you have a better way of doing this? Or found this helpful? Please share your feedback by posting comments..

Ever got spammed? Ever used CAPTCHA?

We all know what spam is and might have already discussed lot about it. But, did you ever face spam which directly impacted your revenue and user experience? I have experienced that. I used to work for an online portal in which users can post their comments, new topics, etc. One fine day series of spam attacks occurred and the site became literally unusable and had to be taken offline. We had to cleanup all that mess and to prevent such attacks, introduced a captcha in all the places where user submissions are taken.

The topic that I want to discuss here is the effect of captcha on the site. Is it all positive or does it have some negative effect as well? This article explains in detail that users don't always like captcha and it drives away considerable number of genuine users.

Fortunately, it also points to a simple solution - Honeypot CAPTCHA technique. Phil Haack suggests use of a blank hidden field (using CSS) which will be ignored by real users but would be filled by the bots. I liked this idea and would surely use it in future.

Did you have any experience with Spam Attacks and Captchas? Share your suggestions/experiences by posting comments..