My Tech Learning

Have you heard of Prezi?

2012-01-23T09:48:00.000+13:00

Late last year one of my colleagues Mark Crosby has introduced the Prezi tool in our company to do presentations. Prezi is a presentation software that can be used in creating more visual presentations. It is different to the traditional powerpoint software as it has a Zooming user interface and uses a vector based illustration and text which creates big visual impact.

The main differences between PRezi and Powerpoint that I have found are as follows:

The first difference I found is that Prezi is an online application that does not need any installation of software whereas Powerpoint is an installed software application.

Prezi is more like a canvas based presentation whereas powerpoint is more based on slides that are arranged in a sequntial manner.

In Prezi you cannot just copy and paste pictures like we do in powerpoint. The pictures need to be uploaded and then included in the Prezi creations.

In Prezi you can use only flash objects and embed youtube videos whereas in powerpoint you can include a wide variety of video formats.

You cannot print the sides as a handout in Prezi like you do in powerpoint.

There are many other differences, strengths and weaknesses in both the tools and for now I will start using PRezi more and more and see as it is easy to learn.

Let me know what your thoughts are

Automatcially download pictures in Outlook 2007

2011-12-19T11:38:00.001+13:00

Today I was asked by my colleague as to how to change outlook 2007 so that she does not manually need to download the pictures every time an email comes in.

Here is what I have suggested to her.

Go to the menu item Tools in Outlook and then click on Trust Center

Then choose Advanced settings

Untick the "Don't Download pictures automatically' option as shown below

Have you heard of space function?

2011-12-13T22:16:00.001+13:00

Today I had a unique scenario where I had to introduce 2 spaces in between two fields.

I came across the space function which I used in my scenario.

The syntax is space(integer_expression)

The integer expression is an integer. IF a negative value is supplied a null is returned.

So for example look at the following syntax:

select First_name, + space(4) + Last_Name from test_table

The above select statement will generate 4 spaces in between the first name and last name

The space function will generate a miximum of 8000 spaces.
For more information click here

Multiply and Divide a group of cells in Excel

2011-12-05T09:13:00.003+13:00

I just now realised that it has been more than a month since I have posted anything on my blog. So here I am with the most recent tip I learnt in Excel.

If you have a column of cells that need to be divided by 1000 for example. Here are the steps that you need to follow.

Type 1000 in a cell lets say A2

Copy the cell A2 using Ctrl+C

Select the cells in the column where you would like to let us say multiply the values by 1000.

Right click and select Paste Special

Click on Multiply as shown below.

You can do the same for adding or subtracting a specific value from a group of cells.

This tip is particularly useful if you want to show the data that is in millions but want to reduce the number of digits by dividing them by 1000.

Hope this heps.

Increasing the number of recent files in SSMS

2011-10-11T23:54:00.000+13:00

In Sql Server Management Studio, I usually access my sql query files that I save -- using the File -- Recent Files option. The default setting of the number of recent files to be displayed in SSMS. Today I wanted to access a file that I had used last week and I could not find that file in the recent list.

So I thought of displaying more than the normal 4 files that are displayed in the Recent Files option. So I set out to find where the settings are (as in the microsoft office programs) -- I clicked on Tools -- Options -- General -- The option reads as

Display 4 files in the recently used list. I changed the number 4 to 10.

Now it is starting to display 10 recently used files.

Export directory list in excel

2011-10-07T10:03:00.000+13:00

Yesterday I wanted to export the contents of a folder into excel to provide it to the users for input.

Here are the steps I followed.

Go to Start -- Run -- CMD

Change to the drive letter of the drive in which the folder you want the contents listed is if you are not already there by typing the drive letter followed by a colon. Eg: d: for going to the d drive.

Change to the folder of which you want the contents to be exported by using the chane directory command cd. Eg: cd shared/test for changing into the a directory called test which in turn is in the shared direcotry on the d drive.

Then list the directories using the dir/d comman. This gives the list.

Right click and click on MAark and select the contents you want to copy.

After selecting right click again.

Then paste this into a new excel document.

Your list of directories is ready.

Save SQL Query results into another SQL database

2011-09-14T11:48:00.000+12:00

Today I had a requirement to save the results of an SQL query into a sql table. Usually it is easy to export to excel by using the option of copying the results and pasting them to an excel file.

But this time I had over a million rows and I didnot want to use excel to store that huge amount of data. So I followed the following 2 simple steps.

1. Create a new table in a new database:

Use the SSMS to create the database and use the create table query to create the new table as follows:

create table databasename.dbo.tablename

([col1] [varchar(20)], [col2] [int], [col3] [datetime])

2. Create a SQL query using the Insert into command as follows:

Insert into databasename.dbo.tablename

(col1, col2, col3)

select a.col1, b.col2, c.col3 from tab1 a, tab2 b, tab3 c

where a.col1=b.col1 and b.col2=c.col3 and col1='zzz'

This query has inserted data into the new table directly.

Add months to date in excell

2011-09-08T08:33:00.000+12:00

Yesterday I had this requirement to add number of months (m) to a cell that contains start date to estimate the end date. In 2003 I had to use a complex formula like below where A1 is the cell that contians the start date.

=DATE(YEAR(A1),MONTH(A1)+m,DAY(A1))

Well in Excel 2007 it is even more easy to add months with the Edate function like below.

=Edate(A1, m).

Using the script task in Management Studio

2011-08-30T08:41:00.004+12:00

Today I was taking a back of a database and for the past two days this has become my regular activity. So I thought there should be some kind of recording option (like recording a macro in excel) so that I can just use the recorded action whenever I need to do the same task again and again.

So I tried to investigate and found this script button. So I thought I would investigate this. The following are the steps I followed to backup a database in the Management Studio.

Right click the database that needs to be backed up.

Click on Task -- Backup

Choose the Backup options like how you choose normally.

Before clicking Ok click on the Script option at the top and choose the option that you want from the 4 options available as shown below.

This has generated a nice script as shown below.

BACKUP
DATABASE [TEST] TO

DISK

= N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TEST20110830.bak' WITH FORMAT, INIT, MEDIANAME = N'TEST', NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

This script can be used to do the same task again and again and also may be you can schedule this to be processed at a certain time every day.

Windows Firewall Settings on Windows 7

2011-08-23T12:45:00.004+12:00

Today I had to access the Windows Firewall to check for blocking of a port. I am used to the command wscui.cpl from the Start -- Run screen of the Windows XP operating system. So I did the same thing on my Windows 7 screen. I was surprised to get the security centre where the Windows Firewall was not visible as shown below.

Luckily I saw the Control Panel Home link at the top and clicked on the link. Then I noticed the Windows Firewall as being a separate entity in the control Paenl and accessed this from there.

24 HOP -- September 7-8 2011

2011-08-05T09:39:00.001+12:00

The registration for the much awaited 24 hours of PASS is now open. Here is an excerpt from the site.

Two Days of Free SQL Server Training!

Don't miss the best 24 hours of free, online SQL Server training in the industry with 24 Hours of PASS: Summit Preview September 7 and 8.

Join us for an exceptional lineup of SQL Server and BI experts, who will be presenting a sneak peek of their PASS Summit 2011 session topics in 24 live webcasts delivered directly to your computer.

Register here to attend these sessions.

Powershell for SQL Server : An Introduction -- Free webinar

2011-08-04T11:11:00.000+12:00

A free webinar is being organised by Idera on Powershell for SQL Server.

Here is an excerpt from the site.

In this session, Microsoft MVP Aaron Nelson will discuss what PowerShell is and demonstrate how and why DBAs should use it. Aaron will step you through the basics of Windows PowerShell and provide you with some commonly used first scripts to help you get started. Register for the webcast today!

The date and time of this webinar is below:

Wednesday, August 10, 2011
2:00 PM - 3:00 PM CST

What the password policy of sql server?

2011-08-03T08:27:00.002+12:00

Yesterday I had a question in my mind as to what could be the sql server password policy. So I started hunting in the Help menu of the SSMS window. Here is how I found that.

Click on Help from Sql Server Management Studio.

Choose contents

Then choose Security considerations for SQL Server

Then choose the Password Policy as shown in the screenshot below.

Using Countif in Excel

2011-07-27T10:36:00.000+12:00

I was using excel today and I wanted to count all the cells in a range only when there is a value greater than 2. So I started hunting for a funciton in excel and found the countif function.

Countif(range,criteria)

But when I started using it like this -- Countif(A1:A100,>2) it didn't work. It was coming up with an error.

So I tried Countif(A1:A100,">2"). This worked. So if we include the criteria in quotes then the formula worked.

I also noticed a sumif function that also works in a similar way. It populates the sum of all the cells in the range specified that satisfy the criteria given.

Webinar-- Best Practices for Sharepoint 2010 upgrade

2011-07-22T08:26:00.000+12:00

Shared Squared is running a webinar on 26th July 2011. The topic is -- " Best Practices for SharePoint 2010 Upgrade and Configuration"

Here is an excerpt from the site

The webinar will include various demonstrations and discuss:

•Who should upgrade, why & when
•How to assess existing SharePoint deployments and identify upgrade risks and opportunities
•Specific tasks that need to be done to prepare for SharePoint 2010
•How to identify and upgrade existing 2007 customizations
•Best Practices for supporting parallel environments, migration & preserving the investment in your existing SharePoint deployment
•Avoiding Upgrade & Migration Pitfalls

