Friday, November 21, 2008

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.

 

  1. 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.
  2. Click Ok to save and return to main pop up window of "Shrink Database – testdb".
  3. On Schedule tab of the pop up, click New Schedule.
  4. Give the name of new schedule (say "Shrink Database – testdb").
  5. Select Recurring and click Change.
  6. Select frequency of job and time for the job and click Ok to return.
  7. Click Ok to return to main pop up window "Shrink Database – testdb".
  8. 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.
  9. Click Ok to save the job and you are done.
  10. Check if the job is executed on time or not and executed properly or not.
  11. 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...

Saturday, November 15, 2008

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...

Thursday, November 13, 2008

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...

Tuesday, November 11, 2008

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...

Saturday, November 8, 2008

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...