Create SQL Job to shrink database

If you are a newbie in SQL database, this post may be helpful for you to create SQL job in SQL Server 2000. SQL Jobs are similar to Windows Scheduled job. It also can be scheduled to run hourly, daily, weekly or monthly as per required, provided that SQL Server agent is on and no other external things stops the execution.

 

1. Open the SQL Server Enterprise Manager.
2. Go to Microsoft SQL Servers -> SQL Server Group (or other group) -> Open the node of required SQL Server running -> Open Management folder -> Open SQL Server Agent -> Jobs.
3. In the Right Pane, right click and select New Job.
4. In General tab, give an appropriate name (say "Shrink Database – testdb") for new job and select owner.
5. In Steps tab, click New to open a pop-up window.
6. Write an appropriate name for step (say "Step 1").
7. Type the required query to shrink database in Command multiline box.

             DBCC SHRINKDATABASE (N'testdb', 10)

             It shrinks database named "testdb". But, it keeps 10% free space in the database.

             It gives the freed space to operating system for its use.

 

8. On Advanced tab, you can choose what to do on successful completion of job and on failure. For now, you can leave it to default.
9. Click Ok to save and return to main pop up window of "Shrink Database – testdb".
10. On Schedule tab of the pop up, click New Schedule.
11. Give the name of new schedule (say "Shrink Database – testdb").
12. Select Recurring and click Change.
13. Select frequency of job and time for the job and click Ok to return.
14. Click Ok to return to main pop up window "Shrink Database – testdb".
15. If you have configured SQL main operator, you can send mail on different stages of job execution through Notification tab. I will discuss more on it in my forth coming posts. For now, you can leave it.
16. Click Ok to save the job and you are done.
17. Check if the job is executed on time or not and executed properly or not.
18. You can always run any job instantly by right clicking the job and selecting Start Job.

 

Shrinking database through SQL Jobs can definitely remove burden on database administrators to manually shrink database each time. I hope this post is helpful for new comers to create basic jobs in SQL Server.

 

 

Read more...

Convert Amount into Words according to Indian or Nepali Numbering Style.

 
In the post http://techcreeze.blogspot.com/2008/11/convert-amount-into-words-according-to.html, I have talked about the converting amount (in number) to amount in words according to English Numbering Style.
 
I am including here, source code of functions required for converting Number into words according to Indian or Nepali Numbering style. In Indian or Nepali Numbering style, 100000 is 1 Lakh and 100 Lakhs or 10000000 is 1 Crore. This makes the numbering style different from English and International Numbering Style.
 
1. Function to Convert one Digit Number to words.
 
CREATE    Function dbo.fConvertDigit(@decNumber decimal)
returns varchar(6)
as
Begin
declare
@strWords varchar(6)
 Select @strWords = Case @decNumber
     When '1' then 'One'
     When '2' then 'Two'
     When '3' then 'Three'
     When '4' then 'Four'
     When '5' then 'Five'
     When '6' then 'Six'
     When '7' then 'Seven'
     When '8' then 'Eight'
     When '9' then 'Nine'
     Else ''
 end
return @strWords
end

 
2. Function to convert 2 digit number to words.
 

CREATE    Function dbo.fConvertTens(@decNumber varchar(2))
returns varchar(30)
as
Begin
declare @strWords varchar(30)
--Is value between 10 and 19?
If Left(@decNumber, 1) = 1
begin
 Select @strWords = Case @decNumber
     When '10' then 'Ten'
     When '11' then 'Eleven'
     When '12' then 'Twelve'
     When '13' then 'Thirteen'
     When '14' then 'Fourteen'
     When '15' then 'Fifteen'
     When '16' then 'Sixteen'
     When '17' then 'Seventeen'
     When '18' then 'Eighteen'
     When '19' then 'Nineteen'
 end
end
else  -- otherwise it's between 20 and 99.
begin
 Select @strWords = Case Left(@decNumber, 1)
     When '0' then ''  
     When '2' then 'Twenty '
     When '3' then 'Thirty '
     When '4' then 'Forty '
     When '5' then 'Fifty '
     When '6' then 'Sixty '
     When '7' then 'Seventy '
     When '8' then 'Eighty '
     When '9' then 'Ninety '
 end
 Select @strWords = @strWords + dbo.fConvertDigit(Right(@decNumber, 1))
end
 --Convert ones place digit.
 
return @strWords
end
 
 
3. Function to convert amt in numbers to words. (Built with the help of above 2 functions)
CREATE function dbo.fNumToWords (@decNumber decimal(12, 2))
returns varchar(300)
As
Begin
Declare
 @strNumber varchar(100),
 @strRupees varchar(200),
 @strPaise varchar(100),
 @strWords varchar(300),
 @intIndex integer,
 @intAndFlag integer

Select @strNumber = Cast(@decNumber as varchar(100))
Select @intIndex = CharIndex('.', @strNumber)
if(@decNumber>99999999.99)
BEGIN 
 RETURN ''
END
If @intIndex > 0
begin
 Select @strPaise = dbo.fConvertTens(Right(@strNumber, Len(@strNumber) - @intIndex))
 Select @strNumber = SubString(@strNumber, 1, Len(@strNumber) - 3)
 If Len(@strPaise) > 0 Select @strPaise = @strPaise + ' paise'
