Improvements on the Learning feature of sql share

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

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...........

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

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

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

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

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

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

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

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

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

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.