T-SQL: Recently Executed Query Oct 14, 2009 SQL SERVER   T-SQL

I am a regular reader of Pinal Dave’s blog SqlAuthority. I always found something new in his blog to work with SQL Server. Here is something I would like to share: Get the recent executed SQL Queries from SQL Server.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle)
AS destORDER BY deqs.last_execution_time DESC
Microsoft Event - Windows Mobile 6.5 & .NET Framework 4.0 Sep 26, 2009 MICROSOFT

I attended the Microsoft developers event at NCUI auditorium in New Delhi on Windows Mobile® 6.5 and .NET Framework 4.0. Abhishek Baxi was a key speaker on Windows Mobile® 6.5. You can read his blog here. He demonstrates all the new features that Win Mob provides to the end users. How do we sync our data using cloud (Windows Live Mesh), configuring Outlook (Exchange ID) and even configure your live and other IDs, access internet using IE and develops gadgets and other application using VS2008 and 2010 on your phone. He also tells us about Microsoft Marketplace and how do we developers get benefits from the marketplace.

Resources for Windows Mobile® 6.5:

After this we have a half-an-hour break time and then we get back in to attend Bijoy’s Singhal’s .NET Framework’s session. Bijoy is a Developer Evengelist at Microsoft. You can read his blog here. So, he started up to make all the developer’s aware of the new features of .NET Framework 4.0 provides. He continues to talk about parallel computing and parallel computing, PLINQ, DLR, enhancements in CLR, MVC, MEF, MAF and other tons of features…which is totally outstanding.

In the end we move to the round-table session where we all developers start with a short introduction of ourselves and end up with a feedback on Microsoft Technologies / Events / Cons / Pros and how can we make more and more people aware of the fact on how Microsoft Technologies affect their lives. Moving further we also discussed on how do we get more and more people especially students at the events so they know about the new technology and eventually this will help them as a fresher to choose a domain / technology to move on. Even personally I have seen students that they are very confused about on what they have to choose …..the technology they want to work in. They just think should I choose Java or .NET but they find it hard to decide. So it’s better that someone from Microsoft or on behalf of Microsoft can guide them to choose what’s best for them. All other developers also put questions regarding Microsoft certifications and the black market associated with it. Bijoy guarantees that he will be informing Microsoft regarding the same and will definitely take a serious action against this. After all this is about software giant’s reputation.

Finally, we decided to get connected once again and we will make our best efforts to promote Microsoft Technologies at every level as we can. I myself is a DieHard Microsoft Fan and I personally will make every possible effort to make people / students aware / beleive in the power of technology Microsoft provides.

Using Hashtables Sep 20, 2009 C#

Here I illustrate how to create and use hash table. Each element of hash table is a combination of Key/Value pair stored in a DictionaryEntry object. A value in a hash table can be null or contains no value but a key cannot be of null reference. Key in hash table work as an identifier for the value where you can search a hash table for a value with a specified key.

First you will need to import the namespace System.Collection. We use System.Collectionnamespace because it contains various interface and classes the defines various collection of objects like list, dictionary, queues, hash tables and bit arrays.

Adding Key/Values to Hash Table

using System;
using System.Collections;
class HashtableClass
{
 public static void Main()
 {
   Hashtable htbl = new Hashtable();
   htbl.Add("AL", "Alabama");
   htbl.Add("CA", "California");
   htbl.Add("FL", "Florida");
   htbl.Add("NY", "New York");
   htbl.Add("WY", "Wyoming");
   // Get a collection of the keys.
   ICollection coll = htbl.Keys;
   foreach (string str in coll)
   {
     Console.WriteLine(str + ": " + htbl[str]);
   }
 }
}

Clear all Key/Value pairs in a hash table

To clear all the key/value pairs in a hash table use Clear() method of a hash table class:

htbl.Clear();

Remove Key/Value pairs from hash table

Just if you want to remove a particular value from a hash table use the Remove() method of hash table class and specify the Key to remove the key/value:

htbl.Remove("NY");

Adding Key/value pair to hash table by using indexer

We can use Add() method of the hash table class to add key/value pair to the hash table, but instead we can use indexer to add key/pair:

htbl["NY"] = "New York";

Use the ContainsKey() method to check if hash table contains a key

Just a simple method to use with an IF condition:

if(htbl.ContainsKey("NY"))
{
       Console.WriteLine("Hashtable contains key NY");
}

