Finding Number of Records in SQL Tables

By using the following query you will be able to get all the tables with at least one record in the selected database. It will be handy when you needed to get an idea of the number of records in a database. This uses two SQL Server system views, namely SysObjects and SysIndexes.

1. SELECT sysobjects.name AS [Table Name],
2.         MAX(sysindexes.rows) AS [Number of Records],
3.         sysobjects.crdate AS [Created Date],
4.         sysobjects.refdate AS [Referenced Date]
5. FROM sysobjects
6. INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
7. WHERE sysobjects.xtype = 'U' -- Filtering all the User Tables.
8.     AND sysindexes.rows > 0 -- Getting all the tables having at least one reoord.
9. GROUP BY sysobjects.name,
10.     sysobjects.crdate,
11.     sysobjects.refdate
12. ORDER BY 2 DESC -- Ordering by the number of records in table.

 

Above query will bring the following results on the Northwind database.

Resetting Windows XP Mode

Recently I had a problem using Windows XP mode simply because the password for Windows XP mode was not working.This can happen when Windows XP mode password was changed in Windows XP Mode or when the Windows XP Mode was created from another user account to the one currently logged into the physical machine.

One way to fix this is by opening Windows XP Mode Windows Virtual PC Settings and deleting saved credentials. But in my case there were no saved credentials so my button was disabled.

Second way to fix this is by uninstalling Windows XP Mode and reinstalling it.

Third way is a quicker way to do this by simply cleaning the virtual machine files in folder “C:\Users\Administrator\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines”. Make sure you are only deleting the files relevant to Windows XP Mode.

Then when you relaunch Windows XP mode, it will say that some of the required files are missing and will prompt to create a new environment. When you press “Create New” it will start the wizard to create a brand new Windows XP Mode.

If it generates any errors, you should try deleting the virtual machine file found in “<User Folder>\Virtual Machines”

After finishing the wizard you will get a new Windows XP Mode.

HTTP Error 404.13 – Not Found – The request filtering module is configured to deny a request that exceeds the request content length.

I recently got this error in one of my applications. When troubleshooting, I found my application is configured to have the default value of 30000000 Bytes for the content length, which was not sufficient for me in some of the file uploading functionalities.

Simply adding / increasing the number in the configuration file corrected this error.

Request Content Length in Bytes.

Default Value – 30000000 Bytes (~28MB)

Maximum Value – 4284229877 Bytes (3.99GB)

1. <system.webServer>
2.   <security>
3.     <requestFiltering>
4.       <requestLimits maxAllowedContentLength="100000000"></requestLimits>
5.     </requestFiltering>
6.   </security>
7.   ...
8. </system.webServer>

 

To obtain more information read this article.

Ruston’s Roses – Renmark South Australia

If you like to enjoy visiting the Australia’s largest Rose garden then you need to travel to Renmark in South Australia. The place is named Ruston’s Roses, they maintain a Rose garden of 50000 bushes in 4000 varieties. I have been there in off peak season but there were few flowers blooming so I expect it will be full of flowers in season.

To make it an interesting place for everyone Ruston’s got a small car museum as well. Till the ladies enjoy the garden gents can enjoy the cars on display. Not to mention the gift / souvenir shopping possibilities at the place. When you are tired after all that you can enjoy a light meal from the restaurant.

You will find plenty of places to park your car freely. The place is pram and disabled friendly and in off peak season admittance is free. In peak season I think they charge $5 for an adult entry. Visiting them wont be a big issue since they are open everyday of the year from 10:00 AM to 4:00 PM. Find out more by contacting them.

Address: Ruston's Roses, Moorna Street, Renmark SA 5341.
Phone: 08 8586 6191
EMail: anne@rustonsroses.com
Web: www.rustonsroses.com

Ruston’s Roses VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

Samsung Galaxy S4 not Connecting to Car Audio Fix

Few months back suddenly my Toyota refused to connect to my Samsung Galaxy S4 without any reason. Earlier I could listen to music, dial calls and answer calls without issues.

But suddenly the connection between the car and the phone broke and the worst thing is I lost entire Bluetooth screen from the car audio system (Normally I have FM1, FM2, Bluetooth and AM). I thought my audio system is malfunctioning so I went to Toyota, but they said it is a known error and Galaxy S4 is having new Bluetooth version and it is not compatible with most of the car audio systems and I will not be able to use the phone with the car. This was very disappointing to me so I asked for them for the audio system update but they said no updates are available.

Then I thought to try out few things and get it fixed by myself, good news for anyone having the same issue is I solved the issue. But it is not always reliable but 95% of times it will work.

To get your S4 (or any other new device) connected into your car audio follow the steps below, they might help you.

