Disable accessing a hidden sheet in Excel workbook

I had a requirement today to not display the hidden worksheets in an excel workbook when you try to unhide sheets. Here are the steps I have taken to achieve this.

• Open the VBE (alt-f11)
• Go to the sheet that you want to disable in the unhide option
• View properties window
• Change Visible property to 2 -XlSheetVeryHidden.
• Save and close

Test the unhide option

Outlook meeting request response myth busted

In outlook when a meeting request is received from the organiser, I was under the impression that when I accept the request and choose do not sent response, then the meeting request at the organisers end gets updated that the request has been accepted but the organiser will not be sent an email. This is a myth. I am sure most of us are under this impression.

When you choose do not send a response, there is no way outlook updates the request at the organisers end. So if you want to let the organiser know that you have accepted then you need to send the reponse when you have accepted.

The credit for this learning today goes to Vanessa Taylor -- our Executive Assistant to the CEO.

Convert date to text in excel

If you have a date in a cell, when you try to use it as it is for example in concatenation, the date gets converted to a number and does not retain the date format.

In order to retain the date format and still be used as text -- the formula that can be used is TExt(datecell, "dd-mm-yyyy")

Reflections from CLoud Camp -- Auckland

I have attended a cloud camp that was held in Auckland on Feb 26th 2010. Here are some points sent by Ben the Cloud camp organiser.

I posted a roundup here giving my thoughts on the event. Thanks to all our sponsors who made the event possible - Gen-i, enStratus, Microsoft, salesforce, vmware, Intergen, the University of Auckland and Zendesk. Thanks also to Anne Bilek for doing a great job of helping to organise things on the ground.

Paul from Gen-i wrote an interesting post with his reflections on the event - you can read it here.

Similarly Ryan from Intergen wrote an interesting perspective here.

Finally Nigel from Microsoft filmed the Lightning Talk given by Darryl Burling - also well worth a look. click here to watch the vid.

Have you heard of Worldcard?

Worldcard is a bizcard recognition solution for iPhones. This uses a built-in camera in smartphone to capture bizcard images and has a powerful recognition function to turn business card information into contacts right away.

To know more about it.........

How to become an exceptional DBA?

Today I have attended the webinar by Brad McGehee titled 'How to become an Exceptional DBA' which was an inspirational talk about why and how you need to be exceptional. Following are the learnings from this webinar.

Characteristics of an Exceptional DBA:
Enjoy Technology, challenges, problem solving, eye for detail and embrace change, trustworthy, accept responsibility, maintain professionalism, dependable, manage time well, be patient, good listener and most of all self confident.


Honing your skills

• Having a 4 year degree differentiates you from the crowd and this degree need not be in IT or CS.
• Boosting your technical skills is a self taught process by attending seminars, conferences, code camps, online training etc.
• Mastering related technical skills like virtualisation, operating system, hardware, clustering
• Practical experience is the key to be an exceptional DBA.
• Mastering soft skills like people skills, teamwork skills, project management, leadership skills, time management, writing skills, speaking skills, knowledge of legal responsibilities. These can be done by volunteering.

Specialising as a DBA:

Choose one or more of the following:

• DBA Adminstrator
• DBA Developer
• DBA High Availability/Disaster Recovery
• DBA Database Designer/Architect
• DBA BI SSAS specialist
• DBA BI SSIS specialist
• DBA BI SSRS specialist
• DBA Trainer
• DBA Consultant

• Professional certification helps you differentiate from other DBAs
• Volunteer and participate in the SQL SERVER community. this is mainly to help yourself as participating develops your soft skills, make new contacts, increases your bdepth and breadth of knowledge, building your network etc.

Manage your internal brand

• Understand business goals of your company, and focus on how to help achieve them.
• Be a leader and take the intitiative.
• Accept responsibilty for your actions
• Have a "Get it done" attitude
• Don't abuse your power
• Actively participate and contribute in meetings
• Help others be successful
• Most importantly avoid office politics
• Be proactive by keeping all the stakeholders in the loop

Manage your online brand

• Seperate professional and personal online presence
• Don't post anything negative
• Online conducta and content should be professional, and

Last but not the least, you should take control of your career by deciding what you want from your career, set realsitic, short term goals in order to reach major milestones in your career plan. Most importantly follow through your goals.

Connecting to SQL Server via Windows PowerShell with SQL Server authentication

Here is a tip from MSSQLTIPS that shows you how to connect to powershell with sql server authentication.

Today I have attended the free webinar on T-SQL for SLQ Server from Pragmatic Works. This was a fantastic session by Patrick LeBlanc. Even though I had some experience in T-SQL I have learnt a few new things mostly the new features of SQL SERVER 2008. The recorded webinar can be viewed from the resources page and click on View Recording at the relevant webinar.

Interesting things I have learnt from this webinar are as follows:

• If you don't want to type the table names you can drag a table into the query window from the object explorer.
• If you don't want to type the field names you can expand the table and drag the columns folder into the query window from the object explorer and the list of fields of the table automatically appear in the query window.
  
  Set Identity_Insert On or OFF
  
• You can update an ID column by setting the above variable on for a specific column. But remember to set the value to Off once you have updated the ID column as this works for one table at a time.
  Example:
  SET Identity_Insert tablename ON
  Insert INTO (IDcolumn, field1, field2)
  Values(6,ssss,zzzz)
  SET Identity_Insert tablename OFF
  
  
