Skip to main content

Posts

Showing posts from 2004

[Yukon] About large value data types

The maximum capacity for Varchar / Varbinary in SQL Server 7 and 2000 are 8,000 bytes. Similarly for nvarchar it is 4,000 bytes. For any content which is more than 8000 bytes we would go for "Text, NText, or Image" data types. In SQL Server 2005 it has changed greatly with the introduction of the "Max" specifier. The Max specifier allows storage of up to 2^31 bytes of data. For Unicode content it allows storage of up to 2^30 bytes. When you use the Varchar(Max) or NVarchar(Max) data type, the data is stored as character strings, whereas for Varbinary(Max) it is stored as bytes. Basic example showing the usage of this new specifier. Create table PatientDetails ( PatientNumber int Identity, FirstName varchar(max), LastName varchar(max), Memo varchar(max) ) In earlier version of SQL Server we cannot use Text, ntext or image data types as variables / parameters in stored procedure or user defined funtions. But that is perfectly valid in Yukon. Passing character data as

Creating reports using Pivot operator

In this article let me try and compare the way to create cross tab reports in SQL Server 2000 and SQL Server 2005. Cross Tab Report: Representing columns as Rows and Rows as Columns is known as cross tab report or PivotTable. Sample Table Structure and Data Create table TestPivot ( YearOfSales Int, SalesQuarter Int, Amount money ) go Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 1, 100) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 2, 200) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 3, 300) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 4, 400) go Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 1, 500) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 2, 600) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 3, 700) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 4, 800) go Insert in

Database Compatibility ...

Database Compatibility This article would explain a practical way of using the system stored procedure "sp_dbcmptlevel". Sp_dbcmptlevel :: Set the database compatibility level. -------------------------------------------------------------------------------- By default, for SQL Server 6.5 the comatibility level is 65, for SQL Server 7.0 the comatibility level is 70, for SQL Server 2000 the comatibility level is 80 -------------------------------------------------------------------------------- One can check their database compatibility level by executing the sp_dbcmptlevel system stored procedure. Let me explain the way I made use of this system stored procedure in my previous company. We were having SQL Server 2k in our development environment but for a project our requirement were to use only SQL Server 6.5 (hmm quite old isn't it? when did you last heard about SQL Server 6.5 :) ). Its for sure that we can't purchase 6.5 version for this project alone. At that time

Row_Number function in Sql Server 2005

In this article let us look into the way of displaying sequential numbers in Yukon (code name of Sql Server 2005). Method 1 (Sql Server 2000) Create table TestTable ( EmployeeNumber int, FirstName varchar(50), Salary money NULL ) Go Insert into TestTable Values (100,'Vadivel',10000) Insert into TestTable Values (200,'Vadi',20000) Insert into TestTable Values (300,'vel',30000) Go Select Identity(int, 1,1) as 'Serial number', * INTO #TempTable from TestTable Select * from #TempTabledrop table #TempTable Method 2 (Sql Server 2005) Row_Number() :: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Select EmployeeNumber, FirstName, Row_Number() Over (Order By EmployeeNumber) as 'Row Number' From TestTable

Creating a very simple WebPart using Whidbey

Web Parts are similar to that of an user controls. But what makes it different is "Customization". Yes webparts allow the users to customize the site by modifying (for ex: moving) controls around the page based on WebPartZones. "ASP.NET version 2.0 introduces new type of personalization called Page Personalization or Web Parts" Any ASP.NET server control can act as a Web Part but by creating a custom control derived from the WebPart class you gain access to advanced features. I tried out an very very simple web part sample and let me try and explain it here. I didn't even write a single line of code in code behind file for this sample. Mind you for more webparts we need to use code behind files :) Btw I used Visual Studio Beta for this testing purpose. Steps I followed 1. I expanded the "Tool box" and had a look at the "Webparts" section. As we have "Standard", "Html", "Webcontrols" etc., "Webparts" is

About TimeStamp datatype of SQL Server ...

SQL Server has a less known datatype called “ TimeStamp ”. But I wasn't able to find any article about it on the web. So I thought I would try a sample myself and write about it. TimeStamp is actually used for record versioning in a table. When you Insert (or) Update a record in a table with a TimeStamp field, that field gets updated automatically. Lets create a sample table to understand the usage of TimeStamp field. Create table TimeStampExample ( RunningNumber int identity, LastName varchar(30), tStamp timestamp ) The above table “TimeStampExample” has been created with a TimeStamp field (tStamp). By the way its not mandatory to provide a field name for timestamp columns. The below table structure is perfectly valid only. Create table TimeStampExample ( RunningNumber int identity, LastName varchar(30), timestamp -- note we haven't mentioned a field name here ) When a record is inserted the value of tStamp gets automatically set by SQL Server. Lets see that in action. Insert