You can register here

Adventure Works for SQL Server Denali CTP3

2011-07-19T15:13:00.001+12:00

I came across this link on Codeplex this morning which will be helpful for learing Denali

Here is an excerpt from the site

This release is dedicated to the Adventure Works sample databases and related samples that ship for Microsoft SQL Server code-named "Denali", Community Technical Preview 3 (CTP3). This release uses the AdventureWorks2008R2 and AdventureWorksDWDenali sample databases. They have been upgraded to the "Denali" CTP3 version.

The only change to AdventureWorks2008R2 is the removal of the FILESTREAM dependency. The FILESTREAM properties have been removed from the Production.Document table. No additional schema or data changes have been made.

The only changes to AdventureWorksDWDenali are the additions of the FactProductInventory table, which contains inventory data, and more date data in the DimDate table to fill the years, 2001 to 2006.

These databases cannot be installed on any version of SQL Server other than SQL Server code-named "Denali" CTP3.

Powerpoint presentation tips

2011-07-14T15:16:00.000+12:00

Today I was sitting in a demo presentation and learned a few tips that my colleague Mark Crosby shared.

Did you know that in the slide show mode of the powerpoint presentation if you want to jump to a particular slide, you can just put in the slide number and enter and you can go straight into that slide without coming out of the presentation mode. He also added that if you have the slides printed and numbered it will be easy for you to go to the respective slide if you know the number of the slide.

Another tip he shared is by pressing the 'B' key you can pause the presentation so that the audience concentrate on you rather than on the presentation. The screen turns black. You can resume the presentation by pressing the same 'B' key.

Pressing the 'W' key will make the screen white instead of black and resuming the presentation can be done by pressing the 'W' key again.

I thought these tips will be helpful for all you presenters out there....

2011-07-14T09:30:00.000+12:00

Free TSQL Webcasts Start Next Week

In SSWUG.ORG’s “Writing T-SQL Queries and Code” webcast series, you will be able to learn how to write aggregate and crosstab queries, as well as how to use common table expressions to write recursive queries.

In three, in-depth sessions, Kathi Kellenberger, SQL Server Technology Specialist for Microsoft and author of Beginning T-SQL 2008, will teach beginning and experienced T-SQL programmers about writing code to ensure their applications are highly performing.

By the end of the series, you will be more familiar with T-SQL queries and know how to simplify your work through new and advanced features.

Learn more about the virtual webcast content and register today.

Adding Hyperlinks in Crystal Reports

2011-07-11T11:22:00.001+12:00

Last week one of my colleagues asked whether there is a possibility to add hyperlinks in crystal reports. I thought that is a good question but felt that you could add and so started searching for an option in crystal reports. Here is what I found.

When you select a particular field for which you want to hyperlink and rightclick and choose the option format field. There is a tab called hyperlink and there are variety of options to add a hyperlink as shown below. As you can see the options to add a hyperlink are a link to a website, an email address, a file, current email field value and current website field value.

What is Google + ?

2011-07-07T11:17:00.001+12:00

In the past one week I have heard this term Google+ so many times that it intrigued me to find out what exactly this is. I am sure some of you may have heard it too. So I resorted to Google and here is an excerpt from the cnet site.

For now, Google is quick to call Google+ a "project," and acknowledged that the social service still has "rough edges." However, it currently has a host of features to help people communicate over the Web with friends and family.

Google+ is designed around "Circles" that allow users to group people within their social sphere into different categories. Google says that the people you tend to meet up with on Saturday nights, for example, can be grouped into their own category, while parents can be placed into another. You can then decide to share only certain information with different Circles.

In addition, the social service includes a feature called Hangouts that lets you find others who are "hanging out" on the Web. If you decide to join a given hangout, you'll be able to engage in a video chat with the others there. Google+ also comes with an Instant Upload option that automatically uploads all photos and videos from your phone to your profile. From there, you can decide who to share that content with.

Read more:

Have you heard of Microsoft Dreams Park?

2011-06-16T08:04:00.002+12:00

This morning I came across this web site by Microsoft called "Microsoft Dreamspark" They have training videos, reading material and provide microsoft professional tools free of cost for students to practice.

Please have a look and sign in and take advantage

SQL Disaster Recovery Event from SSWUG

2011-06-14T10:12:00.000+12:00

Here are the event details from SSWUG.

Attend the Free SQL Disaster Recovery Event

SSWUG's free virtual expo will showcase several ways to prevent a SQL Server disaster and how to recover the database in the event of loss or damage. Through the in-depth sessions with some of the leading SQL Server experts in the information technology (IT) field, you will see many demonstrations and examples on anticipating and reducing the likelihood of a database tragedy. Learn more about the virtual expo content and register today.

Microsft outlook export option

2011-05-23T08:25:00.003+12:00

A couple of days ago my manager asked whether there is a way to convert appointments recorded in outlook into a report so that the data need not be double handled.

I thought there should be some sort of an export option provided in outlook and started exploring the menus in outlook. Sure enouth there I found under the File menu an option called Import and Export as shown below.

I chose the export to file option as shown below.

I them chose the csv for windows option as shown below.

In the next screen I chose the calendar folder and specified the dates to export and the path and the file name. The csv file was exported with all the different fields.

Free SSAAS Expo again from SSWUG

2011-05-20T09:28:00.000+12:00

SSWUG.ORG’s virtual expo will review various aspects of SQL Server Analysis Services (SSAS), which enables the server to be used for analytical processing and data mining.
Through our in-depth sessions with four of the leading experts in the information technology (IT) and business intelligence (BI) fields, you will see many demonstrations and examples on designing, creating and managing data from multiple sources.

By the end of theevent, you should have the tools and understanding needed to bring added functionality, automation and insight into your data.

Sessions will cover the following topics:
Common Mistakes with SSAS Cube Designs
SSAS Partitioning and Aggregation Strategies
Properly Using the SSAS Query Cache
Overcoming SSAS Implementation Issues
Building a Scalable SSAS Solution
Best Practices for Performance and Tuning Techniques

Click here to Register!

Free Webcast Series on Sharepint 2010 from SSWUG

2011-05-19T08:02:00.000+12:00

SharePoint beginners and experts can benefit from attending SSWUG.ORG’s “SharePoint 2010 Basics” webcast series, which will delve into what you can do with the newest version of SharePoint and how the platform can benefit your business.

In four, in-depth sessions, Rebecca Isserman, SharePoint Server MVP and Consultant for Planet Technologies, will explain how to use the platform’s key features, create sample applications in the development environment, blend Silverlight applications with SharePoint and more.

By the end of the series, you will know about the various and available environments and tools you can use to develop a functional and optimized SharePoint project.

Click here to see the session schedule.

Click here to register.

Restore folder in Sharepoint

2011-05-04T09:59:00.002+12:00

We heavily use a customised feature of Image Library on sharepoint 2007 which is our Intranet. One of the new users yesterday had deleted a whole folder from this library and wanted us to restore this. Now we have never done a restore onto the sharepoint.

So I started investigating the sharepoint settings as to how to solve this problem rather than looking at the backup to restore.

So this is what I found! a Recycle Bin in the Sharepoint. And there are the files from the folder that was deleted neatly sitting in there. Wow that's a learning for the day I thought.

So here is the path as to how I found the Recycle Bin.

Click on the Image Ibrary on the intranet

Click on site settings

Click on Recycle Bin which is under Site Collection Administration as shown below.

Free MDX Webcast Series by SSWUG

2011-05-03T08:27:00.000+12:00

Explore the basic functions of MDX and view many practical examples on using the query language in SSWUG's "Introduction to MDX" webcast series.

In three, in-depth sessions, Business Intelligence architect and MDX expert Bill Pearson will focus on the basic components of MDX, as well as provide information on crafting simple MDX expressions and queries that generate result sets.

Click here to see the Session dates

Click here to see the Session Schedule.

I think these sessions will be particularly useful for beginners.

ATBroker.exe

2011-04-15T09:54:00.003+12:00

Last night when I tried to access my computer from home via Remote Desktop Connection an error came up as shown below.

This is the first time I have encountered with this error. Since I had lots of work to do I logged on to one of the servers and did my work thinking that the error will disappear when I access the actual machine in the morning. But to my surprise the error persisted when I switched on the monitor in the morning. So I tried the usual Ctrl+Alt+Delete. And there lies the familiar screen which made me relax as shown below.

So I chose the Start Task MAnager option and luckily got into the computer. But I am not sure why the error has come up. Have any of you had this error before? If so what did you do? And do you know why this error came up?

Increasing SQL SERVER Memory

2011-04-07T14:30:00.003+12:00

In one of our server audits it was discovered that only one third of the memory has been allocated to the sqlserver instance. Questions were asked whether multiple instances of sql server are present. But when there was no other instance of sql server running it was recommended that we increase the memory allocated to double from 10 GB to 20 GB.

The actual memory of the server was 32 GB. Here are the steps we took to achieve this.

1) RDP to the SQLServer and sign on

2) Open up SQL Server Management Studio

3) Select the SQL Server in the Object Explorer on the left

4) Right click and select Properties

5) Select Memory tab as shown below

6) Change Maximum Server Memory from 10240 to 20480

7) Click OK

Close Management Studio

