tag:blogger.com,1999:blog-191652532024-02-28T09:13:42.407-08:00Kiran Marke's BLOGSpace to share my thoughts and technical knowledgeKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.comBlogger49125tag:blogger.com,1999:blog-19165253.post-54150529905750060262009-08-12T01:22:00.000-07:002009-08-12T21:26:13.428-07:00Swine Flu And PuneWe all are battling with "Swine Flu". News channels are all flooded in these news only. Everyday we receive news of 1-2 persons being killed by this dangerous virus. People here in Pune are really feared with this situation. Person coughed once is also seen in doubt. We cant help on this actually, the symptoms for "Swine Flu" are not different from the normal cold, cough symptoms. People having cold or cough alone are also gathering at Naydu and other government recommended hospitals for swine flu testing. I think this is really dangerous as these people are exposing themselves among the "Swine Flu" infected people. One should get tested if one has following symptoms together: fever, cough, runny\stuffy nose, sore throat, chills, fatigue, headache. Government should educate everyone on these actual Swine Flu symptoms.<br /><br />This situation will become worse in coming days. Dahi-Handi and Ganapati festivals are approaching. and They are highly celebrated in Pune. We talk about keeping 6-10 feet distance from the infected people but will we be able to control the "H1N1 virus" if people will group together for such activities?. I think, rather than government imply force on janata for not celebrating these festivals in public manner, people here should understand the current situation and act accordingly. I think we should not celebrate "Dahi-Handi" and "Sarvajnik Ganeshostav" this year. People can worship Ganesh Murthi at their home. But in this way also you cant control on people gathering when it comes to the "Ganapati Visarjan". Better option is not to celebrate any festival. The ideal approach would be to observe curfew in the city for at least 10 days. and government should provide medicines to infected persons to their home.<br /><br />Another thing i observed here is about the Masks distribution. I had to take pair of 3 layered mask for Rs 50. The similar mask was previously available for Rs 5. This is not the condition where we should look for our own benefit, and try to make profit out of the situation. Also even if city is facing shortage of the N-95 masks, everyone is going after them. Doctors have suggested that not everyone need N-95 masks and it is only for relatives of infected personnel. Two 3-layered masks overlapping one another will do.<br /><br />For the precaution measures i am trying to remain away from Pune. Because right now i don't have any major responsibility work in office, I have taken leaves from office and taking rest here in kalyan. But i wont be able to do this for long time. Like me it is not possible for many people to keep away from work and leave Pune , my advice to them would be to drink a lot of water and juices, Vitamin 'C' food while working.<br /><br />Considering the population and poverty of the nation, it is really worst thing happened that "Swine Flu" got spread in India. Till now the patients found are from cities only. If "Swine Flu" reaches to villages, then it would be real disaster. Let us hope, before this happens we will take control over it.Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-19165253.post-24288254225371327042009-07-11T03:21:00.000-07:002009-08-12T21:26:23.663-07:00Tum Bin Jaaoon Kahan...Yesterday, I found Hariharan's version of this song in my friend's Orkut favorite videos. I was not aware of this version and movie. The song is sung in full of feelings and i really loved it the manner in which it is used in the movie.<br /><br />But then critic inside me waked up. I downloaded all 3 versions of this song and started comparing. All of them are truly masterclass. I liked Harharan's version most. This may be because way in which it is used in the movie. You actually feel, hariharan tried to pour a lot of feelings inside it. Only on feelings basis, i will keep Hariharan's version at Top, Rafi's at 2nd position and Kishore's at 3rd position.<br /><br />Hariharan's Version:<br /><br /><object width="425" height="344"><param name="movie" value="http://www.youtube.com/v/J4mPL-eFBjg&hl=en&fs=1&"><param name="allowFullScreen" value="true"><param name="allowscriptaccess" value="always"><embed src="http://www.youtube.com/v/J4mPL-eFBjg&hl=en&fs=1&" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="425" height="344"></embed></object><br /><br />Rafi's Version:<br /><br /><object width="425" height="344"><param name="movie" value="http://www.youtube.com/v/Y95KfJxvPRw&hl=en&fs=1&"><param name="allowFullScreen" value="true"><param name="allowscriptaccess" value="always"><embed src="http://www.youtube.com/v/Y95KfJxvPRw&hl=en&fs=1&" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="425" height="344"></embed></object><br /><br />Kishore's Version:<br /><br /><object width="425" height="344"><param name="movie" value="http://www.youtube.com/v/KVg6UWl8GAw&hl=en&fs=1&"></param><param name="allowFullScreen" value="true"></param><param name="allowscriptaccess" value="always"></param><embed src="http://www.youtube.com/v/KVg6UWl8GAw&hl=en&fs=1&" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="425" height="344"></embed></object><br /><br />~ KiranUnknownnoreply@blogger.com2tag:blogger.com,1999:blog-19165253.post-64106125408704747872009-07-04T03:25:00.000-07:002009-08-12T21:26:36.567-07:00"About Me" PagesI was going through web to have new way to implement personal profile page. Here I found 60 beautiful and effective <a href="http://www.smashingmagazine.com/2009/07/01/best-practices-for-effective-design-of-about-us-pages/">About Me</a> pages.<br /><br />~ KiranUnknownnoreply@blogger.com2tag:blogger.com,1999:blog-19165253.post-37734557183268715482009-06-22T22:45:00.001-07:002009-08-12T21:26:48.626-07:00Pronounce Name Correctly! :)<p>This one is really a useful site. In our daily project work, we interact with Americans who have unfamiliar names. This site helps us to pronounce their name correctly. "How to say that Name" : <a title="http://www.howtosaythatname.com/" href="http://www.howtosaythatname.com/">http://www.howtosaythatname.com/</a></p> <p>I am glad to see my name in the database. <a href="http://howtosaythatname.com/component/option,com_mtree/task,search/Itemid,4/searchword,Kiran/">http://howtosaythatname.com/component/option,com_mtree/task,search/Itemid,4/searchword,Kiran/</a></p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-19165253.post-18985676296878758542009-06-22T03:52:00.001-07:002009-08-12T21:26:48.626-07:00Check Your Hearing! :)<p>They say that it is common for people who are over 25 years of age to not be able to hear above 15kHz. I tested my hearing. I can hear up to 17Khz. Check yours. :)  <a title="http://www.noiseaddicts.com/2009/03/can-you-hear-this-hearing-test/" href="http://www.noiseaddicts.com/2009/03/can-you-hear-this-hearing-test/">http://www.noiseaddicts.com/2009/03/can-you-hear-this-hearing-test/</a></p> Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-19165253.post-64781140080045447262009-06-10T07:17:00.001-07:002009-06-11T06:12:38.619-07:00Audit log Design : Using AFTER INSERT, UPDATE, DELETE Trigger<p>In my last project, we had to implement Audit log functionality for SQL Server Data Changes. Due to very strict timeline(Project was of 2 months and Out of that Audit functionality was having only 10 days to implement), we were goggling for ready made design for Audit functionality. But our efforts gone into vain. Didn't find any good. </p> <p>SQL Server 2008 is having in built functionality to capture data changes in the form of Change Data Capture Tool(CDC). We tried to convince the client to use the SQL Server 2008. But they didn't agree.:(. We had to go with SQL Server 2005.</p> <p>We followed trigger based approach. We created AFTER INSERT, UPDATE, DELETE triggers on the all client tables, and recorded all the changes happened. The design was very simple. Hope this helps you guys.</p> <p><strong><u>Features included are:</u></strong></p> <ol> <li>Audit Log SHOULD have turn off & turn on switch. </li> <li>Audit Log MUST capture information like who made the changes, what changes, when the changes occurred. </li> <li>Audit Log SHOULD have rollback facility. </li> <li>Retrieval of Audit details in Paging. </li> <li>Audit retrieval WOULD have following APIs <ul> <li>Get all changes </li> <li>Get all today’s changes. </li> <li>Get all changes for period. </li> <li>Get all changes for particular transaction. </li> <li>Get single Audit Log entry for given Id </li> </ul> </li> </ol> <p><strong><u>Block diagram to show entire architecture used for Audit:</u></strong></p> <p><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTYjYPhtWa1BNriVtMWbj4xClR8xg2UBqz0uCUwdt38nzAtDT4C0_HDQATSmhDNmsBxAy-P9hn9ii6EW2D2AOfTS5rgVrpf3JbtSShmhhzn8RkWjJpYLSY7iPYZJHsI8pPrYYh/s1600-h/Audit.JPG"><img id="BLOGGER_PHOTO_ID_5345703695135189666" style="display: block; margin: 0px auto 10px; width: 400px; cursor: pointer; height: 254px; text-align: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTYjYPhtWa1BNriVtMWbj4xClR8xg2UBqz0uCUwdt38nzAtDT4C0_HDQATSmhDNmsBxAy-P9hn9ii6EW2D2AOfTS5rgVrpf3JbtSShmhhzn8RkWjJpYLSY7iPYZJHsI8pPrYYh/s400/Audit.JPG" border="0" /></a></p> <p></p> <p><strong><u>Audit Log Table:</u></strong></p> <p><a href="http://lh4.ggpht.com/_IbmLy40kPiE/Si_AbJzksSI/AAAAAAAAACE/qvYDUrifeV0/s1600-h/image%5B2%5D.png"><img title="image" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="244" alt="image" src="http://lh4.ggpht.com/_IbmLy40kPiE/Si_AbxRRX3I/AAAAAAAAACI/soUFjnXHOFQ/image_thumb.png?imgmax=800" width="188" border="0" /></a> </p> <p></p> <table cellspacing="0" cellpadding="2" width="681" border="1"><tbody> <tr> <td valign="top" width="679"> <p><span style="color: #0000ff">CREATE TABLE</span> [Administration].[AuditLog]( <br />[AuditLogID] [bigint] <span style="color: #0000ff">IDENTITY</span>(1,1) NOT NULL, <br />[TransactionID] [bigint] NOT NULL <span style="color: #0000ff">DEFAULT</span> ([dbo].[ufn_GetTransactionID]()), <br />[ApplicationName] [nvarchar](<span style="color: #ff00ff">max</span>) <span style="color: #0000ff">COLLATE</span> SQL_Latin1_General_CP1_CI_AS NULL <span style="color: #0000ff">DEFAULT</span> ([dbo].[ufn_GetApplicationName]()), <br />[ObjectID] [int] NOT NULL, <br />[OperationID] [tinyint] NOT NULL, <br />[PerformedBy] [nvarchar](100) <span style="color: #0000ff">COLLATE</span> SQL_Latin1_General_CP1_CI_AS NULL <span style="color: #0000ff">DEFAULT</span> ([dbo].[ufn_GetContextLogin]()), <br />[PerformedAt] [datetime] NOT NULL <span style="color: #0000ff">DEFAULT</span> (<span style="color: #ff00ff">getdate</span>()), <br />[TSQL] [nvarchar](<span style="color: #ff00ff">max</span>) <span style="color: #0000ff">COLLATE</span> SQL_Latin1_General_CP1_CI_AS NULL, <br />[PrimaryKeyColumn] [nvarchar](<span style="color: #ff00ff">max</span>) <span style="color: #0000ff">COLLATE</span> SQL_Latin1_General_CP1_CI_AS NOT NULL, <br />[PrimaryKeyValue] [int] NOT NULL, <br />[PreviousValue] [xml] NULL, <br />[NextValue] [xml] NULL</p> </td> </tr> </tbody></table> <p><u><strong></strong></u></p> <p><u><strong>Table Schema:</strong></u></p> <table cellspacing="0" cellpadding="0" border="1"><tbody> <tr> <td valign="bottom" width="46"> <br /></td> <td valign="bottom" width="163"> <p><strong>Name</strong></p> </td> <td valign="bottom" width="104"> <p><strong>Data Type</strong></p> </td> <td valign="bottom" width="358"> <p><strong>Description</strong></p> </td> </tr> <tr> <td valign="top" width="46"> <p><a href="http://lh5.ggpht.com/_IbmLy40kPiE/Si_Acn5z6-I/AAAAAAAAACM/zD86my6tYx4/s1600-h/clip_image001%5B3%5D.gif"><img title="clip_image001" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="16" alt="clip_image001" src="http://lh6.ggpht.com/_IbmLy40kPiE/Si_AdzlfYtI/AAAAAAAAACQ/jraimZ7HwGE/clip_image001_thumb.gif?imgmax=800" width="16" border="0" /></a></p> </td> <td valign="top" width="163"> <p>ID</p> </td> <td valign="top" width="104"> <p>bigint</p> </td> <td valign="top" width="358"> <p>Primary key - AuditLogID</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>TransactionID</p> </td> <td valign="top" width="104"> <p>bigint</p> </td> <td valign="top" width="358"> <p>Transaction ID used while performing DML query. This column is used to find out all the changes made in single transaction.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>ApplicationName</p> </td> <td valign="top" width="104"> <p>nvarchar(max)</p> </td> <td valign="top" width="358"> <p>Application though which SQL query is made.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>ObjectID</p> </td> <td valign="top" width="104"> <p>int</p> </td> <td valign="top" width="358"> <p>Table Id – To know on which table the operation happened.</p> </td> </tr> <tr> <td valign="top" width="46"> <p><a href="http://lh4.ggpht.com/_IbmLy40kPiE/Si_Aeg-eTPI/AAAAAAAAACU/YQc0PeM_HMs/s1600-h/clip_image002%5B4%5D%5B2%5D.gif"><img title="clip_image002[4]" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="16" alt="clip_image002[4]" src="http://lh6.ggpht.com/_IbmLy40kPiE/Si_AfifE2cI/AAAAAAAAACY/eRpwb0AFoso/clip_image002%5B4%5D_thumb.gif?imgmax=800" width="16" border="0" /></a></p> </td> <td valign="top" width="163"> <p>OperationID</p> </td> <td valign="top" width="104"> <p>tinyint</p> </td> <td valign="top" width="358"> <p>1 = Delete <br />2 = Insert <br />3 = Update</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>PerformedBy</p> </td> <td valign="top" width="104"> <p>nvarchar(100)</p> </td> <td valign="top" width="358"> <p>To store who has performed the operation.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>PerformedAt</p> </td> <td valign="top" width="104"> <p>datetime</p> </td> <td valign="top" width="358"> <p>Time when operation occurred.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>TSQL</p> </td> <td valign="top" width="104"> <p>nvarchar(max)</p> </td> <td valign="top" width="358"> <p>To store exact query which made this AuditLog entry.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>PrimaryKeyColumn</p> </td> <td valign="top" width="104"> <p>nvarchar(max)</p> </td> <td valign="top" width="358"> <p>To store primarykey column name of the Table on which operation occurred.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>PrimaryKeyValue</p> </td> <td valign="top" width="104"> <p>int</p> </td> <td valign="top" width="358"> <p>Primary key value.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>PreviousValue</p> </td> <td valign="top" width="104"> <p>XML</p> </td> <td valign="top" width="358"> <p>PreviousValue for changed row. <br />Note: For insert operation this value is null.</p> </td> </tr> <tr> <td valign="top" width="46"> <br /></td> <td valign="top" width="163"> <p>NextValue</p> </td> <td valign="top" width="104"> <p>XML</p> </td> <td valign="top" width="358"> <p>CurrentValue for changed row. <br />Note: For delete operation this value is null.</p> </td> </tr> </tbody></table> <p></p> <p><u>Stored Procedures:</u></p> <p>1. [Administration].[usp_EnableAuditLog]</p> <p>Description: Enables Audit for database activities. </p> <table cellspacing="0" cellpadding="2" width="400" border="1"><tbody> <tr> <td valign="top" width="400"><span style="color: #0000ff">CREATE PROCEDURE</span> [Administration].[usp_EnableAuditLog] <br /><span style="color: #0000ff">AS <br />BEGIN</span> <br /><span style="color: #008000">-- All 3 steps are done in transaction-block. <br />-- 1. Create IUD trigger on all ARIC tables. <br />-- 2. Create cleanup job. <br />-- 3. Set Audit flag to true in database.</span> <br /><span style="color: #0000ff">END</span></td> </tr> </tbody></table> <p></p> <p>2. [Administration].[usp_DisableAuditLog]</p> <p>Description: Disables Audit. </p> <table cellspacing="0" cellpadding="2" width="400" border="1"><tbody> <tr> <td valign="top" width="400"> <p><span style="color: #0000ff">CREATE PROCEDURE</span> [Administration].[usp_DisableAuditLog] <br /></p> <span style="color: #0000ff">AS <br />BEGIN</span> <p><span style="color: #008000">-- Both below steps are done in transaction-block. <br />-- 1. Delete all IUD triggers which were used for auditing purpose from all the ARIC tables. <br />-- 2. Delete cleanup job.</span></p> <p><span style="color: #008000">-- 3. Set Audit flag to false in database.</span> <br /><span style="color: #0000ff">END</span></p> </td> </tr> </tbody></table> <p></p> <p><span style="color: #0000ff"></span></p> 3. [Administration].[usp_GetAuditLog] <p></p> <p>Description: Retrieve log depending on specified input parameters. </p> <table cellspacing="0" cellpadding="2" width="400" border="1"><tbody> <tr> <td valign="top" width="400"> <p><span style="color: #0000ff">CREATE PROCEDURE </span>[Administration].[usp_GetAuditLog] <br />@TransactionID <span style="color: #0000ff">BIGINT</span> = NULL, <br />@TableName <span style="color: #0000ff">NVARCHAR</span>(100)= NULL, <br />@UserName <span style="color: #0000ff">NVARCHAR</span>(100)= NULL, <br />@StartDate <span style="color: #0000ff">DATETIME</span> = NULL, <br />@EndDate <span style="color: #0000ff">DATETIME</span> = NULL, <br />@PageSize <span style="color: #0000ff">INT</span> = NULL, <br />@PageNumber <span style="color: #0000ff">INT</span> = NULL, <br />@TotalPages <span style="color: #0000ff">INT OUT</span> <br /></p> <span style="color: #0000ff">AS <br />BEGIN</span> <p><span style="color: #008000">-- This Stored procedure is used by below 4 APIs <br />-- a. Get all changes <br />-- b. Get all today’s changes. <br />-- c. Get all changes for period. <br />-- d. Get all changes for particular transaction. <br />-- Retrieves changes entries as per input criteria specified. <br />-- Entries are returned in pages.</span> <br /><span style="color: #0000ff">END</span></p> </td> </tr> </tbody></table> <p></p> <p><span style="color: #0000ff"></span></p> 4. [Administration].[usp_GetAuditLogEntry] <p></p> <p>Description: Retrieves single log information according to AudiLogID. </p> <table cellspacing="0" cellpadding="2" width="400" border="1"><tbody> <tr> <td valign="top" width="400"> <p><span style="color: #0000ff">CREATE PROCEDURE </span>[Administration].[usp_GetAuditLogEntry] <br />@AuditLogID <span style="color: #0000ff">BIGINT</span> <br /></p> <span style="color: #0000ff">AS <br />BEGIN</span> <p><span style="color: #008000">-- Retrieves single log information according to AudiLogID <br /></span><span style="color: #0000ff">END</span></p> </td> </tr> </tbody></table> <p></p> <p><span style="color: #0000ff"></span></p> 5. [Administration].[usp_Rollback] <p></p> <p>Description: Revert the update change occurred. </p> <table cellspacing="0" cellpadding="2" width="400" border="1"><tbody> <tr> <td valign="top" width="400"> <p><span style="color: #0000ff">CREATE PROCEDURE </span>[Administration].[usp_Rollback] <br />@AuditLogID <span style="color: #0000ff">BIGINT</span> <br /></p> <span style="color: #0000ff">AS <br />BEGIN</span> <p><span style="color: #008000">-- Reverts the update change occurred.</span> <br /><span style="color: #0000ff">END</span></p> </td> </tr> </tbody></table> <p> </p> <p>Other Audit Log Fields can be found out by using In built SQL Server functions  or DMVs. For example,</p> <p><strong>TransactionID: </strong></p> <p><font color="#0000ff">SELECT</font> TRANSACTION_ID <font color="#0000ff">FROM</font> <font color="#008000">SYS.DM_TRAN_CURRENT_TRANSACTION;</font></p> <p><font color="#000000"><strong>ApplicationName</strong>:</font></p> <p><font color="#0000ff">SELECT</font> program_name <font color="#0000ff">FROM</font> <font color="#008000">sys.dm_exec_sessions</font> <font color="#0000ff">WHERE</font> session_id = <font color="#ff00ff">@@SPID</font></p> <p><font color="#000000"><strong>ObjectID</strong>:</font></p> <p><font color="#000000">Here ObjectID is the tableID in which trigger got fired. We calculated it as follows:</font></p> <p><font color="#0000ff">SELECT</font> @ObjectID = parent_id <font color="#0000ff">FROM</font> sys.triggers <font color="#0000ff">WHERE</font> object_id = <font color="#ff00ff">@@PROCID</font>;</p> <p><strong>TSQL</strong>:</p> <p>Here TSQL represents, the query responsible for firing the trigger.</p> <p><font color="#0000ff">DECLARE</font> @ExecStr <font color="#0000ff">NVARCHAR</font>(100); <br /><font color="#0000ff">DECLARE</font> @inputbuffer <font color="#0000ff">TABLE</font>   <br />( <br />          EventType <font color="#0000ff">NVARCHAR</font>(<font color="#ff00ff">MAX</font>),  <br />          Parameters <font color="#0000ff">INT</font>,  <br />          EventInfo <font color="#0000ff">NVARCHAR</font>(<font color="#ff00ff">MAX</font>) <br />) </p> <p><font color="#0000ff">SET</font> @ExecStr = <font color="#ff0000">'DBCC INPUTBUFFER('</font> + <font color="#ff00ff">LTRIM</font>(<font color="#ff00ff">RTRIM</font>(<font color="#ff00ff">STR(@@SPID</font>))) + <font color="#ff0000">')'</font> </p> <p><font color="#0000ff">INSERT INTO</font> @inputbuffer <br /><font color="#0000ff">EXEC</font> (@ExecStr) <br /><font color="#0000ff">SET</font> @TSQL = (<font color="#0000ff">SELECT</font> EventInfo <font color="#0000ff">FROM</font> @inputbuffer);</p> <p><strong>OperationID</strong>:</p> <p><font color="#0000ff">SELECT</font> @DeletedCount = <font color="#ff00ff">COUNT</font>(1) <font color="#0000ff">FROM</font> deleted <br /><font color="#0000ff">SELECT</font> @InsertedCount = <font color="#ff00ff">COUNT</font>(1) <font color="#0000ff">FROM</font> inserted <br /><font color="#0000ff">IF</font> @InsertedCount= 0 <font color="#0000ff">AND</font> @DeletedCount = 0  <br />       RETURN; <br /><font color="#0000ff">SET</font> @OperationID = <font color="#0000ff">CASE WHEN</font> @DeletedCount > @InsertedCount <font color="#0000ff">THEN</font> 1 – DELETE <br />           <font color="#0000ff">WHEN</font> @DeletedCount < @InsertedCount <font color="#0000ff">THEN</font> 2 -- INSERT  <br />           <font color="#0000ff">WHEN</font> @DeletedCount = @InsertedCount <font color="#0000ff">THEN</font> 3 -- UPDATE <br /> <font color="#0000ff">END</font></p> <p>etc…..</p> <p>~ Kiran</p> Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-19165253.post-40530985280500669382009-03-28T06:13:00.000-07:002009-03-28T06:19:20.833-07:00Meeting with Dr. KakarmathLast to last Sunday when I met to Dr. Kakarmath, that was one of the scheduled meeting of Giants International Group.<br /><br />By the way, I am the member of Giants International Group and Dr. Kakarmath is the president of this group for Kalyan Metro. For last several years, the organization is busy in doing social work, such as helping poor but needy and talented students getting free school books and school dresses, tree plantation on open spaces etc. ( I have mentioned only those activities in which i was involved. Actually there are so many to mention that space here will come short.).<br /><br />Dr. Kakarmath has been my family doctor for last 10 years. So we have developed good relationship between us. I met him after Giants meeting. In a flow of talk, I just asked him about his dispensary and asked him to come out of the daily paperwork for keeping patients information.(I was knowing the fact that he keeps one file per family and whenever any family member visits his dispensary; he keeps paper, scribbled after checking the patient and prescription given to him, inside it. That is the way, he keeps patients information.). I queried the reason behind not using the software to keep this information. In reply to that, Doctor told me that he was aware of the softwares available in the market but all softwares were meant for the hospitals or for the dispensaries having beds for the patients.<br /><br />When I see the family doctors in my area, all of them still use diaries or the folders similar to Dr. Kakarmath to keep patients information and their last prescription given. I think small doctors are still away from this Software Revolution. I have promised Dr. Kakarmath to help in this case. and have started working on it. I hope the software which I am implementing will come up as handy tool for him and some of his doctor friends.<br /><br />As a part of the software, I am done with the database design. And really forcing myself to create a very good UI. I am learning WPF but i think, getting mastery in WPF will take some time. So I will start with Windows Forms only. Some of my friends are also having experience in implementing their own softwares.( obviously in different domain.). Their experiences will guide me to frame user friendly and well presented GUI.<br /><br />I will keep on posting my experiences on my blog.<br /><br />~ KiranUnknownnoreply@blogger.com2tag:blogger.com,1999:blog-19165253.post-10639268842461905352009-03-25T03:12:00.001-07:002009-03-28T06:20:57.505-07:00Delete All Databases Except System Databases<p>Many times I require to create databases with different configurations for testing purpose.(This includes databases having special characters in their names, databases with different sizes, databases having data files on different locations. etc). As soon as I finish unit testing of the functionality , I have to delete those many databases manually from SQL Server Management Studio. </p> <p>Today,Two times I had to delete 15 such databases manually :(. But now no more manual work. I have written SQL script for it :)</p> <p><font color="#0000ff">USE</font> [master] <br /><font color="#0000ff">GO</font> </p> <p><font color="#0000ff">DECLARE</font> @database_name <font color="#0000ff">NVARCHAR</font> (4000);</p> <p><font color="#80ff00">-- Collect all user created databases into cursor</font> <br /><font color="#0000ff">DECLARE</font> databases_cursor <font color="#0000ff">CURSOR FOR</font> <br /><font color="#0000ff">SELECT</font> name <font color="#0000ff">FROM</font> <font color="#00ff00">sys.databases</font> as d where d.database_id > 4 <br /><font color="#0000ff">OPEN</font> databases_cursor </p> <p><font color="#0000ff">FETCH NEXT FROM</font> databases_cursor <br /><font color="#0000ff">INTO</font> @database_name </p> <p><font color="#0000ff">WHILE</font> <font color="#ff00ff">@@FETCH_STATUS</font> = 0 <br /><font color="#0000ff">BEGIN</font> <br />    <font color="#0000ff">PRINT</font><font color="#ff8040"> 'Dropping Database '</font> + @database_name <br />    <font color="#0000ff">DECLARE</font> @cmd <font color="#0000ff">NVARCHAR</font>(4000); <br />    <font color="#0000ff">SET</font> @database_name = <font color="#ff00ff">REPLACE</font>(@database_name,<font color="#ff8040">']'</font>,<font color="#ff8040">']]'</font>); <br />    <font color="#0000ff">SET</font> @cmd =<font color="#ff8040"> 'ALTER DATABASE ['</font>+@database_name+<font color="#ff8040">'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ['</font>+@database_name+<font color="#ff8040">'];' <br /></font>    <font color="#0000ff">EXECUTE</font> <font color="#800000">sp_executesql</font> @cmd <br />    <font color="#0000ff">FETCH NEXT</font> <font color="#0000ff">FROM</font> databases_cursor <br />    <font color="#0000ff">INTO</font> @database_name <br /><font color="#0000ff">END</font> <br /><font color="#0000ff">CLOSE</font> databases_cursor <br /><font color="#0000ff">DEALLOCATE</font> databases_cursor</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-54249524764591545892009-01-24T03:20:00.000-08:002009-03-28T06:21:34.659-07:00KeePass : Password ManagerI have so many passwords with me for every website I visit. Other than that there are Windows Network login, Net Banking, Gmail, Yahoo, MSN, My Office Email passwords. Hussss...the list is endless. It was really difficult task for me to keep remembering all those passwords. Many of the times, I was dependable on 'Forget Password' link to reset my password and set new one. And you will agree me, it is really boredom task.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhz6hedDxdAryFnbLd6e0IPXMuv2sDR0Qfm0PTQugTs7X1DoPfzVwGDTMu1ZpHSfqp47Aye_G7guC2HNonfUNn6FDFzp6TTfMndgMwJ_oGzXXZNNmu5sLyMfrZT7Gqp-anekVDu/s1600-h/main_big.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer; width: 400px; height: 275px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhz6hedDxdAryFnbLd6e0IPXMuv2sDR0Qfm0PTQugTs7X1DoPfzVwGDTMu1ZpHSfqp47Aye_G7guC2HNonfUNn6FDFzp6TTfMndgMwJ_oGzXXZNNmu5sLyMfrZT7Gqp-anekVDu/s400/main_big.png" alt="" id="BLOGGER_PHOTO_ID_5294818784150342578" border="0" /></a><br />Thanks to <a href="http://keepass.info/">KeePass</a>. This software has really saved me from this trouble. <a href="http://keepass.info/">KeePass </a>is a free open source password manager, which helps me to manage my passwords in a secure way. You can put all your passwords in one database, which is locked with one master key. So you only have to remember one single master password to unlock the whole database.<br /><br />I am currently using it and very much satisfied with it. :)<br /><br />These <a href="http://keepass.info/features.html">features </a>(http://keepass.info/features.html) and <a href="http://keepass.info/screenshots.html">screenshots </a>(http://keepass.info/screenshots.html) will help the first time users.<br /><br />~ KiranUnknownnoreply@blogger.com3tag:blogger.com,1999:blog-19165253.post-21928157320312175442009-01-09T22:42:00.000-08:002009-01-09T23:02:50.281-08:00Search Engine for Videos!<a href="http://www.truveo.com/">Truveo</a> : I was in search for such site so long. Earlier i used to run around video sharing sites (There are so many. YouTube, MySpace, Google Videos, Metacafe etc) for searching any particular video.<br /><br />This site does this whole thing for me. This search engine lets me find and play videos from all over the web.[:)]<br /><br />~ KiranUnknownnoreply@blogger.com1tag:blogger.com,1999:blog-19165253.post-16974307840649481022009-01-02T23:25:00.000-08:002009-03-28T06:21:34.659-07:00Animoto!There is a lot of fun on the Internet! <a href="http://animoto.com/">Animoto</a> is one of them.<br /><br />This web application turns your pictures into very interesting movie trailers. and you require only 3 steps to get it. Just a) get your images b) get music background and c) provide some details.<br /><br />The only single problem is you can produce, remix, and share videos freely of 30 second length only. But it is worth to try this out. :)<br />Here is my first composition.[:D]<br /><br /><script src="http://widgets.clearspring.com/o/46928cc51133af17/495f14bdf75e0e01/46928cc5552b8140/9e7d5549/-cpid/8b326966c6117f5/autostart/false/widget.js" type="text/javascript"></script>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-15861116963622629442008-12-29T06:51:00.000-08:002009-03-28T06:21:34.659-07:0010 Minute Mail!Today I came across really a fascinating website. <a href="http://10minutemail.com/10MinuteMail/">10 Minute Mail</a>.<br /><br />Many a times you need to provide your email address while downloading any software or file and then respond to a confirmation link to prove it. More often you don’t trust the website or you want to avoid bunch of spam getting after that; you end up not downloading the file\software.<br /><br />This is the one area the website targets. The website provides you a temporary e-mail address. Any e-mails sent to that address will show up automatically on the web page. You can read them, click on links, and even reply to them. The e-mail address will expire after 10 minutes.<br /><br />~ KiranUnknownnoreply@blogger.com1tag:blogger.com,1999:blog-19165253.post-6242084214701789192008-12-23T08:45:00.001-08:002008-12-23T09:51:09.003-08:00Online photo editor!<a href="http://www.pizap.com/application.htm">PiZap</a> is an online photo editor. You can play with some demo photos or upload your own. You can then add text, stickers, change faces and a whole lot more. Here's an example.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmL-d0_rNHZCIEB_1c5RrGWHmgEP5dzzElbaZ0Vt44ycTcnojSZwqjy-u2UrLAyZ0izAFxeo9vsnxczu8HJqoeWb68jSUtltX2ImzSxaHcLPyyLWOe9hEZNEoiebDBNI8SA-gB/s1600-h/Obama.jpg"><img id="BLOGGER_PHOTO_ID_5283027744834009714" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 400px; CURSOR: hand; HEIGHT: 300px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmL-d0_rNHZCIEB_1c5RrGWHmgEP5dzzElbaZ0Vt44ycTcnojSZwqjy-u2UrLAyZ0izAFxeo9vsnxczu8HJqoeWb68jSUtltX2ImzSxaHcLPyyLWOe9hEZNEoiebDBNI8SA-gB/s400/Obama.jpg" border="0" /></a><br /><div>~ Kiran</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-22598538174337279622008-12-23T08:01:00.000-08:002008-12-23T09:51:09.003-08:00Cut on your arm!!<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU7J0eXfEsTNijEThjQvGnDIJIU-qNbR_ShDVv3vw3ui6Ojw27jNstkMqRQUXxFM2qKCK1vflp5DyFK7nMDwFLxdxNvomKoRdOyF309I_whdsadCQdA6-xDAgQoytsrJ5jISVy/s1600-h/cutting.jpg"><img id="BLOGGER_PHOTO_ID_5283017333212463442" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 400px; CURSOR: hand; HEIGHT: 226px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU7J0eXfEsTNijEThjQvGnDIJIU-qNbR_ShDVv3vw3ui6Ojw27jNstkMqRQUXxFM2qKCK1vflp5DyFK7nMDwFLxdxNvomKoRdOyF309I_whdsadCQdA6-xDAgQoytsrJ5jISVy/s400/cutting.jpg" border="0" /></a> Cut anything (lover's name??) you want on your arm!!! <a href="http://says-it.com/cutting/index.php">Go here</a><br /><div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-80734686226180726992008-10-07T22:36:00.000-07:002008-12-23T08:11:50.819-08:00English To Hindi Translation Bots in Google Talk<div>Google Talk Team announced translation bots for many languages to help you translate texts using your Google Talk interface. If you don’t know, a bot is a piece of software that acts as a chat contact and provides some fun or useful functionality.<br />Today morning I added English To Hindi translation bot. It works amazingly. </div><div></div><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6UWL5jM_gU5XE2JO5DAIkeCpeT7EuCCy_qjpjDdsSEhAGb1rBb8RGADY9VXNaFjT-VB3wpFAZHCwCaTaXz1xhHNrB6Fed3DI8CQi6lQEWP__Tg1ROmwQOOd1jDKJ3rHJkTq5oLw/s1600-h/google-bot1.JPG"><img id="BLOGGER_PHOTO_ID_5254654890610585778" style="DISPLAY: block; MARGIN: 0px auto 10px; CURSOR: hand; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6UWL5jM_gU5XE2JO5DAIkeCpeT7EuCCy_qjpjDdsSEhAGb1rBb8RGADY9VXNaFjT-VB3wpFAZHCwCaTaXz1xhHNrB6Fed3DI8CQi6lQEWP__Tg1ROmwQOOd1jDKJ3rHJkTq5oLw/s320/google-bot1.JPG" border="0" /></a><br /><div></div><div></div><div>You should also try this out. You just need to add <a href="mailto:en2hi@bot.talk.google.com">en2hi@bot.talk.google.com</a> in your gtalk contact list.<br />The post below will give you more information:<br /><a href="http://teck.in/language-translation-bots-for-google.html">http://teck.in/language-translation-bots-for-google.html</a><br />~ Kiran</div>Kiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-13047222656187127522008-09-11T22:50:00.000-07:002008-12-23T08:12:16.974-08:00माझे मराठीला योगदान<span class="">Proud to be Marathi? Yes, Then let us be ready to enrich our language with some new ideas,with some new words.<br />In our daily routine many times we use english words as is. The website <a href="http://www.marathishabda.com/shabda/">http://www.marathishabda.com/shabda/</a> is platform to introduce marathi words for them. I have pasted author's preface below as is.</span><br /><span class=""></span><br /><blockquote>कठीण व उगीचच मोठे असलेले, उदा महाविद्यालय, केशकर्तनालय, मराठी शब्द वापरताना फार त्रास होतो ना? अनेक ईंग्लिश शब्द तर आपण जसेच्यातसे वापारतो. कारण त्यांना पर्यायी शब्दच नाहीत.<br />तसेच काही वाक्ये वापरताना गोंधळ उडतो. उदा “मी त्याचा वडील आहे” हे योग्य की नाही? मग काय म्हणायला हवे? प्रश्न पडतो..<br />माझ्या मातृभाषेसाठी मी काहीतरी केले पाहीजे हे नेहमी वाटते ना? चला आपण सगळे मिळून मराठी ला वेगळी ऊंची देउया. ह्या जागेचा वापर करुन आपण ते साध्य करु शकतो.<br />हे कसे करता येइल ते मी तुम्हाला सांगतो. आपल्याला एखादा शब्द खटकला की, त्याचे मराठी बारसं कसे करायचे त्याचा विचार करायला सुरुवात करायची. एखादा पर्यायी शब्द सापडला की, ह्या जागेवर येउन, तो दान करायचा. समजा तुम्ही आणि ईतरांनीही काही दानं दिली असतील तर, त्या वेळी मी एक मतं चाचणी घेइन. ज्या पहील्या ३ शब्दांना जास्त मते असतील ते शब्द मी आपल्या शब्द संचयात नोंदवेन.<br />तुम्ही दिलेल्या दानाबद्दल मी तुम्हाला प्रत्येक निवडलेल्या शब्दाला एक दान देणार. ज्यामुळे तुमच्या “योगदाना” खरे मुल्यं सगळ्यांना ह्या जागेच्या माध्यमातुन दिसेल.<br />आपला एक सामाजिक कट्टा पण आहे. - <strong>http://www.marathishabda.com/forum</strong> ह्या कट्ट्यावर बरीच दिग्गज मंडळी तुम्हाला भेटू शकतील. तुम्ही त्याच्याबरोबर चर्चा करू शकता. मतं चाचणी सुध्दा ईथेच घेतली जाईल. ह्या कट्ट्यावर जाऊन तुम्हाला एक “वापरू” बनवावा लागेल.<br />आता प्रश्न राहीला तो नवे शब्द कसे तयार करायचे ह्याचा. हे पहा- कालच मी एक नवीन शब्द वाचला. हा पर्यायी मराठी शब्द कसा वाटतो पहा.<br /><br /><strong>Ghost fishing: पाताळकोळी, भुत्याकोळी</strong><br />तुम्हीही असे नवीन शब्द सुचवा. आपण सगळे मिळून नवीन मराठी शब्द तयार करू. नवीन शब्द तयार करण्यासाठी मी काही वाटा (Categories) करतोय, उदा, “घरातील शब्द”. आपण इथून सुरूवात करूया. अजुन पुढच्या सुचना मी देइनच.<br /><br /><strong>चला तर या माझ्याबरोबर…</strong></blockquote><br />~ KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com1tag:blogger.com,1999:blog-19165253.post-46342678784072870702008-09-10T04:22:00.000-07:002008-12-23T08:12:16.974-08:00Mail Forwards!!! - My New Blog!This blog will hold all the good forwards i have ever got.<br />Here is the link for it. <a href="http://mymailsforwards.blogspot.com/">Mail Forwards!!!</a><br />Watch out for more and more forwards on this blog. They could be helpful to you pleasing your mates.Kiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-10778486109696159832008-07-30T06:55:00.000-07:002008-08-17T07:15:10.059-07:00Online file conversion...<a href="http://www.zamzar.com/"><strong>Zamzar:</strong></a><br /><br />You may have come across the situation when you have some files and don't have softwares or utilities to run them. Website Zamzar will help you in that case.<br />You just need to upload the file you want to convert and choose the format to convert that file to on the <a href="http://www.zamzar.com/">Zamzar</a> website. Within few minutes you will get link in your mailbox for accessing the converted file.<br /><br />~KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-11341206391571929782008-07-29T23:24:00.000-07:002008-08-17T07:15:17.926-07:00पु.ल. च्या फँन्ससाठी खास मेजवानी!खाली दिलेली लिंक तुम्हाला पु.ल.च्या बर्याचश्या लेखांचे संकलन केलेल्या वेबसाइट वर घेउन जाईल.<br /><a href="http://www.puladeshpande.net/index.php">येथे क्लिक करा</a><br />~ किरणKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com1tag:blogger.com,1999:blog-19165253.post-36458710780110346832008-07-19T08:20:00.000-07:002008-07-21T23:43:16.695-07:00…and finally I have learnt motorcycling!….husssh…and finally I have learnt motorcycling!. Actually it was enjoyable journey while learning it [:)]. I thought, I should share it with you all.<br /><br />Have you ever watched film award functions?? Whenever any actor wins a title, he gives a long speech to thank all of them whoever have contributed in his success. If the same thing is told me to do then first of all I will thank to traffic police (better word “Mamu” [:)]). Why? Why because he has not caught me yet while driving, even if I don’t have driving license with me.<br /><br />Jokes apart. But really if somebody forces me to take their names, then I will take Archana’s and Ankit’s name.<br /><br />I brought up in that family background which has not seen 2 wheeler in its veranda before I buy it.(In last week only, i bought Bajaj Discover). In school-hood, I have learnt cycling. That cycle was the only 2 wheeler we had. But I had not got a chance to learn motorbike.<br /><br />I should thank Archana for giving me confidence in driving her “Scooty Pept” during traffic peak hours. As I had learnt the cycle, I could balance the vehicle very well but there are some small small things which are necessary while driving; she taught me them. She taught me how to use mirrors while driving and how to use indicator buttons while taking turns and etc.<br /><br />Ankit helped me knowing details about the “geared” bike. He taught me, how we can control the bike using its clutch and break combination, changing the gears while driving.<br />One important thing, he trained me to understand bike’s language. [:)]. That makes me identify differences between the noises engine makes while I accelerate and therefore to know whether I should change gear or not.<br /><br />~ KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com5tag:blogger.com,1999:blog-19165253.post-47568433665286558012008-07-17T07:45:00.000-07:002008-07-18T08:07:14.409-07:00Let us conserve water and electricity!Today, as soon as i entered into the office and opened my desktop, a mail flashed on my desktop screen. The subject line was "Humble request". and The sender was "Neha Shaikh". I have recieved so many mails from her to help the child or old person immediately for rare blood group. I always feel there is hidden social worker in her. (Later on, i came to know that she is member of "Vanrai" group which activates against tree cutting.)<br /><br />But today, mail message body was suggesting something different. It was request to all of us to use less water and electricity. The reason for this scarcity of water and electricity was long break in the mansoon leading to reduced water level in dams.<br /><br />Whatever be the reason of it. But i love Neha's proactiveness making people aware to their responsibilites. I also want to put forward my steps in that direction. I loved the last sentence from her mail “Together we can really make a difference.” :). I am pasting excerpt from her mail:<br /><blockquote><em><span style="font-size:85%;">Hi All,<br />As we all are aware that there are no rains since last one and a half months and MSEB has planned to begin load shedding program for Pune, we can definitely do something to conserve water and electricity.<br />This is my humble request for the following:<br />1) Kindly ask everyone to switch off the machines at EOD if their machine is not a server, if<br />anybody’s machine needs to be kept on for project requirements then that is an exception.<br />2) Kindly ask them not to waste water and use only as much as required. Usually we do not consume all the water we fill up in the bottle and the next day we just pour off the water and fill up the bottle again. We can save a plenty amount of water by not doing this.<br />3) Can we save electricity at office by not using all the tube lights and bulbs and only use only as much as we require?? We can at least keep toilet/pantry lights off and can switch them on<br />when we need it and again switch them off after the use.<br />4) Kindly ask people to switch off the conference room lights after the meeting is over. I have observed that people just get out of the room without switching off the lights and the lights are<br />burning even if there is no one inside the room. We can also ask the people to just burn one light in the conf room while in the meeting.<br />5) Last but not the least, we can also request people to follow above at home to conserve water and electricity. I would really appreciate if you forward the mail to your friends/relatives.<br />“Together we can really make a difference.” :)</span></em></blockquote><br />~ KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-12131431273340908202008-07-01T23:22:00.000-07:002008-07-01T23:25:20.680-07:00Tips for a Successful Technical PresentationWhile surfing the internet, i came across a very good article.<br /><a href="http://www.hanselman.com/blog/11TopTipsForASuccessfulTechnicalPresentation.aspx">Tips for a Successful Technical Presentation</a><br /><br />~ KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-88882998817356413092008-06-04T23:26:00.000-07:002008-06-05T00:12:24.452-07:00Disconnect all users from databaseThis is to reply Mohit's comment on my yesterday's post "<a href="http://kiranmarke.blogspot.com/2008/06/forcefully-disconnect-all-users-and.html">Forcefully Disconnect All Users and Drop a Database</a>" . I thought it would be nice to have it as new post rather than just a comment.<br /><br />He had mentioned one scenario where there are times when you just have to disconnect other users from the database. We can acheive it by just killing all the processes connected to that particular database. I have written small stored procedure to do this:<br /><br /><span style="color:blue;">CREATE PROCEDURE</span> dbo.removeConnections<br />@database_name <span style="color:blue;">SYSNAME</span><br /><span style="color:blue;">AS</span><br /><span style="color:blue;">BEGIN</span><br /><span style="color:blue;">DECLARE</span> @spid <span style="color:blue;">INT</span><br /><span style="color:blue;">DECLARE</span> @query <span style="color:blue;">NVARCHAR</span>(255)<br /><br /><span style="color:blue;">DECLARE</span> processes <span style="color:blue;">CURSOR FOR</span><br /><span style="color:blue;">SELECT</span> spid <span style="color:blue;">FROM master</span>..sysprocesses<br /><span style="color:blue;">WHERE dbid</span> = <span style="color:fuchsia;">DB_ID</span>(@database_name)<br /><span style="color:blue;">AND</span> spid != <span style="color:fuchsia;">@@SPID</span><br /><br /><div align="left"><span style="color:blue;">OPEN</span> processes</div><span style="color:blue;">FETCH NEXT FROM</span> processes<br /><span style="color:blue;">INTO</span> @spid<br /><br /><span style="color:blue;">WHILE</span> <span style="color:fuchsia;">@@FETCH_STATUS</span> = 0<br /><span style="color:blue;">BEGIN</span><br /><br /><span style="color:blue;">PRINT</span> <span style="color:red;">'KILLing '</span>+<span style="color:fuchsia;">RTRIM</span>(@spid)<br /><span style="color:blue;">SET</span> @query = <span style="color:red;">'KILL '</span>+<span style="color:fuchsia;">RTRIM</span>(@spid)<br /><span style="color:blue;">EXEC</span>(@query)<br /><br /><span style="color:blue;">FETCH NEXT FROM</span> processes<br /><span style="color:blue;">INTO</span> @spid<br /><br /><span style="color:blue;">END</span><br /><span style="color:blue;">CLOSE</span> processes<br /><span style="color:blue;">DEALLOCATE</span> processes<br /><span style="color:blue;">END</span><br /><span style="color:blue;">GO</span><br /><br />~ KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com0tag:blogger.com,1999:blog-19165253.post-83243066387382616172008-06-03T23:22:00.000-07:002008-06-03T23:43:36.367-07:00Forcefully Disconnect All Users and Drop a DatabaseYesterday, one of my colleague was telling me that he was not able to delete the database. When I checked the issue at his desk, I saw that other users were connected to that database without his knowledge and hence he was getting error while deleting it.<br /><br />One way to get rid of it immediately:<br /><br /><span style="color:blue;">ALTER DATABASE</span> MyDatabase<br /><span style="color:blue;">SET SINGLE_USER WITH ROLLBACK IMMEDIATE</span><br /><span style="color:blue;">DROP DATABASE</span> MyDatabase<br /><br />~ KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com2tag:blogger.com,1999:blog-19165253.post-79976487990589280192008-05-22T23:09:00.000-07:002008-05-23T00:55:25.912-07:00Confusing @@VERSION?I was confused when i first ran the <span style="color:blue;">SELECT</span> <span style="color:fuchsia;">@@VERSION</span> query and saw the result out of that query. Try to run the following query in SSMS:<br /><br /><span style="color:blue;">PRINT</span> <span style="color:fuchsia;">@@VERSION</span><br /><span style="color:blue;">GO</span><br /><span style="color:blue;">PRINT</span> <span style="color:#ff0000;">'Edition: '</span> + <span style="color:fuchsia;">CONVERT</span>(<span style="color:blue;">CHAR</span>(30), <span style="color:fuchsia;">SERVERPROPERTY</span>(<span style="color:#ff0000;">'Edition'</span>))<br /><span style="color:blue;">GO</span><br /><span style="color:blue;">PRINT</span> <span style="color:#ff0000;">'Product Version: '</span> + <span style="color:fuchsia;">CONVERT</span>(<span style="color:blue;">CHAR</span>(20), <span style="color:fuchsia;">SERVERPROPERTY</span>(<span style="color:#ff0000;">'ProductVersion'</span>))<br /><span style="color:blue;">GO</span><br /><span style="color:blue;">PRINT</span> <span style="color:#ff0000;">'Product Level: '</span> + <span style="color:fuchsia;">CONVERT</span>(<span style="color:blue;">CHAR</span>(20),<span style="color:fuchsia;">SERVERPROPERTY</span>(<span style="color:#ff0000;">'ProductLevel'</span>))<br /><span style="color:blue;">GO</span><br /><br /><strong>Result:</strong>-<br />---------------------------------------------------------<br />Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)<br />Feb 9 2007 22:47:07<br />Copyright (c) 1988-2005 Microsoft Corporation<br />Enterprise Edition on Windows NT 5.2 (<span style="color:#ff0000;">Build 3790: Service Pack 2</span>)<br />Edition: Enterprise Edition<br />Product Version: 9.00.3042.00<br />Product Level: SP2<br /><br />I have highlighted the confusing part in <span style="color:#ff0000;">RED</span>. This is Windows NT Service Pack Level, Not the SQL service Pack Level.<br /><br />The reason i went to conclusion that the output provided by the query is very detailed one but i guess they missed to include sql server pack infrmation there.[;)]. Anyway we can get that information through <span style="color:fuchsia;">SERVERPROPERTY</span>(<span style="color:#ff0000;">'ProductLevel'</span>).<br /><br />~ KiranKiran Markehttp://www.blogger.com/profile/11698883723421730106noreply@blogger.com0