About TSEqual function (SQL 2K)

One of the common problem the database developers face in their day to day life is record concurrency issues. Lets try to address that with the help of timestamp data type. Lets assume that Sarah and Ram are reading a same record. First Ram updates that record with some new data. Later if Sarah also updates the record (mind you she is viewing the old content only still) then it would overwrite the changes made by Ram. There are two ways of solving this issue they are: 1. Pessimistic Locking 2. Optimistic Locking Pessimistic Locking: First person who reads the record would put a lock on it so that nobody else can change it until he is done with it. Only when he releases the record the other user can make use of it. This method is not recommended because it might also takes hours or days together for the first person to release his lock due to various reasons. Optimistic Locking: The record would be locked only when a user wants to modify its content. SQL Server has a TSEqual (I presum

Text functions in SQL 2k

As much as possible it is advisable to keep large text in a document on the file system and store a link to that within the database. Why do I say this? Because for storing large chunk of data we need to depend on the TEXT, NTEXT and IMAGE data types. So What? There are 2 disadvantages in it. They are: 1. These data types does not support commonly used string functions such as Len, Left, Right etc., 2. It occupies more / large space in the DB which internally means there might be a performance issues if you store such data in the database. Inspite of all these if you still want to use TEXT data type due to your business requirment then these functions might interest you! 1. PatIndex() 2. TextPtr() 3. ReadText() 4. TextValid() PatIndex() This function is useful with TEXT, CHAR and VARCHAR data types. This seeks for the first occurrence of a pattern within a string. If the pattern is found, it returns the character number where the first occurrence of the pattern begins. For better under

Avoid using sp_rename ...

sp_rename is the system stored procedure used to rename user created objects like “Stored procedure“, “Trigger“, “Table“ etc., This SP works pretty fine as long as you don't use it to rename a “Stored proc“, “Trigger“ or a “View“. Let me try and explain this with an example. Normally once a SP, Trigger or a View is created an entry is made into sysobjects as well as syscomments tables. For better understanding follow the following steps (examples uses Pubs database). Step 1: /* Description: Sample procedure to demonstrate sp_rename issue Author: M. Vadivel Date: August 12, 2004 */ Create Procedure FetchAuthorsDetails As Select * from Authors Step 2: [self explanatory] /* Description: Query to see the stored proc details in sysobjects Author: M. Vadivel Date: August 12, 2004 */ Select [name] from sysobjects where name like 'FetchAuthors%' Step 3: [self explanatory] /* Description: Query to see the stored proc details in syscomments Author: M. Vadivel Date: August 12, 2004 *

Delete Vs Truncate Statement

• Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. • Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Truncate table can be rolled back if it happens within a Transaction . • Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete. • Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is. • In truncate table the counter used by an identity column for new rows is reset to the seed for the column. • If you want to retain the identity counter, use delete statement instead. • If you want to remove table definition and its data, use the drop tabl

Comparing tables ...

There are times when we would like to check whether the content in two tables are same or not. As of now there isn’t any built-in function in SQL Server to do the same (who knows they might come up with something in Yukon!). At present we need to manually compare the contents of tables to find out whether they are matching or not. Won’t it be nice to have a stored procedure which would do the job for us? Read on … Setting the environment First let us create a test table and populate it with some test data. Create table Student ( [name] varchar(50), [age] int ) Insert into Student ([name],age) Values ('Vadivel',27) Insert into Student ([name],age) Values ('Ash',30) Let us now create a copy of this table with a new name: Select * into StudentCopy from Student Now both the table ‘Student’ and ‘StudentCopy’ has the same structure and values. Solution!! The stored procedure helps in comparing two tables: Create Procedure usp_CompareTable ( @FirstTableName varchar(128), @Se

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

UDF's in Constraints ...

In this post I have explained the way to use UDF (User Defined Functions) in constraints. For the purpose of discussion I have provided the structure of 2 (self explanatory) tables " MasterTable " and " ChildTable ". Sample data for MasterTable have also been provided below. --Table structure of MasterTable: Create Table MasterTable ( ItemID int Identity Primary Key, ItemName Varchar(50), Status bit ) --Sample data for the table MasterTable: Insert Into MasterTable (ItemName, Status) Values ('Rice',1) Insert Into MasterTable (ItemName, Status) Values ('Wheat',1) Insert Into MasterTable (ItemName, Status) Values ('Corn flakes',0) --Table structure of ChildTable: Create Table ChildTable ( ItemID int Foreign Key References MasterTable(ItemID), Quantity int ) Go /* Let us now create an user defined function to check whether the Item exist and its status is 1. According to this sample Status 1 means the record is enabled if it is 0 it is disabled.

Got a GMail account today ...

Yesterday I created a GMail a/c (vmvadivel@gmail.com) for myself. Thought would inform you all about that. If you also have an active blogger a/c (www.blogger.com) you could see the invite for creating a Gmail account immediately after logging into the site (blogger.com). If at all you don't have an blogger account check the heading "Interested in an account" here . I guess that would help you to get a GMail id at some later date!! I was actually wondering if Google gives 1 GB of space to each user how big their servers needs to be?!??! Won't they run into storage problem within few months after they release it for public?? At that time I got to see the post from my good old friend Pandurang Nayak which had some interesting facts about GMail !!

Won an article contest !!

One of my article has won last months (Feb 2004) article contest at developershed.com. As of now my name and the article title is displayed in the site. They are yet to point the title to my article. Hopefully they would do at least before end of March :) For further details check out http://www.developershed.com/contest.php

Using accesskeys for previous/next

I happened to read http://linuxtoday.com/news_story.php3?ltsn=2004-02-24-023-26-OP-CY-DV-0100 wherein it was said that accesskeys for previous or next is as follows: 1. Mozilla/Netscape: Ctrl+Alt+P or Ctrl+Alt+N 2. IE: Alt+P or Alt+N, then press RETURN But I always prefer using, ALT + right arrow for moving to the next screen and ALT + left arrow for moving to the previous screen in IE

Closing the parent window ...

Many times I have been asked how to close a parent window without getting that ugly warning dialogbox. You know what the solution is pretty straight forward :) Script of the Parent window: <html>  <head>   <Script language=""javaScript"">    window.opener=self;    window.close();    window.open('test.htm','Test','fullscreen');   </script>  </head>  <body>  </body> </html> Here 'Test' is a name of the window. You can give any name of your choice there. 'test.htm' is the name of the file which would be opened in fullscreen. This way we could avoid that ugly warning dialogbox.

Credit ...

Sometime back I happened to check the George Hernandez's site http://www.georgehernandez.com/. In one of his page I found an error in his code snippet. I sent him a note on the same with a code snippet which could be used instead. George has reviewed it and has responed back: Vadivel: Hey thanks a lot for the correction. I updated my page with your suggestion and credited you for it. http://www.georgehernandez.com/xDatabases/SQL/Programming/HandyCode.htm#Get%20info%20about%20columns%20in%20all%20databases%20in%20a%20server -George

Microsoft Consumer Virus Alert:

Why We Are Issuing This Alert W32/Mydoom@MM spreads through e-mail. This worm can disguise the sender's address, a tactic known as spoofing, and may generate e-mails that appear to have been sent by Microsoft. Many of the addresses Mydoom uses are valid addresses that are being spoofed for malicious purposes. Mydoom Virus Alert: What to Do Treat all e-mail attachments with caution, particularly .zip files in the case of this virus, even if they appear to be from a trusted source. Learn what to do about virus infections. http://www.microsoft.com/security/antivirus/mydoom.asp Complete Information: http://www.microsoft.com/security/antivirus/mydoom.asp

New Virus ...

This is an Informational update about the mass-mailing computer virus, W32.novarg.A@mm : The virus can infect several files on your computer and spread itself by sending an email to all addresses in your address book. E-mails with the following subject line and characteristics can contain the virus: Subject: Random - "Test" or "Hi" or "Hello" Message: Attachment: Files with the extensions of .exe, .pif, .scr, and .zip. If you receive an e-mail with one of the subject lines and attachments listed above YOU SHOULD DELETE the e-mail immediately.

Good link ...

The easiest way to see all classes inside a namespace is to check this Class Library Comparison Tool . The interesting part is, this page contains a "Display options" go and explore that for yourself :)

Security Bulletins released last week ..

Important information : On Jan 13th, Microsoft have released 3 security bulletins. Of these one that deals with ISA is a serious one (critical). If you are running ISA as a firewall in your organizations, you should consider patching it. Here are all of them for your reference. a.) Vulnerability in Microsoft Internet Security and Acceleration Server 2000 H.323 Filter Could Allow Remote Code Execution (816458): http://www.microsoft.com/technet/treeview/?url=/technet/security/bulletin/MS04-001.asp b.) Vulnerability in Exchange Server 2003 Could Lead to Privilege Escalation (832759): http://www.microsoft.com/technet/treeview/?url=/technet/security/bulletin/MS04-002.asp c.) Buffer Overrun in MDAC Function Could Allow Code Execution (832483): http://www.microsoft.com/technet/treeview/?url=/technet/security/bulletin/MS04-003.asp

Do you know this?

We can get complete information about each table in a database using the following syntax: Execute sp_help <<tablename>> But if we have hundreds of tables in a database then the above method would surely be a tedious way to do. So to over come that we could use the following query: Execute sp_MSForeachTable @command1 = "sp_help '?'" As sp_help lists information about a single table this sp_MSForeachTable displays all information about all tables within the existing database. Neat isn’t it?
Dear Reader, Bitwise, the annual online programming contest organized by the Computer Science & Engg. Department Society, Indian Institute of Technology Kharagpur, India, is being conducted this year on Sunday, the 8th of February. Bitwise is an algorithm intensive programming contest which aims to provide programmers across the globe, a platform to compete, testing their ability to develop efficient algorithms to problems within a given time constraint. It is a non-profit competition organized by 4th year undergraduate students of CSE, IIT Kharagpur. Over the last three years, the contest has become extremely popular around the world. In Bitwise 2K3, over 900 teams participated, with a team from Singapore bagging the first prize. The registration is free and open to all. Contestants solve a set of problems posted on a site using C or C++. The solutions are evaluated, not only on the basis of correctness, but also on execution time and space complexity. The top 50 conte

How to detect a 2-way mirror?

Not to scare you, but to make sure that you are aware. Many of the hotels and textile showrooms cheat the costumers & watch them privately :( HOW TO DETECT A 2-WAY MIRROR: When we visit toilets, bathrooms, hotel rooms, changing rooms, etc., how many of you know for sure that the seemingly ordinary mirror hanging on the wall is a real mirror, or actually a 2-way mirror i.e., they can see you, but you can't see them). There have been many cases of people installing 2-way mirrors in female changing rooms or bathroom or bedrooms. It is very difficult to positively identify the surface by just looking at it. So, how do we determine with any amount of certainty what type of mirror we are looking at? CONDUCT THIS SIMPLE TEST: Place the tip of your fingernail against the reflective surface and if there is a GAP between your fingernail and the image of the nail, then it is a GENUINE mirror. However, if your fingernail DIRECTLY TOUCHES the image of your nail, then BE

Wanna move from PHP/JSP to ASP.NET?

As you know Microsoft has been developing a new tool to help developers move to ASP.NET called the ASP to ASP.NET Migration Assistant. You probably played around with it earlier on. On 9th Jan 2004 they have announced the Beta release of this tool. The ASP to ASP.NET Migration Assistant is designed to help you convert ASP pages and applications to ASP.NET. It does not make the conversion process completely automatic, but it will speed up your project by automating some of the steps required for migration. Here’s how to try the ASP to ASP.NET Migration Assistant Beta: 1) Visit www.asp.net/migrationassistants to learn more and download the tool. 2) Visit www.asp.net/migrationassistants/forums to post your questions and feedback. BTW – there is a PHP to ASP.NET and JSP to ASP.NET migration wizard too. Source : Deepak Gulati ...

Tutorial and Presentation Creation Software...

Wink is a Tutorial and Presentation creation software, primarily aimed at creating tutorials on how to use software (like a tutor for MS-Word/Excel etc). Using Wink you can capture screenshots of your software, use images that you already have, type-in explanations for each step, create a navigation sequence complete with buttons, delays, titles etc and create a highly effective tutorial for your users. Using Wink, you can create tutorials, by capturing screenshots, mouse movements and specifying your own explanations with them. And all this in a standard Windows-based UI with drag-and-drop editing makes it very easy to create high quality tutorials/documentation. It is estimated that Macromedia Flash Player is installed in more than 90% of the PCs. Using Wink you can now create high quality tutorials/documentation which will be viewable across the web in all these users' desktops. Similar applications sell for hundreds of dollars, while Wink is free with unrivaled featur

(Re)Initializing an identity column to 0

Reinitializing an identity column is of two folds. If there is no foregin key on the table where we want to reset the identity to 0 then the following process would work. create table testTable1 (   sno int identity,   lastname varchar(25) ) insert into testTable1 values('lastname1') truncate table testTable1 -- This statement alone would do the trick for us :) If there is a foreign key relationship then we can't use the above statement instead follow the steps explained below: create table testTable1 -- example table 1 (  sno int identity primary key,  lastname varchar(25) ) create table testTable2 -- example table 2 (   sno int references testTable1(sno),   email varchar(50) ) insert into testTable1 values('lastname1') insert into testTable2 values(1,'vmvadivel@yahoo.com') delete testTable2 -- remove all the records from the child table first delete testTable1-- remove all the records from the parent table Afte

Find the product of a field ...

There isn't a prebuild function for finding the product of a field (or) set of values in SQL Server. But there is a workaround by using a combination of SUM(), POWER() and LOG10() functions. To read my complete TIP on this visit techtarget . Needless to say, if you like this tip do rate it so that I could be in contention for winning the best tip of the month award :)

Protection bypass vulnerability in Ms-Word ...

Microsoft Word 2003 and 2002 contains a protection-bypass vulnerability. By performing a simple process outlined below, a user can unprotect a protected document without the use of a password cracker or other special tools. :( This bug was discovered by Thorsten Delbrouck . To see the bug for yourself follow the below demonstartion!! 1.) Open a protected document in Word. 2.) Choose the Save As Web Page (*.htm; *.html) option and close Word. 3.) Open the HTML document in any text editor. 4.) Search the <w:UnprotectPassword> tag for a line that looks like: <w:UnprotectPassword>ABCDEF01</w:UnprotectPassword>. Gather the password. 5.) Open the original .doc document with any hex editor. 6.) Search for hex values of the password (reverse order). 7.) Overwrite all four double-bytes with 0x00. Save, and close. 8.) Open the document in Word. Select Tools, Unprotect Document. Password is blank. Source : winnetmag.com

Want to know the Processor ID of a machine?

Use the below code snippet in your page to display the Processor ID of the machine. Don't forget to add "using System.Management;" at the top. ManagementClass managementClass = new ManagementClass("Win32_Processor"); ManagementObjectCollection managementObj = managementClass.GetInstances(); foreach(ManagementObject mo in managementObj) {  strResult ="Processor " + mo["ProcessorId"].ToString(); }

Surviving a heart attack

Don’t know about the authenticity of this information, but better to leave no stone unturned!! Let's say it's 6.15p m and you're driving home (alone of course), after an unusually hard day on the job. You're really tired, upset and frustrated. Suddenly you start experiencing severe pain in your chest that starts to radiate out into your arm and up into your jaw. You are only about five miles from the hospital nearest your home. Unfortunately you don't know if you’ll be able to make it that far. You have been trained in CPR, but the guy that taught the course did not tell you how to perform it on yourself. HOW TO SURVIVE A HEART ATTACK WHEN ALONE Since many people are alone when they suffer a heart attack, without help, the person whose heart is beating improperly and who begins to feel faint, has only about 10 seconds left before losing consciousness. However, these victims can help themselves by coughing repeatedly and very vigorously. A deep breath sh

New Virus ...

Two new viruses have been detected. One which is not so destructive is Jitux.A virus and the other one which is more troublesome is PE_QUIS.A worm. Check out the full artilce here .

GUID Vs. IDENTITY

For inserting a unique value for each record in a table, we can either create a GUID (Globally Unique IDentifier) with the help of the function NEWID() (or) create an IDENTITY column which would take care of inserting the unique value itself. The major advantage of using GUIDs is that they are perfectly unique :) We can blindly use this if we are planning to consolidate records from different SQL Servers into a single table. The disadvantage is that the value of GUID are quite BIG. Hope it won't be too much if I say that this is "one" of the biggest datatypes in SQL Server. Due to that if we create an Indexes on a GUID field it would have a considerable performance hit . Other disadvantage which I see is it is not that easy to understand / remember a GUID value. On the flip side, we can use IDENTITY column which would most probably be an Integer field. Due to the less space it occupy it has an upper hand when we compare this with GUID with respect to performance.

Fetching IDENTITY value ...

The question is "How to fetch the IDENTITY value of the record which we INSERTED now?" . Till the days of SQL Server 7.0 we used to rely on @@IDENTITY function. But in my experience that function isn't that reliable. i.e., @@IDENTITY isn't dependent on the current scope. Even if we have inserted some records in a different table it would fetch us that identity value ;) I am sure we wouldn't be interested in that. From SQL 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. So its better to use SCOPE_IDENTITY in our select statement to retrieve the identity value for the record which we inserted now. Example : SELECT SCOPE_IDENTITY()

Passing data between pages ...

Some of the common methods of passing data between pages (web forms) are: 1. Query String 2. Session Variables 3. Server.Transfer Querystring and Session variables has its own limitations. ( For example, you can't pass objects or sensitive data using Query string method. More over there are limitations on number of characters which can be passed on a URL etc.,). So Server.Transfer is the better method for passing data across pages. Note: If you use Server.Transfer inside a Try..Catch Block it would throw an ThreadAbortException. Its not a bug that's how they have designed it to clean up the Stack :) In order to overcome that either opt for Server.Execute or Response.Redirect('page.aspx', false ).