1. First we need to get the missing Bluetooth mode (screen) back in the car audio, for this I removed all the registered devices I had in the car audio.
2. Then I registered my old Windows Mobile phone to the audio system using the settings screen (since the Bluetooth mode is not available). Any old phone like an iPhone 4G or below, Samsung Galaxy S2, Windows Mobile phones will be ok. If you get the device properly registered you will get the Bluetooth screen back when you restart the car audio system.
3. Then register your Galaxy S4 with the car audio system, and change the connection behaviour immediately. Normally the car audio will try to connect to the phone, but since the phone is having new version of Bluetooth car audio will not recognise the commands transmitted by the phone. So the system will think there is no device.

To over come the issue you need to configure the phone to make the connection. In my Toyota this is done by selecting the connection settings after the Bluetooth connection is made using the Bluetooth mode (screen). Because latest phones are intelligent they will detect that the car audio is having older version of Bluetooth and it will change the instruction set accordingly and start communicating with an instruction set which is recognised by the car audio, making everything back to normal again.

After making my phone to connect to the car, I could play my music, answer calls and dial calls without any issues.

Hope this will help you to get your car audio connected to your latest phone.

Mount Lofty Lookout

A place you would visit to enjoy great scenery while you are in Adelaide South Australia is the Mt. Lofty Summit Lookout. You will be able to see most of the Adelaide while being there. At 720 meters above sea level and 17 kilometers from the city is a good place to enjoy an evening.

To assist visitors there is an information centre to gather more information about the area and a restaurant (http://www.mtloftysummit.com) to enjoy a cup of tea. If you got some more time to spare you could also visit Cleland wild life park which is nearby.

Drive with care since the roads in the area are having bends, also be on alert since there are many cyclists active in the area. There s a big car park so you will find a space to park your car but it will cost (was $3 on May 2013) you since at all times parking fees do apply.

Mount Lofty Lookout VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

Kogan Smart TV Dongle Restarting

If you also bought Kogan Smart TV dongle and it keeps on restarting whenever you try to turn it on you might be able to fix it by reading on.

Mine always restarted, at a time when the Android robot man is in view or when Android word is shown.

I did two main things to get it to work normally.

A) Changed the power source – I found the Red LED light in the dongle was not having the brightness it used to have. After doing some testing I found the power adapter which came with the dongle was faulty, so I used another similar power adapter and immediately the restarting of the dongle became less.

Also sometimes this power limitation is caused by the connector slack between the power adapter and the cable. Try changing the cable or adjusting the orientation of the adapter. For example if the adapter is lying flat try to turn it to a side or try to put some kind of an object underneath to reduce the slack between the cable and power adapter.

B) Update the firmware in the dongle – In my dongle updating the firmware made the restarting issue almost vanish. So if you like to make the fix complete try updating the dongle by following the steps below.

Before starting the process make sure that you will not have any power outages until you complete your firmware update (for about 30 mins). If power is interrupted while the update is happening most likely you will have problems using your dongle again, it might even be bricked so be careful.

1. Download the new version of firmware  from the following link.
2. Find an empty USB flash drive or a Micro SD card of capacity 512MB or more.
3. Unzip the downloaded zip file into the USB flash or the SD card. Make sure only the contents of the zip file is present on SD card or flash drive.
4. Plug the SD card or the USB flash into the HDMI dongle.
5. Go to Settings, find System Update then select Local Device and select either USB flash drive or the SD card.
6. This will start the firmware update process and when completed dongle will be restarted.
7. Then you need to do the initial configuration again for things like screen size, WiFi, accounts etc.

Hope this helps you, if you have any questions let me know I might be able to help. Some of the information about updating the firmware is thanks to a reply made by Ozzie Tigar in forums.whirlpool.net.au.

Windows Shortcut Keys

If you are using Windows, following are some handy shortcut keys with their actions to make your life bit easy.

Key Combination	Action
Windows Key + E	Opens Windows Explorer
Windows Key + L	Locks Windows
Windows Key + F	Open Search
Windows Key + R	Open Run Window
Windows Key + D	Minimise / Restore All Applications
Windows Key + Space	Peek to Desktop
Windows Key + Home	Minimise all Applications except the current Application
Shift + F10	Right Click
Windows Key + Number Keys from 1 - 9	Activate / Show Application on Taskbar.
Windows Key + Right Arrow	Switch between different Application Layouts
Windows Key + Down Arrow	Minimize an Application
Windows Key + Up Arrow	Maximize Application
Windows Key + Shift + Up Arrow	Fit Application height to screen height
Windows Key + Shift + Right / Left Arrow	Move Applications among the connected Displays when having multiple displays.
In an Application Alt + F4	Closes the Application
At Desktop Alt + F4	Brings Shutdown Window

 

I did find these in Windows 7, but have not yet tested on other versions of Windows.

 

Dolphin Cruises – Port Adelaide South Australia

Recently after a friend of mine told me about these cruses, we went for the cruise with few families we are friends with. In Port Adelaide 2 companies are offering Dolphin cruses. Namely they are Dolphin Explorer and Port Princess Dolphin Cruises. (For contact details please read on.)