• A very good description (illustration) of rather differnces between, Inner join, left outer join and right outer join.
• The use of output statement
• Grouping Sets feature of SQL SERVER 2008
  

Hope you also find this resource useful.

Do your backups for SQL server still exist?

Now that you have confirgured your backups on SQLSERVER, the backups themselves seem to be working, but are the files still where you think they are? How do you know they have not been moved, or worse, deleted to free up some space on your server? And when would you find out that the files were no longer there? If you are looking for the file to do a restore, and cannot find it, then it is too late.

Here is an tip from MSsqltips that shows you a simple way to check through the backup history to find the last full backup for each database and also to check that the file still exists where it is supposed to exist. Read More .............

Cloud computing Costs and ROI

I came across this video on you tube that shows how to calculate the costs and ROI for cloud computing.

What is Cloud Computing?

Cloud computing has taken the IT world by storm, quickly making its way up the list of technology buzz words - but what does the term really mean? And how will cloud computing change the way you manage and organize your digital information in the future? (Relax News)

Here is the link to an article published by The Independent on What is Cloud Computing

Here is a video published on You Tube on What is Cloud Computing?

Cloudcamp in Auckland

Today I have registered for the CLoudcamp event that is happening in Auckland. If you also want to register for the Auckland cloudcamp please go to this link.

Cloud camps are in the tradition of unconferences where anyone can put themselves forward to present a session.

CloudCamp is an unconference where early adopters of Cloud Computing technologies exchange ideas. With the rapid change occurring in the industry, we need a place where we can meet to share our experiences, challenges and solutions. At CloudCamp, you are encouraged to share your thoughts in several open discussions, as we strive for the advancement of Cloud Computing. End users, IT professionals and vendors are all encouraged to participate.

Adding a calculated field in pivot table

The following are the steps to add a calculated field inside a pivot table in Excel

• Click on a cell inside a pivot table.
• Go the PivotTable Tools -- Options -- Click on Formula --Calculated Field
• Add the formula for the calculated field
• Click ok. This will add the calculated field to the Pivot table as well.

Upgrade plan for SQL SERVER 2008

We have to upgrade out sql server 2005 to sql server 2008 in the coming months. Here is a comprehensive article that I found on MSSQLTIPS that could be used when you plan an upgrade. Read More

Data storage and retrieval for geospatial data in SQL Server 2008

I have a need to use geospatial data which will assist plotting the trainees available by region and compare wtih the sales staff region in our company. Here is a link from myssqltips that I found as part of that project.

Why Powershell?

Did you also have this question when you first heard about Powershell? I did indeed and googled the why........ Here are a couple of links that answer this question.

Windows Powershell: Why developers should care?
Why Powershell?

Have you heard about Dropbox?

Dropbox is the easiest way to store, sync, and, share files online. There's no complicated interface to learn. Dropbox works seamlessly with your operating system. Here is the link to it.

Common DBA tasks

Any idea what a common DBA tasks are?

• Adding users in SQL Server is one of the most funtamental tasks you'll perform. This article describes in great detail the different steps you have to take to establish a new user in SQL Server.
• Have you ever needed moving the database from one location to another? In SQL Server, you will need functionality to detach and attach your database in order to move it from one location to another. Here you will find a thorough description of what you'll need to do.
• As a DBA, you will very likely need to move bulks of data from one database to another, or make a copy of your data. This article provides a step-by-step tutorial on Import and Export in SQL Server
• Today's database systems need mechanisms to increase performance. One of the most important things you can do, is adding SQL Server indexes
• Scheduling jobs is a very useful functionality of the SQL Server database. There are several uses of this feature. Read more in this article on SQL Server jobs
• Preparing a maintenance plan is an important task, but SQL Server provides you with a wisard for such a plan. Read this tutorial on how to set up a SQL Server Maintenance Plan
• Every database needs a backup copy. Hopefully, you will never need it, but all practice has learnt us that a backup can come in very handy. Read this tutorial on how to produce a SQL Server backup
• When disaster happens, you have of course a disaster plan and a backup of the database?. If so, it is easy to perform a SQL Server Restore Database operation.
• Sooner or later, you will most likely have to search for performance bottlenecks, or perform other monitoring tasks. This article is a simple tutorial on how to use SQL Server Profiler
• If your database should go down, you need to bring it back to as current state as possible. In case of a system failure, the SQL Server Transaction Log will prove invaluable.

What is Windows Azure?

The Windows Azure platform offers a flexible, familiar environment for developers to create cloud applications and services. With Windows Azure, you can shorten your time to market and adapt as demand for your service grows.


The Windows Azure platform gives you:

• A familiar development experience
• On-demand scalability
• Reduced time-to-market for your applications

Windows Azure offers a platform that is easily implemented alongside your current environment.

• Windows Azure: operating system as an online service
• Microsoft SQL Azure: fully relational cloud database solution
• Windows Azure platform AppFabric: makes it simpler to connect cloud services and on-premises applications

Unsubscribe from alerts in Sharepoint 2007

Today I had a need to remove alerts from a sharepoint site for a group of users. I did not want to login as each user and then unsubscribe. It took me a while to figure the following.

To unsubscribe users for a sharepoint alert Click on Site Actions ---> site Settings -----> User alerts.

Now select each user and click on Update. The alerts are shown which can then be selected and deleted.