Tuesday, October 28, 2008

Problems with Editor of Blogger.com

If you have a little interest in blogging, then you must have heard name of blogger.com. It's the most popular blogging platform. It is owned by Google, leading company in Internet world. I have been using this since more than 2 years. In this post, I have tried to focus on a particular problem with blogger.com.

 

The WYSIWYG Editor provided by blogger.com is not easy to use. It has got many problems. Some are listed out below.

 

  1. The line spacing between is too much high. It seems that there is an empty line between two lines.
  2. Blogger.com usually changes the font of text after we publish the post. Usually it compresses the text. I usually post by email. If some words are to be edited, then while using its editor, the font gets changed after publishing.
  3. There is also problem in including pictures. Whenever a picture is attached, the picture comes at top of the editor not considering the position of cursor. If the post is too long and the picture needs to be at bottom, this is a huge problem as other text needs to be copied to the top of that picture. The problem is severe when there are multiple pictures.
  4. Sometimes if the post is too wide or contains multiple wide tables, the post when published displaces all side widgets to below, occupying their space.
  5. Also, there is not much facility

 

 

I don't know why Blogger.com has not addressed such critical issues till date. Even all other competitors like Microsoft, Yahoo, WordPress has very good WYSIWYG Editor providing ease in using, blogger.com seems not to be worried. Due to such problems, I always prefer to post articles through email. I often use software like Windows Live Writer to write and publish post.

 

 

 

Read more...

Temporary Tables in SQL Server

Temporary tables are tables used to hold data for temporary purpose. There are two types of temporary tables.

1. Local Temporary table

2. Global Temporary table

Local Temporary table is a table created with "#" preceding the name of table. It exists till the connection to database, which has created it, lasts. The table is accessible by that connection only. The table is created in temporary database i.e. tempdb. The name of database.tablename should not be more than 116 characters for such tables. Suppose if we create local temporary table with name #tt, then a table with name like "#tt_________________________________________________________________________________________________________________000000000038"

is created in tempdb database. The length of name is 128 characters.

Example : create table #tt ( a varchar(20))

Multiple local temporary tables can be created with same name from different connections. In such case, these tables are also created in tempdb database with name slightly varying last suffix number. A normal programmer will use local temp table more than 90% of times.

Global Temporary Table is a table created with "##" preceding the name of table. It also exists till the connection to database, which has created it, lasts. But the table is globally accessible by other connections also. But, when the connection creating it terminates, then other connections also cannot use it. These tables are also created in tempdb database of SQL Server. It is created with same name as provided by user.

Example : create table ##tt ( a varchar(20))