As a precaution restart the server. It is better if you do it after hours.

2011-04-05T10:24:00.003+12:00

I had a strange problem today. I went to the website I have developed and maintain and tried to click on the print button and expected to get a normal printout. But instead was surprised to get a blank print out with just the header and footer.

I thought I haven't changed any code why did it stop working. I just assumed that it was not working for anyone without testing on other computers. After about 15 min. I had a request from another colleague to print from some other govt. website. So I hit the print button provided on their website. Then the output was the same with the blank page being printed with just the header and the footer.

Then I have asked one of my other colleagues to print and he could print. Only then I have realised that the problem is with my print spooler.

So developers beware of this problem. You might also face this stuation at some stage.

So the following are the steps that I have taken to restart the print spooler.

Right click on 'My Computer' -- and choose Manage

From the left hand menu -- choose Services and Applications

Then click on Services

From the list that appears on the right hand side choose Print Spooler as shown below.

Stop the service and start it again.

Now the important part : If you test Print form the same browser session the print might not work. Make sure that you open a new session and print.

Power Switch for a blade server -- an interesting find

2011-03-31T07:53:00.002+13:00

We had a power outage for about 6 hrs at our office and the UPS would last only for 1 hr. We decided to do a managed shutdown of the servers and then power them back up the next day when the power came back up. I was responsible for coming early in the morning and switching on all the servers.

As mentioned in an earlier post our systems administrator is no longer with us, Andrew joined us to take over this role. He did the shut down part the previous night and called and said that he cannot find the power switches for the blade servers. I also didn't know where the power switches for the blade servers were. I have asked him to go home and said that I will deal with that in the morning.

Now the image below is how the blade server looks.

As you can see there is no way you will know that there is no power switch that is visible to the human eye. I did a lot of googling the night before to get some insight but could not find any documentation. For those who have dealt with the blade servers this could be very easy. But finally I figured out that the glass apnel above can be opened. When that was opened there lies the hidden power switch neatly as shown in the image below highlighted in red.

The keyboard shortcuts I use

2011-03-25T13:57:00.000+13:00

Yesterday I was working with my colleague and she was surprised with the number of shortcuts I use with Windows Key .

So I thought I could list all the shortcuts for the benefit of her and others

To open the Start Menu -- just click Windows Key
To open the windows exlpoer -- click Windows Key + E
To open the seach/find window -- click Windows Key + F
To display the desktop by minimising all the open windows -- Click Windows Key + D (I have found this really helpful)
To lock your computer -- click Windows Key + L (Easy to remember)
To open the run command -- click Windows Key + R
To display the Help window -- click Windows key + F1
To zoom in the screen -- click Windows Key + =
To Zoom out the screen -- click Windows Key + -
To go through the open windows one by one -- Windows Key + Tab (Similar to Alt+Tab) but the display is really nice.

If you have windows open and your windows 7 gadets are hidden behind them you could bring them on top of the applications you use -- Windows Key + G

If you use two screens -- two helpful shortcuts are
Moves the current windows to the left screen, when running dual monitors -- Windows Key + Shift + Left arrow
Moves the current windows to the right screen, when running dual monitors -- Windows Key + Shift + Right arrow

There could be other shortcuts which you also might be using. Please let me know so that I could start using them as well.

Have you heard about Channel 9?

2011-03-23T23:37:00.000+13:00

I stumbled across Channel 9 yesterday while researching. Channel9 is a Microsoft maintained site which is full of videos, blogs, shows, series etc. There is too much information on this site for one to absorb and learn.

Here is the link to the site. I hope you will enjoy this learning.

Data Mining and Analysis -- Free Expo from SSWUG

2011-03-07T10:47:00.002+13:00

Here is another Free Expo Event from SSWUG that covers Data Mining and Analysis. The event is scheduled for March 18th 2011 from 9 am to 12 pm PST.

Sessions will cover the following topics:

Using Data Mining Plugins with Excel
Developing SQL Server Data Mining Models
Understanding Data Mining APIs
Finding Patterns and Relationships in Server Data

You can register now if you would like to learn from three skilled data mining experts.

Conditional formatting using multiple conditions - Excel 2007

2011-03-04T14:52:00.004+13:00

Today I had a requirement to colour code in Excel based on a few conditions. At first I thouhgt I could use thre to four rules in the conditional formatting to achieve this result but when I did that the results that I was getting was not very satisfactory. So I thought I would try the And function of excel in conjunction with the conditional formatting and it worked wonders.

I wanted the entire row to be highlighted in a certain colour based on three conditions.

Here are the steps I followed to achieve what I was after.

Select all the data that you want to colour code.
Click on Conditional Formating on the Home Tab
Choose Manage Rules as shown below

From the dialog that appears click New Rule.
Add the multiple conditions using the And function in the formula box
The most important thing you need to do when you are adding the formula to highlight rows is to remove the $ sign that gets assigned when you choose the cell for entering a formula with a mouse.
Choose the colour by clicking the format button and click ok
Click Ok again to come out of the Manage Rules window.

As you can see below I have added two rules and it is giving me the result that I want.

Have you heard about Snipping Tool?

2011-03-03T12:58:00.003+13:00

Thanks to one of my colleagues, yesterday I learnt about the Snipping Tool which is avaibale in Windows 7. To activate the tool -- Click on Start -- All Programs -- Accessories -- Snipping Tool. A screenshot of the tool is shown below.

Snipping tool enables you to select an area on your desktop or the entire desktop using your mouse and save this as an image for future use.
Until now I used to use the key board shortcuts PRintscn for capturing a screenshot of the entire desktop or Ctrl + Alt + PrintScn to capture the active window. Then open Paint and then save as an image.

Only drawback of the snipping tool that I found was if you have try to capture a screenshot of the values in a drop down of the filter feature of the excel, you could not do that using the Snipping Tool as you are trying to use your mouse to invoke the snipping tool. When you do this the drop down disappears in excel and hence you cannot take a screenshot of what is not appearing on your screen.

But the command that I always used Ctrl + Alt + PrintScn worked as shown below.

If anyone of you know of a workaround for this I would be pleased to know about it.

Processing of SSAS cube -- steps for beginners

2011-03-01T09:14:00.001+13:00

I thought today I will cover the steps that can be followed to process a Sql Server Analysis Services Cube from Sql Server Management Studio.

The assumption is that you already have a cube that is built in SSAS and you would like to process this cube from sql server management studio

Step 1
Connect to the sql server analysis services instance from the sql server management studio as shown below.

Step 2
From the left hande side menu click on the plus beside Databases.

Step 3
Right click on the cube that you would like to process as shown below.

Step 4
The following dialog appears. Click on change settings if you need to and click on OK. Depending on the amount of data the processing takes a few minutes upto a few hours.

Have you used Outlook Anywhere?

2011-02-22T18:11:00.000+13:00

Since our systems administrator left our company I have had a lot of opportunity to learn about things outside my normal coverage area. I am very thankful to my manager Ashley for giving me that opportunity.

In one of the meetings I sat with the external consultants 'Outlook Anywhere' was mentioned.

Outlook Anywhere is one of the Exchange 2007 feature that Microsoft has developed. The consultant mentioned that the Outlook Anywhere is a remote access method for Outlook Clients to connect through the internet to the exchange server without the need for a VPN.

This was not configured in our Exchange Server and explained the two main benefits as follows:

There is no need for a vpn connection to be on to access Outlook Anywhere.
If there is an internet connection available then you can just login to Outlook Anywhere and access your email.

Do any of you use Outlook anywhere? If not you can suggest your system admins to enable Outlook Anywhere if you are using Exchange server 2007 or 2010.

My first linked server -- SSMS 2005

2011-02-22T06:34:00.000+13:00

Yesterday I was exploring Sql Server Management Studio, when I spotted Linked servers. I thought I will investigate what that and started trying out the options. So I right clicked on linked server, only three options came up as shown below.

I clicked on the New linked server .
I was browsing through the provider list as shown below and was keen to learn more about the provider for Microsoft Directory Services.

I thought I will experiment by using excel hence I gave the product name as excel, and data source -- a file in my c directory. But did not know what to give as a Provider string and location. I just gave the Provider as Excel 8.0 which is commonly used for excel in reports. Left the location blank and clicked ok.

To my surprise there was no error and the Test linked server was created as shown below.
So I right clicked on the test and chose TEst connection and the connection succeeded.

I am happy that it all went well but yet since there were no other options that are visible on how to use this linked server I thought I would find out when I had a bit more time on hand. So I will post my findings in another post.

Managind SQL Server Source Code -- Webinar

2011-02-21T21:44:00.000+13:00

Here is the information on very interesting topic -- Managing SQL Server Source code. Here is an excerpt from the communication I have recieved from MSSQLTips

Live Webinar: Managing SQL Server Source Code

Now is the time to properly manage source code in SQL Server. Put an end to the chaos and stress of having to manage source code in a haphazard manner. Source control for application code has been the norm for years, but that is not necessarily the case with SQL Server code.
Come to this web cast to learn how to manage SQL Server source code with simple, predictable steps. We will show how to do so with tools you already have in addition to a solution from Red-Gate that integrates directly with SQL Server Management Studio.

Click here to register

Excel Web Access Error in Sharepoint 2007

2011-02-17T06:35:00.000+13:00