end
Select @strRupees = ''
Select @intIndex=len(@strNumber)
Select @intAndFlag=2
while(@intIndex>0)
begin
 if(@intIndex=8)
 begin
  Select @strRupees=@strRupees+dbo.fConvertDigit(left(@decNumber,1))+' Crore '
  Select @strNumber=substring(@strNumber,2,len(@strNumber))
  Select @intIndex=@intIndex-1
  
 end
 else if(@intIndex=7)
 begin
  if(substring(@strNumber,1,1)='0')
  begin
   if substring(@strNumber,2,1)<>'0'
   begin 
    if (@strRupees<>NULL and substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise=NULL)
    begin
     Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
    end
    
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end 
  else
  begin
   if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0'  and @intAndFlag=2 and @strPaise='')
   begin   
    Select @strRupees=@strRupees+' and ' + dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
    Select @intAndFlag=1
   end
   else
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
   end
   Select @strNumber=substring(@strNumber,3,len(@strNumber))
   Select @intIndex=@intIndex-2
  end
 end 
 else if(@intIndex=6)
  begin
   if(substring(@strNumber,2,1)<>'0' or substring(@strNumber,3,1)<>'0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and @intAndFlag=2 and @strPaise='')
   begin
    
    if len(@strRupees) <= 0
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=2
     end
     else
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=2
     end
    end
    else
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=1
     end 
    end
   end
   else
   begin
    if convert(int,substring(@strNumber,1,1)) = 1
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
    end
    else
    begin 
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
    end 
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
 else if(@intIndex=5)
  begin
   if(substring(@strNumber,1,1)='0')
   begin
    if substring(@strNumber,2,1)<>'0'
    begin
     if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
     begin
      Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
     end
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
    else
    begin
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
   end 
   else
   begin
    if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
    begin
     Select @strRupees=@strRupees+' and '+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
    end
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end 
 else if(@intIndex=4)
  begin
   if ( (substring(@strNumber,3,1)<>'0' or substring(@strNumber,4,1)<>'0') and substring(@strNumber,2,1)='0' and  @intAndFlag=2 and @strPaise='')
   begin
    Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
    Select @intAndFlag=1
   end
   else
   begin
   Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
 else if(@intIndex=3)
  begin
   if  substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Hundred '
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
    
    if( (substring(@strNumber,1,1)<>'0' or  substring(@strNumber,2,1)<>'0') and @intAndFlag=2 )
    begin
     Select @strRupees=@strRupees+' and '
     Select @intAndFlag=1
    end
    Select @intIndex=@intIndex-1
   end
   else
   begin
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
    Select @intIndex=@intIndex-1
   end
  end 
 else if(@intIndex=2)
  begin
   if substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @intIndex=@intIndex-1
   end
  end
 else if(@intIndex=1)
  begin
   if(@strNumber<>'0')
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(@strNumber)
   end
   Select @intIndex=@intIndex-1
    
  end
continue
end
if len(@strRupees)>0 Select @strRupees=@strRupees+ ' rupees '
IF(len(@strPaise)<>0)
BEGIN
 if len(@strRupees)>0 Select @strRupees=@strRupees + ' and '
END
Select @strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
select @strWords = @strWords + ' only'
Return @strWords
End
 
 

Read more...

Compare records of Same SQL Table

If you have to compare a record of SQL table to another record (which could be any), you would probably think of a nested while loop. Nested while loop is ok, but it may costlier. Here, I am discussing a trick to avoid loop to compare results.

 

Consider a case of Bank Charge. It is an amount charged by bank for transferring amt to another bank. Here, let's say a manufacturing company has many customers. The customers pay some bank charge while transferring amt from his bank to company's bank. The company pays back some amount to customers against the bank charges. It has own standards to calculate the bank charges.

 

If you are not up to previous example, leave it. Just assume a table with following data.

-- Table Structure

create table tbl_bank_charge

(

            tid                    int identity(1,1),

            customer          varchar(3),

            bank                 varchar(20),

            from_amt          numeric(18,3),

            to_amt              numeric(18,3),

            bc_amt             numeric(18,3)

)

 

-- Table Data

Tid	Customer	Bank	From_Amt	To_Amt	BC_Amt
8	ABC	Nabil	0	50,000	100
9	ABC	Nabil	50,001	100,000	200
10	ABC	RBB	50,000	100,000	333
11	ABC	RBB	200,000	300,000	600
12	SBO	RBB	0	10,000	50
13	ABC	Nabil	1,000	50,000	120
14	SBO	RBB	10,001	90,000	500

 

Check out the data in rows with tid 8 and 13 carefully. Customer ABC is given Rs. 100 for deposit amt of 0 to 50,000 in row with tid 8, while in row with tid 13 it is given Rs 120 for deposit amt of 10,000 to 50,000. This is not correct. How will you find out that? Using nested loop, you will have to compare each row with every another row in table. Now, use the query below to find out overlapping buckets.

 

 

if exists (           select    *

                        from     tbl_bank_charge a

                        inner join tbl_bank_charge b

                                    on        a.customer        = b.customer

                                    and       a.bank              = b.bank

                        where   a.tid      <> b.tid

                        and       (           a.from_amt between b.from_amt and b.to_amt

                                    or         a.to_amt between b.from_amt and b.to_amt)

            )

            begin

                        print 'Error: Buckets Overlap'

                        return

            end

 

It simply joins the same table 2 times and compares each row with another row having same customer and bank. If it finds out any overlapping margins then prints error and returns. Thus, it is a simple tweak to remove loop and optimize query processing.

 

Read more...

Transport SQL Data from a server to another server

Sometimes, you may want to copy data from a table of a server to another table to another server which is not connected to source server. What will you do?

 

In fact, it is not much a deal. It can be easily done with the help of "Import and Export Data" utility provided by SQL Server 2000.

 

Just open the utility, select the source data source (Microsoft OLE DB Provider), source SQL Server name and database name.

 

Then, select the destination data source as Microsoft Excel (any version), Access or Text file.

 

Then, select "Copy Tables" option and click "Next".

Finally, select the table(s) you want to copy. If you use Text file as destination, then you can choose only one table from source to export.

 

The process can be reversed for importing data from exported Text File or Excel. You have to choose the Text file or Excel as source and SQL Server as destination this time.

 

SQL Sever uses bulk insert in background to achieve this. It works fine under normal conditions. For this to work fine, of course, source and destination tables should have same structure or destination tables should have columns of same data type as source tables. The mapping can be done at the time of importing.

 

If you are using table with column of "uniqueidentifier" data type, then it is preferable to use Excel for import export because while using Text file it converts the values to string and creates problem while importing string to column of datatype uniqueidentifier.

 

Under default options, this utility appends new imported rows to destination tables. So, if it contains identity and uniqueidentifier columns, then chances are there that after importing, it contains duplicate identities and uniqueidentifier. However, there are options to delete all rows first or drop and create a new destination table.

 

If the destination tables contain some constraints which prevent from inserting import data, then error is thrown and import fails.

 

Finally, SQL Import and Export Data is an excellent utility to copy raw data from one location to another location not having direct connection.

 

 

Read more...

How to use data of another SQL Server?

If you are inside a database server and want to use data from another database server, then, it is possible through linked servers. Link server links two servers. Through the use of link server, you can execute any queries in another server. You will have to map a local login (or all) to remote user to connect.

 

To create a link server, you can use sp_addlinkedserver procedure provided by SQL Server. Its syntax and example is shown below.

 

Syntax : exec sp_addlinkedserver <remote server>, <server product>, <provider>, <data source>, <location>, <provider string>, <catalog>

Example : exec sp_addlinkedserver 'testserver', 'sql server'

 

The above example uses default provider i.e. SQLOLEDB and other default values. You can get a list of linked servers by running a procedure sp_linkedservers. Only adding link server is not enough. Now, you will have to map at least a local login to remote user for connecting. It can be done by using procedure sp_addlinkedsrvlogin. The syntax and example is shown below.

 

Syntax : exec sp_addlinkedsrvlogin <remote server name>, <use self>, <local login>, <remote user>, <remote password>

Example : EXEC sp_addlinkedsrvlogin 'testserver', 'false', 'suraj', 'admin', 'NewPassword'

 

This is all done. Now you can access data from remote server i.e. 'testserver' like shown below provided that table tbl_test_id exists in 'testserver'.

 

Select   *

From    testserver.testdb.admin.tbl_test_id

 

The syntax used is.

Select   *

From    <remote server>.<database>.<user>.<table name>.

 

This is all done. The same format can be used for all queries to be executed. All executions are of course subjected to limitations of remote user mapped to the login.

 

The whole process can be done through SQL Server Enterprise Manager. For that, go to Enterprise Manager -> SQL Server Group -> "Server Name" -> Security -> Linked Servers -> Right click and select New Linked Server. In the new pop-up window, type the name of linked server, select SQL Server as server type. In "Security" tab map a local login to remote user with password. Also, you can map all local logins to a particular remote login. Finally, click "OK" to save and you are done. Now, you can begin using linked server.

 

Last but not the least, you can use linked server to connect to other data sources besides SQL Server like Oracle, Microsoft Excel and even text files.

Read more...

Use calculated field in Excel Pivot Table

Excel Pivot Table is an excellent tool for analyzing business data. I am talking here to discuss on a facility provided by Microsoft Excel. If you use Excel Pivot Table frequently, sometimes you may require data that can be obtained only from figures of pivot table and not from source data. Consider an example shown below.

 

It simply shows the actual amt, plan amt, change amt and change percentage amt in different expense heads on month wise basis.

Head	Month	Actual_amt	Plan_amt	Change_amt	Change_per
Salary	January	2,000.00	3,000.00	(1,000.00)	(33.33)
Salary	February	1,000.00	1,000.00	-	-
Stationary	January	4,000.00	2,000.00	2,000.00	100.00
Stationary	February	3,000.00	2,000.00	1,000.00	50.00
Marketing	January	3,000.00	2,000.00	1,000.00	50.00
Marketing	February	2,500.00	2,100.00	400.00	19.05

Now, from pivot table you require following output. Please, note that the change_per field below is not the same as in previous table. It is actually calculated from field values from pivot table, i.e. change_per = (sum of change_amt / sum of plan_amt) * 100. This field cannot be calculated from field values from above table easily. So, this can be achieved by using Calculated Field in the pivot table.

Month	Sum of Actual_amt	Sum of Plan_amt	Sum of Change_amt	Change_per
January	9000	7000	2000	28.57
February	6500	5100	1400	27.45
Grand Total	15500	12100	3400	28.10

At first draw a pivot table from first table (source table) without any calculated fields as shown below.

 

Now, in the PivotTable toolbar, go to Pivot Table -> Formulas -> Calculated Field. In the pop up window, type the formula as shown below and name for new field and click add.

The formula used is as below.

Change_per_recalc =  Change_amt / Plan_amt * 100.

 

Then a new field is added automatically in the pivot table in Excel. If not added automatically, you can go to wizard and add it. Only sum function is available for it. Now, finally you will get required output like this.

 

Read more...

What is SQL Server Index?

  

SQL Server Indexes are the objects used to optimize the performance of queries. It makes SQL queries select, update or delete run faster. The whole purpose of indexes is to make finding data rows in a table easier.

 

If a table is created with no indexes, then the data rows are not stored in any particular order. This structure is called a heap.

If a clustered index is created in a table, then data rows are stored in sorted order on the clustered index key making easier to find rows.

But a nonclustered index is rather complex. The data rows are not stored in order based on the noclustered key rather each nonclustered key value entry has pointers to the data rows containing the key value.

 

Here, I want to elaborate mainly the difference between using an index and not using it.

For that I first, created two tables one without index and other with index. Then, I inserted some sample data in both of them.

The SQL query used is shown below.

 

create table tbl_test_index ( pid int, cname varchar(50))

insert into tbl_test_index values (1, 'Nepal')

insert into tbl_test_index values (2, 'India')

 

create table tbl_test_index2 ( pid int , cname varchar(50))

create index test_index2 on tbl_test_index2(pid)

insert into tbl_test_index2 values (1, 'Nepal')

insert into tbl_test_index2 values (2, 'India')

 

Then, I tried to find out the cost of running a select query with order by clause.

For that I highlighted the following query and clicked "Display Estimated Execution Plan (Ctrl + L)" button on tool bar of SQL Query Analyzer to display execution plan.

 

select    *

from     tbl_test_index

order by 1

 

The output is shown below.

 

Fig: SQL Execution Plan 1

 

Then, I highlighted following query in query analyzer and viewed execution plan.

select    *

from     tbl_test_index2

order by 1

 

 

Fig: SQL Execution Plan2

 

From the first figure for case without index, following things can be obtained.

Total CPU cost = 0.0009

Total I/O cost = 0.0487

From the second figure for case with index, following things can be obtained.

Total CPU cost = 0.000812

Total I/O cost = 0.04375

Also, the estimated cost is lower in second case.

From these results, it can be concluded the use of indexes optimizes the performance of SQL Queries or makes queries faster.

However, the choice of column as key should be made carefully depending upon maximum use of the column.

 

Read more...

Remove comments from SQL Server Stored Procedure.

 

Comments have been a part of SQL Server procedures. If you work in ERP applications or other huge applications, then comments are even more important. They are kept for multiple purposes like to mark author, dates, causes, implications. But often, huge comments become so disgusting that, it becomes difficult to study the stored procedures. In multiple thousand line stored procedures in huge applications, it is also likely that working code is quite little while comment portion is large. I have tried here to create a stored procedure which removes comment portion from a given stored procedure.

 

The procedure works for if the length of procedue is less than 8000. Also, I have considered only multiline comment marks for developing this procedue. That means, I have considered that no single line comment in the procedure exists. The single line comment can also be removed by a single loop. I will write about that in my next post.

 

First I created a test procedure with name sp_Proc1.

Here is the code.

 

create proc sp_proc1   
as   
begin   
 create table ##tt   
 (   
  col1 varchar(200)   
 )   

/*
/*

/* insert */ into ##tt values ('test data')  */
*/

select * 
from ##tt  
end   

 

Then I created the procedure to remove comments.

This procedue just uses sp_helptext , a built function, to get the text of given input stored procedure with comments.

It puts that in a temporary table and then a single variable @sp_text. 

Then it loops through each character of that variable and removes the content between opening comment mark '/*' and closing comment mark '*./'. The procedure also works for comments inside comments. It also takes care of line returns by using custom string ('\n') to represent new line.

The code goes like this.

 

 

 /* drop table #tbl_sp_text */
create table #tbl_sp_text ( id int identity(1,1), sp_text varchar(8000))

delete from #tbl_sp_text
insert into #tbl_sp_text
exec sp_helptext  sp_proc1 /* procedure name */

declare @sp_text varchar(8000), @sp_text_row varchar(8000), @sp_no_comment varchar(8000)
declare @c char(1)
declare @i int, @rowcount int
set @sp_text = ''

select @sp_text = @sp_text + case when len(@sp_text) > 0 then '\n' else '' end + sp_text
from #tbl_sp_text

select  @i  = 1
select @rowcount = len(@sp_text)
declare @comment_count int
select @comment_count  = 0
select @sp_no_comment = ''

while @i <= @rowcount
begin
 if substring(@sp_text,@i,2) = '/*'
  select @comment_count = @comment_count + 1
 else if substring(@sp_text,@i,2) = '*/'  
  select @comment_count = @comment_count - 1  
 else if @comment_count = 0
  select @sp_no_comment = @sp_no_comment + substring(@sp_text,@i,1)

 if substring(@sp_text,@i,2) = '*/' 
  select @i = @i + 2
 else
  select @i = @i + 1
end

/* drop table #tbl_sp_no_comments */
create table #tbl_sp_no_comments ( sp_text varchar(8000))

while len(@sp_no_comment) >0
begin
 insert into #tbl_sp_no_comments
 select substring( @sp_no_comment, 0, charindex('\n', @sp_no_comment))
 select @sp_no_comment = substring(@sp_no_comment, charindex('\n',@sp_no_comment) + 2, len(@sp_no_comment))
end

select *
from #tbl_sp_no_comments

 

Read more...

Convert Amount into Words according to English Numbering Style.

If you are application programmer, you may require displaying amount figures in words for user ease.  For example, if the amount figure is $12445.41 then, in words, it becomes Twelve Thousand Four Hundred Forty Five and Forty One Cents Only.

 

There may be some built-in functions in applications. Here, I have created MS SQL functions to calculate amount in words. This function works for amount up to 999,999,999.99 i.e. Nine Hundred Ninety Nine Million Nine Hundred Ninety Nine Thousand Nine Hundred Ninety Nine and Ninety Nine Cents Only. If you require, you can change the units by replacing the words.

 

First, I create a function which converts number less than 10 to words. The function goes like this.

 

CREATE    Function dbo.fConvertDigit(@decNumber decimal)

returns varchar(6)

as

Begin

declare

@strWords varchar(6)

            Select @strWords = Case @decNumber

                When '1' then 'One'

                When '2' then 'Two'

                When '3' then 'Three'

                When '4' then 'Four'

                When '5' then 'Five'

                When '6' then 'Six'

                When '7' then 'Seven'

                When '8' then 'Eight'

                When '9' then 'Nine'

                Else ''

            end

return @strWords

end

 

Then, I created a function to convert number less than 100 to words using above function.

 

CREATE    Function dbo.fConvertTens(@decNumber varchar(2)) 

returns varchar(30) 

as 

Begin 

declare 

@strWords varchar(30) 

-- If amt is between 10 and 19

If Left(@decNumber, 1) = 1  

begin 

 Select @strWords = Case @decNumber 

     When '10' then 'Ten' 

     When '11' then 'Eleven' 

     When '12' then 'Twelve' 

     When '13' then 'Thirteen' 

     When '14' then 'Fourteen' 

     When '15' then 'Fifteen' 

     When '16' then 'Sixteen' 

     When '17' then 'Seventeen' 

     When '18' then 'Eighteen' 

     When '19' then 'Nineteen' 

 end 

end 

else  -- if amt is between 20 and 99

begin 

 Select @strWords = Case Left(@decNumber, 1) 

     When '0' then ''   

     When '2' then 'Twenty ' 

     When '3' then 'Thirty ' 

     When '4' then 'Forty ' 

     When '5' then 'Fifty ' 

     When '6' then 'Sixty ' 

     When '7' then 'Seventy ' 

     When '8' then 'Eighty ' 

     When '9' then 'Ninety ' 

 end 

 Select @strWords = @strWords + dbo.fConvertDigit(Right(@decNumber, 1)) 

end 

 --Convert ones place digit. 

  

return @strWords 

end 

 

  Now, I created function to convert numbers less than 1000 to words using above functions.

 

CREATE Function dbo.fConvertHundreds (@decNumber varchar(3)) 

returns varchar(200) 

as  

Begin 

declare @strWords varchar(200) 

 

 Select @strWords = Case left(@decNumber,1) 

     When '1' then 'One' 

     When '2' then 'Two' 

     When '3' then 'Three' 

     When '4' then 'Four' 

     When '5' then 'Five' 

     When '6' then 'Six'  

     When '7' then 'Seven' 

     When '8' then 'Eight' 

     When '9' then 'Nine' 

     Else '' 

 end 

  

 if ltrim(rtrim(@strWords)) <> '' and @strWords is not null 

  select @strWords = @strWords + ' Hundred '+ dbo.fconvertTens(right(@decNumber,2)) 

 else 

  select @strWords = dbo.fconvertTens(right(@decNumber,2)) 

  

return @strWords 

end 

 

Finally, I created the function to convert amount less than 999,999,999.99 to words using above 3 functions. The basic concepts used are follows.

If the input amt contains decimal (.), then take two numbers after decimal and convert them to words using fConvertTens() function.

If the input amt contains 3 or less characters before decimal, then the amt will be less than 1000, so, use fConvertHundreds() function.

If the input amt contains 4 to 6 characters before decimal, then amt in words will contain thousand parts and hundred parts only. Use fConvertHundreds() for final 3 numbers to calculate hundreds part and numbers before that to calculate thousand parts.

If the input amt contains 7 to 9 characters before decimal, then amt in words will contain million part, thousand part and hundred part. Use fConvertHundreds() for final 3 numbers to calculate hundreds part, and 3 numbers before that to calculate thousands part and remaining numbers before 6 numbers to calculate million part.

 

The function is given below.

CREATE function dbo.fNumToWords

(@decNumber decimal(12, 2)) 

returns varchar(300) 

As 

Begin 

Declare 

 @strnum varchar(100), 

 @strCents varchar(100), 

 @strWords varchar(300), 

 @intIndex integer 

 

 

Select @strnum = Cast(@decNumber as varchar(100)) 

Select @intIndex = CharIndex('.', @strnum) 

select @strCents = '' 

 

if(@decNumber>999999999.99) 

BEGIN  

 RETURN '' 

END 

 

If @intIndex > 0  

begin 

 Select @strCents = dbo.fConvertTens(Right(@strnum, Len(@strnum) - @intIndex)) 

 Select @strnum = SubString(@strnum, 1, Len(@strnum) - 3) 

 If Len(@strCents) > 0 Select @strCents = @strCents + ' Cents' 

end 

 

declare @trail_zeros  varchar(3) 

declare @strthousands varchar(3) 

declare @strMillions varchar(3) 

 

set @trail_zeros = '000' 

 

if len(@strnum) <= 3 

begin 

 select @strWords = dbo.fConvertHundreds(left(@trail_zeros,3-len(right(@strnum,3)))+ right(@strnum,3)) 

end  

if len(@strnum) >= 4 and len(@strnum) <=6 

begin 

 select @strthousands = left(@trail_zeros,3 - len(left(right(@strnum,6),len(@strnum)-3))) + left(right(@strnum,6),len(@strnum)-3) 

 select @strWords = dbo.fConvertHundreds(@strthousands) + ' Thousand ' + dbo.fConvertHundreds(left(@trail_zeros,3-len(right(@strnum,3)))+ right(@strnum,3)) 

end 

if len(@strnum) >= 7 and len(@strnum) <=9 

begin 

 select @strMillions = left(@trail_zeros,3-len(left(@strnum,len(@strnum)-6))) + left(@strnum,len(@strnum)-6) 

 select @strthousands = left(right(@strnum,6),3) 

 select @strWords = dbo.fConvertHundreds(@strMillions) + ' Million ' + dbo.fConvertHundreds(@strthousands) + ' Thousand ' + dbo.fConvertHundreds(left(@trail_zeros,3-len(right(@strnum,3)))+ right(@strnum,3)) 

end 

 

if @strCents <> '' 

 select @strWords = @strWords + ' and ' + @strCents + ' Only' 

else 

 select @strWords = @strWords + ' Only' 

 

return  @strWords 

 

end 

 

Read more...

Paging in Web Applications (SQL Trick)

Web Applications here means the applications that are used through browsers. Web applications are tougher to develop and use as compared to windows applications. In this topic, I want to put focus on paging techniques for web applications.

If you are a web programmer, you may be having problem to display a large report or data in a data grid with paging. Paging is required as it might not be practical to display large no. of rows in a single grid. User will have to keep scrolling down and up. Also, a large memory will be constantly consumed. There are no. of ways to use paging in data grids. Here, I would like to focus on data grid displayed by pulling data from RDBMS database like SQL Server.

Suppose you are running a stored procedure (say sp_report1) to pull data from SQL Server database and it returns all data (say 5000 rows) at one time. A simple way would be to pass two input variables like @from_row_no and @to_row_no to the procedure and modify it to return rows of data from @from_row_no to @to_row_no.

This also can be done two ways. In first way, you put all output data in a temporary table which also contain an identity column. Then, while running final select statement from that final output table, use conditions to display from @from_row_no to @to_row_no.

Here is an example.

Create proc sp_Report1

( @from_row_no int, @to_row_no int)

as

begin

create table #tbl_final_display

(

id  int identity(1,1),

col1 varchar(30),

col2 varchar(30)

)

/* now insert data into #tbl_final_display as per your requirement */

-- for final select

select *

from #tbl_final_display

where id >= @from_row_no

and id <= @to_row_no

end

If you have to display results in order then you will have to pass ordering column name also to stored procedure. If the data in #tbl_final_display is going in single statement you can use order by clause in that statement with passed input variable. If not, you will have to create two temporary tables. First one to just hold the data and second one to copy data from first one by using order by clause. The second table will obviously have one more column than first one for id field. This process becomes tedious when there are many sortable columns as many if else conditions are required.

Here "id int identity(1,1)" means that id column of the table will have datatype int and the values will be automatically inserted while inserting rows in that column. The values will start from 1 and will keep on increasing by 1 for each new row.

In Second Method, you need to use the permanent table in which data are kept temporary. The stored procedure will just populate the table. Final select from table is run from front end like this.

For example to retrieve data from table tbl_final_display from row 9 to row 14 order by first field, you can use this query.

select    *
from    (
    select    top 6 *
    from    (
        select    top 14 *
        from    tbl_final_display
        order by 1
    ) tmp
    order by 1 desc
    ) tmp
order by 1

It uses multiple sub-queries and multiple select. First it selects 14 rows (@to_row_no) and then selects last 6 rows from that select. This has to be done from front end as while using "top 6 " we cannot pass variable instead of 6. It may be tedious for programmers to handle from front-end as much additional care must be taken. Such as provide fields to distinguish multiple users to eradicate the chances of wrong output when same report is used by multiple users.

Read more...

Factory Incentive Calculation System (FICS)

 

Few months ago, I visited Simra for study of FICS (Factory Incentive Calculation System).

It is an application implemented in that location for calculation of incentives ( additional pay besides salary) of workers in factory.

I prepared technical and functional documents of the system. The same has been shown below.

 

 a.      Technical Details

 

Applications Used :	FICS, FIS
Current Status :	Used
Front End Technology :	Lotus Notes (FICS) Visual Basic (FIS)
Database Technology :	SQL Server 2000, Lotus Notes database
Database Name :	SNPLFCT ( SQL Server), SNPLFIS (Lotus Notes)
Database Size :	160 MB
Front End Username/ Password :	As the application operates in Lotus Notes, it is directly accessible from lotus notes of limited users only.
Database Username/ Password :	sa/snpl administrator/godknows
Source code availability :	Yes
Common Users :	Shift Managers (Niraj Nepali, Bipin Sharma, Mihir Jha), Rahul Shrivastav (Filter), Sarbojit Rana
Application Server Details :
	Hostname :	LOTUS
	IP Address :	192.168.3.10
	RAM :	2 GB
	Hard Disk :	80 GB (30% free)
	OS :	Windows 2000 Server
Database Server Details :	Same as Application Server
Application Backup :	Daily Backup along with mail backup
Database Backup :	Daily Backup through job schedule
Logic By :	Baburam Karki, Rahul Shrivastav
System Implemented By :	Dinesh Rao, Soumya
Software Vendor :	Internally developed
System Developed By :	Dinesh Rao, Soumya
Support person from software vendor :
Support person from SNPL :	Binod Pandey, Soumya, Suraj Shrestha
AMC Details :	No AMC
Screen Shot :

 

b.      Functional Details

 

Basic Objective :	To calculate different types of incentives for employees according to machine outputs.
Business Logic & Process :
After the decision of management to give additional incentives to employees to workers according to their performance, FICS project was initiated. This system mainly intends to capture the outputs of machines entered by managers and calculate the incentives to workers allocated to machines.   There are basically following types of incentives. Machine Incentive: It is incentive given to workers allocated to a machine according to output of that machine. The output of the machine is compared with standard output and efficiency is calculated, accordingly incentive is calculated. The workers here are machine crew, technician, supervisor and manager. Modular Incentive: One or more machines comprise a module. It is incentive given according to output of a module. Shift Incentive: It is incentive given according to output of the particular shift either late, early or normal shift. The output is calculated in terms of output of packing machine i.e. final output. Monthly Incentive: It is incentive given according to output of the particular month. The output is also calculated in terms of output of packing machine. Others Incentive: It is incentive given to other employees besides workers. The others employees may be from HRM, Production.   The employee's names, modules, shifts, machine names, standard output of machines, logic for incentive calculation are fixed in system as per agreement. At first, a small utility called "FIS" is run to copy attendance details from ARS to FICS database by Time Office. Until and unless attendance details are copied to FICS database, managers cannot enter the output of machines in system. After copying, the manager log into his lotus notes mail and opens FICS system. He then creates a new record and enters date and shift. There are different combinations of machines provided for different modules (RSFT, KSFT, Filter and PLAINS). In particular module also a specific machine can be disabled if it is not operable. After the FICS user chooses combination and machine condition, he allocates available technicians, crews, leader and supervisor machine wise. The system shows the default technicians, crews, leader and supervisor according to there presence as obtained from ARS system. The FICS user can change the default data as per availability. Finally, the FICS user fills the output of machine as per document available.   The detailed and summary incentive calculation can be seen from excel sheet report by entering date range. After the end of each month, the summary data from excel is cross checked with physical document and given to HRM staff (Suman) for entering in Ramco ERP. HRM Staff opens Recurring or Non-Recurring pay element entry screen of Payroll module of Ramco ERP and copies data from excel to those screen and saves in specified process period and pay element. The incentives are added to salary of employees after salary processing from Ramco ERP.
Problems :	1. All the master entries need to be done from back end. There is no provision for creation of employees, machines, modules, shifts in front end. Also, logic for incentive calculation and standard output of machine has to be fixed from back end. Also, employee module, employee department mapping has to be done from back end.
	2. There is no data flow from FICS to ARS. If shift of an employee is changed in FICS, it is not reflected in ARS, thus he will not get paid.
	3. As the system is developed in Lotus Notes, it is difficult to give support due to lack of trained manpower.
	4. The system is quite inflexible. A little modification needs to be done in code level.
	5. A particular record cannot be deleted after insertion even in case of mistake. Only, its date and other details can be changed.
	6. A particular FICS user can alter details entered by another user of another department.
Solutions / Enhancements/ Suggestions  :
	Facility for creation of masters like creating employees; mapping them to particular department, module; creating modules; creating machines; entering standard output of machines; defining incentives and calculation logic can be added.   A new web based application can be developed internally with above mentioned facilities and user access rights maintenance and without needing separate utility to copy data from ARS and view incentive calculation sheet.

 

Read more...

Attendance Recording System (ARS)

 

I work as a Application Support Manager in one of a leading companies of Nepal. 

During my visit to Simra for study of ARS (Attendance Recording System), I have prepared the following document.

It includes the technical details and functional details that need to be maintained by Central IT team.

 

 a.      Technical Details

 

Applications Used :	RMS Access Management System, Download data stars, STARS,  ARS
Current Status :	RMS Access Management System, Download data stars, STARS are used but ARS is not used.
Front End Technology :	Visual Basic (RMS, Download data stars, STARS) ASP / JavaScript / HTML (ARS)
Database Technology :	Microsoft Access 2003. (RMS) Microsoft Access 2000. (STARS) SQL Server 2000 (ARS) No separate database (Download data stars)
Database Name :	RMS_A.mdb, Back_A.mdb ARS.mdb (STARS) ARS_DB (ARS)
Current Database Size :	80 MB (RMS) 280 MB (STARS) 82 MB (ARS)
Front End Username/ Password :	admin/NEWNEPAAL (STARS) harihar/vintuna (ARS)
Database Username/ Password :	No password protection (RMS, STARS) sa/notknown (ARS)
Source code availability :	Available for RMS, ARS, Download data stars. Not available for ARS.
Common Users :	Harihar Uday, Dharmendra, Shreedhar Joshi
Application Server Details :
	Hostname :	Shikhar
	IP Address :	192.168.3.7
	RAM :
	Hard Disk :
	OS :	Windows 2000 Server
Database Server Details :	Same as Application Server
Application Backup :
Database Backup :	Daily backup of database of RMS and STARS
System Implemented By :	Binod Pandey, Secure Alarm Traders
Software Vendor :	Reco Research (RMS) Zenith Control and Systems Pvt. Ltd. –Zecons  (STARS) Value One Nepal (ARS)
System Developed By :
Support person from software vendor :	Value One Nepal
Support person from SNPL :	Binod Pandey, Dharmendra
AMC Details :	No AMC
Screen Shot :
	1. RMS
	2. STARS

 

b.      Functional Details

 

Basic Objective :	To capture and process attendance data of workers and generate reports for the Time Office and company management.
Business Logic & Process :
RMS is a system to record entry and exits of workers. STARS helps in processing and manipulating the attendance data captured by RMS. STARS has facility of creating departments, employees, entering different types of leaves, encashing leave, etc. It has a system for handling employees of different shifts and provision for shift change. STARS is designed for downloading data from bridge reader. But as the bridge reader is replaced by Smart Card Reader, RMS is used to download data and STARS only manipulates that data. This entire system does not do any financial transactions.   First of all, the user of STARS has to create employees with provided card numbers, calendars, leaves type, holidays, shifts, etc. Every worker has got a unique smart card. Whenever he enters or exits office, he scans his card in Smart Card Reader (RAC 800) placed at the entrance and exit points. The Smart Card Reader is connected to Isolated Repeater Data Converter at server room which is connected to serial port of server machine. RMS application continuously monitors the data coming and downloads it to its database (table data_card). The Smart Card Reader has its own memory. So, it can also store records for no. of days in case RMS is not running.   The use of RMS is limited to this point only. After total downloads for a shift is complete (not necessarily), a utility "Download data STARS" is run to copy new attendance records from RMS database (table data_card) to a new text file with name same as date and time of creation. The file is then renamed as ars.txt. Now the user opens STARS and runs posting. It will update STARS database (table: tbl_muster) with the new data from ars.txt and changes the extension of that file.   If some worker has to take leave or change shift, he will write a memo and give it to Time Office after approval of shift manager or other seniors. The STARS user will enter the same in system. If that worker comes to office and scans card, then system will show error. The worker is not paid for that. But the memo can be passed into the system later on also. STARS also provides facility for leave encashment but the financial impact is not shown only the leave balance of that employee is reduced.   After the end of each month, the attendance details needs to be submitted to HRM for payroll processing of workers. The Time Office gets the backup database (ars.mdb) of month end, deletes unnecessary records of all previous months by running a query in access database and gives the file to HRM (Shreedhar).  The HRM staff copies the file to \\marshal\ramcoapp\upload_file folder and uploads the file in Ramco ERP from Attendance Allowance Upload screen of Payroll module with help of IT person. This is done to calculate attendance allowance of workers. Workers present in all working days will get additional allowance as per logic incorporated in Ramco ERP.   The Time Office also generates a text file by running payroll report of STARS and gives it to HRM. The HRM staff deletes records of managers, deletes second column, saves as ars.txt, copies it to \\marshal\ramcoapp\upload_file folder and runs ARS Upload from ARS Upload screen of Payroll module of Ramco ERP with the help of IT person. Finally, the allowance calculated is checked from Ramco Payroll report and salary of workers is processed.
Problems :	1. The system is troublesome to operate as the users have to use multiple modules.
	2. There is greater chance of data vulnerability as Access databases are not password protected and users also have to work in those databases and notepads containing data.
	3. Access database has limited capacity to handle data. The size of STARS database has grown sufficiently large with more than 5 lakhs of data. So, there is risk of data corruption. Nonetheless, IT personnel take backup of database daily and also purge database once in a year keeping old backup for later reference.
	3. Error comes frequently while entering leaves of workers.
	4. There are frequent errors in reports also.
Solutions / Enhancements/ Suggestions  :
	The use of three separate modules; one for capturing, other for manipulating data and other for interlinking them requiring human intervention in data is illogical. This entire system should be integrated in single module.   Access database technology has become old. Working in this is absurd for handling thousands of data daily. A better database technology such as SQL Server is more appropriate.   The ARS system developed by Value One is not used. Either this system could be enhanced to overcome all errors or new system could be developed.

 

Read more...