Wednesday, May 31, 2006

Split function in SQL Server -- Method 2

There are instances when we want to insert a record into a table and based on the identity value of that row insert remaining content into another table. For the purpose of better understanding let me explain the case with an example.

Assume from a front-end page a user would provide a student's "Name", "Age' and "List of subjects he/she would be learning". Once the user clicks on "Save" button "Student Name and Age" needs to be stored in Table1 and with its identity value insert subject list in Table2. Hope I have made the case clear :)

There are actually two methods to approach this situation. They are:

1. Making use of sp_xml_preparedocument, sp_xml_removedocument and
2. Custom split function

Well, now let us take these items one by one and see how to solve this problem.

Sample table structure

Both the methods explained in this post would use the below table structure only.

Create table StudentMaster
(
StudentID int Identity(1,1) not null,
StudentName varchar(100),
StudentAge int
)
Go

Create table StudentDetails
(
StudentID int,
SubjectName varchar(10)
)

Making use of sp_xml_preparedocument and sp_xml_removedocument

Before having a look at the SP let us first have a look at important functions and system stored procs used in this approach.

In order to use and manipulate XML data in SQL Server, two new system-stored procedures and the OPENXML function have been added to SQL Server 2000.

OpenXML() functions expose XML data as a relational row set. This set enables you to insert and update the XML data in a SQL Server 2k database.

In order for SQL Server to process XML documents, we can use the system stored procedure sp_xml_preparedocument, to read the documents and verify whether it is a valid XML document or not. The stored procedure then returns a numeric handle to the XML document. That handle is passed to and used by the OPENXML function to convert the tree structure representation of the XML document to a relational format. The OpenXML() function then executes the INSERT statement to insert the data in a relational format in a SQL Server DB.

Once a document has been fully processed, we should use the sp_xml_removedocument stored procedure to reclaim the memory used by the node tree (by destroying the XML document and release the server's resources). Getting into the habit of deleting the document tree can help prevent memory shortage problems in our SQL Server application.

From SQL Server 2k there is a new function by name Scope_Identity() which returns the last identity value, produced on a connection and by a statement in the same scope.

Create procedure InsertStudent
@StudentName varchar(100),
@StudentAge int,
@SubjectString varchar(1000)
/*
Students subjects passed as XML in format:
root

----- row subjectname="ABC" studentid="123456"
----- row subjectname="DEF" studentid="123456"
/root

here, 123456 is hard-coded for replacing later in the SP and "ABC, DEF" are test vlaues; they will be changed as per the string passed
*/
As

Begin Tran StudentTransaction

Declare @NewRowId int
Declare @SubjectXmlDoc int

/* Insert Master record and get identity value */
Insert into StudentMaster (StudentName, StudentAge) Values (@StudentName, @StudentAge)

--Retreive the last Identity value inserted into the identity column (StudentID)
Select @NewRowId = Scope_Identity()

/* Replace dummy identity value with actual id value*/
Select @SubjectString = Replace(@SubjectString, '123456', Convert(varchar(10), @NewRowId))

/* XML Bulk Insert the Subjects */

-- The below line creates XML Document and returns numeric ID
Exec sp_xml_preparedocument @SubjectXmlDoc OUTPUT, @SubjectString

Insert into StudentDetails (StudentId, SubjectName) Select studentid, subjectname from OPENXML (@SubjectXmlDoc, '/root/row') With (StudentId int, SubjectName varchar(1000))

-- Deletes the XML document
Exec sp_xml_removedocument @SubjectXmlDoc

Commit Tran StudentTransaction

Hope the above stored procedure would give you a good idea of how to make use of these systems SP to solve our given problem. The only hitch with this approach is we need to pass the values as a XML format string to the SP.

Now lets have a look at the Custome Split function to solve the same issue.

Custom Split function

Create Procedure InsertStudent_Revised
@StudentName varchar(100),
@StudentAge int,

/*
Comma seperated value. A comma needs to be added at the end of the string as well.
*/

@SubjectString varchar(1000)

As

Begin Tran StudentTransaction

Declare @NewRowId int
Declare @strDelimitedString varchar(50)
Declare @Strat int
Declare @End int
Declare @strValue varchar(50)

--Insert master record and get the identity value

Insert into StudentMaster (StudentName, StudentAge) Values (@StudentName, @StudentAge)

Select @NewRowId = Scope_Identity()

--- Custom split function starts here

Set @strDelimitedString = @SubjectString
Set @Start = 1
Set @End = charindex(',',@strDelimitedString)

While (@End > 0)
Begin

Set @strValue = Substring(@strDelimitedString, @Start, @End - @Start)
Set @Start = @End + 1
Set @End = charindex (',',@strDelimitedString, @End+1)

Insert into StudentDetails (StudentId, SubjectName) Values (@NewRowId, @strValue)
End

Commit Tran StudentTransaction

To test this SP:

Exec InsertStudent_Revised 'Vadivel', 29, 'Maths,Science,Chemistry,'

By using a custom split logic we are splitting the values passed into the SP. This approach would be of great use in many cases where it is easy to pass as a comma-seperated value than XML format string.

Lock your workstation on just click of a Mouse ...

Are you lazy like me ... then read on :)

If you think pressing Ctrl + Alt + Del key to lock your machine is really too much then I am sure you would love this post.

Step 1: Right click an empty spot on your desktop and choose New >> Shortcut.

Step 2: In the Create Shortcut dialog box, type the following in the 'Type the location' of the item text box: rundll32 user32.dll,LockWorkStation .

Step 3: Click Next and give a name of your choice for this short cut.