Use the ContainsValue() method to check if hash table contains a key

Just a simple method to use with an IF condition:

if(htbl.ContainsValue("New York"))
{
    Console.WriteLine("Hashtable contains value New York");
}

Copy the keys from hash table into an array using the CopyTo() method

string[] keys=new string[5];
htbl.Keys.CopyTo(keys,0);
Laws of Computer Programming Sep 13, 2009 FUNDOO
  1. Any given program, once deployed, is already obsolete.

  2. It is easier to change the specification to fit the program than vice versa.

  3. If a program is useful, it will have to be changed.

  4. If a program is useless, it will have to be documented.

  5. Only ten percent of the code in any given program will ever execute.

  6. Software expands to consume all available resources.

  7. Any non-trivial program contains at least one error.

  8. The probability of a flawless demo is inversely proportional to the number of people watching, raised to the power of the amount of money involved.

  9. Not until a program has been in production for at least six months will its most harmful error be discovered.

  10. Undetectable errors are infinite in variety, in contrast to detectable errors, which by definition are limited.

  11. The effort required to correct an error increases exponentially with time.

  12. Program complexity grows until it exceeds the capabilities of the programmer who must maintain it.

  13. Any code of your own that you haven’t looked at in months might as well have been written by someone else.

  14. Inside every small program is a large program struggling to get out.

  15. The sooner you start coding a program, the longer it will take.

  16. A carelessly planned project takes three times longer to complete than expected; a carefully planned project takes only twice as long.

  17. Adding programmers to a late project makes it later.

  18. A program is never less than 90% complete, and never more than 95% complete.

  19. If you automate a mess, you get an automated mess.

  20. Build a program that even a fool can use, and only a fool will want to use it.

  21. Users truly don’t know what they want in a program until they use it.

Import Excel to SQL Table Jul 23, 2009 T-SQL

Using bulk operations using code with sql can be complex, time consuming and tedious for most of the programmers. I just used a stored procedure in one of my projects instead of traversing from cell to cell and save the value in the database, so I search the net and found this SQL Stored Procedure achieve this. I have just copied and paste the code as it is.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--drop procedure sp_excel_Convert_Excel_To_Table
--go
Create procedure [dbo].[sp_excel_Convert_Excel_To_Table]
(
@excel_full_file_name varchar(666)
,@convert_to_table_name varchar(50)
,@transfer_to_table bit=1
,@clear_existing_records_first bit=1
,@good int =null output
,@error_code int =null
,@error_description varchar(255) = null output
)
as
set nocount on
declare @command varchar(8000)
-- copy excel file under temp and change worksheet name
set @good=0
set @error_description = ''
set @error_code=0
declare @object int
,@hr int
,@src varchar(255)
exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '1. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=1
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', 'false'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '2. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=2
set @good=0
goto error
end
declare @workbook int
,@workbook_path_save_as varchar(666)
set @workbook_path_save_as = 'c:\test.xls'
exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out,@excel_full_file_name
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '3. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=3
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'Workbooks(1).Worksheets(1).Name','excel_data'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '4. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=4
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @workbook ,'SaveAs',null,@workbook_path_save_as
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '5. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=5
set @good=0
goto error
end
-- get full file name
exec @hr = master.dbo.sp_OAGetProperty @object, 'Workbooks(1).FullName',
@workbook_path_save_as output
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '6. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=6
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit'--,@workbook out,@workbook_path
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
goto error
end
if @transfer_to_table = 1
begin
if @clear_existing_records_first=1
begin
set @command = 'delete from ' + @convert_to_table_name
exec (@command)
end
-- copy records from excel into table:
set @command = 'insert into ' + @convert_to_table_name +
' select * from ' +
' OpenRowSet(''MSDASQL'' '+
', ''Driver={Microsoft Excel Driver (*.xls)};'+
'DBQ=' + @workbook_path_save_as + ''''+
',''SELECT * FROM [excel_data$]'')'
print @command
exec (@command)
set @command = 'select * from ' + @convert_to_table_name
exec (@command)
end
error:
select @error_description as 'Error', @error_code as 'Error Code'
exec @hr = master.dbo.sp_OADestroy @workbook
if @hr <> 0
begin
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
end
exec @hr = master.dbo.sp_OADestroy @object
if @hr <> 0
begin
set @error_description = '8. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=8
set @good=0
end