Shortcut key to display Macro dialogue

In excel the shortcut key to display the macro dialog box is Alt+F8. This can then be used to create, run, edit, or delete a macro.

Crystal reports not working from CRM system

today I had a problem where the crystal reports that render through the CRM system that print on a label printer were not printing. The error coming up was - "Error printing to crystal reports." Our system admin tried to reinstall the printer software, but that didnot fix the problem. Then I asked him to check whether it is the Microsoft updates.

Then he restarted the system and allowed the updates to go through and that fixed the problem.

I had a requirement to import an excel 2007 spreadsheet into a database as a table in sql server 2005. In Excel 2007 the drivers required for exporting have changed when compared to the previous versions. With Excel 2007 you use the driver named 'Microsoft Access 12.0 database engine OL DB provider'

Following are the steps to import data into sql server 2005 from Excel.

• Right click on the database from sql server management studio and choose tasks -- import data
• Choose the data source as the driver name specified above ('Microsoft Access 12.0 database engine OL DB provider')
• Click on properties button and click on All tab
• Double click on the data source line and give the file name with the exact path in the property value field. Click ok
• Double click on the Extended properties line and enter Excel12.0 in the property value field. Click ok twice.
• Click next through the import wizard and preview the data and click finish.

Your data is imported into the sql server database.

I have attended the idera sponsored webinar on 'Top 10 Most Useful SQL Server DMVs' by Greg Robidoux

Here are my learnings of that webcast

What are DMVs
Dynamic Management Views are views and functions introduced in sql server 2005 for monitoring and tuning sql server performance.

Dynamic Management Objects (DMOs)
Dynamic Management Views (DMVs) -- can select like a view
Dynamic Management Functions(DMFs) --Requires input parameters like a function

When and Why use them
Provides information that was not available in previous version of sql server
Provides a simpler way to query the data just like any other view versus using DBCC commands or system stored procedures

Types of DMVs

• change data capture
• common language runtime
• database mirroring
• database
• execution
• full-text search
• I/O
• Index
• Object
• Query notifications
• Replication
• Resource governor
• SQL Operating System
  

Get a list of all DMOs

select name, type_descfrom sys.all_objects where name like 'dm%' order by name

Permissions

Server scoped-- view server state

Database scoped--view database state

Deny takes prescedencedeny state or deny select on an object
People should have sys admin privileges

Grant permissions

grant view server state to loginname

grant view database state to user
deny view server state to loginname

deny view database state
must create user in master first

Specific types of DMVs

• database
• execution
• IO
• Index
• SQL operatng system

Database for page and row count

select object_name(object_id) as objname, * from sys.dm_db_partition_stats order by 1

Tips 1851 -- mssqltips.com

Execution--- (when sql server is restart everything is reset)
sys.dm_exec_sessions-- info about all active user connections and internal tasks

sys.dm_exec_connections-- info about connections established

sys.dm_exec_requests-- info about each request that is executing (including all system processes)
Tips 1811, 1817, 1829, 1861

Execution--- Query plans
sys.dm_exec_sql_text--returns text of sql batch

sys.dm_exec_query_plan--returns showplan in xml

select * from sys.dm_exec_query_stats-- returns stats for cached query plans sys.dm_exec_cached_plans--each query plan that is cached

Exection -- example

select * from dm_exec_connections cross apply
sys.dm_exec_sql_text(most_recent_sql_handle)
select * from dm_exec_requests cross apply
sys.dm_exec_sql_text(sql_handle)

Select T.[text],p.[query_plan], s.[program_name],s.host_name, s.client_interface_name, s.login_name, r.* from sys_dm_exec_requests r inner join sys.dm_exec_sessions S ONs.session_id = r.session_idcross apply sql_text cross apply sys.dm_execsql_query_plan
select usecounts, cacheobjype, objtype, text from sys.dm_exec_cached_planscross apply dm_exec_sql_text(plan_handle)where usecounts > 1 order by use counts desc