That's it. Now when ever you feel like locking your machine, just click on this shortcut. Cool isn't it?!?

Tuesday, May 30, 2006

Split function in SQL Server -- Method 1

Off late in many of the usergroups I see people enquiring about how to split data in SQL Server. I know couple of methods to achieve this. This post would explain the first method. I would write a seperate post on the other method of doing the same task.

Here we go ....



CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
(
Items NVARCHAR(4000)
)
AS
BEGIN
DECLARE @Index INT
DECLARE @Slice NVARCHAR(4000)
SELECT @Index = 1
IF @String Is NULL RETURN

WHILE @Index != 0
BEGIN
SELECT @Index = CHARINDEX(@Delimiter, @String)
IF @Index != 0
SELECT @Slice = LEFT(@String, @Index - 1)
ELSE
SELECT @Slice = @String

INSERT INTO @Results(Items) VALUES (@Slice)
SELECT @String = RIGHT(@String, LEN(@String) - @Index)
IF LEN(@String) = 0 BREAK
END
RETURN
END;
GO


Lets test it out:

SELECT [items] FROM [dbo].[Split] ('1,2,3,4,5,6,7,8,9,10', ',')


Sample table Structure:


CREATE TABLE testTable
(
Sno INT,
Fname VARCHAR(100),
Address VARCHAR(100)
)
GO


Make use of the SPLIT function to insert same Sno, Fname but different Address into this table.
INSERT INTO testTable ( Sno, Fname, Address )
SELECT 1, 'Vadivel', * FROM [dbo].[Split] ('xxxxx~yyyyyyy~zzzzzz','~')
GO

Lets see how over SPLIT function has performed:

SELECT Sno, Fname, Address FROM testTable
GO

Result:

1, Vadivel, xxxxx
1, Vadivel, yyyyyyy
1, Vadivel, zzzzzz
Hope this helps!

Saturday, May 27, 2006

Microsoft releases new Image format

The software maker detailed the new image format by name "Windows Media Photo" at the Windows Hardware Engineering Conference.

Windows Media Photo will be supported in Windows Vista and also be made available for Windows XP.

Friday, May 26, 2006

Error while trying to run project: Unable to start debugging on the web server. The project is not configured to be debugged.

I have planned to share my experience on the various .NET errors which I have faced till now and the solution for the same. This is the first post in this series.

Error: Error while trying to run project: Unable to start debugging on the web server. The project is not configured to be debugged.

Cause: Either debug is not enabled in your web.config (OR) For some reasons IIS is not considering your web application as a virtual directory. Instead it is considering it to be a normal folder.

Solution 1:

Step 1: Open the web.config
Step 2: If debug is set to "false" change it to "True".

For ex:
In my web.config I have a line like this <compilation defaultLanguage="c#" debug="false"/>
I would change it to <compilation defaultLanguage="c#" debug="true"/>

If at all this doesn't work. Try out the next solution.

Solution 2:

Step 1: Start >> Run >> inetmgr
Step 2: Navigate to the virtual directory of the project which is throwing this error.
Step 3: Right click on the virtual directory and choose "Properties"
Step 4: Next to the application name there would be a button named "Create". Click on it. [After that, the button should read as "Remove"]

If at also doesn't work, try the next solution.

Solution 3:

If you are running Visual Studio 2003 (or ASP.NET v1.1) you should setup IIS to use ASP.NET v1.1 and not ASP.NET v2.0

May be you have installed .NET framework 1.1 as well as 2.0 in your box and trying to run an application via VS.NET 2003. If so, then do the following:

Step 1: Start >> Run >> inetmgr
Step 2: Navigate to the virtual directory of the project which is throwing this error.
Step 3: Right click on the virtual directory and choose "Properties"
Step 4: Choose the tab ASP.NET
Step 5: The first dropdown would read "ASP.NET Version", choose 1.1.4322

That's it. Hope this helps somebody sometime somewhere in the world :)

Thursday, May 25, 2006

Is there any problem with Gmail?

For past 3 days I am having trouble accessing my gmail a/c. Actually it takes too much time to login and show my inbox. Many times, only blank screen is displayed and I had no other option than to hit my keyboards F5 key.

I am really surprised!! One of the advantages which people said when GMail came into picture was SPEED.

Get it right, I am able to access Yahoo or Hotmail pretty normally on the same box. So something somewhere is going wrong for GMail. May be they are not able to handle the real load!

Update on May 26, 2006 @ 7.30 PM:

I forgot to mention that "TOO MANY" junk/spam mails are delivered to my Inbox. I am wondering how it fooled the spam filter and managed to get into my inbox. Something is SURELY wrong with Gmail server / application. Hope Gmail team to look into this ASAP before realising that it has gone above their head!

Nice to know that my blog has been linked from AdventNet.com

I am glad to know that my blog has been featured in 3 places within Adventnet.com's site.

1. Database Migration Solution
2. Database Migration Review
3. SQL Converter

Tuesday, May 23, 2006

Intellisense in SQL Server for FREE!!

If you are developer working majorly on Microsoft IDE's then you would be aware of the intellisense feature which they provide.

As I am very much used to it when ever I work on SQL Query Analyser I miss that feature :) Yesterday night when i was browsing without any specific agenda in mind I stumbled on this.

I was really happy to know that Red-Gate is offering their product SQL Prompt obsolutely free (till Sep 2006).

Come-on guys make use of this wonderful opportunity at the earliest :) You can download the SQL Prompt here.

Monday, May 22, 2006

Yoga ...

