- 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
Friday, October 22, 2010
Tuesday, October 19, 2010
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
Monday, October 18, 2010
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.
Thursday, October 14, 2010
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))
Monday, October 11, 2010
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
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
Friday, October 08, 2010
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.
Thursday, October 07, 2010
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.
Tuesday, October 05, 2010
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.
Monday, October 04, 2010
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)
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.
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.
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.
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.
Saturday, October 02, 2010
Home » Blogs » indupriya » 24HOP PASS recordings are now available24HOP PASS recordings are now available
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.
Friday, October 01, 2010
- 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.
This morning I was trying to call a stored procedure and insert the results in a temp table using the following code -- create table #...
The following features cannot be saved in macro-free workbooks VB Project To save a file with these features, click no, and then choose ...
I have been in hibernation for too long now. I have changed jobs and now hjaving an opportunity to work on SAS code after nearly 11 years. ...