In our cruise Dolphin Explorer took 2 and half hours to take us along the Port river. The ride is comfortable since it is not going to rough sea. On the way you will see big ships which comes into port. Unfortunately on the day we went it started raining so it was bit cold. Since the big boats are having fully covered restaurants no need to worry about the weather.

When talking about Dolphins it is bit frustrating since in our cruise we only saw few Dolphins at the start. But noting on our way, sometimes this might be because of the bad weather we had on the day. Since it costs $18 for an adult with meal I think it is value for money, so we thought to have another go later some day to check out more Dolphins. Until then I will post few photos I captured to make you interested.

There are plenty of free and paid parking in the area, so not to worry on parking. You will be able to find more information including the meal menus and other costs on their web sites.

	Dolphin Explorer	Port Princess Dolphin Cruises
Address :	Dolphin Explorer PO Box 177, Port Adelaide, SA 5015.	Port Princess Dolphin Cruises, Commercial Road, Port Adelaide SA 5015.
Phone :	(08) 8447 2366	(08) 8243 2757 (04) 1881 7837
Fax :	(08) 8447 1344
EMail :	info@dolphinexplorer.com.au
Web :	http://www.dolphinexplorer.com.au	http://www.portprincess.com.au

 

Dolphin Cruises VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

Lock Windows

If you wanted to lock your Windows using a batch file it can be achieved using the following command.

rundll32.exe user32.dll, LockWorkStation

This will be handy if you need to lock your computer after a certain scheduled action. Simply place this in a bat file, and schedule the bat file to be run to get the computer locked.

I tried this on Windows 7.

Enabling Hibernate in Windows 8

If you have start using Microsoft’s latest Personal Computer (PC) Operating System (OS) Windows 8, by now you may have found it is different in some ways to it’s predecessors. One feature I am heavily using in Windows was again missing after installing this version of Windows, which is the hibernation feature.

So if you like hibernation shown on your power menu try doing the following.

1. Move your mouse cursor to the right side of your screen to activate the quick access menu and click on Settings and then on Control Panel.

2. Then select “System and Security”.

3. Now select Power Options.

4. From the left hand navigation menu select “Choose what the power button does” or “Choose what closing the lid does”.

5. Now on system settings screen you need to click on “Change settings that are currently unavailable” to modify the disabled options.

6. Scroll down to Shutdown settings and check the box named Hibernate and click Save Changes button.

Now if you look at the options available in your power menu Hibernate will also be part of it.

If this does not bring hibernation back, please try the following as well.

1. From the power options screen select “Change plan settings” of the active plan.

2. Then select “Change advanced power settings”.

3. Now click on “Change settings that are currently unavailable”. This will list down all the features available in the system. Scroll down and expand Sleep –> Allow hybrid sleep and turn on hibernation as you want.

 

With these changes the “Hibernate” option should start appearing in your Power options menu.

Aviation Museum – Port Adelaide South Australia

If you are an aviation fan you might enjoy a visit to South Australian Aviation Museum. They do have some aircrafts on display where you can get on board to have a look without a passport. This is a non profit organization managed by aviation enthusiasts. There is also a shop for you to buy related things such as model aircrafts and magazines.

Entry tickets are priced at $9 per adult and free parking is available onsite.

Address: 66 Lipson Street, Port Adelaide, SA 5015
Phone: 08 8240 1230
EMail: saam@adam.com.au
Web: http://www.saam.org.au/

SA Aviation Museum VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

Marble Hill Cherry Farm – Ashton South Australia

Like to pick your own Cherries, then good place to visit is the Marble Hill Cherry farm located in Ashton SA 5137. This family operated farm is usually a pick your own farm meaning most of their produce is picked and bought by people who visit the farm in Cherry season like us.

Ashton is hill country so on your way to the farm you will encounter great scenery, also be careful and drive safe since some roads are windy and narrow.

Cherry season is a short 2 month period normally from December to January and depending on the number of people visiting Cherries might finish even before the season end, so always call or check their web site before going.

Cherry orchard has uneven ground (slope), so make sure to wear something you can easily walk in slopes and grass. Hats are also suggested. If you are travelling with young children and like to stay behind, you can stay in the scenic shed. It is having plenty of chairs. The management is also very kind, they provide free cool drinking water and ice cream for a small amount. If you like to continue cherry picking with young children then a child carrier sling is suggested.

Normally entrance fee is $ 5 and they will provide a bucket as the container to hold the Cherries you pick. Whatever amount of Cherries you pick should be payed for ($ 8 per Kilo). On your entry it is better to check the price since it change every year. Since Cherries can be kept fresh for about 2 weeks in fridge make sure to pick only the amount you want.

Address: Marble Hill Cherries, 25 Marble Hill Rd, Ashton, SA 5137.
Phone: (08) 8390 0327, 04 1136 1651
EMail: marblehillcherries@cherriessa.com.au
Web: http://www.marblehillcherries.com

Marble Hill Cherry Farm VIEW SLIDE SHOW DOWNLOAD ALL

Map

View Larger Map

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.