My wife used to attend Yoga classes which is conducted within our colony by an external teacher. She used to say, its really good. I am more into cricket, chess, carrom, TT etc., than Yoga. Meaning, I am not at all against Yoga, I am saying, I am more interested in other activities than this. But still I attended Yoga classes for a month myself and found that its really easy and helpful.

Btw, why am I blogging about this all of a sudden? [ People who are reading my blogs regularly would be really surprised to see a non-tech post made by me :) ]

One of my close friend Mr. X is pretty well known to my family. Once when we were generally chit-chatting the topic turned to yoga. Mr. X told us that in christianity Yoga is considered to be illegal or something of that sort. I just took that as an information which I was not aware before. Period.

My wife is having a pain in her neck and her right hand for quite sometime. That's due to various OTHER reasons. Let me not get into it now.

Last thursday I took off and took my wife to a doctor and based on his advice took an MRI Scan Cervical spine. After seeing the Scan report the doctor suggested she needs to be admitted for 7 to 10 days :( After 10 days of treatment if the situation doesn't improve a surgery needs to be done :( I love my wife more than anything else in this world. I actually wanted to get a second opinion from couple of doctors (Dr. T.K. Shanmugha sundaram - Ortho specialist and Dr. Rout -- Neuro specialist from Ramachandra hospital) before making a final decision on it. Needless to say, I was under severe trauma.

On Friday, when I was informing my close friends regarding that I was surprised to hear from Mr. X that these pains came because of doing Yoga. I left that as it is without giving too much importance to his comments. But today afternoon he again touch based on the same subject. He asked me whether doing some posture in Yoga might have caused those pain for my wife. I really got pissed off.


See I like apple so I eat it. Its not acceptable if you want me to stop eating apples just because you don't like it. I really don't think it makes sense :)


I am a kind of person who doesn't involve in caste / religious talks at all. I believe that people should always keep their caste / religious feeling / beliefs within their house and should not carry it along with them everywhere. People still have superstitious beliefs like this (that too in this century).

To my knowledge, the average life span for a human being (in this generation) is going to be around 60 - 70 years. In this short span what are we going to achieve by splitting ourself based on Caste and Religion? For me, just like humans invented programming languages like C, C# they have invented Caste and Religions too. So lets not give too much of importance to any one specific religion or caste. Lets be united and grow together.


I remember reading something relating to "Christian yoga" but without knowing about a subject fully I don't want to talk about that.

I know Mr. X used to read my blog regularly. Just for him I made this post as I don't want to tell this straight on his face and lose his friendship :( Hope Mr. X would understand my point and take it in right spirit!

YubNub a (social) command line for the web

Check out this post from Jonathan. Its really interesting. Have a look @ http://www.yubnub.org/ and have fun.

Thursday, May 18, 2006

[Memo from Bill Gates] - Beyond Business Intelligence: Delivering a Comprehensive Approach to Enterprise Information Management

From: Bill Gates [mailto:billgates@chairman.microsoft.com]
Sent: Thursday, May 18, 2006 12:43 AM
To: xxxxxxxxxxxxxxx
Subject: Beyond Business Intelligence: Delivering a Comprehensive Approach to Enterprise Information Management


This week, more than 100 CEOs representing many of the world's leading companies are meeting in Redmond, Washington, to discuss technology trends that promise to reshape the corporate landscape. The occasion is the Microsoft CEO Summit, an annual event that we've been hosting since 1997.

In the decade since that first CEO Summit, technology has transformed the world of business in profound ways. Back then, e-mail was just emerging as a preferred medium for business communication. E-commerce was in its infancy. Most companies still relied on faxes and phone calls to conduct business.

Today, we communicate and collaborate instantly with colleagues, customers and partners around the world. Global supply chains speed the flow of products from factory floor to store shelf. Cell phones are ubiquitous. Mobile access to e-mail is rapidly becoming the norm.

The impact on the workforce is remarkable. Productivity is higher than it's ever been. Buyers can shop the entire world without leaving their desk. Sellers have access to markets that were once beyond reach. The amount of information collected about customers, competitors and markets is unprecedented.

But there are times when it feels like all of these changes have overwhelmed the tools we use to do our day-to-day jobs. I wanted to share my thoughts on this important issue with you and other business decision makers and IT professionals.

The problem, really, is twofold. The first is information overload. Faced with the endless deluge of data that is generated every second of every day, how can we hope to keep up? And in the struggle to keep up, how can we stay focused on the tasks that are most important and deliver the greatest value?

The other problem is something I call information underload. We're flooded with information, but that doesn't mean we have tools that let us use the information effectively.

Companies pay a high price for information overload and underload. Estimates are that information workers spend as much as 30 percent of their time searching for information, at a cost of $18,000 each year per employee in lost productivity. Meanwhile, the University of California, Berkeley predicts that the volume of digital data we store will nearly double in the next two years.

That makes solving information overload/underload a critical task. Fortunately, a new generation of technology innovations is opening the door to solutions that will make it dramatically easier to find relevant information quickly; to use that information to drive intelligent decision-making; and to instantly share the knowledge that results across the enterprise and beyond. Resolving the information overload and underload problem will take more than just better search tools. What's required is a comprehensive approach to enterprise information management that spans information creation, collection and use and helps ensure that organizations can unlock the full value of their investments in both information and people.

As these solutions enter the mainstream, we will expect dramatic improvements across the key drivers of business success. Software that streamlines how we find, use and share business information will enable us to strengthen relationships with customers, speed innovation, improve operations and create more flexible connections to partners and suppliers.

The End of Information Underload/Overload

To deliver on the promise of this new generation of solutions, Microsoft is focused on creating software that addresses specific businesses priorities:

Productivity: Information fatigue is one inevitable result of information overload. We are working to develop tools that help information workers prioritize their work and focus on the tasks that are truly important. At the same time, we are working to create unified communication solutions that provide a single entry point to all of the tools we use to communicate with coworkers and customers.

Collaboration: New meeting technologies will make distributed meetings simple and cost effective, and provide rich tools that enable team members to work together to create documents and plans. In addition, companies will be able to capture all of the interaction in meetings and preserve institutional knowledge that is often lost today.

Business intelligence: Powerful yet intuitive software that supports advanced visualization and modeling of information will be used every day by information workers to find meaningful patterns in the vast sea of data they collect. This software will also help employees use the insight they gain to trigger processes that enable organizations to respond quickly as business conditions change.

Workflow optimization: Smarter workflow software will eliminate friction points that hamper organizational agility. These tools will automate the movement of approvals, alerts and exceptions. They will also have the intelligence to recognize inefficiencies in existing processes and make improvements.

Microsoft is also devoting particular attention to the problem of enterprise information access. In a world where information can be stored on the desktop, the intranet or out on the Web, and where the right people may be located in an office halfway around the world, enabling seamless access to enterprise information is a complex problem.

An important starting point is to move beyond the traditional search tools that people use today to find information on the Web and elsewhere. Instead, software needs to be tuned to better match the way information is created and stored in the enterprise so that it is capable of searching all types of structured and unstructured business content, from emails to information stored in line of business applications to data stored in corporate databases. This software needs to be built on a search engine that utilizes algorithms and incorporates features that are designed specifically to deal with business content so that relevance and ranking results meet the needs of business users.

Enterprise-enabled search is just one aspect of the solution. One of the biggest barriers to information access in the enterprise is the fact that data is often stored in so many different repositories. This leads to painfully inefficient processes that force information workers to leave one application, logon to another, find a single piece of data and write it on a piece of paper, and then return to their original application, just to complete a simple task like sending an email to a customer. This is a significant drag on productivity. Microsoft's goal is to deliver enterprise information access solutions that present information workers with a single, unified way to get at the information they need no matter where it resides without leaving the application they are currently working in so they can make smart decisions and take action with greater speed.

In addition, finding ways to take full advantage of the knowledge that employees possess remains a challenge in all organizations. According to some estimates, 80 percent of the expertise within the average organizations is "tacit knowledge" that is undocumented and difficult to locate. Next-generation solutions will enable information workers to tap into social networks to find subject matter experts who can provide the support they need to accomplish specific tasks.

Looking Ahead to the Next 10 Years

To solve the problem of enterprise information access, we've made significant R&D investments. As a result, over the next 12 months we intend to roll out a wide range of new technologies that will transform the way people find, use and share information in the workplace. Products like Windows Vista and Microsoft Office SharePoint Server 2007 and Microsoft Office Outlook 2007 in the 2007 Office System will bridge the gap between information workers and the information they need to be more effective.

Another new technology aimed at streamlining information access that should be available in the near future is an enhanced search tool called Knowledge Network for Microsoft Office SharePoint Server 2007. This add-on will track expertise and relationships in an organization so information workers can quickly connect to people with the right skills and knowledge. We also plan to introduce a test version of Windows Live Search, a one-stop entry point for finding information on the desktop, the intranet and the Web.

In my first CEO Summit keynote speech in 1997, I looked ahead 10 years to a time when bandwidth would be vastly improved, the majority of adults would use e-mail and the Web would be integral to the way we plan trips, make purchases and coordinate with friends. Not all of my predictions have come true yet-I also said using speech to interface with computers would be the norm by now. But that is coming soon.

The theme of this year's CEO Summit is "The Next 10 Years." So where will we be 10 years from now? As I said back in 1997, there's a tendency to overestimate how much things will change in two years and underestimate how much change will occur over 10 years. But I think there are some things we can say with a reasonable degree of certainty.

During the next 10 years, networks will get faster, computer processing will continue to increase in accordance with Moore's Law, and data storage will continue to fall in price. Meanwhile, high-definition screens will be cheaper, lighter and more portable. Mobile phones will rival today's desktop PCs for power and storage. Most important, the software that ties it all together will become increasingly sophisticated in its ability to understand the way you work, and increasingly streamlined and intuitive in the ways you use it.

During the next 10 years, the idea of "search" will give way to a notion of seamless access to knowledge as people begin to utilize tools that let them interact with their computers using plain English-or plain Spanish, French, Chinese or Russian-to instantly link to the information or people they need. In this New World of Work, repetitive, uninteresting tasks like moving data from one system to another will be automated and employees will focus much more of their time and creative energy on work that generates real value and growth.

In 1997, the theme of CEO Summit was "Corporate Transformation for Friction Free Capitalism." Today, in a world where we have access to virtually unlimited information at our fingertips, global supply chains, international markets that operate 24 hours a day and communication tools that enable us to move data around the world instantly have brought us a lot closer to a world of friction free-capitalism than many people thought possible back then.

As we look ahead to the next 10 years and the promise of the New World of Work, I believe we are on the verge of an idea that is even more powerful: the age of friction-free innovation.

Bill Gates

You can read more about our vision for enterprise information access at http://www.microsoft.com/mscorp/execmail

Data-mining pioneer joins Microsoft


Not every big name in search is going to Google.

Rakesh Agrawal, who is credited with creating data mining, or the science of extracting trends from large and often disparate databases, has left IBM to become a Microsoft technical fellow in the company's Search Labs.

Source: http://news.com.com/2100-1022_3-6072321.html

Wednesday, May 17, 2006

