SQL Truncate vs. Delete

By now as you know, in this post I am going to discuss about two SQL commands we use regularly to clear data in our tables.

Usage

1. DELETE FROM TABLENAME
3. TRUNCATE TABLE TABLENAME

Even though they do a similar thing, there are some differences which makes them unique commands.

	DELETE	TRUNCATE
1.	Considered as Data Mining Language (DML) statement.	Considered as Data Definition Language (DDL) statement.
2.	Can be used to delete all or part of data in a table.	Will delete all the data in a table.
3.	Will log the actions.	There will be no logging on record deletion.
4.	Will use more locks.	Will use fewer locks.
5.	Will use more resources.	Will use less resources.
6.	Slow.	Fast.
7.	Will not reset the identity seed.	Will reset the identity seed to 0.
8.	Can be used to delete data in tables having relationships.	Cannot use in tables having relationships.
9.	Can be used in tables involved in log shipping or replication.	Cannot be used in tables involved in log shipping or replication.
10.	Transaction can be rolled back.	No rollback.
11.	Table may keep the empty pages. Can be released by running, SHRINKDATABASE (Database Name).	Data pages related to the table will be de allocated and returned to the system.
12.	Related Triggers are fired.	Triggers are not fired.

 

If you used DELETE to fully remove all the records, you can reset the identity value by running the following command.

1. DBCC CHECKIDENT (TABLENAME, RESEED, 0)

If you do have some rows left in table, simply replace 0 with the last identity column value. For example if you put 5 instead of 0 then the next record inserted into the table will have an identity column value of 6.

If you jus need to check the current identity value just use the following command.

1. DBCC CHECKIDENT (TABLENAME, NORESEED)

Beerenberg Strawberry Farm – Hahndorf South Australia

If you like Strawberries and would like to pick your own, then there is a good place you can do it in Hahndorf South Australia.

It is called Beerenberg Strawberry farm, located on the Mount Barker road. If you are planning to visit them to pick your own strawberries you need to visit them from October to May which is the Strawberry season. Else you can buy their produce from the shop.

Normally they are open everyday except Christmas day from 9:00AM to 04:00PM. Anyway it is always good idea to give them a call before going.

There is enough parking on site and there will be a helper on busy days.

Once you are there you can visit the shop to get permission to enter the patch. When I was there entry was $ 3 per person. They will also provide containers to bring the Strawberries you pick. Remember to pick whatever amount of Strawberries you would like to buy. Normally you can keep them from few days to about 10 days in fridge (obviously you can keep bit more days than this but then they wont be tasting fresh). Once you have enough Strawberries picked you can bring them back to the shop where they will weigh it, so you can pay. While I was there they sold a Kilo of Strawberry for $ 9, but the price always changes from season to season, so it is always best to check the price they are selling before heading to the Strawberry patch.

If you are travelling with young children, I do not recommend a pram since the ride will be bumpy, best is to use a child carrier sling.

Address: Beerenberg Strawberry Farm,
Mount Barker Road Hahndorf,
South Australia, 5245
Phone: +61 8 8388 7272
Fax: +61 8 8388 1108
Email: admin@beerenberg.com.au
Web: http://www.beerenberg.com.au/

Beerenberg Strawberry Farm VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

Importing Data into SQL

If you need to import data from a file, this can be achieved by using SQL Bulk Insert command. Recently I did use this method to import some 500 000 data from few comma separated value (CSV) files and thought to share it with you. What you need to remember is, if you are importing data from more than one file the data should have the same format through out the files.

This is a sample set of data I imported into my table from the file named File1.csv.

Login,Name,Date,Result,Pass

U0001,Roman Silva,1/10/2010 17:23,100,TRUE

U0002,Anthony Don,28/09/2010 10:01,70,TRUE

U0003,Saman Perera,16/09/2010 11:31,90,TRUE

U0004,Silvia Raz,26/09/2010 22:11,40,FALSE

U0005,Rebecca Maine,18/09/2010 11:30,100,TRUE

I used the following script to create a temporary table for my imported data.