IO

select * sys.dm_io_pending_io_requests can be run when you think that io can be a bottleneck select * from sys.dm_io_virtual_file_stats (null,null)

select db_name(database_id), * from sys.dm_io_virtual_file_stats(null,null) --shows io stats for data and log files -- database id and
file id -- null returns all datadb_name is a funtion to return the name of the actual
database rather than database id

Index (when sql server is restart everything is reset)
sys.dm_dm_db_index_operational_stats (DMF) -- shows io, locking and access information such as inserts, deletes, updates
sys.dm_dm_db_index_physical_stats (DMF) -- shows index storage and fragmaentation info,
sys.dm_dm_db_index_usage_stats (DMV) -- shows how often indexes are used and for what type of SQL operation
Tips 1239, 1545, 1642, 1749, 1766, 1789

Index examples
select db_name(dtabase_id), object_name(), * from operation_stats(5,null,null,null)
parameters databaseid, objectid, indexid, partition number
select db_name(dtabase_id), object_name(), * from physical_stats(DB_ID(N'Northwind'),5,null,null,null, detailed)
parameters databaseid, objectid, indexid, partition number, mode

Missing indexes

• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_columns
  Tip 1634

SQL Operating system

sys.dm_os_schedulers-- information abt processors
sys.dm_os_sys_info-- info abt computer and abt resources available to and consumed by sql server
sys.dm_os_sys_memory-- how memory is used overall on the server, and how much memory is available.
sys.dm_os_wait_stats-- info abt all waitsDBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
Tips 1949

sys.dm_os_buffer_descriptors-- info abt all data pages that are currently in the sql server buffer pool
Tips 1181, 1187

memory use by database

memory use by table

SSAS does not work if copied from another machine as a VM -- Why?

Did you know that even if you create an exact copy of the sql server virtual machine, the analysis services will not work?

I faced this problem last week.

The exisitng development machine (lets call it DEV1) stopped working and was giving lots of errrors.
So we thought that we will create a copy of the exisitng production system as a virtual machine and use that as the development machine and we created an exact copy of the production system as a virtual machine but gave it a different name (DEV2).
Then decomissioned the original development machine.

When I tried to access the sql server database on the new development machine (DEV2) the access was fine. But when I tried to access the analysis services through the management console, there was no response.

So I thought I had two options to make analysis services to work --

• Option 1: Reload sql server from scratch and ssas and SSRS and then copy all databases and cubes from the production server.
• Option 2: Try renaming the new development machine (Dev2) to the old development machine (Dev1)

Since option 2 was easier I thought I would try that first. And bingo the trick worked.

But I still don't know why analysis services does not work from a copy.

Can anyone of you help me understand why?

PASS Hosts New Com.PASS Content Feeds

PASS is pleased to host Com.PASS, a new set of content feeds that provide Microsoft SQL Server and Business Intelligence professionals broad access to quality information across respected community Websites.

Conceived by Brian Knight of Pragmatic Works and developed in collaboration with PASS President Rushabh Mehta, SQLServerCentral.com Editor Steve Jones, and sswug.org Founder and Managing Editor Stephen Wynkoop, Com.PASS employs a SQL Server Integration Services (SSIS) package that uses keywords to scrub selected community Websites for relevant content.
“Com.PASS is about making it faster and easier for busy SQL Server pros to find the information they need to do their jobs better,” notes PASS’s Mehta. “You can quickly get lost in the sea of links and information available on the Web. Com.PASS feeds you content in your target topic areas from sites that you can trust.”

Initially focusing on BI content, Com.PASS currently includes five feeds:

§ Com.PASS.BI, for BI content across the Microsoft SQL Server and Office stacks
§ Com.PASS.SSAS, for SQL Server Analysis Services content
§ Com.PASS.SSIS, for SQL Server Integration Services content
§ Com.PASS.SSRS, for SQL Server Reporting Services content
§ Com.PASS, which combines all the feeds Just click a feed to add it to your RSS reader.