Good reference for HTML, PHP, JS, CSS, XML, Java etc.,

I came to know about GotAPI from Matt's blog. GotAPI is really cool and it would come in handy if you need quick reference to the following topics:

1. HTML
2. CSS
3. Javascript
4. JAVA
5. Ruby
6. Perl
7. PostgreSQL
8. ActionScripts
9. Coldfusion
10. XML
11. XML DOM
12. C / C++ / Standard Template Library etc.,

Tuesday, May 16, 2006

[Product Review] SwisSQL SQLOne Console 3.0

Few days back Srikanth from SwisSQL got in touch with me and introduced me to their new product called "SwisSQL - SQLOne Console 3.0". I initially thought I would fiddle with it sometime in the weekend. But one of the meeting which was about to happen yesterday evening got cancelled and so I came early to home than usual. I just utilized that time to review this product.

About the product:

1) SQLOne Console is an automated SQL Migration tool offering SQL Conversion, Execution and Comparison across multiple databases.

2) SQLOne Console migrates SQLs, either available as stand alone scripts or as embedded SQL statements within Application code like VB, PB, VC++, Java etc.,.

3) It supports Migration across Oracle, SQL Server, DB2, MySQL, Sybase, PostgreSQL, Informix and ANSI-SQL database SQL dialects.

4) This software also has features to test the converted SQLs in target databases. It would be a very handy product to have in database migration projects as well DBAs working in multi-database environment.

Here we go ....

I downloaded and installed the 30-day trial pack of that product from here. The trial pack was not restricted in anyway as far as the functionalities are concerned. But the only hitch is we can run / test only 50 queries by using it.

When I clicked on the SwisSQL shortcut, which got created, on my desktop it first opened the command prompt. After couple of seconds it opened the GUI application.


Once I clicked on "OK" button the application got loaded without any hassles. The screen was divided horizontally into three parts as shown in the screenshot below.



This app consists of 3 frames and they are,

1) The first part of the screen is where you could type your code snippet that needs to be converted into other database dialect. Once you type a query and click on "Convert" from the toolbar (you could also press F5 key to run the queries) the converted query would be displayed in the middle frame.

2) The center frame in the above screenshot is where the converted code snippet would be displayed. Various DB, which is supported by this product, is displayed in a tabbed fashion in this frame. Interestingly one F5 is enough for the product to convert the query into all the 8 DB dialect at one short.

3) The bottom frame is where the executed result would be displayed. Obviously we need to set the connection strings (which could be easily done using settings menu >> Database Connection settings).

Few of the code snippets which I have tried myself are:

Sample 1 :: First I tested with NewID() function of SQL Server.

SQL Server -- Select newId()
Oracle - SELECT SYS_GUID() FROM SYS.DUAL
DB2 - SELECT GENERATE_UNIQUE() FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY
Informix - SELECT FIRST 1 SYS_GUID() FROM SYSTABLES
PostgreSQL - SELECT SYS_GUID()
MySQL - SELECT SYS_GUID()
ANSI SQL - SELECT SYS_GUID()
Sybase - SELECT NEWID()

Sample 2: Then I checked out how Identity() and Primary Key constraint performs in all the DBs.

SQL Server -
CREATE TABLE tblTest
(
Field1 int Identity NOT NULL ,
Field2 varchar (30) ,
Field3 int CONSTRAINT pk_parent PRIMARY KEY nonclustered (Field1)
)

Oracle -
CREATE SEQUENCE tblTest_Field1_SEQ
START WITH 1
INCREMENT BY 1
/

/* AdventNet SwisSQL Message : Query split into multiple Queries. */

CREATE TABLE tblTest
(
Field1 int NOT NULL ,
Field2 VARCHAR2 (30) ,
Field3 int null ,
CONSTRAINT pk_parent PRIMARY KEY (Field1)
)


DB2 -
CREATE TABLE tblTest
(
Field1 int GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1) NOT NULL ,
Field2 varchar (30) ,
Field3 int CONSTRAINT pk_parent PRIMARY KEY NOT NULL
)

Informix -
CREATE TABLE tblTest
(
Field1 int NOT NULL ,
Field2 varchar (30) ,
Field3 int PRIMARY KEY
)

PostgreSQL -
CREATE TABLE tblTest
(
Field1 INT8 NOT NULL ,
Field2 varchar (30) ,
Field3 INT8 CONSTRAINT pk_parent PRIMARY KEY null
)

MySQL ::
CREATE TABLE `tblTest`
(
`Field1` int NOT NULL ,
`Field2` varchar (30) ,
`Field3` int CONSTRAINT `pk_parent` PRIMARY KEY null
)

ANSI SQL ::
CREATE TABLE tblTest
(
Field1 int NOT NULL ,
Field2 varchar (30) ,
Field3 int CONSTRAINT pk_parent PRIMARY KEY null
)

Sybase ::
CREATE TABLE tblTest
(
Field1 NUMERIC Identity NOT NULL ,
Field2 varchar (30) ,
Field3 int ,
CONSTRAINT pk_parent PRIMARY KEY nonclustered (Field1)
)

Sample 3 : I tried to convert this SQL Server query Execute sp_MSForeachTable @command1 = "sp_help '?'"

It didn't work out. When I discussed regarding this with Srikanth I came to know that SwisSQL SQLOne Console converts SQLs only and does not handle T-SQL blocks.

To migrate Procedure syntaxes like T-SQL, PL/SQL etc they offer solution based on the source and destination database combination. For those who are interested in that check the below URLs:

http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html
http://www.swissql.com/products/sqlserver-to-db2/sql-server-to-db2.html