1. SET ANSI_NULLS ON
2. GO
3.  
4. SET QUOTED_IDENTIFIER ON
5. GO
6.  
7. CREATE TABLE [dbo].[TABLENAME_ImportedData](
8.     [LoginId] [NVARCHAR](50) NOT NULL,
9.     [Name] [NVARCHAR](200) NULL,
10.     [Date] [DATETIME] NULL,
11.     [Result] [INT] NULL,
12.     [Pass] [NVARCHAR](8) NULL
13. ) ON [PRIMARY]
14.  
15. GO

To fetch data from the file I used the following script.

1. BULK INSERT [TABLENAME_ImportedData]
2. FROM 'D:\DataFiles\File1.csv'
3. WITH (
4.          FIELDTERMINATOR =',', -- Since my columns are seperated using commas (,).
5.          ROWTERMINATOR ='\n',  -- Since each data row is in its own line.
6.          FIRSTROW = 2          -- Since my first row is having column names. Please note FIRSTROW is not recommended to skip the column names.
7.       )

While running the script I faced an issue with the date since my server was set to US English as the default language. In US English the dates should be in MDY format. So I had 3 choices, either to change the date formats on my data files, change the server default language to another language which has its date format as DMY or change the date format of the server. I used the easy way to change the date format of the server by running  the following command.

1. SET DATEFORMAT dmy

If you like to change SQL server default language and need help please read my article on that.

Changing SQL Server Default Language

If you needed to change the default language of a SQL Server login you can do so by using sp_defaultlanguage command.

First determine the current default language by using the @@language variable as follows.

1. SELECT @@language

Then choose the language you want to change to from the available languages. You can find the available languages by the following command.

1. EXEC sys.sp_helplanguage

After selecting the language wanted to change, run the following command to change the default language, I am changing it to British using the following command.

1. DECLARE @Login NVARCHAR(30)
2. SET @Login = SYSTEM_USER
3. EXEC sp_defaultlanguage @Login, 'British'

Run the @@language to see whether the default language is changed as you wanted. Do not forget to use a new query window to see the changes.

Change Quick Access Default Application Icons on Samsung Galaxy S2

If you ever wanted to change the quick access default application icons on Samsung Galaxy S2 you can do this by going to applications screen. For example as seen in the following image we have Phone, Contacts, Messaging and Applications as the default set of icons.

To change this first go to the applications page by touching Applications.

Then touch the menu button on the phone to activate the menu and select Edit. This will take you to edit screen view. Now it is simple as dragging the application icon you want on your quick access to the icon you need to be replaced.

When you are satisfied, touch on the back button to save the changes. If you need to ignore the changes press the middle button.

As seen in the following images I have replaced the Contacts with Play Movies. Also if you want you keep only 3 or less icons in quick access you can achieve this by deleting unwanted icons using the edit view.

Beetaloo Reservoir – Beetaloo South Australia

If you like to enjoy nature like me another place you can visit in South Australia is the Beetaloo Reservoir in Beetaloo which is managed by SA Water. It is about 220 kms from Adelaide. It is a reservoir built in the year of 1885 with a capacity of 3150 mega liters of water and having a catchment area of 4856 hectares. Its wall is 33.53 meters high and the maximum water level could reach 23.88 meters.

After parking your car you need to walk for about 10 minutes to visit the lookout area. Walk is not difficult, but if it rains some areas might be slippery. You do not need a 4WD to reach Beetaloo Reservoir. Its gate is open from 9:00 AM to 4:00 PM each weekday and on Saturday, Sunday and public holidays open till 5:00 PM.

Beetaloo Reservoir VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

Handler was not added through the Sys.UI.DomEvent.addHandler method.

Sometime back I started getting this error in all the forms in which AJAX tools were used. The thing worried me most is that there were no changes made to the project source code. Later a friend of mine found that it is happening because of the wrong AjaxControlToolkit.

As I feel this dll swap happens when a control is dragged from the Visual Studio toolbox because the toolbox is referenced to a newer version of the toolkit than the project was using.

If you are also getting this error simply delete all the AJAX related files and folders in your projects’ bin directory. Once you are done there should not be any folders such as ar, cs, de, etc. Also remove the AjaxControlToolkit.dll. Then copy the version of the toolkit dll your project was using earlier to the Bin directory. Now clean and build your project to see the error disappear.