I wanted to use Excel Services on Sharepoint 2007 to migrate some of our BI reports from a third party application that we use. So I tried publishing to Excel Services from Excel 2007 assuming that our Sharepoint was setup to be used with Excel Services.
When I tried to publish and open in a browser I started getting an error "Excel Web Access: An error has occurred ".
When I checked the event log the following was logged in the event viewer.

There was an error in communicating with Excel Calculation Services http://sharepoint:56737/SPAdmin/ExcelCalculationServer/ExcelService.asmx exception: The request failed with HTTP status 401: Unauthorized.
[Session: (null) User:[Domain\user].

I have followed all the following steps to get to this stage.

I have created a document library
Added this document library as a trusted file location
Added the document library to trusted data connection libraries
Also ensured that the user being used has the proper rights in each of the databases
Started the single signon service
Ensured that the Excel Calculation service is running

Still the error was persistent. After a lot of struggling for 3 days, I finally fixed the problem by removing the integrated windows authentication option on the ExcelCalculationServer folder from the Office Server Web Services website on IIS.

SSIS Free Expo Event

2011-02-15T06:02:00.000+13:00

Here is another Free Expo Event from SSWUG that covers Basic and Complex SSIS Features. The event is scheduled for February 18th 2011 from 9 am to 1 pm PST.

Sessions will cover the following topics:

SSIS Sorting and Package Protection
SSIS Package Checkpoints and Transactions
SSIS Native Logging Features
Best Practices with SSIS Package Design
Deploying, Scheduling and Administering SSIS in Production
Real-world Business Scenarios with SSIS

Speakers include the Knights of Pragmatic Works, a very good reason to attend :)
Register now

Friday Backup Job

2011-02-14T20:39:00.000+13:00

As mentioned in an earlier post since our systems administrator left our company I am taking care of the backups until the position is replaced. The backup scheduled for last Friday failed as the backup agent was stuck on a server. Since it was the weekend I thought I will rerun the backup after fixing up the cause. I had a look at the log and restarted the server where the backup agent was stuck.

Now I wanted to reschedule the Friday backup as it is with the same features as to going into the same tape (we have multiple tapes in the backup rack). So I was looking for some options as to how to do this when I saw the option "Retry the Job Now".

So I thought I will try this option. What I expected was the backup job to finish earlier as the ob was sstuck on the last server. So I expected it to be finished within an hour. But when I didn't get the alert even after 4 hrs, I started to get worried and logged in to check what was happening. Only then I realised that the backup was running from he beginning. This was the first time I tried this option and worked very well as the job has completed in the way I wanted without much configuration.

As you can see Clean Drive job failed and I will have to resolve this as well. :)

Another learning for me.

24 Hours of PASS Registration Now Open

2011-02-10T19:56:00.000+13:00

As mentioned in my earlier post the SQL PASS 24 hr sessions are scheduled for March 15-16 2011. Registration is now open for these sessions.
Here is an excerpt from the site.

The LiveMeeting webcasts will begin at 12:00 GMT (8am ET) on March 15 and run for 12 straight hours. They start again on March 16 for another 12 hours. This is your chance to join the elite group of hardcore #24HOP veterans who watch all 24 sessions! Learn more and register today!

Sharepoint usage reports

2011-02-09T21:23:00.000+13:00

Our Systems Administrator left our company last week after being 8 years with us. I have taken over the administration of our intranet which uses Sharepoint 2007 MOSS. I started to explore the site settings of the sharepoint web front end (WFE) from the past 2 days. I was excited when I saw the term site usage statistics under site administration as shown below.