Learn more and subscribe to your favorite feeds.

Exceptional DBA Awards -- Get nominated or nominate people you know

Nominations for The Exceptional DBA of 2010 open today, giving database administrators around the world the chance to be recognized for service to their employers, the SQL Server community, and the IT industry at large.
The link to the site is:

http://www.exceptionaldba.com/?utm_source=ssc&utm_medium=survey&utm_content=dba_awards&utm_campaign=sqlbackupbundle

Secure and available data is crucial for a company's success, and so are the DBAs. All too often DBAs don't get the respect they deserve.
And if you agree with us that it's time to change this, then please help us find 2010's Exceptional DBA Awards winner!

"If you are an exceptional DBA, or know of an exceptional DBA, I encourage you to participate in the Exceptional DBA Awards. Not only will it give you or some exceptional DBA some much-deserved recognition, it will also help to increase the awareness of the importance of the DBA role among the IT community."
Brad McGehee, Exceptional DBA Awards Judge

Would you like to nominate yourself or a DBA you know? Nominations are now open, and we are waiting for your entry! Please make sure that all details have been submitted before June 4, 2010.

Free resources for exceptional DBAs
===========================
The awards sponsor, Red Gate Software, is offering you Brad McGehee's "Day-to-Day DBA Best Practices" poster and a free trial of the SQL Backup Bundle – all Red Gate's DBA tools in a single suite. Red Gate's SQL Backup Bundle includes products such as SQL Backup, to compress, encrypt and strengthen backups, and SQL Response, to monitor SQL Server health and activity. Download free resources now.

XSLT, XML tools

Here are two free tools for working with XSLT, XQuery and XPath.

XRay is a free XML editing enviroment. Now in its second major release, XRay provides support for XML Schema (XSD) and an integrated online XML tutorial system.
XRay is free. Download it right away!.

Kernow is an open source tool designed to make it faster and easier to repeatedly run transforms using Saxon.
It uses compiled stylesheets, multiple threads and caching resolvers to make the transforms run efficiently, and comboboxes that remember between runs to save your fingers having to retype paths. Kernow is runnable from Ant allowing it to slot into your build process, and its a high level API for Saxon making it very easy to run transforms from your own Java applications.
Kernow is written by Andrew Welch using Java 1.6.

SSIS session in Auckland by Patrick

We had a really interesting session with Patrick of Pragmatic Works yesterday on the topic "Introduction to SSIS".

He has covered the following basic aspects of SSIS

• Opening BIDS environment with a -NOSPLASH
• Creating a small package with some package requirements -- Control flow, Data flow, Event Handler, package explorer
• Deplying the created package
• Scheduling a package

Here are my takeways from this session

Tip 1

You can use -NOSPLASH in the shortcut of the Visual studio icon to avoid the initial start up screen when you open Visual Studio

Tip 2

Data Connections can be created in two ways --

• Via the Data Source on the right hand side of the screen of the solution. If the data source is created this way, it can be used by all the packages in the project.
• Via the connection manager within the package itself. If this is used, the connections can be used only within that package.

Tip 3

Data Flow Taks can be created in two ways

• Via the Control flow tab -- by dragging the Data flow task from the toolbox
• Via the Data Flow Tab -- by just clikcing on the link available in this tab.
  

Tip 4

Always use OLEDB source instead of ADO.NET source for greater performance

Tip 5

In the OLEDB source tool always use SQL command instead of Table/view. Using the Table/View can slow down the SSIS package considerably.

Tip 6

When sql command in used, use the Preview button instead of the Parse Query button

Tip 7

You can use look up task and derived column task to redirect the un match rows to an error output.

Tip 8

If you use the row count task in the data flow tab that creates an unnamed variable, to name this varaible right click on the grey space on the control flow tab and name the variable.