If you are still getting the error after correctly doing all this you might be getting the error due to another reason causing the same error. Since there are many reasons for this same error you better check the internet for other reasons to find out the exact reason causing the error for you.

Correcting AJAX Calendar Extender Popup Calendar Position

Recently I encountered a positioning error in AJAX calendar extender. When I use the calendar extender inside of other container controls the popup calendar started appearing few inches above the button. You will be able to get an idea of the problem by the following image.

Since I couldn’t get it fixed by changing the properties I thought to find a solution for this.

One way to correct this is by applying a CSS style sheet to change the calendar positioning manually. I found this method while searching the web. This way since you need to enter the location of the calendar you need to try several times to get the correct positioning. If you are using this method simply place the following CSS style in your page and apply the style as shown. Remember you need to change the value to suit your form.

1. <style type="text/css">
2.     .fromDtPos
3.     {
4.         left: 245px !important;
5.     }
6. </style>

Apply the style to your calendar extender.

1. <cc2:calendarextender id="calExpiry" runat="server" targetcontrolid="txtExpiry"
2.     format="dd MMM yyyy" popupbuttonid="imgExpiry" enabled="True" cssclass="ajax__calendar fromDtPos">
3. </cc2:calendarextender>

My preferred way to do this is by using the JavaScript that I wrote below. Since you do not need to enter the position manually this will be easier. Also this code will work irrespective of the number of parent containers it is having above of the control.

Insert the below JavaScript into your page.

1. <script type="text/javascript" language="javascript">
2.     function showCalendar(sender, args) {
3.         var processingControl = $get(sender._button.id); // Getting the control which triggered the calendar.
4.         sender._popupDiv.parentElement.style.top = processingControl.offsetTop + processingControl.height + 'px';
5.         sender._popupDiv.parentElement.style.left = processingControl.offsetLeft + 'px';
6.  
7.         var positionTop = processingControl.height + processingControl.offsetTop;
8.         var positionLeft = processingControl.offsetLeft;
9.         var processingParent;
10.         var continueLoop = false;
11.  
12.         do {
13.             // If the control has parents continue loop.
14.             if (processingControl.offsetParent != null) {
15.                 processingParent = processingControl.offsetParent;
16.                 positionTop += processingParent.offsetTop;
17.                 positionLeft += processingParent.offsetLeft;
18.                 processingControl = processingParent;
19.                 continueLoop = true;
20.             }
21.             else {
22.                 continueLoop = false;
23.             }
24.         } while (continueLoop);
25.  
26.         sender._popupDiv.parentElement.style.top = positionTop + 'px';
27.         sender._popupDiv.parentElement.style.left = positionLeft + 'px';
28.     }
29. </script>

Then call the function showCalendar on onClientShown event of the calendar extender as seen below.

1. <cc2:calendarextender id="calExpiry" runat="server" targetcontrolid="txtExpiry"
2.     format="dd MMM yyyy" popupbuttonid="imgExpiry" enabled="True" onclientshown="showCalendar">
3. </cc2:calendarextender>

Both of the above methods will correct the appearance of the popup calendar of the AJAX Calendar Extender as seen below.

Aldinga Beach – South Australia

If you are a beach person then Aldinga beach is a place not to miss. One speciality in this South Australian beach is that you can take your vehicle into the beach and have a drive. Since the sand is hard on this beach you do not need a 4WD, a normal 2WD car will do the job, but do not get over excited since then there is a chance you can get into trouble.

Without trying to explain using words I will upload some photos I have taken since a picture is worth thousand words.

Aldinga Beach VIEW SLIDE SHOW DOWNLOAD ALL

While we were there, we experienced an extraordinary scene provided by mother nature. Suddenly on top of wet sand we started seeing sand flying with wind for few minutes. Just enjoy it by watching the following video.

Aldinga Beach – Sandy Wind

Map

View Larger Map

HTTP Error 500.19 – Internal Server Error

Recently in one of my machines I got the above HTTP error when running an application hosted on IIS 7. The detailed error was as below.