As the table has global scope, the table name should be unique. Often, the global temp table can be replaced by temporary nature of permanent table. I mean better create a permanent table (with no # and ## in prefix) with a column to identify accessing users like SPID (Stored Procedure ID) or hostname or username to track and separate users. This permanent table has to be wiped out with user data whenever he/she finishes using it. This will remove need to recreate the table every time. In this case, benefit of global temp table becomes that, it does not consume space in database unlike permanent table.


Read more...

How to use Search in DOS mode?

 

Suppose your Windows Search does not work due to some virus or malware, then how would you search for a file or folder? DOS Search is a very good utility provided by Microsoft. You will have to run the command prompt ( Start -> Run -> command)  and using the following command.

 

C:\> dir C: /s | find "tech"

 

The above command searches for all files and folders in C: drive which contain "tech" in their names. The output may be something like this.

 

10/27/2008 11:44 AM             321      suraj shrestha@techcreeze.blogspot[1].txt

09/18/2007  07:43 AM            1,296   tech2525.jpg

 

The first column is Date of Modification of file or folder, second column is size in bytes and last one is the name of file or folder.

 

The above command is an example only. You can use other switches with this command. Or also you can get help using dir /? Command.

For example the following command searches for files and folders with name "tech" but displays the last access date instead of date of modification.

 

C:\> dir /s /t:a | find "tech.

 

 

Read more...

Monday, October 27, 2008

Probable causes of high memory usage by SQL Server

Under normal conditions, 2 GB RAM memory of database server dedicated to hold SQL Server databases for a multi-user application is more than enough. But, some times the memory usage becomes so high that, application users will not be able to use the application, letting system to knees. Then, the database administrator will have to restart the SQL Server service to clear the memory usage requiring some down time. So, it's always better to prevent SQL Server from using so much high memory.

 

So, what may be the causes for high memory usage by SQL Server? I have listed out some of the most probable causes.

 

  1. Of course, too many users are using the application at the same time, this is likely and a general case.
  2. Some other external process or virus or malware may be causing the problem.
  3. A lot of uncommitted transactions in SQL may also cause this problem.
  4. Many open connections to SQL Database by application increase the use of memory in database server. For a web application, it's likely that users do not close the application by properly logging out. Due to this, the connection to database remains open till the garbage collector finds it and terminates the connection. If a user initiates a time consuming process in SQL from application and closes the web application, the process keeps running in back end and keeps using the memory.
  5. Excessive use of full outer joins in SQL procedures or query uses more memory while executing.
  6. Excessive use of temporary tables also uses more memory.
  7. Use of repetitive and many dead locks in SQL Server cause the increase in memory usage.

 

Usually, SQL server has not tendency to free memory space after it has finished using. This means, if the usage reaches 100 MB sometime due to some process, even after the process is completed, SQL Server will the free unneeded memory space.

 

The SQL Server memory usage can be controlled by SQL Server Enterprise Manager. First open Enterprise Manager; expand the Microsoft SQL Servers, SQL Server group and the server; right click the SQL Server instance and select properties. In the memory tab of properties window, select "Use a fixed memory size (MB)" and then slide the slider to limit the SQL Server memory usage. This is the memory usage for SQL Server instance only not for extended procedures, dlls and other external process. So, SQL Server may be using a little more that allocated over here sometimes.

 

 

 

Read more...

How to find the cause of deadlock in SQL Server?

If you are a database administrator or technical support person for a large application, you may have come across this problem. Sometimes users say that their application is hanging while doing some usual operations, like running report, fetching details, etc. The cause is due to dead lock in some table of SQL Server.

 

If a user at some end is generating Purchase Order and another user at other end is running a Purchase Order Report which fetches Purchase Order details, then this is a likely case of dead lock. These two processes most probably use the same tables. If a user is inserting in Purchase Order table, then he will lock the table stopping others to select data from there until the insertion completes. Usually the insertion takes in no time, so in small process, dead lock is rare case. But, in large applications there are processes which take hours to run and complete. So, if the process locks the tables and another user runs select on that table, then the later user is kept hold.

 

There may be some cases when a user process A (say) is waiting for another process B (say) to complete while B is waiting for A to complete. Then, this is a dead lock. None of the process completes and it makes system hang. This is also likely case when the application does not handle it properly. If A is locking some table1 and waiting to use table2, while B is locking table2 and waiting for table1 to release, it is a case of deadlock. You can find out who is dead locking the system by logging to ISQLW ( SQL Server Query Analyzer) and running sp_who and SP_WHO2 commands.

 

Example : sp_who2 'active'

 

This gives the spid, status, login, hostname, BlkBy (blocked by), DBname (Database Name),  etc. If BlkBy field is filled by some value, the value is the SPID of blocking process. Then find out row in output whose id is the blocking id. From the hostname and login you will be able to find out who is the blocking user. You can then call the user to ask what he is doing and tell him to run the process sometime after.

 

Output of sp_who2.

SPID

Status

Login

HostName

BlkBy

DBName

Command

51

sleeping                     

SURAJSHRESTHA\Suraj Shrestha

SURAJSHRESTHA

  . 

master

AWAITING COMMAND

53

sleeping                     

NT AUTHORITY\SYSTEM

SURAJSHRESTHA

  . 

msdb

AWAITING COMMAND

54

RUNNABLE                     

Tech

Techcreeze

  . 

TESTDB

UPDATE         

55

sleeping                     

sa

SURAJSHRESTHA

54 

TESTDB

AWAITING COMMAND

 

In above case, process 55 is blocked by process 54 run by login tech from computer with name Techcreeze.

 

Read more...

Saturday, October 25, 2008

SQL: Datatype, Smalldatetime does not store second.

Smalldatetime datatype is less precision version of datatime datatype. Datetime variable takes 4 bytes while smalldatetime takes 2 bytes of memory. If you do not require precision to second level then, it's ok to use smalldatetime. But problem comes when you use datetime and smalldatetime variables interchangeably.

When you convert a datetime variable to smalldatetime, values with 29.998 seconds or lower are rounded down to nearest minute whereas values with 29.999 seconds or higher are rounded up to nearest minute. Here are two examples.

When following query is run in ISQLW (SQL Query Analyzer),

Select convert(smalldatetime,'2008-10-25 23:59:29.998')

The output is 2008-10-25 23:59:00.

Now, if we run the following query,

Select convert(smalldatetime,'2008-10-25 23:59:29.999')

The output is 2008-10-26 00:00:00.

The difference is obvious.

If we take only year, month and day from the variables and compare with operators like >, <, = then, the difference becomes 1 day.

This type of problem may not arise frequently.

But, if it comes, rounding of dates may not come to your mind at first.

After I notice, I have stopped using smalldatetime for just saving 2 bytes of memory.


Read more...

Friday, October 24, 2008

SQL- Concatenate data in Table

 
If you want to merge strings in different rows a table separated by a character (say comma[,]),
then you can do so easily with a simple SQL Trick without requiring any loop.
 
You just have to declare a varchar variable long enough to hold all data.
and write a select statement for the table. The following example illustrates this more.
 
First create a test table with some test data.
 
create table #tbl_test ( address varchar(50))
insert into #tbl_test values ( 'Lubhu')
insert into #tbl_test values ( 'Lalitpur')
insert into #tbl_test values ( 'Kathmandu')

 
Now, declare the variable and write select statement as below.
 
declare @Result varchar(8000)
set  @Result = ''

select  @Result = @Result + case when len(@Result)>0 then ',' else '' end + address
from #tbl_test
 
select @Result

 
You will be surprised by how it works.
The output will be Lubhu,Lalitpur,Kathmandu.
 
Remeber that, the variable should be long enough to hold all data otherwise it will lose excessive data.
Varchar variable cannot hold more that 8000 characters in a single variable.
Also, the separtion character is not compulsory or can be any. No obligation on that.
 

Read more...

SQL: The table '#tbl_result' is ambiguous.

 
If you are SQL programmer, there may be cases where you want to join same table multiple times to pull out same data.
This is ok for select operation. But in case of update and delete operation, it may not be easy. The error
"The table 'tablename' is ambiguos' comes while performing such update operation.
The error can be overcomed by simple trick of sql. You can use a sub-query instead of table name.
 
Here is an example.
 
First a test table is created.
 
create table #tbl_result
(
 head varchar(20),
 item varchar(20),
 data numeric(18,3)
)

 
Some sample data is entered.
insert into #tbl_result values ( 'Volume', 'A', 10.2)
insert into #tbl_result values ( 'Volume', 'B', 11.2)
insert into #tbl_result values ( 'GTO', 'A', 1020)
insert into #tbl_result values ( 'GTO', 'B', 627.2)
insert into #tbl_result values ( 'Avg Price', 'A', 0)
insert into #tbl_result values ( 'Avg Price', 'B', 0)

 
--
Then following select statement works fine even though it contains same table multiple times

select *
from #tbl_result a
inner join #tbl_result b
 on a.item = b.item
 and a.head = 'Volume'
 and b.head = 'GTO'

 
But following update statement does not work and displays error message
-- Server: Msg 8154, Level 16, State 1, Line 1
-- The table '#tbl_result' is ambiguous.
 
update #tbl_result
set data = case when isnull(a.data,0) <> 0 then  b.data / a.data end
from #tbl_result a
inner join #tbl_result b
 on a.item  = b.item
 and b.head = 'GTO'
inner join #tbl_result c
 on a.item = c.item
 and c.head = 'Volume'
where a.head = 'Avg Price'

 
The update can be changed with a little trick to make it work. Here the table name is replaced with a sub query.

update #tbl_result
set data = case when isnull(c.data,0) <> 0 then  b.data / c.data end
from #tbl_result a
inner join ( select * from #tbl_result where head = 'GTO') b
 on a.item = b.item
inner join ( select * from #tbl_result where head = 'Volume') c
 on a.item = c.item
where a.head = 'Avg Price'

 
You can check out result by following select statement.
select *
from #tbl_result

 

Read more...

Thursday, October 23, 2008

How to change the columns of SQL Query output or table to rows?

If you are database programmer, you may have come across this issue. There may be cases in which user should be allowed to enter data column wise.

 Here is an example. Suppose, for a manufacturing company, a user should enter plan volume to be produced in month wise basis. The screen provided should be like this.

 

Head

Year

Shrawan

Bhadra

Aswin

Kartik

Mangsir

Poush

Magh

Falgun

Chiatra

Baiskh

Jestha

Asadh

Plan Volume

2065

2.3

33.2

12

2

4

5

2.1

3.2

2.0

1.2

5.6

2.1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In above figure, Shrawan to Asadh are Nepali Months. The application should provide the user to enter Plan Volume data from screen as shown above. So, the main table structure should be same as above. But for calculation purpose and manipulation, it is often required to retrieve same data as shown below.

 

Head

Year

Month

Amt

Plan Volume

2065

Shrawan

2.3

Plan Volume

2065

Bhadra

33.2

Plan Volume

2065

Aswin

12

Plan Volume

2065

Kartik

2

Plan Volume

2065

Mangsir

4

Plan Volume

2065

Poush

5

Plan Volume

2065

Magh

2.1

Plan Volume

2065

Falgun

3.2

Plan Volume

2065

Chaitra

2.0

Plan Volume

2065

Baisakh

1.2

Plan Volume

2065

Jestha

5.6

Plan Volume

2065

Asadh

2.1

 

The data are same as above.

This can be achieved by a simple trick of SQL. The second data structure can be achieved by creating a view. This is logical as the no. of columns is known previously.

Suppose, if the previous data table name is "Tbl_plan". The created view will be like this.

 

create view Tbl_plan_vw

as

 select    [head], [year], 'Shrawan' [Month], [Shrawan]

from     Tbl_plan

union all

select    [head], [year], 'Bhadra' [Month], [Bhadra]

from     Tbl_plan

union all

select    [head], [year], 'Aswin' [Month], [Aswin]

from     Tbl_plan

union all

select    [head], [year], 'Kartik' [Month], [Bhadra]

from     Tbl_plan

union all

select    [head], [year], 'Mangsir' [Month], [Mangsir]

from     Tbl_plan

union all

select    [head], [year], 'Poush' [Month], [Poush]

from     Tbl_plan

union all

select    [head], [year], 'Magh' [Month], [Magh]

from     Tbl_plan

union all

select    [head], [year], 'Falgun' [Month], [Magh]

from     Tbl_plan

union all

select    [head], [year], 'Chaitra' [Month], [Chaitra]

from     Tbl_plan

union all

select    [head], [year], 'Baisakh' [Month], [Baisakh]

from     Tbl_plan

union all

select    [head], [year], 'Jestha' [Month], [Jestha]

from     Tbl_plan

union all

select    [head], [year], 'Asadh' [Month], [Asadh]

from     Tbl_plan

 

 

The above technique is more appropriate when the no. of columns is less.

If the data in Tbl_plan is to be shown directly in report (Excel Report, Crystal Report) then, pivoting can be directly done in the report from Tbl_plan, without needing to create additional view.

 

Read more...

What if your Windows Explorer Crashes?

Windows Explorer is one of the major program that has to keep running always for us to use computer. But it may some times crash due to some virus, anti-virus or other threats. Then, we generally have to restart the machine. If it did not work, the other options remained are to boot in safe mode, or to start in "Last known good configuration". But these options may not work always.

Some days before, one of my friends have problem in computer. He installed a fresh copy of Kaspersky 7 Anti-Virus. During installed, it was asked to restart machine and he did the same. Then after, the problem cropped. It gave error message like "Windows Explorer was crashed." It's the antivirus which is the convict here. It is likely the explorer in machine was infected earlier and the antivirus while trying to recover it crashes it. It is also supported by the fact that Kaspersky scanning runs when we logon to user profile from logon screen. The error came because Windows Explorer was not able to read some specific address in memory.

I tried to boot in safe mode and "Last known good configuration" but both did not work. I then removed Kaspersky from start up and also stopped the service from running at start up. The main thing here to be kept in mind is that Windows Explorer is just a program that helps to browse the machine; it is not Operating system or Windows. First, I pressed Ctrl + Alt + Del to bring Task Manager. Then, I selected New Task (Run..) from File menu. There I typed "msconfig" and pressed Enter. I then removed Kaspersky from Startup tab and also from Services tab. Then, I clicked apply and restarted the machine. Unfortunately, it did not work. Then, I have another idea to completely remove the antivirus. I typed appwiz.cpl, shortcut for "Add or Remove Programs" in Run mode and uninstalled the antivirus completed and restarted the machine. It worked. The machine successfully started like in normal condition. "Add or Remove Programs" can also be launched from "msconfig" (System Configuration Utility) -> Tools ->Programs -> Launch.

I have heard such issues with Kaspersky. Kaspersky should look forward to addressing this issue immediately. For a not much technical person, the only last option of reinstalling the operating system is remained.

Read more...

Warning: Null value is eliminated by an aggregate or other SET operation.


If you are a programmer or database administrator, you must have come across this problem. But, this does not make a lot of difference usually. But we should be always aware of such warnings too.

This problem usually comes while using multiple joins and aggregate functions like sum(), avg(), etc in SQL Server. The main cause is due to the fact the while aggregating some data may have null. And we know that when we add, subtract, multiply or divide with null, it will return null. So, the warning alerts that the result may not be correct or aggregate function is returning null due to null value being manipulated with other numerical value.

Once in SQL, I encountered with same problem while using
Isnull(sum(e.tcd_amt),0)
in a query statement.

The reason is that e.tcd_amt is sometimes null so problem in aggregating.
So, I replaced the code with following.
sum(isnull(e.tcd_amt,0))

The code worked fine without warning as it changes the null value to 0 before summing up, so not a problem for other normal numerical values.

Read more...