Tip 9

You can right click on the control flow tab and click on the Package Configurations to configure the package. These configurations can be created as an XML file which can then be modified at deployment.

Tip 10

When you build a project and deploy, the package will be in the MSDB database of the sql server.

Tip 11

You can schedule an SSIS package using the SQL SERVER Agent or using dt exec command line.

Highlights of Visual Studio 2010 and Windows Azure Launch event

Today I have attended the Visual Studio 2010 and Windows Azure Launch event in Auckland. There were close to 1000 people for this event.

The first presentation on Visual Studio was done by Sam Guckenheimer. He took us through the Visual Studio product suite

• He explained how this product can now reduce time wastage in the SDLC,
• also how transparent the VS has become in terms of managing a project from a team perspective (using excel and sharepoint to monitor the team progress on a project)
• how the use of layer diagrams helps communicate the design intent and later helps check the implementation against the design.

The second presentation was done by David Chappell on Windows Azure platform which is the Microsoft Cloud Computing Technology.

He said that there are two broad categories for cloud computing -- Cloud Applications and Cloud Platform.

He covered on the cloud platform of Windows Azure. Windows Azure is a platform where you ask for an application to run (instead of a VM) and the application will control how many VMs to run etc. The Windows Azure platform recognises if an instance failed and restarts a new instance automatically.

He also mentioned about the SQL Azure which is basically Sql server and this needs the TDS (Tabular Data Stream) protocol

Also talked about the applications of Azure as follows:

• Apps that need massive scale
• Apps that need high reliability
• Apps with variable load
• Apps with a short or unpredictable lifetime
• Apps that do parallel processing
• Apps that must fail fast or scale fast
• Apps that don't fit well in an organisation's data centre
• Apps that can benefit from external storage

He also mentioend the differences between the various cloud platforms (Amazon web services, Google App Engine, Force.com) and whether they are competitors or not.

Tools for collecting SQL Server instances

Due to the proliferation of personal firewalls, inconsistent network library configurations, and multiple-instance support, SQL Server installations are becoming increasingly difficult to discover, assess, and maintain.

Peter Ward from WardyIT Solutions while presenting in the SQL Saturday event at Auckland last weekend has asked all of us the following question:

“Do you know how many instances are there in your network?”

Only two people in the entire room have raised their hands.

He mentioned about the following tools being available for collecting sql server instances in a network.

Sqlping from www.sqlsecurity.com

SQLPing 3.0 is a free tool available which performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise.
SQLPing 3.0 is designed to remedy this problem by combining all known means of SQL Server/MSDE discovery into a single tool which can be used to ferret-out servers you never knew existed on your network so you can properly secure them. .NET Framework v2.0 Required.

You can find more information about SQLPING here

Microsoft assessment and planning toolkit (MAP Toolkit)

MAP Toolkit is a free tool from Microsoft that does a lot more than just scanning the network and identifying all instances of SQL Servers. Some of the features are outlined below.

• Secure, Agentless Discovery and Inventory
• Generate Automated, Network-wide Readiness Assessments
• Quickly Create Reports and Proposals
• Capitalise on Cross-Sell and Up-Sell Opportunities
• Shorten Your Presales Cycle, Increase Your Opportunities
• Accelerate Server and PC Migration Planning
• Leverage Virtualisation to Help Reduce Customers' Costs

For more information please click here

SQLIO
In the presentation Peter also briefly mentioned about SQLIO as a tool to measure SAN performance. I have googled for SQLIO and came up with the following.

SQLIO is a free utility from Microsoft that measures storage IO performance. The name "SQLIO" is horribly misleading, because it doesn't really have anything to do with SQL Server. There is a lot of information about SQLIO in the SQLServerpedia website

There may be lot of other tools out there. Please suggest me other tools if you can

SQL Server 2008 R2 Training Kit