“This configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault="Deny"), or set explicitly by a location tag with overrideMode="Deny" or the legacy allowOverride="false".”

After doing some troubleshooting I was managed to get this error fixed by reinstalling ASP.Net by running the aspnet_regiis –ir command.

For more information on running the command please refer to my old article Unable to start debugging on the web server.

Styles missing in CalendarExtender

When you put an Ajax Calendar Extender inside of a GridView you will see the calendar without any styling on it meaning the calendar will show only the dates overlapping with other items on your form. Sometimes some dates might even be missing. See the sample appearances below.

This happens because the style sheets are not loaded at the correct time due to a bug in toolkit, there are several ways to fix this.

One is to add another calendar extender outside of the update panel and keep it hidden.

Another is to disable partial rendering in the script manager. But this will slow down your site reducing the benefits gained from AJAX.

1. <asp:ScriptManager ID="ScriptManager1" EnablePartialRendering="false" runat="server">
2. </asp:ScriptManager>

My chosen method is to add the styles manually to the style sheet. To move forward this way simply copy the following styles which are used by the calendar extender to your style sheet.

1. .ajax__calendar_container {padding:4px;cursor:default;width:170px;font-size:11px;text-align:center;font-family:tahoma,verdana,helvetica;}
2. .ajax__calendar_body {height:139px;width:170px;position:relative;overflow:hidden;margin:auto;}
3. .ajax__calendar_days, .ajax__calendar_months, .ajax__calendar_years {top:0px;left:0px;height:139px;width:170px;position:absolute;text-align:center;margin:auto;}
4. .ajax__calendar_container TABLE {padding:0px;margin:0px;font-size:11px;}
5. .ajax__calendar_container TD {padding:0px;margin:0px;font-size:11px;}
6. .ajax__calendar_header {height:20px;width:100%;}
7. .ajax__calendar_prev {cursor:pointer;width:15px;height:15px;float:left;background-repeat:no-repeat;background-position:50% 50%;background-image:url(../images/arrow-left.gif);}
8. .ajax__calendar_next {cursor:pointer;width:15px;height:15px;float:right;background-repeat:no-repeat;background-position:50% 50%;background-image:url(../images/arrow-right.gif);}
9. .ajax__calendar_title {cursor:pointer;font-weight:bold; margin-left:15px; margin-right:15px;}
10. .ajax__calendar_footer {height:15px;}
11. .ajax__calendar_today {cursor:pointer;padding-top:3px;}
12. .ajax__calendar_dayname {height:17px;width:17px;text-align:right;padding:0 2px;}
13. .ajax__calendar_day {height:17px;width:18px;text-align:right;padding:0 2px;cursor:pointer;}
14. .ajax__calendar_month {height:44px;width:40px;text-align:center;cursor:pointer;overflow:hidden;}
15. .ajax__calendar_year {height:44px;width:40px;text-align:center;cursor:pointer;overflow:hidden;}
16.  
17. .ajax__calendar .ajax__calendar_container {border:1px solid #646464;background-color:#ffffff;color:#000000;}
18. .ajax__calendar .ajax__calendar_footer {border-top:1px solid #f5f5f5;}
19. .ajax__calendar .ajax__calendar_dayname {border-bottom:1px solid #f5f5f5;}
20. .ajax__calendar .ajax__calendar_day {border:1px solid #ffffff;}
21. .ajax__calendar .ajax__calendar_month {border:1px solid #ffffff;}
22. .ajax__calendar .ajax__calendar_year {border:1px solid #ffffff;}
23.  
24. .ajax__calendar .ajax__calendar_active .ajax__calendar_day {background-color:#edf9ff;border-color:#0066cc;color:#0066cc;}
25. .ajax__calendar .ajax__calendar_active .ajax__calendar_month {background-color:#edf9ff;border-color:#0066cc;color:#0066cc;}
26. .ajax__calendar .ajax__calendar_active .ajax__calendar_year {background-color:#edf9ff;border-color:#0066cc;color:#0066cc;}
27.  
28. .ajax__calendar .ajax__calendar_today .ajax__calendar_day {border-color:#0066cc;}
29. .ajax__calendar .ajax__calendar_today .ajax__calendar_month {border-color:#0066cc;}
30. .ajax__calendar .ajax__calendar_today .ajax__calendar_year {border-color:#0066cc;}
31.  
32. .ajax__calendar .ajax__calendar_other .ajax__calendar_day {background-color:#ffffff;border-color:#ffffff;color:#646464;}
33. .ajax__calendar .ajax__calendar_other .ajax__calendar_year {background-color:#ffffff;border-color:#ffffff;color:#646464;}
34.  
35. .ajax__calendar .ajax__calendar_hover .ajax__calendar_day {background-color:#edf9ff;border-color:#daf2fc;color:#0066cc;}
36. .ajax__calendar .ajax__calendar_hover .ajax__calendar_month {background-color:#edf9ff;border-color:#daf2fc;color:#0066cc;}
37. .ajax__calendar .ajax__calendar_hover .ajax__calendar_year {background-color:#edf9ff;border-color:#daf2fc;color:#0066cc;}
38.  
39. .ajax__calendar .ajax__calendar_hover .ajax__calendar_title {color:#0066cc;}
40. .ajax__calendar .ajax__calendar_hover .ajax__calendar_today {color:#0066cc;}
41.  
42. /* styles for invalid dates as defined by startDate and endDate*/
43. .ajax__calendar .ajax__calendar_invalid .ajax__calendar_day {background-color:#ffffff;border-color:#ffffff; color:#646464; text-decoration:line-through; cursor:default;}
44. .ajax__calendar .ajax__calendar_invalid .ajax__calendar_month {background-color:#ffffff;border-color:#ffffff; color:#646464; text-decoration:line-through; cursor:default;}
45. .ajax__calendar .ajax__calendar_invalid .ajax__calendar_year {background-color:#ffffff;border-color:#ffffff; color:#646464; text-decoration:line-through; cursor:default;}
46.   .ajax__calendar .ajax__calendar_invalid .ajax__calendar_today{visibility:hidden; cursor:default;}

Note that the above CSS uses the “arrow-left.gif” and “arrow-right.gif” for the previous and next buttons. If you want, you can use any other image which suits you. In case you need the originals they are below. To correctly show the previous and next buttons you need to place these images on to the “Images” folder under your project. If the folder is different in your project please change the image paths in the above CSS.

• arrow-left.gif -
• arrow-right.gif -

After doing all this remember to link the style sheet to your web page.

1. <link href="Styles/Site.css" rel="stylesheet" type="text/css" />

If you have done everything correctly your calendar will be shown properly while residing inside of the grid.

Reloading a Page using JavaScript

Ways to reload a page using Java script.

When doing web development if you need to reload / refresh a page using Java script, you can use one of the following three methods.

1. <script language="javascript" type="text/javascript">
2.     function ReloadMethod1() {
3.         window.location.href = window.location.href;
4.     }
5.     function ReloadMethod2() {
6.         window.location.reload();
7.     }
8.     function ReloadMethod3() {
9.         history.go(0);
10.     }
11. </script>
12. <input type="button" value="Reload" onclick="window.location.href=window.location.href" />
13. <%--Calling Javascript function on button click.--%>
14. <input type="button" value="Reload" onclick="ReloadMethod2()" />
15. <input type="button" value="Reload" onclick="window.location.reload()" />
16.  
17. <!-- ASP Buttons -->
18. <asp:Button ID="Button1" runat="server" Text="Reload" OnClientClick="window.location.href=window.location.href" />
19. <%--Calling Javascript function on button click.--%>
20. <asp:Button ID="Button2" runat="server" Text="Reload" OnClientClick="ReloadMethod2()" />
21. <asp:Button ID="Button3" runat="server" Text="Reload" OnClientClick="ReloadMethod3()" />

Enabling Windows Aero

Recently suddenly my computer lost Windows Aero effects. After trying out many things I found that it is due to the Log Me In mirror driver.

It seems Log Me In mirror driver is not compatible with Windows Aero effects so if you also lost Windows Aero and you have Log Me In installed on your machine to activate Aero back first try disabling “LogMeIn Mirror Driver” and restart your machine.

This can be done by going to Device Manager by clicking,

Start –> Control Panel –> Administrative Tools.

Then double click on Computer Management. Now in the Computer Management window click on the Device Manager.

Another way to reach the Device Manager is by pressing the Windows key and the Pause key in your keyboard simultaneously.

Now expand the Display Adapters and right click on LogMeIn Mirror Driver and click on Disable.

This will disable the Log Me In mirror driver enabling Windows Aero after you restart your machine.

If you cannot see Aero effects after the restart search for the word Aero in your start menu and click on the Aero troubleshooter and follow the wizard to get Aero effects back.

Creating Database Table and Moving Data

When the need comes to port tables from one server / database to another server / database there is an easy way than creating the tables manually and importing data into table.

 

By using the following query you can import the table structure and the data of the required table.

1. SELECT *
2. INTO [DestinationServerName\SQLServerInstanceName].[DestinationDatabaseName].[OwnerName].[DestinationTableName]
3. FROM [SourceServerName\SQLServerInstanceName].[SourceDatabaseName].[OwnerName].[SourceTableName]

 

Using the above method you can transfer tables between different databases, Server instances or different database servers.

Consider the following example in which I am transferring the Customers table from Northwind database to my Test database.

 

1. SELECT *
2. INTO [Test].[dbo].[Customers]
3. FROM [Northwind].[dbo].[Customers]

 

Here SELECT statement will behave the same way as in a normal SELECT * FROM TableName statement meaning you can use all the techniques used in SELECT statements to filter the data needed to be ported. For example following query will only bring customers who are from UK.

1. SELECT *
2. INTO [Test].[dbo].[Customers]
3. FROM [Northwind].[dbo].[Customers]
4. WHERE Country = 'UK'

 

One thing to remember is, this method will not import the keys, meaning if you had a primary key set to CustomerId it will not exist in your new table. So you need to add the keys (primary and secondary) to the new table to match it with the source table. If you had identity specification turned on in your source table this will set it for you, so you do not need to set it again.

Painted Desert – Arckaringa South Australia

One of the most famous places in Australian outback is the painted desert. Which consists of different beautiful landscapes which was formed millions years ago while it way covered by water when it was under the sea. Since these attractions are in remote locations for some of us it will be difficult to find them. I agree some of the attractions are in the guide books but I assure you they are not that easy to find when you are on your own without any phone facilities or mobile data connections to browse maps. So for people who are interested in visiting these places I hope this article will help them to locate some of the attractions. Remember to cache your GPS or maps before you leave town or simply buy a detailed map.

All these places are accessible through unsealed / dirt roads and it is always better if you have an AWD or a 4WD. But if you are travelling in dry whether and if you are cautious you will be able to visit all of them in a 2WD as well. Few other things to remember when going to outback places are mentioned in my other post.

The Breakaways

The breakaways has colourful small hills which are broken away from Stuart Range, hence their name “The Breakaways”

You will not miss the road to the lookout since you need to turn right just before the Dingo fence when you travel from Coober Pedy to Oodnadatta. There will also be road signs for this.

The Breakaways VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

Dingo Fence

Dingo fence or Dog fence is holding the title of longest man made object in the world having a length of 5614 kms. This runs from Surfaces Paradise in Queensland to Bight in near Western Australia border. This was put up to protect the live stock on the south side of the country from the northern predators.

You can have a look into this if you travel about 15 kms from Cobber Pedy to Oodnadatta.

Dingo Fence VIEW SLIDE SHOW DOWNLOAD ALL

 

Map

View Larger Map

Painted Desert

Painted desert lookout is also giving you a great view onto the vivid coloured mountains. There is a walk of about 15-20 minutes after parking your car. The way is marked in a nature friendly manner by collecting stones to make up arrows as seen in my photos. Some parts of the walk is bit challenging so if you do not like walking or afraid of heights or slopes best is to wait in the car.

Painted Desert VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

On your way to these you will encounter other interesting natures creations as well.

On the Way to Painted Desert VIEW SLIDE SHOW DOWNLOAD ALL