But my excitement died down after seeing the following message. I wondered why our system adminsitrator did not set up the usage statistics. :(

So now I started exploring as to how to enable the usage statistics. So I logged into the sharepoint server and opened the central administration and started exploring application management and operations tabs. Finally under the Operations tab I found the Loggin and Reporting section as shown below.

I clicked the Usage Analysis Processing and looked at the options to set. I was surprised to see just 2 settings to enable site usage reports.

I have set them and will be monitoring the usage from now on. I am pleased with my learning and little discovery today.

Sharepoint Administration Expo By SSWUG -- February 11 2011

2011-02-02T09:41:00.001+13:00

Here is a link to the Free Sharepoint Administration Expo organised by SSWUG on February 11th 2011 from 9 am to 1 pm PST

Sessions will cover the following topics:

* Configuring SharePoint Anonymous Access: Tips and Tricks
* Understanding SharePoint Blogs, Wikis, and Discussion Boards
* Letting Go - It's so hard to do
* Infrastructure deployment via infrastructure and features

This expo is all about providing the foundation for providing outstanding service with SharePoint by SSWUG.

Hope these sessions are useful for some of you

Print formuals from Excel 2007

2011-02-01T09:05:00.004+13:00

Yesterday I had a requirement to compare formulas between two sheets. So I wanted to print the formulas and compare. This is a basic feature but I straight away did not know how to do it. So I started investigating the ribbon in Excel 2007.
After about 2-3 minutes Voila I found this.

Under Formulas Tab -- There was Show Formulas button in the Formulas Auditing Group as shown below. This button works as a toggle for showing and hiding the formulas.

24 Hours of PASS March Sessions Announced

2011-01-27T08:01:00.001+13:00

The 24 Hours of Pass sessions for March 2011 have been announced. Here is an extract from the site.

Hear from SQL Server and BI experts such as Jen McCown, Karen Lopez, Michelle Ufford, Wendy Patrick and Cindy Gross, just to name a few! Check out the great sessions in store and be sure to save the dates. Online registration will open soon; visit the 24 Hours of PASS website often for information updates.

Here is a list of the sessions in the BI Track, DBA Track and Dev Track. Can't wait to register!

BI Track
Session 02 (BI) – Start time 13:00 GMT on March 15
Dashboards Design and Practice using SSRS
Presenter: Jen Stirrup

Session 05 (BI) - Start time: 16:00 GMT on March 15
Cool Tricks to Pull From Your SSIS Hat
Presenter: Julie Smith

Session 09 (BI) – Start time 20:00 GMT on March 15
Multidimensional Thinking
Presenter: Stacia Misner

Session 12 (BI) – Start time 23:00 GMT on March 15
Many-to-Many Dimensions – ETL to Cube
Presenters: Lisa Phillip

Session 13 (BI) – Start time: 12:00 GMT on March 16
Reporting Services 201: the Next Level
Presenter: Jes Borland

Session 17 (BI) - Start time 16:00 GMT on March 16
Tips & Tricks for Dynamic Reporting Services Reports
Presenter: Pam Shaw

Session 19 (BI) - Start time 18:00 GMT on March 16
Intelligent ETL with SQL Server
Presenter: Jyoti Gupta

Session 20 (BI) - Start time 19:00 GMT on March 16
Clever Queries: Crafting MDX Queries to get the Most out of SSRS
Presenter: Erika Bakse

Session 22 (BI) - Start time 21:00 GMT on March 16
What You Don’t Know about SSRS 2008R2
Presenter: Kathi Kellenberger

DBA Track Session 01 (DBA) – Start time 12:00 GMT on March 15
SQL Server AlwaysOn: the Next Generation High Availability Solution
Presenter: Lara Rubbelke

Session 04 (DBA) - Start time 15:00 GMT on March 15
SQL Server Performance Tools
Presenter: Cindy Gross

Session 07 (DBA) – Start time 18:00 GMT on March 15
SQL Server Performance
Presenter: Isabel de la Barra

Session 10 (DBA) – Start time 21:00 GMT on March 15
Bad Plan! Sit!
Presenter: Gail Shaw

Session 11 (DBA) – Start time 22:00 GMT on March 15
Indexes and Execution Plans
Presenter: Kim Tessereau

Session 14 (DBA): Start time 13:00 GMT on March 16
Replication, Log Shipping and Mirroring: Oh My!
Presenter: Wendy Pastrick

Session 16 (DBA) – Start time 15:00 GMT on March 16
All about SQL Server Memory Settings for DBAs
Presenter: Vyshnavi Thota

Session 23 (DBA) - Start time 22:00 GMT on March 16
Index Internals for Mere Mortals
Presenter: Michelle Ufford

Session 24 (DBA) - Start time 23:00 GMT on March 16
TwitterData on Azure (end-to-end demo) – How We Did It
Presenter: Lynn Langit

Dev Track
Session 03 (Dev) - Start time 14:00 GMT on March 15
Spatial Data: Cooler Than You’d Think
Presenter: Hope Foley

Session 06 (Dev) – Start time 17:00 GMT on March 15
No More Bad Dates: Using Temporal Data Wisely
Presenter: Kendra Little

Session 08 (Dev) – Start time 19:00 GMT on March 15
T-SQL Code Sins: The Worst Things We Do to Code and Why
Presenter: Jen McCown

Session 15 (Dev) – Start time 14:00 GMT on March16
Entity Framework: Not as Evil as You May Think
Presenter: Julie Lerman

Session 18 (Dev) – Start time 17:00 GMT on March 16
T-SQL Awesomeness: 3 Ways to Write Cool SQL
Presenter: Audrey Hammonds

Session 21 (Dev) – Start time 20:00 GMT on March 16
Five Physical Database Design Blunders and How to Avoid Them
Presenter: Karen Lopez

SQL Bits Content

2011-01-25T23:25:00.002+13:00

I came across this link on SQL Bits where there are about 164 videos on various aspects of SQL Server and sharepoint which I found useful. Altogether there are 215 articles.

Have a look at the compiled sessions and content page.

SSWUG Free Expo Event: SQL Server Performance Monitoring and Tuning -- Jan 14 9 am to 1 pm PST

2011-01-14T09:03:00.001+13:00

Today I received an email about the free virtual expo from SSWUG

Here is an excerpt from the email.

Sessions will cover the following topics:

SQL Statement Tuning with Indexing Strategies (Presented by Kevin Kline)
Performance Tuning SSIS (Presented by Brian Knight)
Using DMVs to Diagnose Performance Issues with High OTLP Workloads (Presented by Glenn Berry)
Implementing Resource Governor (Presented by Buck Woody)

With registration, all attendees will also receive a complimentary month of full membership to SSWUG.org, where they can learn even more about SQL Server and other databases and database technologies through in-depth articles, podcasts, how-to videos and more. All the content will also be able for seven calendar days after Jan. 14, allowing attendees to revisit key portions of information at a convenience.

Please register now to save your place for this information-packed expo!

So register and enjoy!

Shortcut to enable Fullscreen in SSMS

2011-01-13T10:03:00.001+13:00

Today I was experimenting with the query results in Sql server management studio and saw this shortcut in he View menu of the SSMS.

So in order to show full screen I tried the shortcut -- Shift + Alt + Enter. The same shortcut works to come out of the full screen mode as well. What the full screen does is closes all the other windows and like object explorer, registered servers etc that you have open and maximises the query and results windows.

Likewise if you want to go to the properties just press F4.
To get the Solution Explorer window the shortcut is Ctrl + Alt + L
To get the Toolbox window -- the shortcut is Ctrl + Alt + X

Hope these shortcuts are useful for you.

Have you heard about BizIntelligence TV ?

2011-01-09T12:42:00.001+13:00

Yesterday I came across this blog on MSDN by Bruno Aziza.

Bruno says that Biz Intelligence TV is a new and innovative way to let industry leaders express and gain access to thought-leadership on Business Intelligence. The goal of the BizIntelligence.TV program is to start an ongoing conversation by providing compelling content from peers and industry stars.

Here is the Biz Intelligence TV channel on Youtube

SSIS 2008: Tips and Tricks video by Steve Swartz

2011-01-07T13:44:00.000+13:00

Today I listened to this very good informative video by Steve Swartz which I wanted to share with you all.

This is a presentation by Steve Swartz in the Microsoft TechEd Europe 2010. In this presentation Steve says that he desires to teach you how to fish rather than give the fish itself.

Thanks to Steve for posting this.

SSAS Interview Questions

2011-01-06T13:49:00.000+13:00

I was looking for some SSAS interview questions and found some very good resources on the web.

The links are below.
SSAS Interview Questions from Siddharth Mehta's blog
Sql Server 2005 Analysis Services Interview Questions
Dave Rodabaugh's Analysis Services interview questions
SSAS MDX Query interview questions
SSAS interview questions for MDX from Learn Microsoft BI
MDX Questions from datawarehousing guide

Hopefully these resources are useful for you as well.

Happy New Year 2011 to Everyone

2011-01-01T13:48:00.000+13:00

My first post this year is to wish all the readers here a HAPPY NEW YEAR!

As the new year blossoms, may the journey of your life be fragrant with new opportunities, your days be bright with new hopes and your heart be happy with love!
My main new year resolution is to start the certification process to achieve Microsoft Certified Technology Specialist (MCTS) Microsoft SQL SERVER 2008, Business Intelligence Development and Maintenance.

I have no doubt I will be using all the resources available here on BIDN in addition to getting some formal training in order to achieve that.

I look forward to all the suggestions in order to achieve this from all of the certified professionals in this area.

SSIS + CDC = SCD -- A PASS webinar on 20th December by Patrick LeBlanc

2010-12-17T12:39:00.000+13:00

I got interested in the title of this webinar and hence thought that all of you might be interested in this webinar event as well. Below are the details

Date / Time

Date:
12/20/2010

Start Time:
12:00:00 PM

End Time:
1:00:00 PM

Timezone:
(GMT-05:00) Eastern Time (US & Canada)

Short Description

Building dimensions using the Slowly changing dimension wizard in SSIS is simple and quick. However its performance and flexibility is questionable. Even further, when trying to perform incremental loads of your Dimensions using the aforementioned approach or a custom approach prior to Change Data Captured (CDC) offered certain challenges. In this session Patrick will show you how to utilize CDC and SSIS to incrementally load Type I and Type II dimensions ;using features that are all native to SQL Server 2008.

Event Description

Speaker: Patrick LeBlanc
Patrick LeBlanc, SQL Server MVP and Author, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events and a PASS Regional Mentor.

Mon Dec 20th 12pm EST SSIS + CDC = SCD

URLhttps://www.livemeeting.com/cc/usergroups/join?id=53BGCZ&role=attend&pw=3%3C%2C9%27CDcs

SQL Azure free trial

2010-12-16T13:54:00.001+13:00

Here is a link to the special free trial offer for SQL Azure

For a limited time, new customers can sign up for SQL Azure and get a 1GB Web Edition Database for no charge…and no commitment. We ask for your credit card information when you accept this offer, as any additional usage per month will be billed at the standard rate. This is a great way to try SQL Azure – and the Windows Azure platform – without the risk.

Denali Resource Centre

2010-12-10T09:46:00.001+13:00

This morning I found the following link on the MSDN blogs that is quite useful. There are plenty of articles and links to community blog posts, as well as 12 videos about the new functionality in SSIS.

Head over to the SQL Server Denali Resource Center.

Enjoy Denali using this resource centre

Enable Macros in Access 2007

2010-11-30T09:50:00.000+13:00

We have a couple of access programs that we use for data conversions. These programs convert the csv files that are generated from our CRM to a fixed file format that is then uploaded to interface with other government institutions.
With the introduction of OFfice 2007 there is this extra step that the users were required to do to enable macros manually every time they use these programs.
I thought this manual should not be there for the users so I set out to look at all the options available to automate this step.
HEre are the results.
Open the access file -- click on the office button at the top and click on Access Options
From the menu that appears choose the Trust Centre and click on the Trust Centre Settings.
From the menu that appears choose Macro settings and Enable (the fourth option) and click ok.
Next time when you open the porgram the annoying notification where there is a tendency to forget to enable will disappear.

39 videos on Microsoft BI Tools

2010-11-26T08:40:00.002+13:00

I stumbled across this site today that contains very useful videos on Microsoft BI tools.

There are 39 videos of which I have listed the top five from thier site here.

The first video is a twelve minute introduction entitled "What is Business Intelligence?" This video covers what is meant by terms such as data warehousing and business intelligence and why companies undertake such projects.
The second video is a 34 minute overview of how a single data warehouse can be used to deliver business value to a wide variety of users through scorecards, dashboards, reports, analytic applications, and custom applications.
The third video discusses the process of data warehousing, from the initial problem definition through the creation of the cubes and delivery of the data. This video runs 19.5 minutes.
The fourth video is "Why Business Intelligence Projects Fail (and what you can do about it)." This covers some of the primary reasons that BI projects fail along with tips for addressing the problems. This video is 32.5 minutes in length.
The fifth video is "Introduction to Business Intelligence Development Studio" and covers the primary tool used to create data warehouses. This is the environment for creating SSIS packages, SSAS cubes, and SSRS reports. This video runs 15.5 minutes.

So go ahead and register and enjoy the videos

Show/Hide Office Ribbons

2010-11-25T08:12:00.001+13:00

Yesterday I was in a presentation and because we wanted to see most of the screen, there was a requirement to hide the top ribbon see the image below.

I suggested to double click on the menu item to hide the ribbon. That is, double click either on Home, Insert, Pge Layout etc.

Someone in the room suggested a keyboard shortcut Ctrl+F1. That worked too.
Also keep in mind that this works across all Office 2007 and 2010 applications including Excel, Powerpoint, Access, Outlook etc.

I thought of sharing this here so that in case you have a similar requirement you could use it.

Project Crescent BI Tool for Denali

2010-11-23T22:45:00.000+13:00

I found the following interesting announcement on the SilverLight Team Blog about Project Crescent.

Here is an extract from the announcement.

Project Crescent allows business users to manage data and show information in a truly innovative and exciting way, allowing people to visualize, interact and report on data using highly interactive visualizations, animations and smart querying.

One of Crescent’s most exciting features is its integration with PowerPoint. Through a feature called Storyboarding, users can embed reports in PowerPoint and manipulate the live data during a presentation.

New Path to Microsoft Certified Master (SQL Server 2008)

2010-11-17T22:31:00.001+13:00

I came across an interesting blog link yesterday by Joseph Sack which outlines the new path to get certified for Microsoft Certified Master.

The current MCM program requires you to take three written exams and a six hour lab exam which would cost around 15,000 USD.

The new MCM program requires you to take a knowledge exam and a lab exam which would cost $2500.

In his blog, Joseph says -- "It’s our goal to reach the SQL Server experts worldwide who may be qualified to achieve MCM certification, but who’ve run up against the previous barriers of time, cost and location. By reducing or removing these barriers, while keeping the integrity and value of the certification, we expect to grow the community of SQL Server MCMs and increase its visibility and awareness of its value."

I hope this helps all the people who are trying to get certified as MCM.

PASS Summit 2010 Day 2 Keynote recording

2010-11-16T22:27:00.000+13:00

If you have missed the PASS Summit ay Two Keynote by Quentin Clark you can view the recodring below.

PASS Summit DAy TWo KeyNote Recording

Speaker: Quentin Clark, General Manager of Database Systems Group at MicrosoftWith opening remarks from Bill Graziano, VP Finance at PASSQuentin Clark will showcase the next version of SQL Server and will share how features in this upcoming product milestone continue to deliver on Microsoft’s Information Platform vision. Clark will also demonstrate how developers can leverage new industry-leading tools with powerful features for data developers and a unified database development experience.

PASS Summit -- Women In Technology luncheon recording

2010-11-15T22:25:00.000+13:00

If you have missed the WIT Luncheon live streaming event, you can now click on the below link to view the recording.
PASS Summit Day WIT Luncheon

The panelists for the WIT luncheon were as below:

Billie Jo Murray, General Manager, SQL Central Services, Microsoft
Nora Denzel, Senior Vice President and General Manager - Employee Management Solutions, Intuit
Michelle Ufford, Senior SQL Server DBA, GoDaddy.com
Denise McInerney, Staff Database Administrator, Intuit
Stacia Misner, Principal, Data Inspirations

PASS Summit Day One Keynote recording

2010-11-12T22:24:00.000+13:00

If you have missed the Day One Keynote live streaming event, you can now click on the below link to view the recording.
PASS Summit Day One keynote recording

You can also get a copy of just released PASS 2010 Database Security report: "Data in the Dark: Organizational Disconnect Hampers Information Security"

Thanks to the PASS organsiers for providing these to all of us who are not able to attend the SQL PASS Summit.

List of users and their permissions

2010-11-11T22:23:00.000+13:00

Yesterday I wanted to know which users have what permissions to a database. So I started querying the two tables related to it.
select * from sys.database_permissionsselect * from sys.database_principals
I came up with the following script to suit my needs.

select USER_NAME(perm.grantee_principal_id) AS user_name, princ.principal_id, princ.type_desc AS principal_type_desc,
perm.class_desc,
OBJECT_NAME(perm.major_id) AS object_name,
perm.permission_name, perm.state_desc AS permission_state_desc
from sys.database_permissions perm
inner JOIN sys.database_principals princ
on perm.grantee_principal_id = princ.principal_id

There may be numerous other ways to do this.

Grant and Revoke Privileges

2010-11-10T22:21:00.000+13:00

Today I have accidentally given access to a user using the grant option by issuing the Grant sql command.

GRANT VIEW ANY DATABASE TO username;

GO
I have immediately realised that I didn't want to give access to that user and hence I had to revoke the permissions.

REVOKE VIEW ANY DATABASE From username;

Thought I would document this here so that any one doing the same would benefit from this.

Also there is a With Grant option that you can use while using Grant option.

The main difference between Grant and With Grant option is that --
If only GRANT is used, the user cannot grant the same permission to other users.

IT Systems Viability

2010-11-09T22:19:00.000+13:00

We have been using a CRM application heavily customised to fulfil our needs as a Training Management System for the last 10 years. Now the applications vendor has changed the technology behind this application to be in line with the latest technology and released a brand new application.

This means that we have to rethink the capability of our heavily customised system in future as it is not a case of simply upgrading to the new application. It is basically a rebuild of all the functionality we have developed over the last 10 years.

This situation has made me think of the question – How long should IT systems be viable? Of course there is no right or wrong answers to this question as it depends entirely on the business requirements and the functionality available at the time in the IT systems being used.

The bottom line is our expectations keep on changing as per the business and most systems cannot keep pace with these changes. Hence there is a need to choose new systems.
The life span of Systems has a big impact on the business costs. You can reduce the cost of replacement if the systems are utilised longer. We are thinking of utilising our CRM application for a bit longer as the functionality released by the new version of the application is no where near the functionality that our current system provides. WE just have to make sure that we do not roll out new versions of other applications like office and operating systems without proper testing for compatibility.

What are your views of IT systems viability?

The hardly used stuff function

2010-11-05T22:16:00.000+13:00

My share of todays learning at sql share is the unusual stuff function. I have not heard of this function before and here are my learnings from the video by Andy Warren.

Stuff function inserts a string into a string at a specified position. It feels a bit like REPLACE (and you can certainly use it that way), but it's really designed to do something different - push x characters into a string based on an index and length.
Andy was surprised to realize it was the only string function he had never used to solve a problem.
If you try to use the stuff function to insert a null at a certain position of the string to replace a few characters, it does not error but it just returns the remaining characters after removing the number of characters you have specified.
If you try to use the stuff function that has null in its string, no matter what you try to replace it with it returns null

To learn more watch the video by Andy Warren.

PASS Summit 2010 Live Streaming Events

2010-11-04T22:14:00.000+13:00

Following is a post from the PASS website. This also provides a link the free live streaming events.

Tune in for our Live Keynote Addresses from Top Microsoft SQL Server Executives
Top Microsoft SQL Server executives, Ted Kummert, Quentin Clark and David DeWitt, will take center stage in Seattle, Washington at PASS Summit 2010 to share the latest and greatest SQL Server news on Nov 9, 10 and 11 from 8:00am to 10:00am Pacific each day.And join us on Wednesday, November 10 from 12 noon-1:30pm Pacific for the live streaming of the 8th Annual Women in Technology Panel Discussion on Recruiting, Retaining and Advancing Women in Technology: Why Does it Matter?

Register below for the live streaming keynotes and we'll send you the link to the live streaming event.

PASS Summit is the education and networking event of the year for thousands of SQL Server professionals.

If you have not already registered I strongly recommend you to register by clicking here

Quotename Function in SQL

2010-11-03T22:12:00.001+13:00

I have learned something very basic today from the sql share video today. It is about the Quotename TSQL function. This function I think is hardly used and hence some of the developers may not be aware of this. Hence I thought I will put my learning about this function here.

Quotename function returns the string along with the delimiters added in the function.
This function can be used if you are using the delimiters repeatedly.
If you are using delimiters on an ad-hoc basis you can just use something like select "-" +@variable +"-"
One requirement for using this funciton is that the string cannot be more than 128 characters long as depicted in Andy's video

Thanks to Andy Warren who explains the various ways of using the quotename function in this video.

Copy Paste problem in excel 2007

2010-11-02T22:10:00.000+13:00

Today one of my colleagues was working in excel. He had a dataset that in one sheet which was filtered. He wanted to copy those records that were filtered into a new worksheet. But when he copied and pasted the cells into another sheet the cells that were pasted contained all the rows. What he wanted to copy was only the filtered rows. This becomes a problem sometimes in Excel 2007

To overcome his paste problem I have suggested the following steps

Go to the Editing group in the Home tab
Click on the "Find and Select" button
Click on the "Go to Special" from the list
And click on "Visible cells only " radio button and click ok

Now you can copy and paste only filtered rows in excel.

Another quick turn around is open the workbook in another computer where the filtered cells copy paste works and do the first copy and paste and close the worksheet.
Now if you open the spreadsheet on the first computer the filtered cells copy and paste works !

Improvements on the Learning feature of sql share

2010-10-22T13:21:00.001+13:00

Andy Warren of SQL Share is bent upon motivating learning to all the sql share subscribers. Here are some of the new features he has included in his recent emails.

The first feature he launched was to show the learning goal in minutes per month. Now we can set an annual goal and monitor our learning.
The other new feature is you can log in your own learning which is outside of sql share so that all learning can be monitored which is a really cool feature which helps the learner focussed on all the learning he is undertaking.
All these can be monitored from the Profile page.

Thanks to Andy for providing these tools which helps all of us in our learning.

If you have not yet subscribed for the sql share videos please do so by clicking here

Using Registered Servers in Managment Studio By Andy Warren

2010-10-19T13:18:00.000+13:00

Today's featured video on SQLshare / Jumpstart TV is Using Registered Servers in Managment Studio By Andy Warren . Until I saw this video I didnot know about the Registered servers view in sql server 2005 even though I have been using the Management studio for the past 3 years.

Here are my learnings of today.

Ctrl+Alt+G is the shortcut for getting in to the registered servers pane. Alternatively click on the -- View menu -- Registered servers
From the registered servers pane you create a server group by right clicking on the database engine and creating a new server group.
Once a group is created you can also create a registered server instance by right clicking on the server group and creating a new server registration
You can rename the server from the Register server name
By double clicking on the registered server instance you will be taken to the object explorer which I am familiar with.
Most important thing is the settings that you have on your local machine are not reflected on the server and also it is not going to change anything about the server, it is not going to change how we connect to it or how our users see it.
Setting custom colour is a very useful new feature (available only in sql server 2008) discussed by Andy in this video. This can be used to differentiate the test servers with the production servers.
You can export registered server inforamtion to a file by right clicking on the server instance and choosing export. This feature is very useful when you are moving machines.
The option "Do not include user names and passwords in the export file" is also very useful if you are giving the export file to a new DBA or another user.

Thanks to Andy Warren for helping me learn all this within 4 min. 50 secs. If you are interested in viewing this video please click here

When my joystick started giving in...........

2010-10-18T13:16:00.000+13:00

The other day I had about 20 excel workbooks to work with in a single day to complete some outstanding work in a tight timeframe. In each one of the excel workbook I was working on, there were about 20-25 worksheets. I use a joystick instead of normal mouse, and we didn't have any spare joysticks. My joystick thought that I have had enough of this and started giving in to my moves. I didn't want to increase my RSI by uisng a normal mouse. Instead I thought I will use some excel key board short cuts to work in these excel files.

Here are the shortcuts that I thought will come handy to everyone.

CTRL + PgUp -- To switch to the right hand side of the sheets
CTRL + PgDn -- To switch to the left hand side of the sheets
F4 -- To repeat last action in Excel (This works only in Excel, I wish it worked in word as well!)
CTRL + --> -- To move to the last populated cell on the right hand side
CTRL + <-- -- To move to the last populated cell on the left hand side
CTRL + down arrow -- To move to the last populated cell in the downward direction
CTRL + --> -- To move to the last populated cell in the upward direction.
If you hold down the SHIFT key as well for the above four commands the cells are selected.
CTRL + Spacebar -- To select an entire column in a worksheet
SHIFT+ Spacebar -- To select an entire row in a worksheet

All the above shortcuts helped me in doing my job quickly that day in spite of having a problem with my joystick.

String Handling Functions - Part 1 By Andy Warren

2010-10-14T10:33:00.000+13:00

In this video, within 6 min. 29 secs Andy not only shows the usage of Lower, upper, left, right, ltrim and ltrim functions but also displays a few what if scenarios which we may otherwise not think about if we just go by the book.

Here are my learnings:

upper and lower functions does not return an error when used with null data.
The second argument for left and right functions should be a positive value and cannot be a negative value.
If a negative value is used the select statement errors out.
If a zero is used as the second argument it does not return an error but it just returns a blank row.
There is no trim functions. If you need a trim function in sql then combine the ltrim and rtrim functions to get the desired result. Eg: rtrim(ltrim(@test))

Another interesting feature in SQL Share / Jump Start TV

2010-10-11T10:31:00.000+13:00

I noticed another interesting and innovative feature in the SQL share email today. The email features "Your Scorecard". A view of it below.

This scorecard tells me that I have done 35 minutes of learning and my goal should be 120 minutes per month.
I think this is a great feature to motivate you and monitor yourself of how much you are learning. If you can set yourself a goal of 120 minutes of learning per month that should be really good for your career development.

So if you are not yet subscribed please do so from the sql share login link

Delivering KPIs with Analysis Services -- 24HOP recording by Peter Myers

2010-10-11T10:27:00.000+13:00

Today I had the chance to view the 24HOP session recording "Delivering KPIs with Analysis Services by Peter Myers"

Here are my learnings.

What are KPIs?

KPI -- Key Performance Indicators are quantifiable measures comparing business performance to goals
KPIs are aligned with corporate strategy and objectives.
KPIs are designed to drive desired behavours
KPIs presents a measure of overall organisational health when combined into a collection for a business scorecard

KPI data requirements

A KPI should have at least an actual and a target value
Ideally corporate data systems will deliver both values
Actuals are typically retrieved from operational databases
Targets can be retrieved from planing systems
Sometimes the target values can be stored in supplementary data stores or can have fixed traget values.

The Demos included covered the following aspects.

Preparing the cube to store target values
Seeding arget values based on historical actual values using simple factor, data mining (time series)
Contributing target values using Excel 2010

Using Checkpoints in SSIS By Brian Knight

2010-10-08T10:06:00.000+13:00

Here is the link of today SQL Share / Jumpstart TV video. Prior to viewing this video I had no knowledge of what checkpoints are.... here are my learnings out of this video.
What are checkpoints?

Checkpoints are setup to ensure that you can run the package from the failure point. These checkpoints are particularly useful when packages take a very long time to run.

When the package fails -- the problem can be fixed and the package is restarted. When checkpoints are used when the package is restarted the package restarts from the point where the package has failed. This will save alot of time for the DBAs and Developers.

Here is the process for creating simple check points by Brian

Step 1 Configuring the packageIn the package properties pane give a name to the property -- checkpointfilename The next property to be set is the CheckPointUsage -- Choose the If Exists option.The next property to be set is the SaveCheckPoints -- set it True
Step 2 Task propertiesChoose the task in the control flow and under the Execution group of the task properties falipackageonfailure -- TrueSet this property to True for all the tasks in the package.
This is the process of creating the checkpoints in the control flow layer in SSIS packages.

Converting Crystal reports to SSRS

2010-10-07T09:46:00.000+13:00

We have a number of crystal reports that we use and in the near future we need to convert them into SSRS reports. I have done some research on the internet and here are my findings.

The first one is a manual migration process suggested by Microsoft -- Link here I cannot imagine converting almost a 1000 reports to SSRS reports. I cannot imagine myself taking up this process.

The second one which came up in my search was the rpt to rdl website run by Jeff-Net. They have two types of conversion service as they call it. Jump Start conversion service and Full Conversion service. They say that it is a service and they donot have a product.

The third one was a product called Crystal Converter by KTL Solutions You can download a Demo version of this product and try out for yourself.

The fourth one is an online service called Crystal Migrator where you can give your email address and upload a single crystal report at a time. The converted report will be emailed to you.

This is just a list of what I have found worthy of note. I still haven't tried any of these yet. Will post my feedback once I try them.

If I have missed out anything please let me know so that I can update my list.

Notepad Basics

2010-10-05T09:09:00.000+13:00

The other day my colleague showed me the menu item "View -- Status Bar" in the notepad. I always used to see a grayed out option of the status bar in the past. But this time I could choose the option to actually show the status bar in the notepad. This status bar shows you the curent postion of the cursor in terms of line and character position.

I was puzzled at this and did some research and found that if you enable the wordwrap feature the status bar option is grayed out. If you disbale the wordwrap feature then the status bar option is enabled.

I thought this would be useful particularly when a code page needs to be (is) opened in notepad.

Introduction to the CASE Expression By Andy Warren

2010-10-04T09:06:00.000+13:00

I have used case statements before several times but there were some new learnings from the SQL Share video of Andy Warren

Here are my learnings of the case statement summarised:

People often get confused whether to use if else statements or case statements in T-SQL. According to Andy, only case statements can be used in T-SQL statements. If else statements cannot be used in select statements because if else statements can be used in control flow.
Following are some of the situations and syntax that can be used with case statements. The most important is included in the last syntax (Syntax 5)

Syntax 1:
case columnname when 'exisitng value1' then 'new value1'when 'exisitng value2' then 'new value2'end as aliascolumnname
The drawback with the above syntax is when the conditions of the when statement is not matched the result returned is null in the column.

Syntax 2:
case columnname when 'exisitng value1' then 'new value1'when 'exisitng value2' then 'new value2'else columnameend as aliascolumnname
When the else condition is used the drawback in Syntax1 is fixed.

Syntax 3:
If you want to use case statement for multiple columns the syntax is as follows;
case when column1='exisitng value' then 'new value'when column2='exisitng value' then 'new value'else column1end as aliascolumnname
Notice that there is no column name after the case in the above statement.

Syntax 4:
Nested case statementswhen formatted looks better and you can understand better
case when columnname1 = 'exisitng value' then case when clumnname2 = 'exisitng value1' then 'new value' else columnname1 end else columnname1end as aliascolumnname
The above can also be written in one case statement doing two tests.

Syntax 5 Last but not the least (Very important learning of the day):
You can use case in the order by clause
select columnname from table name order by case when columnname = 'value1' then 0 else 1 end, columnname.

Home » Blogs » indupriya » 24HOP PASS recordings are now available24HOP PASS recordings are now available

2010-10-02T09:05:00.000+13:00

The much awaited 24 hours of PASS session recordings are now available on the PASS website.

This requires a PASS member login. If you have not done so already, a PASS member login with username and password (free and simple to set up) is required to access the 24 Hours of PASS session recordings.

Aggregrate Queries in SQL Server T-SQL for Beginners By Kathi Kellenberger

2010-10-01T09:03:00.000+13:00

My learnings from the sql share video

I knew that we cannot use where clause with aggregate functions but I didn't know the reason. The reason is the where clause is executed before the aggregate functions are applied and hence cannot use the where clause with.
Count(*) and count(columnname) may yield different results. This is because Count(*) does not ignore null values and count(columnname) ignores or does not count null values.
When you do aggregation using the AVG function using the isnull function is recommended. Use of isnull function to substitute the null value of the column will give you the correct answer. eg: AVg(IsNull(columnname,0)
Always remember to supply an alias for each aggregated function
You can use other expression in the aggregate functions instead of just the column names. eg: sum(1) as "sum of ones"
When you use a group by clause, make sure select items are exactly same as group by clause, otherwise you might get incorrect results.

Home » Blogs » indupriya » Free SQL Server Training ResourcesFree SQL Server Training Resources

2010-09-30T09:01:00.000+13:00

Yesterday I stumbled across this site while looking for something else. I couldn't find who the author is for this peice of work.

This page contains a very comprehensive list of resources for SQL server developers and DBAs. Here is a snapshot of the table of contents from thsi site.

Table of Contents
Training: Labs
Training: Podcasts
Training: Tutorials
Training: Videos
Training: Webcasts
Training: Whitepapers
General Skills
Skills: SQL
Skills: Windows Scripting
SQL Server
SQL Server 2008 R2
SQL 2008 R2: Virtualization
SQL Server 2008
SQL 2008: Administration
SQL 2008: Analysis Services (SSAS)
SQL 2008: Data Mining
SQL 2008: Filestream
SQL 2008: High Availability
SQL 2008: Integration Services (SSIS)
SQL 2008: Performance
SQL 2008: Reporting Services (SSRS)
SQL 2008: Power Shell
SQL 2008: XML
SQL Server 2005
SQL 2005: Administration
SQL 2005: Analysis Services (SSAS)
SQL 2005: Data Mining
SQL 2005: High Availability
SQL 2005: Failover - Cluster
SQL 2005: Integration Services (SSIS)
SQL 2005: Integration Services (SSIS) Migration
SQL 2005: Performance
SQL 2005: Reporting Services (SSRS)
SQL 2005: Windows Scripts
Virtual Server

SQL Share a very useful video resource -- some positive changes that are helpful

2010-09-29T13:05:00.004+13:00

For those of you who don't know about SQL Share here is a snippet from thei About Us page.

SQLShare is a division of Fourdeuce Inc, an Orlando, FL based company that also owns End to End Training, a well known provider of technical training. Fourdeuce is owned by Andy Warren, Brian Knight, and Steve Jones.

I have been getting daily emails from SQL Share since my subscription in April 2010. I used to watch some of the videos. These are small videos that cover various aspects of SQL SERVER by various authors. This is a very good resource for beginners and sometimes helps professionals as well. Everybody will learn something from here. There is no doubt about it.

But getting the email today made a difference compelling me to watch it as soon as I saw the email.

The reason for it is below. Have a look at the image below.

I just couldn't click on "Watch it Later" because I wanted to see because it is written in bold writing clearly that it is going to take only 3 min.

If you don't click on the link after seeing the "Learn Something today in 3 min. 46 secs" which is a minute fraction of 24 hrs in a day then I must say that you are not commited to learning. I looked at the topic and thought "Let me see what I will learn in this which I do day in and day out" But I did learn two points which I have posted in my previous blog.

Andy in his email said that he is experimenting this week. I think this little change will influence a lot of peopl in their learning goals and also credit the author of each featured video who take lot of pains recording it to be useful for the community.

Thanks to Andy who is doing a good job of sending these innovative emails.

Another very important change that was done is when you finished watching the video automatically two multiple choice questions come up to test whether you were really concentrating on the video which I found really useful.

Added to this there is a link "challenge your friend" which I quite like that we can send to colleagues for them to answer if they can.

Using Login Auditing in SQL SERVER by Brian Kelly

2010-09-28T08:25:00.000+13:00

Today featured video in SQL Share was Brian Kelly's "Using Login Auditing in SQL SERVER". I watched this 3 min. video and learnt the following.

I know that we can view sql server error logs. I monitor them on a daily basis but I didnt know that the login audit requires a setup that is a bit similar to the normal windows event log set up. This can be set up when you right click on the sql server connection in the management studio and choose properties and then choose Security. There is n option to choose Failed logins only, Successful logins only and both failed and successful logins and of course the none option aswell.

These login auditing will not only appear in the sql server error logs but in the windows event logs as well.

The best part that we are getting as part of watching the sql share videos is testing the knowledge we have acquired immediately after the video finishes which I think is very innovative.

If you would like to watch the same video click on the link

What is Sargability?

2010-09-18T09:19:00.000+12:00

I planned to attend Rob Farley's 24HOP session but could not make it. I didn't know what Sargability meant at the time and thought I will google it.

And here is what I have found out thanks to the internet.

Here is a blog post which gives the history of how the word came into existence back in 2005 where Brad McGhee explains that the sargability is a real word but it is used a lot in the SQL Server world.

So what exactly does Sargable mean?
SARG is a contraction for Search ARGument ABLE as mentioned in Rob Farley's article.

I am looking forward for the recording of this 24HOP session. Meanwhile you can have a look at the posting by Rob Farley in his blog where he gives links to the resources he has mentioned in his presentation.

My takeaways from Adam Jorgensen's 24HOP session -- Zero to cube

2010-09-17T10:14:00.002+12:00

Here are my takeaways from today's fantastic session of Adam

The first basic point was you can use "." instead of "localhost" to choose the localhost server.
Defining the attribute relationship was very clearly explained that the relatiosnhps should be from the lowest level to the highest level.
Proactive caching -- available in from SQL SERver 2008 but the refresh property is available in Excel 2007 but sometimes may not work properly.

The following two resources which are quite useful was also discussed.

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

Microsoft SQL Server Product Samples: Database

Free SQL Tools as blogged by

2010-09-10T13:34:00.001+12:00

Today I stumbled across this very useful post by Mladen Prajdić

Click here to read more

As he says in the end ...

Even if none of this stuff helps you at the moment it's good to know they exist.

Because DBA-ing is like a box of chocolates, you never know what you're going to get :)

What are plan guides?

2010-09-02T14:20:00.002+12:00

Just a while ago I was watching a video on sqlshare Creating a Plan Guide for a Parameterized Statement By Andy Warren . Only then I heard about plan guides. I didn't know what plan guides were. Here is my find from googling

In SQL Server 2005, there is a new feature called Plan Guides that can help out in some cases where you discover poorly performing queries that you don't have direct control over. Essentially, a Plan Guide allows you to add or modify query hints to queries on the fly, just before they are executed. Click here for a detailed article on plan guides by Brad McGehee.

Count number of months between two dates in excel

2010-08-16T12:11:00.001+12:00

The formula I used for this is

datediff(cell1,cell2,"m").

This will give you the number of months.

Automatically forward email messages to another user

2010-08-02T15:50:00.001+12:00

Today I had a requirement to automatcially forward email messages to another user, since this user has left the organisation.

On my research, I have come up with this article which gives a detailed step by step instructiions

Click here to go to that article

MMC cannot open -- in Citrix

2010-07-30T10:32:00.003+12:00

Today I had a problem with our citrix server, where in the Management Console was not opening as usual when I try to open any of the below processes. Citrix Access Management console, Event Viewer, Manage Server, Administrative tools etc.

but I could access the event viewer from another machine. Here is what the event viewer error message says.

Here are the steps I have been advised to follow by Aaron Mountford (gen-i consultant) to resolve this.

I have registered the two dlls as follows.

Click Start, click Run, type regsvr32 Msxml3.dll, and then click OK
Click Start, click Run, type regsvr32 %systemroot%\system32\inetsrv\wamreg.dll, and then click OK.

Convert a date to show AM/PM in SQL

2010-07-29T22:53:00.001+12:00

Today I was working on a date column -- create_date. I wanted to show whether the row was created in the morning or in the evening i.e., AM/PM

This is what I did to achieve this.

SELECT rn_create_date, SUBSTRING(CONVERT(varchar(20), Rn_create_date, 22), 18, 3) AS Expr1
FROM tablename;

Here is the link to the BIDN blog where I have posted

2010-07-29T13:55:00.002+12:00

If we are used to the Query Analyser of SQL Server 2000, we might find it frustrating that the query window does not automatically come up when you start SSMS.

You have to either press Ctrl+N or Click on New Query to start it up.

Here is a way to start up the New Query window automatically.

Click on Tools-- Options click on the + to exapnd environment and click on General.

The following screen comes up.

Click on the drop down beside start up and choose ' Open Object Exlpoer and New query'.

Click Ok. Next time when you open SSMS the query window will automatically be opened for you.

Here is link to the blog I posted in my BIDN blog.

Hiding your folders using command prompt

2010-07-23T13:56:00.001+12:00

To hide your folders using the command prompt, click start -- run -- Type cmd and press enter

The commoan screen appear.

Do the following:

To hide, d:/>attrib +h +s +r

To revert d:/>attrib -h -s -r

The folder ill be still hidden even if you set 'show all hidden files' under folder view options

Make sure you keep a note of your fodler name otherwise you cannot unhide it.

Star Schema vs. Snowflake Schema

2010-07-22T13:50:00.000+12:00

Star Schema

• Start schema is simpler and hence good to use for small data warehouses. The rules that apply here are as follows:
• Each dimension is represented by a single dimension table
• Each dimensional table is related to or linked to a fact table.
• The relation is merely a master and detail relationship with Primary key being the dimension and reference key in the fact table.
• When there are 5 or more dimensions referring to one fact table it appears like a star and hence the name star schema
• In terms of ease of use you need less complex queries and easy to understand
• This design has redundant data and hence hard to maintain and change.
• Lesser query execution time due to lesser number of queries

Snowflake Schema
• Snowflake schema starts like a star schema and more complex and hence it is good to use the snow flake schema for large data warehouses. The rules that apply here are as follows:
• Each dimension is represented by a two or more dimension tables
• Each dimensional table is not directly related to fact table
• Here the tables that describe the dimensions are normalised.
• In terms of ease of use you have to use more complex queries
• There is no redundancy and easy to maintain and change
• More query execution time because of more foreign keys

Make movies online

2010-07-16T10:00:00.000+12:00

Today I came across this site where you can make movies on the web. Here is the link.

You can start creating movies just by typing your text.