SQL Server 2008 R2 Training Kit

New updated version of SQL Server 2008 R2 Training Kit is available for download from the microsoft site.

This has some hands on labs, tutorials, presentations, and videos.

Download from:http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

Hope you will enjoy using this training kit.

Recap from SQL Saturday Auckland event -- 10th April 2010

This past weekend I have attended the SQL Saturday event in Auckland.

The turnout for this event was really good with close to 100 people attending including the organizers and presenters. Thanks to everyone that helped out to put on a very successful event.

Here is a list of the presenters and the topics presented. We had to cancel the main organizer’s (Dave Dustin) due to lack of time.

Session Title	My learning’s in brief
Analysis Services - Introduction to Business Intelligence with SQL 2008 Justin King	The demonstration was really good as he took us through the entire cycle of creating an SSAS project. Also covered the basics of terminology in his presentation.
What is LINQ – and what the heck is it doing to my database? Ivan Towlson	Didn’t know what LINQ was before and now I know what it is and what advantages we can have with it.
Database Index investigation with DMVs Leo Miller	Learnt a whole heap of stuff on DMVs from DMV concepts, DMV groups to how to use them. Very valuable session.
SQL Server Integration Services 2008 Ben Gracewood	Learnt about SSIS custom templates which was new to me even though I had worked on SSIS and didn’t know anything about them.
36 to 6 SQL Servers – Consolidation from the Trenches Peter Ward	Peter is a very good presenter and I am impressed with his presentation that analogy he gives are realy amazing. Learnt what consolidation is and why we need to consolidation.
Common Misconceptions about Clustering Leo Miller	Why clustering is done and what the actual misconceptions.
Data warehousing & BI on SQL Server 2008 Douglas Barrett	Learnt what’s new in SQL SERVER 2008 R2 in the data warehousing world.
Comparing SQL in large vs small environments James Stewart	Learnt about the various performance tools and monitoring models. Also the various types of alerts.
Training for SQL Server 2008 certification, and why it's worth it Amanda Jackson	I am planning on to do the Business Intelligence certification which was very clear in Amanda’s presentation. Also information about what is available in Microsoft Elearning portal.

The lunch was Pizzas and subway sandwiches which was delicious. We also had an afternoon tea of Giapo Gelato Ice cream.

During every session there were various prizes for the people who were asking questions and also at the end there were some prizes from the sponsors.

I will try to blog on each presentation in detail a bit later.

Disable report filter selection in a Pivot Table in Excel

I had to work on disabling a field (report filter) on a Pivot Table in Excel 2007. I googled a lot on this and there was no straight forward answer. Hence thought of sharing my solution with anyone who wants to do the same thing

Here is the code that I have used to achieve this

Right click on the sheet where there is a pivot table and click on View Code
Add the following code

Option Explicit
Private Sub Workbook_Open()DisableSectorSelectionEnd Sub
Sub DisableSectorSelection() Dim pt As PivotTable On Error Resume NextSet pt = Selection.PivotTableIf Not pt Is Nothing Then pt.PivotFields("Sector").EnableItemSelection = False
End Sub

Save and reopen the file.
The filter should not be disbaled on the worksheet.

The following features cannot be saved in macro-free workbooks

The following features cannot be saved in macro-free workbooks

VB Project

To save a file with these features, click no, and then choose a macro -
enabled file type in the file type list.

To continue saving as a macro freee workbook, click yes.

That was the error I was getting today when I wanted to write a macro in Excel 2007.

In order to make an Excel 2007 workbook macro enabled, you need to save the workbook as .xlsm rather than .xlsx That solves the problem of the macros not being enabled.

PowerPoint Show (*.pps) file copies as a PowerPoint (.ppt) file when downloaded from the internet

Today we had a probelm on our website, where when you try to download a PowerPoint Show (.pps) file, the file was downloading as a PowerPoint Presentation (.ppt) file instead of a .pps file.