As of now, I found that the tool doesn't convert Temporary tables and queries like Identity(int, 1, 1). I actually tried

Select Identity(int, 1,1) RowNum, au_id, au_lname, au_fname, phone into #tmpTable from authors

and it failed to convert. That said, these aren't supported in the current version of the product. Hope their development team is working on it.

The site claims that they support SQL Server 2005 but I haven't tested it out. Would do it sometime later and update this post.

I feel this to be an useful tool for the DBAs / senior developers in particular who handle multiple databases parallely. They only know the pain of doing database migrations :) I am sure this tool will be handy and it would really help them do migrate most of the queries with ease.

Important URLs for your reference:

1. Download SwisSQL - SQLOne Console 3.0
2. Various Products of SwisSQL

2. Product Support

As of now the support given by the team is really great. I was able to get response to my queries within a hour of so. I am impressed.

Technorati tags: ,

Dynamically create zip files programmatically using .NET

One of my collegue was enquiring whether we could zip a folder of files programmatically using .NET. Though I told him its possible, I couldn't remember the URL where I have read about it in MSDN.

To be frank the first option which came to my mind was to use some third party components to do this task. But after little bit of googling I was able to find out the exact URL which explains the way to do it without using any third party components.

If at all you are also interested in that check out this MSDNMag article.

Extract:

Zip compression lets you save space and network bandwidth when storing files or sending them over the wire. In addition, you don't lose the directory structure of folders you Zip, which makes it a pretty useful compression scheme. The C# language doesn't have any classes that let you manipulate Zip files, but since .NET-targeted languages can share class implementations, and J# exposes classes in the java.util.zip namespace, you can get to those classes in your C# code.
This article explains how to use the Microsoft J# class libraries to create an application in C# that compresses and decompresses Zip files. It also shows other unique parts of the J# runtime you can use from any .NET-compliant language to save some coding.

Monday, May 15, 2006

SQL Server 2000 not compatible with Media Center!!

One of my trustworthy friend Maruthi recently came from US. He actually went on a 3 months B1 visa for a project in Verizon. When he returned back in April 2006 he bought me a new DELL Inspiron 1505 laptop. This is the first time I am working in a laptop and I really loved it.

The laptop is pre-loaded with Windows XP Media Center and has 1 GB RAM, 80 GB HDD and lots of other features in it. For the past 3 weeks or so I was just browsing and doing nothing else using that :)

Last weekend I got bored with browsing and thought let me install SQL Server in my box. Since I know that, one need Windows Server 2000 or above to install SQL Enterprise edition I opted for SQL Server 2k Developer edition.

But I was really surprised to see that the installation wasn't working for me. Meaning, I was able to install only the client tools!! Really strange.

Why? Because one can install SQL 2k Developer edition in Win XP professional itself.

Since Media center is the next verion of XP obviously one would expect backward compatibility isn't it? If rich user experience (GUI) is one eye for Microsoft, backward compatibility is the other eye for it. But why this isn't working?

More interestingly SQL Server 2005 (next version which Microsoft release after SQL Server 2000) can be installed in Windows XP Media Center if we have SP2 (Service pack 2) installed in that box. I really donno what's the justification Microsoft have to say for this.

Kindly update me, if at all I am really missing something!!

[Only after seeing a similar query being raised by a person in MS SQL Usergroup today I realised that its an issue. Till yesterday I was thinking that I have made some mistake while installing :)]

Terry Semel (Yahoo CEO) says he missed the opportunity to buy Google

In a talk with The New Yorker writer Ken Auletta as part of a Newhouse School lecture series, Semel was asked what his dumbest decision was after taking the job at the Net media company in 2001.

Shortly after joining Yahoo, Semel said, company founders Jerry Yang and David Filo suggested he look at buying up-and-comer Google, whose Stanford grad founders looked up Yahoo's inventors. So Semel said he had dinner with Larry Page and Sergey Brin, asking them what their business was with Yahoo paying only $7 million annually as its biggest licensor of Google search technology.

"They had no thought process on the subject," Semel said.

So Semel nevertheless asked to buy Google. They replied that they wanted $1 billion and didn't want to sell. Semel said he'd think about the price.

Another dinner and Semel agreed to the $1 billion. Larry and Sergey replied that they wanted $3 billion and didn't want to sell. "I couldn't and didn't buy this company and the rest is history," Semel said

Check the full Video here

Sunday, May 14, 2006

Testing Anconia RocketPost Blog Software

Today I downloaded Anconia RocketPost trial version and was testing it out. My initial impression is I am not at all satisfied with it.

I fiddled with the software for more than 30 mins and was trying to make a blog post from it. But the software crashed thrice. Its really strange.

1. First and Second attempt crashed when i tried to create more than one hyperlink in my post!!

2. In the third attempt I thought of keeping it pretty simple. So I just typed few lines of text alone (no html content in it) and clicked on "Post to web" button from the toolbar.

All the time it throwed the below error message only.

My buddies are blogging ...

Flash news: Check out http://vadivel.blogspot.com/ to know the latest tech news :)

Yesterday I messaged the above quote to few of my office buddies. Why? Just to inform them that I blog some tech stuff and those who are interested can drop by.
Also, summa oru suya vilambaram dhan :)


Only then i came to know from Srini that he has also started blogging recently. I was glad to know that. I immediately started going through his posts and there waited a pleasant surprise for me. I saw Vignesh and Prathiba name listed in his blogrolls.

Short note about them:

Srini -- I interviewed him a year back. At that time he was a fresher and I was impressed to know that he was having vague idea about VS 2005 and SQL Server 2005. Nice guy and the first impression I got about him is "he is willing to learn". Srini correct me if I am wrong :)

