Live Situation #3 : Clearing the transaction log in SQL Server 2005

5 months ago – in one of my project, Mirth integration server was linked to SQL Server to store the processing data. One of the channel in Mirth has a flat file reader, parses it and at  every major point it inserts the data to a table.
With a live demand, Mirth was asked to process a file of more than 2lacs of lines. This resulted a large log file in SQL server.
Situation came to somehow empty the content of this log file(.ldf) without affecting the actual database file(.mdf).
One of the frequent answer you get after Googling by several bloggers – Detach the database, rename the log file and again attach the database. But please don’t do this. This can damage your database so badly that you cant retrieve it even. Yes I have struggled with one of the database badly.

Here is the way that sounds pretty smooth toward the solution:
This is very quick and reduced the log file size from 1 GB to 1MB in just 10 seconds. Amazing!!!

1. Select a database.
Right Click -> Properties -> Options -> Database Recovery Model -> Simple [change it to Simple from anything else]

2. Right Click on database -> Tasks -> Shrink -> Files
From dropdown select file type as Log
Release unused space
or Shrink to file size – Give your own choice e.g 1 MB

3. Right Click -> Properties -> Options -> Database Recovery Model -> Full [change it to Full or whatever you had initially from Simple]

MySQL Query for a Programmer

I am writing some useful queries collected in past specially when I was working with Ruby & Java projects.
They are more than SIMPLE and lesser than COMPLEX and often used by a programmer. This post is more for my self reference. Good if somebody else is benefited…
To make it simple, I have wrote them with examples only rather than having a complete syntax.
You can do a browser search(Ctrl+F) within this post based on few keywords:
mysqldump, alter, add index, add column, csv to database, group concat, rename etc.

1. Copy a table : Copies a table along with data structure and data into another table:
create table trackedi_duplicate as select * from trackedi

2. Insert multiple rows using single query
INSERT INTO “table1” (“column1”, “column2”)
SELECT “column1”, “column2” FROM table2

3. Repeating the same character:
SELECT REPEAT(‘a’,5)
Outputs aaaaa

4. Trimming the \n from a long description like column
select trim(both ‘\n’ from description) from base;

5. Position of a char or string in a column :  Give me all the rows containing the word “pro” in status column
select * from trackedi where LOCATE(‘pro’,status) > 0

6. Size of each table
SELECT TABLE_SCHEMA AS ‘Database’, TABLE_NAME AS ‘Table’,
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),15),” Mb”) AS Size FROM INFORMATION_SCHEMA.TABLES;

7. Loading data from CSV to a table
LOAD DATA LOCAL INFILE ‘C:\\dbs_dump\\mytable1.csv’ INTO TABLE myTable1 FIELDS  TERMINATED BY ‘,’ LINES TERMINATED BY ‘\r\n’;

8. Dump a database
mysqldump -u root -p testDb  > c:\dbs_dump\testDb.sql

9. Dump few tables only
mysqldump -u root -p testDb table1 table2 > c:\dbs_dump\fewTables.sql // table structure along with data
mysqldump -u root -p -d testDb table1  table2 > c:\fewTablesStructure.sql // table structure without data

 10. Storing/Running a sql scriptMake sure that you are at the prompt of mysql
mysql> source fewTables.sql

11. Renaming a table
rename table analytical_charts to data_charts;

 12. Dropping/Adding a foreign key
alter table data_analysis drop foreign key chart_id;
alter table data_analysis add foreign key(`chart_id`) REFERENCES `data_charts` (`id`);

13. Adding a new column
alter table data_charts add column `chart_type` varchar(80);

 14. Adding a new index
alter table data_charts add index `chart_type` (`chart_type`);

15. Updating a column by string replacement
update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’);
update myTable set myColm1 = replace(address,’\”,”);

16. Group Concat – Give me the list of all names grouped by status – MySQL specific, you don’t have in SQLServer
SELECT status, GROUP_CONCAT(name) FROM trackedi group by status

Live Situation #2 : Return value from Stored Procedure

Sometimes a problem sounds the damn easiest, but we get trapped into a really weird situation.

Because we just try to solve these problems by relating them with a similar concept. The technology resemblance is not so simple.
Problems may be as easier as to just add two numbers. There could be a number of technical approaches. Somebody just write a C function, somebody using pointers, somebody can play around the address values to display the same result value or someone using built-in API.

In this ORM world of programming (Object Relationship Mapping – Hibernate, Active Records etc.), the framework is giving a flexibility to a programmer to treat the entire database tuple (rows) as just like basic data types variable. And hence we over mistakes ourselves assuming they behave exactly.

Recently we had an issue with a Biztalk project around SQL Server.

Situation was like to retrieve the party name from one table in SQL Server. Later on based on the party name you need to populate another table.

currentParty = getPartyName();
if(null != currentParty ){
   // Populate the other table
}

We wrote a stored procedure pretty simple to above. But that did not work.
This is not the right place to explore each byte of our experiment; but finally what we concluded is – HOW TO CALL A SP, HOW TO PASS ARGUMENTS TO IT, HOW TO RETRIEVE PARAMETERS.

If you are a C#, Java, Ruby etc developers – You think this is pretty simple. But that’s not the case.
Don’ttreat the store procedure as normal functions. Syntactically stored procedures differs a lot.

1. Below is an example on – HOW TO RETURN VALUE from a SP

CREATE PROCEDURE doAddition_Using_ReturnValue
AS
BEGIN
      DECLARE @retRes int;
      --SET @retRes  = exec getSum_As_ReturnValue 4,5
      exec @retRes = getSum_As_ReturnValue 4,5
      print @retRes
END
GO

CREATE PROCEDURE getSum_As_ReturnValue
      -- Add the parameters for the stored procedure here
      @n1 int, @n2 int --// No special keyword for input paramaters
AS
BEGIN
      declare @res int
      set @res = @n1 + @n2;
      return @res
END

Your SP call should be – exec doAddition_Using_ReturnValue;

So what a big deal here. The big deal is hereYour return statement is compatible with int data type only. What if you want to return a string value and capture into a variable? As in my case party name has to be a string.

The answer is using OUTPUT parameter and that happens to be using a “SELECT” in SP.

2. Below is an example on How to return value using SELECT as OPUTPUT parameter

CREATE PROCEDURE doAddition_Using_OutParameter
AS
BEGIN
      DECLARE @outRes int;
      exec getSum_As_OutParameter 4,5, @outRes OUTPUT --// @outRes holds the returned value getSum_As_OutParameter having two in parameters 4,5
      print @outRes
END
GO

CREATE PROCEDURE getSum_As_OutParameter
      -- Add the parameters for the stored procedure here
      @n1 int, @n2 int, @res int OUTPUT   // @res must come in a SELECT statement for returning purpose
AS
BEGIN
      set @res = @n1 + @n2;
      SELECT @res
END
GO

Special thanks to Mario for letting me know that SELECT and return are different in SP calls.
Thanks to Biztalk friends here – Sandeep, Som & Manish for trapping me into a silly(sorry toughest!) problem.