Enable Macros in Access 2007

• 
  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

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

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

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)

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

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

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

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

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

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

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

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

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

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

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 !