Vignesh -- I was one of the person to interview him. The day when this guy came for interview to DBS center in Nungambakkam .... I initially thought one another guy. But when he started answering we were really impressed. There was a point when myself, Raju (our then tech mgr) and another guy where throwing questions at him just to make him say "I am sorry, I don't know this". At last after a hour or so we succeeded in that mission :)

Prathiba -- If my memory is right I interviewed her also. I read few posts in her blog and they are really interesting. She has good writing skills. Keep it up prathiba.

I know the freshers who joined us last year are really sharp ppl. But I seriously didn't expect them to have writing skills as what they have showcased in their respective blogs. Great show guys. I have added you guys into my blogroll. Expecting lot (bit of technical blogging :) ) more from you all.

[Updated @ 12:46PM] -- It wouldn't be fair on my part if I didn't mention about Santhi. She is a pretty close friend of mine and one of the few level headed person i have met in life. I was really touched on seeing the "About me" section of her blog :)

One thing which I like as well as dislike in her is she never changes her mind after deciding something. For example, I have asked her many times in past 2+ yrs to "leave a space" after a period or a comma. She hasn't changed her habbit till now (you could check it for yourself in her About me section :)

Saturday, May 13, 2006

ICANN chokes off .xxx porn registry

Plans for an area of the internet dedicated to pornography were killed last night in a vote by overseeing organisation Internet Corporation for Assigned Names and Numbers (ICANN). Check the complete article here

I am personally disappointed on reading this. Till now all parents was complaining about their kids accidently entering into porn sites. I guess they would have been using some filtering software to block porn sites. Thats really cumbersome as there is no easy way to find out whether a site is porn related or not.

That said, if at all this voting was won, all porn sites would end with .XXX thats real simple for parents to block right? Then why the hell have they not approved this ... only god knows.

AJAX is the buzzword now ...

AJAX, a new name for old wine -- Asynchronous Javascript + XmlHttpRequest. I really don't think this to be a really new concept!! may we ppl didn't realise earlier that such a thing existed earlier :)

To my knowledge XMLHttpRequest was introduced within IE 5.0 when it was released with NT or Win 2K (now we are talking about IE7 with Windows Vista).

The companies which have released some sort of AJAX framework are:

1. Our big brother "Microsofts" framework for AJAX is called Atlas and you can read about it here.

2. Google's version is called "Google-ajaxslt". One can download their framework here.

3. Check out Project jMaki from SUN.

4. Yahoo's version is "Yahoo! User Interface Library"

All the above mentioned companies where competing directly/indirectly with each other for quite sometime now. But the surprise entry (Atleast for me) is Adobe.

Adobe has released a AJAX framework by name SPRY.

Offlate almost all major technology companies are into AJAX hmmm let the technology WAR on AJAX begin :)

Friday, May 12, 2006

Interview with Gary (the guy who hacked into NASAs computer)

In 2002, Gary McKinnon was arrested by the UK's national high-tech crime unit, after being accused of hacking into Nasa and the US military computer networks.

He says he spent two years looking for photographic evidence of alien spacecraft and advanced power technology.

America now wants to put him on trial, and if tried there he could face 60 years behind bars.

Banned from using the internet, Gary spoke to Click presenter Spencer Kelly to tell his side of the story, ahead of his extradition hearing on Wednesday, 10 May.

Click here to Read the full interview ...

Thursday, May 11, 2006

How to fetch files based on Version label in VSS?

My collegue Prem and sriram on the other day where asking about how labling works in VSS. I was explaining them the details which I know. Incidently, today I happened to read this article on Dave GoodAll and found it really useful. The point which I learned is we can do it from command line also.

Extract from that article:

In Visual SourceSafe Explorer, highlighting the 'Revision 2.1' item line and clicking on the 'Get' button will extract the same revision of the file as if you had highlighted version 7.
Similarly, the command lines:
ss get "$/Test/date.java" -V"Release 2.1"
ss get "$/Test/date.java" -V7
are exactly equivalent, and will extract the same revision of the file.

Check out http://www.ezds.com/html/ss_info_index.html if you interested in reading about various articles relating to Visual SourceSafe (VSS).

Search engine results are almost always wrong!

This is the mail which I sent to my team mates / group of friends in Verizon on 9th Feb 2006. I thought let me post it here as well.


Does search engines lie on the number of results shown for a search string?

I tried searching for the word "vadivel.blogspot" in Google. It said there are 178 results found. But actually they are able to display only 23 results. The funny part is ... at the end of the page there was this block of text.

"In order to show you the most relevant results, we have omitted some entries very similar to the 23 already displayed. If you like, you can repeat the search with the omitted results included."


So now I searched again including the duplicates as well .. still it is able to list only 168 results :)

After this, I searched the same search string in Yahoo it said 164 results are there. But on clicking 2nd page ... the numbers changed to 280!! very strange. Still they where listing 129 records only.

So to my knowledge the numbers which "Search engines" show is almost always wrong. Don't get misleaded by that number.

I tried the same search query on MSN and it returned only 23 results. No disclaimers found whatsoever. All 23 entries were unique and there were no duplicates. So to me MSN is the best search engine (atleast for this search string :)).

Hmm one another reason why we need to stick with Microsoft products.

Wednesday, May 10, 2006

How to find out the Service Pack of .NET Framework installed?

Few weeks back one of my team mate asked me this and I said I am not aware of it. Today morning one another guy asked me the same question. Only then i took this to be a serious query :) and tried to find out an solution for it. I am really surprised that there isn't any easy way of doing it.