I have done some research on google and found the following solution to my problem.

• In IIS Administrative Console snap-in, right-click the specific Web server where the .pps file is located, and then click Properties.
• Click the HTTP Headers tab.
• Click on Mime Types button in IIS 6.0
• Click the File Types button in older versions of IIS.
• Click New Type.
• In the Extension box, type .pps, and then in the Content type (MIME) box, type application/vnd.ms-pps.
• Click OK, and then restart IIS.

To know more click on the link from Microsoft

Checksum Vs. Hashbytes

What is Checksum?
Checksum is a function available in sql server 2005 and sql server 2008. It is intended to build a hash index based on an expression or column list.

Determining if two rows or expressions are equal can be a difficult and resource intensive process. This can be the case where the update was conditional based on all of the columns being equal or not for a specific row. Without checksums it is a long process of using innerjoins to identify the exact row to update in the update statements.

When is Checksum used?
When you need to compare the unique characteristics of an expression, columns or a table consider using the Checksum function.

When you have to update a row where many columns are compared to determine if the data is unique, use CHECKSUM function to build a unique value using checksum function and then compare the CHECKSUM values.



Example:

Hashbytes

What is hashbytes function?
HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats.

When can you use a hash function?
Hash functions are a useful option to improve the efficiency of particular queries on large volumes of data. For example, on long strings of text, you can build a hash-index to perform efficient lookups or to speed up aggregate operations.


Differences between checksum and Hashbytes

Checksum	Hashbytes
Faster but can produce lot of duplicate values	Slower but efficient than checksum
Returns an int value	Returns a varbinary(8000)
Microsoft does NOT recommend using CHECKSUM for change detection purposes	Use Hashbytes for change detection purposes

Problem problems with Powerpoint

I had problems printing powerpoint slides this morning and here is an excerpt from an article on pptfaq.

Workaround

If you're willing to accept a slightly smaller slide image, you can print Notes Pages instead of Slides. Modify the Notes Master in your presentation:

• Choose File, Slide Setup and set Notes pages to print in the same orientation as your slide pages
• Choose View, Master, Notes Master
• Select and delete the notes text and any other text placeholders you don't want to appear on your slide printouts.
• Select and scale the slide image on the notes master to a larger size. NOT full page, but somewhat smaller. You'll need to find out what the trailing margin on your printer is and make the margins on both sides of your notes master at least that large.
• Print a trial notes page from one of your slides. It will probably be off center. Go back to the notes master and move the slide image accordingly. It'll probably take several trial printouts to get right, but once you've got it nailed down, you should be able to get printouts of just your slides, properly centered on your inkjet printouts.
  
• Once you've got it nailed, right click the slide image, choose Format and record the size and position of the slide image so you can use the same info in your other presentations.

Source: http://www.pptfaq.com/FAQ00097.htm

Password protect a workbook in Excel 2007

If you want to password protect your workbook in Excel 2007 the following are the steps that need to be followed.

• Click on the Office button on the top left hand corner of the workbook
• click on prepare
• choose restrict permission
• choose a pasword
• you may be asked to reenter the password

That's it your workbook is password protected.

Displaying powerpoint files on a website

I had trouble displaying powerpoint files on the website that I was working on. The problem was the link to the powerpoint file was a powerpoint 2007 show file (.ppsx). But when you clikc on the hyperlink, it was downloading with a .ppt extension

I have done some googling to resolve this problem and found that in order for links to PPTX/PPSX files to work, the two conditions must be met:

• The user must have PowerPoint 2007, the 2007 Viewer or an older version of PowerPoint with the compatibility pack installed so that they can open PPTX/PPSX files in the first place
• The web server configuration on your site must have the correct mediatype/MIME settings to serve PPTX/PPSX files as PowerPoint 2007 presentations.

The above is an extract from the article on pptfaq called Control how the browser opens powerpoint files