The workaround which I found is described below:

Step 1: Open windows registry (Start >> Run >> Regedit)

Step 2: For .NET Framework 1.0, look for the below key
Key name: HKEY_LOCAL_MACHINE\Software\Microsoft\Active Setup\Installed Components\{78705f0d-e8db-4b2d-8193-982bdda15ecd}

Step 3: Within it you could find this, Value : Version

Step 4: Just double click on it. In my box, the data within it was "1,0,3705,2". It means .NET Framework Service Pack 2 has been installed in my machine.

Similarly for .NET Framework 1.1 the Key Name is HKEY_LOCAL_MACHINE\Software\Microsoft\Active Setup\Installed Components\{CB2FEDD-9D1F-43C1-90FC-4F52EAE172A1}

Command Line Calculator ...

I wasn't aware that DOS has an in-built calculator in it till yesterday morning. Ok based on my understaning I have written the following ... hope this info would be of some help to you!!

DOS Shell has a built in mini calculator to perform mathematical calculation.

How?

Syntax for SET command: Set /a Your_Expression_Comes_here

As you would be knowing, type set /? to display the complete help of that command. Btw, this supports "SHIFT" operators (both left <<>>).

Try it yourself:

Step1: Open the command prompt (Start >> Run >> cmd)
Step 2: Find below the screenshot which shows few examples I have tried myself.



FYI,
i) 10<<2 means 10 x ( 2 to the power of 2)
i.e., 10 x 4 = 40

ii) 40>>2 means, 40 / ( 2 to the power of 2)
i.e., 40 / 4 = 10

Hope this helps!

Friday, May 05, 2006

Web 2.0 Award ... PageFlakes beats Google!

Check out http://web2.0awards.org/?short to know what I am talking about :)

The number one start page seems to be PageFlakes, number two is Googles IG and the third is our Microsoft Windows Live.

Little bit of googling showed that PageFlakes have been develeped using ASP.NET 2.0 and Microsoft Atlas.

Extract from the blog which I saw in http://omar.mvps.org/

Here's what Pageflakes say:

Pageflakes is your personalized Internet. You can add what you like and remove what you don't like - and it's totally simple. What you see on the site right now is just a selection of a few standard modules ("flakes", as we call them) that allow you to read blogs, do web searches, create a to-do list, check your Gmail account and read the latest news. Normally you'd have to go to a different web page for each of those things. With Pageflakes, you have it all on one page! And it's all free.

Note: You can now create public sites and share your page with your friends! Setup a personal site very easily and collaboratively work together

Thursday, May 04, 2006

Why pay for MS hotfixes?

I happened to go through Chaz blog yesterday night. One of the post which made me think is "Why pay for MS hotfixes?".

Extract from chaz blog entry:

Okay, I don't want this to be an MS bashing session. But I ran into a problem where I believe a hotfix will resolve a problem I am having in a .Net 1.1 application. The problem is around a bug in certain kinds of serialization/deserialization in the .Net 1.1 framework SP1. The MS Knowledge Base article says there is a hotfix, but I have to go through support to obtain it. If MS knows there is a bug in the .Net framework and has a hotfix, then why do I have to pay $99 for e-mail support to obtain the fix?Is there a legitimate reason? Again I am not looking to bash Microsoft.

I feel he has a real valid point. May be I am missing something! So I am hoping to hear some (valid) response from Microsoft ppl on this.

Wednesday, May 03, 2006

Amazon is powered by MSN Live search ...

Check out this article on washitonpost ... Amazon has dropped Google and is now being powered by MSN Live search engine.

Extract from that article:

"Whenever the largest online retailer makes a decision, it's going to be a big deal," said Silicon Valley technology analyst Rob Enderle. "They're the bellwether for their segment. What they do, others will likely do or consider doing."

[Follow up] New Microsoft Browser Raises Google's Hackles

In continuation to my yesterdays post I read an interesting article Google cries foul, but for what?

Ed Bott in this article has explained with screenshots on how changing providers in IE7 is about the same as changing in Firefox with one exception - MSN Search is NOT an option in Firefox while Google IS an option in IE7!!!!!!!!!

Now, will Google put real pressure on Firefox to let Microsoft Search in their existing list? As I mentioned in the earlier post if you purchase a DELL machines Google search box comes in-built. So I don't think people would really take seriously as everyone by now would have known that "Google follow double standards" :)

Btw apple's new browser (called safari) has google search in-built in it. I have never used it. But I have read that there isn't any option to change it to any other search providers. If this is a fair game .. then whats the problem with Microsofts approach?

Tuesday, May 02, 2006

New Microsoft Browser Raises Google's Hackles

Check out http://tech.memeorandum.com/060501/p8#a060501p8

In this article I found this, "The new browser includes a search box in the upper-right corner that is typically set up to send users to Microsoft's MSN search service. Google contends that this puts Microsoft in a position to unfairly grab Web traffic and advertising dollars from its competitors."

I also found this,

"The focus of Google's concern is a slender box in the corner of the browser window that allows users to start a search directly instead of first going to the Web site of a search engine like Google, Yahoo or MSN. Typing a query and hitting "Enter" immediately brings up a page of results from a designated search engine."

hmm i am really pissed off on seeing Google's reaction. Why? Because just couple of weeks back I bought a new Dell Inspiron 1505 notebook. I was really shocked to see that Google search box was by DEFAULT added to the Internet Explorer.

Now if the same thing is done by Microsoft on its (IE 7) own Browser to set MSN as their default search engine (that too configurable) google is getting paranoid .. really funny :)