5 (now) Free SQL Tools

Atlantis Interactive provide next-generation free SQL tools specifically tailored for performance and ease of use when architecting or administering large Microsoft SQL Server database systems.


Includes an SSMS app with additional features, SQL compare and data compare, table space analyzer, data visualization and analysis, and schema/metadata dependency and analysis tools.


http://www.atlantis-interactive.co.uk/blog/default.aspx

Definitely worth the download(s)

Resmon - Resource Monitor for Analysis Services

Teo Lachev posts about a new resource monitor tool for Analysis Services.

Greg Galloway just published a ResMon cube sample on CodePlex that aggregates execution statistics (rolls up information about Analysis Services such as memory usage by object, perfmon counters, aggregation hits/misses, and current session stats) from Analysis Services dynamic management views (DMVs) and makes it easily available for slicing and dicing in a cube. I think this will be a very useful tool to analyze the runtime performance of an Analysis Services server or as a learning tool to understand how to work with DMVs. Kudos to Greg!


http://prologika.com/CS/blogs/blog/archive/2010/11/04/resmon-cube-sample.aspx

Feature pack for SQL 2008 R2

The Microsoft® SQL Server® 2008 R2 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2008 R2.


As with other releases, SQL 2008 R2 has a feature pack which is required for standalone installs of certain components, and adds additional functionality to the product.

Sql-Rd addon for Reporting Services

For those looking for Report Bursting and other complex scheduling, exporting, and printing requirements, this tool fills in the blanks with Reporting Services.

SQL-RD is a dynamic, flexible, function-rich and intuitive automation tool for scheduling, exporting, distributing and delivering your Microsoft® SQL Server Reporting Services (SSRS) Reports.



SQL-RD contains powerful system, event and business process automation functions which will make an instant impact on the efficiency of your business.

A couple of the cool features I saw were bulk printing to multiple printers, SMS text messaging, Zipped/PGP'd reports, deferred delivery, even an export to DBase option...

Two new tools for diagnosing performance bottlenecks with SQL

TraceTune is designed to read SQL Server traces and tell you which SQL statements or or stored procedures are consuming the most resources. TraceTune is the online version of ClearTrace and is maintained by scaleSQL Consulting.


http://www.tracetune.com/

Project Lucy analyzes performance data that you upload. It gives you deep analysis and leverages the collective intelligence of other Project Lucy users.

https://www.projectlucy.com/

Nothing wrong with just using a few DMVs either.

http://furrukhbaig.wordpress.com/category/dmvs/

On DESKI, Proclarity, and Executive Crystal (Ball) Decisions

Sometimes I wonder whether BI vendors actually use their own tools to look at their market.  If they do, then perhaps they are missing the "sentiment" KPI.

Microsoft lost a bunch of sentiment points by discontinuing BI tools like Proclarity and PerformancePoint planning.  Proclarity as a desktop and dashboard tool met quite a few needs for my clients that aren't filled anywhere else without jumping up price point notches and complexity.  I'm still supporting PerformancePoint, the discontinued flavour.

Now it appears that SAP has taken a similar approach, dropping their desktop client DESKI in favour of their web client WEBI.  Which, of course, has less functionality.  Web apps just don't feel as concrete, physical, or hardened as desktop apps.  Although the technologies are coming together more and more, desktop apps still have points over ease of use and functionality.

How come the BI vendors don't ask their clients how they can serve them better rather than analyzing the P and L for quick-hit cost savings?

IBM (Cognos (formerly SAP (formerly BusinessObjects (formerly Crystal))) developers are an angry bunch!

Am I missing a bracket or two there?

BI in the Cloud or Head in the clouds?

Last week I went to a .NET user group presentation for Microsoft's Azure cloud computing platform.  The topic, though a bit dry, was interesting.  Cloud storage confuses me a bit since I'm a relational database kind of guy.  Azure Cloud Storage provides a basic API to store BLOBs (binary objects like images, music or video) in its massive redundant storage data centers.  It has 3 major types of semi-structured or unstructured storage:

• Blobs
• Queues
• Tables

The coding examples actually offended my sense of good form with databases and coding, probably since the opposite design philosophies are true for Azure Storage apps versus relational database apps.  Or at least this is what I got from the presentation, which might be a bit off.

• Keys are no longer supposed to be small, integer, incrementing for performance.  
• Keys are long strings, sometimes with pipe delimiters.  
• There is no concept of ATOMIC transactions - the app does the work.  
• A table can hold two different types of object information (invoice/invoice details) with the key differentiating.   Duplicate data is replicated everywhere.  
• Message Queues have a chance of dropping data which the app needs to handle.  
• There are limits to the scalability of requests and storage size that the app needs to be aware of and design around.  
• There's only 1 key on a table, so searching against a non-partition-key field causes table scans.
• It's better to update / merge data than to append to the end.

The cloud will scale to terabytes, petabytes, exabytes of data depending on your budget, however good luck doing any complex searches against your metadata.  Seems rather painful right now.

Many of the stable, reliable, tried-and-true server and developer tools available outside the "cloud" provide a huge step up in terms of ease of development effort and data quality.  Why are we taking steps backwards in development to accommodate buzzwords and marketing stories?  Is there really a great benefit to moving your enterprise into the cloud?

Cloud in simple words is 'Pay as you use'. Cloud computing is analogous to the way we pay for our utilities like electricity or water based on the consumption. The underlying Infrastructure is owned  and managed by some third party and cost is billed to us in form of units consumed - BI In the Cloud


Driving around eastern Ontario a couple weekends ago, I was amazed by all the farms and houses having solar panels that weren't there last year.  I told my wife some salesperson was doing a good job.  This type of micropower distribution structure seems like the way of the future; not a huge smoke-belching power plant.  Why isn't it the same for the cloud?


The thing that bothered me the most though?  MS has Azure Cloud Data Centers around the world.  But no Azure Cloud Data Centres, eh?  I guess Canada is considered Northern US to the Redmond folks...  :)

SAP upgrades its BI tools to 4.0 release

"This release is huge," said Dave Weisbeck, senior vice president of BI and EIM solution management. "Every single tool has had some major upgrades. There's not a single place we haven't touched."

One of my clients I worked with in the past had 2 different versions of their BusinessObjects Universes, with 2 different versions of client and server toolsets running. This was due to the poor migration story BO had, with reports and client tools losing functionality and formatting, a parallel-server migration, and an angry user base (and double the administration) as a result. Hopefully this doesn't happen with the new SAP 4.0 BI tools and there is a clear upgrade path and roadmap.

SAP purchased Sybase last year. Sybase created the original Microsoft SQL product, and MS branched off with their own codebase in SQL 7, further in SQL 2000.  Interesting how many of the BI vendors with a long history (Cognos, Sybase) are being consolidated into the mega software conglomerates.

There may be more than five computers in the world, however it's slowly looking like there's only going to be 5 enterprise BI companies soon..

• IBM
• Oracle
• Microsoft
• SAP
• The little guys

iSql - SQL for iPad/iPhone

iSql SDK:The *simplest* way to query SQL Server from your apps!

Windows® API Code Pack for Microsoft® .NET Framework

Windows® API Code Pack for Microsoft® .NET Framework provides a source code library that can be used to access some features of Windows 7 and Windows Vista from managed code. These Windows features are not available to developers today in the .NET Framework.

http://code.msdn.microsoft.com/WindowsAPICodePack

Office Converters and HTML Viewers

In support of Microsoft’s ongoing efforts to increase the interoperability of its various technologies, we have partnered with Dialogika to create a translator that converts the Microsoft Office binary file formats (.DOC, .XLS, and .PPT) into the Office Open XML standard format (.DOCX, .XLSX, .PPTX).

http://blogs.msdn.com/b/interoperability/archive/2009/05/11/binary-to-open-xml-b2x-translator-interoperability-for-the-office-binary-file-formats.aspx

http://b2xtranslator.sourceforge.net/

Enables OpenXML documents to be viewed on any client system in any browser as HTML, and includes an add-ins for Internet Explorer, Firefox and Opera (Windows/Linux)

http://www.interoperabilitybridges.com/projects/openxml-document-viewer

Enables bi-directional interoperability between Microsoft Office (Open XML) and Open Office (ODF) file formats

http://www.interoperabilitybridges.com/projects/openxml-document-viewer

Rollback Transaction in SQL Server

Including one command, SAVE TRANSACTION, that could be useful for multiple iterations of a task with error handling.

Rollback Transaction in SQL Server

Business Intelligence: SSAS 2005: Cube Performance Tuning Lessons

Some good pointers for tuning SSAS 2005 cubes here.

Business Intelligence: SSAS 2005: Cube Performance Tuning Lessons

SQL Server - New Drives Use 4K Sector Size - CSS SQL Server Engineers - Site Home - MSDN Blogs

Bob Dorr talks about drive sectors, new standards, and implications on SQL Server.  Somehow this seems to me to be a conspiracy to sell SQL bundled with appliance hardware that is already configured for SQL. I can see why there is a move to a SQL operating system and dedicated appliances rather than dealing with all the intricacies of the o/s and 3rd-party hardware, firmware updates, drive arrays and configuration, memory settings, disk alignment, partitioning, raid configurations, etc.

The implications of incorrect drive sector settings?  Risk of losing data, slow performance and not being able to restore databases to upgraded hardware.

In a lengthy discussion this past week I was reminded that Jan 2011 is when the hard drive manufactures agreed to focus on drives with sector sizes of 4K.    I have read all the latest materials about this over the past week and you can too.  Just search for 512e or Advanced Format Sector sizes and you will find the same articles I read.  I concentrated on articles by Seagate, Western Digital and other manufactures.

Why am I talking about this on a SQL Server blog? - The change has impact to your SQL Servers.   There are two areas you need to be aware of.  PERFORMANCE and DATA INTEGRITY

SQL Server - New Drives Use 4K Sector Size - CSS SQL Server Engineers - Site Home - MSDN Blogs

TwInbox - Use Twitter directly from Outlook.

Twinbox + Access Linked Table to Outlook Folder = Quick and dirty Twitter mining.

If you want to archive your twitter posts, search posts offline, or just hack around with the data, Access allows you to link to Outlook folders, including your Twinbox folders.

If you are using Twitter and Outlook and haven’t tried Twinbox, you’re missing out.

http://msdn.microsoft.com/en-us/library/aa164914(v=office.10).aspx 

TwInbox - Use Twitter directly from Outlook.

The hidden gems among DMVs: sys.dm_os_sys_memory - Microsoft SQL Server Tips & Tricks - Site Home - MSDN Blogs

Some of the hidden gems are also here:

http://www.brentozar.com/sql-server-training-videos/playing-doctor-with-dmvs/ 

The hidden gems among DMVs: sys.dm_os_sys_memory - Microsoft SQL Server Tips & Tricks - Site Home - MSDN Blogs

Programming Solution

Taking the U out of CRUD for performance reasons.

So, to conclude, we can say that if we have to do many updates in a very large table (table with several million rows), we can avoid update statement and use select and insert.

Programming Solution

MDX Functions MDX Examples MDX Queries

Links to some of the more useful MDX functions.

MDX Functions MDX Examples MDX Queries

Exists + Descendants function

Returns the set of members based on the member attribute in MDX.

EXISTS(DESCENDANTS([Account].[COA],,leaves),  [Account].[AccountType].[Revenue])

IBM Information Management Software for z/OS Solutions Information Center

VBA Functions in MDX | Programming Solution

Left, Right and Contains in MDX.

There are some VBA functions that are implemented in MDX code base and so they are faster than using VBA functions. We can call them “internal VBA” as by Irina Gorbach. Here we will see some example of such “internal VBA” in MDX.

VBA Functions in MDX | Programming Solution

How to Use Excel's CUBESET Function | eHow.com

For Cube function reporting in Excel, one function that doesn’t get translated during a pivot table formula convert is CUBESET.

Here is a way to get the leaves (lowest levels) of a dimension using the cube set formula in Excel 2007/2010.

=CUBESET(connectionstring,filter([Account].[AcctHier].members,isleaf([Account].[AcctHier].currentmember))","Account Leaves")

To retrieve the members, create a list of numbers ending at the CUBESETCOUNT() of above, and use the following formula:

=CUBERANKEDMEMBER(connectionstring,

CUBESET(connectionstring,filter([Account].[AcctHier].members,isleaf([Account].[AcctHier].currentmember))","Account Leaves")

,1))

Retrieves the first member of the set.

How to Use Excel's CUBESET Function | eHow.com

Download details: Infrastructure Planning and Design

 

The Infrastructure Planning and Design (IPD) guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.

Download details: Infrastructure Planning and Design

Query Performance and multi-statement table valued functions - CSS SQL Server Engineers - Site Home - MSDN Blogs

Common Table Expressions (CTEs) and table-valued functions (TVFs) are features in SQL 2005/2008 that solve a lot of problems for me in terms of functionality and compartmentalizing code. 

They also cause issues with performance for larger datasets.  There are some ways around this.  Hopefully SQL.Next solves some of these issues for us…. in the meantime.

Lately I worked with a customer to help tune his query involving multi-statement table valued function.   When using table valued functions, you should be aware of a couple of things

First, there are two type of table valued functions which are inline table valued function (Inline TVF) and multi-statement table valued function (multi-statement TVF).    Inline table valued function refers to a TVF where the function body just contains one line of select statement.   There is not return variable.   Multi-statement table valued function refers to a TVF where it has a return table  variable.  Inside the function body, there will be statements populating this table variable.  In the demo at the end of this blog, there are examples of inline TVF and multi-statement TVF.

Secondly, multi-statement TVF in general gives very low cardinality estimate.

Query Performance and multi-statement table valued functions - CSS SQL Server Engineers - Site Home - MSDN Blogs

Data Type Mapping with Distributed Queries

For those using linked servers or SSIS/DTS to external databases, this MSDN article provides details on data type mappings.

An OLE DB provider exposes the data types of its data according to OLE DB type identifiers called DBTYPEs. Data types are converted between OLE DB data types and SQL Server system data types by mapping data:

• From OLE DB data types to SQL Server system data types. This conversion occurs when SQL Server reads data from the OLE DB data source, either in SELECT statements or in the reading side of UPDATE, INSERT, or DELETE statements.

• From SQL Server system data types to OLE DB data types. This conversion occurs when SQL Server writes data, mostly in INSERT or UPDATE statements, into the OLE DB data source in which the modified table is a remote table.

Data Type Mapping with Distributed Queries

I Need #SQLHelp! | Brent Ozar - Too Much Information

Need instant SQL Help?  Here is the post.

I Need #SQLHelp! | Brent Ozar - Too Much Information

SQL Azure Compatibility

An enormous 95-slide deck has been published as a briefing on cloud computing at MS and the Azure platform.

Slide 67 has some of the features in and out of SQL Azure.

What’s not supported:

• XML, HierarchyID, Sparse Columns, Filestream data types. (XML support would be a biggie for some clients)
• Partitions
• Full-text indexes (again, some clients leveraging this)
• SQL-CLR

SQL Server: Top 10 Secrets of a SQL Server Expert | TechNet Magazine

Paul Randal provides 10 musts for SQL DBAs

SQL Server: Top 10 Secrets of a SQL Server Expert

Maintaining a SQL Server environment is a potentially complex endeavor. Here are the top 10 ways you can minimize complexity and reduce stress.

SQL Server: Top 10 Secrets of a SQL Server Expert | TechNet Magazine

Report Server Catalog Best Practices - Technical Notes

Excellent best practices guide for RS 2005 installs

Report Server Catalog Best Practices

Building and Deploying Large Scale SQL Server Reporting Services Environments—Technical Note Series

Report Server Catalog Best Practices - Technical Notes

Memory and Handle Quotas in the WMI Provider Service - Ask the Performance Team - Site Home - TechNet Blogs

Not necessarily related to BI or SQL unless perhaps you are reporting on performance or software inventory, or using it for an ETL process.  However, wmiprivse.exe is one of those important Windows mystery components that does a lot behind the scenes and is good to be aware of if you are a developer, and good to be an expert of if you are a support person.  It also seems to come up often as a big consumer of CPU and memory.   To me it always seems like a virus since it takes over my Windows randomly and pops up multiple processes, without any details on which application is generating them.

It really is quite a simple piece of software.

Well, not so much. 

This article describes how to configure the component’s quotas to ignore some needless errors that some monitoring tools will throw.

When the WMI Provider service reaches its quota limit, WMI queries that are being handled by that instance of WMIPRVSE.EXE will most likely fail.  However, there are applications like System Center that may require more memory or handles for the process.  These quotas are configurable, however – do not modify these quotas for the sake of modifying them!  If the WMIPRVSE.EXE process is actually leaking handles or memory, modifying the quota will only delay the issue from occurring, not eliminate it.  In instances such as these, normal leak troubleshooting must be performed to identify the root cause.

Memory and Handle Quotas in the WMI Provider Service - Ask the Performance Team - Site Home - TechNet Blogs

More on WMI at Wikipedia.

And further on the (needless?) complexities of WMI.

Greg was quite enthusiastic about the power of Windows PowerShell but during the BOF someone asked him directly, "So which is it - should we be using VBscript or Windows PowerShell to script SMS".  I forgot exactly what Greg said but it was along the line so of, "if you want to do reporting, use Windows PowerShell because it is very powerful but if you want to get things done, use VBScript because you can't invoke WMI methods (at least not easily)".

This was a real wake up call for us.  Greg is exactly the sort of person that we want to be a ardent, unabashed, no reservations, hard core Windows PowerShell user and the message was loud and clear - almost but not quite. 

We knew that our WMI support was not what we wanted it to be but as I've mentioned many times, to ship is to choose.  We thought that we had reasonable support and that WMI users wouldn't be phased by some of the deficiencies - yes calling methods was complex but we figured that if you were using WMI, you were self-qualified as being able to cope with complexity :-) . 

Kind of sad that an MS team ignored implementing functionality with WMI because they had issues with the complexity of their own product.  Perhaps changing the names and methods for Windows Management Instrumentation to something a little bit more obvious would help with that complexity.  The WMI counters and naming conventions appear to come from the days of Windows 95.

WMI is a very powerful and sometimes misunderstood provider.  Some of the useful functions:

1. Get drive space for all drives & computers on a network.
2. Perform a software/hardware inventory
3. Start and schedule remote applications
4. Reboot computers remotely
5. Read and consolidate event logs

Being able to right-click on a service or device to explore WMI properties seems the most obvious piece of functionality that was left out of Windows.

Perhaps it could be used as a BI tool, to better understand how your network is functioning and mine data on how drive space is used?  Get started with mining data using WQL.

Some more recent info on using Powershell and WMI. 

http://www.hackyeah.com/2010/08/powershell-pc-info-script-wmi/

http://blogs.technet.com/b/heyscriptingguy/archive/2010/08/26/query-active-directory-with-powershell-and-run-wmi-commands.aspx

http://www.computerperformance.co.uk/powershell/powershell_wmi.htm

Enterprise Event Forwarding
http://blogs.technet.com/b/otto/archive/2008/07/08/quick-and-dirty-enterprise-eventing-for-windows.aspx

WMI for Analysis Services
http://social.msdn.microsoft.com/forums/en-us/sqlanalysisservices/thread/CA9B89EE-EC6A-4025-AFDC-0AD852C594B6

SQL WMI Provider
http://yongslee.wordpress.com/2010/02/15/sql-wmi-provider/

Comparing WMI to SQL
http://technet.microsoft.com/en-us/library/cc180454.aspx

OLE-DB to WMI
http://msdn.microsoft.com/en-us/library/h63swas7%28VS.80%29.aspx

WMI Explorer
http://www.ks-soft.net/hostmon.eng/wmi/index.htm

WMI Event Watcher for SSIS
http://msdn.microsoft.com/en-us/library/ms141130.aspx

WMI Data Reader Task
http://msdn.microsoft.com/en-us/library/ms141744.aspx

WMI Blog
http://blogs.msdn.com/b/wmi/

Open Source Scientific Library (C/C++, .NET, Excel)

Trying to understand complex equations in Excel for reverse-engineering formulas?  The Excel Equations Add-in converts Excel formulas to graphical formula equivalents.

Find it here.

http://www.codecogs.com/components/excel_render/excel_install.php

Open Source Scientific Library (C/C++, .NET, Excel)

Troubleshooting Excel Services

For some reason Excel Services seems a bit convoluted to set up properly.

Some things I had to enable in Sharepoint 2010:
Create a data source to a cube using Other Data in Excel.
Properties of the connection to get the location of the .odc file
Upload the file to trusted Data Sources library
Connect to the data source connection in Sharepoint library from Excel pivot
Upload pivot workbook to trusted document library.

Select the document. View in Browser. Get an error - please try again. (huh)

Go to Site Settings - Features - and enable Excel Services.

Select the document. View in Browser. Works. Refresh data. Errors due to delegation.

Go to Central Admin - Manage Services on Server (Where else would they be) Claims Windows Token Service - Start.

Logs for troubleshooting Excel Services can be found buried in:
Program Files
Common Files
Microsoft Shared
Web Server Extensions
14
Logs

Help is your friend with Sharepoint 2010.

Still, a bit easier to configure in 2010 than in 2007.

SharePoint Dashboard Trick: Drive Multiple Excel Worksheet and Pivot Table Reports | EndUserSharePoint.com

Driving global filters with Excel and Sharepoint.

SharePoint Dashboard Trick: Drive Multiple Excel Worksheet and Pivot Table Reports | EndUserSharePoint.com

SQLVillage.com – Stop Recompilation

With stored procedures that are called hundreds or thousands of times per minute, recompilation is a killer.  Some tricks to avoid.

SET Option that Causes Stored Procedure to Recompile

By Mohan Kumar  -- Published on 09/14/2010

We often use SET option in stored procedure without realizing if it can cause performance problem. I was working on Critical Database Performance issue recently arguing on SET option causing recompile without realizing which one causes or which one not. Moreover I came across in situation when some experts mentioned that "set isolation level read uncommitted" and "set nocount on" are causing recompilation. Whereas truth is these options DO NOT cause recompilation. So I thought of putting following list available handy for reference whenever we write code or optimize code or find stored procedure recompiling due to "set option changed".

 

SQLVillage.com

SQLVillage.com – How to run with VS2005/SSIS 2005 on SQL 2008

 

How to Resolve a BIDS 2005 and SQL Server 2008 Compatibility Issue

By Mohan Kumar  -- Published on 05/24/10 (Earlier published on www.sqlservercentral.com on 09/08/13)

We all know that Microsoft has introduced new feature/component in SSIS 2008. They also renamed few system procedures for SSIS.

I was working on a SQL Server 2008 upgrade project that included SSIS, but the requirement was to hold our SSIS upgrade until the Production upgrade was completed. Therefore, it was important to provide the ability to developers to design and develop their SSIS packages using Visual Studio 2005 but store the packages in SSIS 2008 storage in order to

1. Finish the nonproduction servers upgrade
2. Do not let developers to use SSIS 2008 features until the production server was upgraded so that any package under development can go live (if needed) on the existing SQL Server 2005

I encountered bunch of errors while saving the package that I had opened in Visual Studio for some changes and later trying to save that package in SSIS 2008 storage. Below is the complete scenario that explains what error messages occurred and how to resolve them.

SQLVillage.com

dtexecRemote - Home

One way to get around remotely executing an SSIS package …. without SQL Agent.

Comments

vdeineko wrote  Mar 26 2009 at 3:42 PM

How to remotely execute MS SQL SSIS package
I find my solution the easiest of all. It does not require neither SQL Agent job, nor SMO, or any Visual Studio development , just one-line primitive scripting.
1. Say, you created a local SSIS package, called MySSIS.dtsx in the directory c:\MyDir, located on the SQL Server, named MySQLServer.
2. Now, create a simple batch file (or a vb script) on the server allowing you to execute the 'dtexec' command. Something like this:
dtexec /FILE "C:\MyDir\MySSIS.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
(Full 'dtexec' command options are here: http://technet.microsoft.com/en-us/library/ms162810.aspx)
Save the batch file, say, as 'c:\MyDir\MyBatch.bat'
3. Create a Scheduled Task to execute the batch file. Name it, say, as 'MyTask.' You can set the task to execute once, and even with the past execution time. This way it really will not execute, unless you manually execute it. Test the manual task execution by running the following command from the command prompt:
>schtasks /run /tn MyTask
(More on the ‘schtasks’ options type schtasks /? at the command prompt.)
Well, if everything is correct, it will work, so let's do the next step.
4. Download the PSEXEC tool from here:
http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx
It's free, and it will allow you to remotely execute programs. Install it on your PC, from where you would execute the remote SSIS. Say, you installed it in the c:\pstools directory.
5. Lastly, open the command prompt and type
>"c:\pstools\psexec" \\MySQLServer schtasks /run /tn MyTask
6. Of course, you can encapsulate the last command into a vbs script or a batch or a MS Access Macro, and just click to execute it.
Resuming the above: You need to execute PSEXEC command to execute the remote SCHEDULED task which executes the local SSIS package. The solution seemlessly bypasses the SQL licensing restriction.
Please let me know what you think.

dtexecRemote - Home

Implicit Conversion issues and SQL performance

One commonly-missed performance tuning option is to ensure that parameters and variables passed into SQL are the same data type as  the columns they are referencing.

If you pass in an @nvarchar(25) variable to a varchar(15) column, an implicit conversion is done.  This is performed on the entire resultset, which could impact performance.

It shows up often when using .NET apps and with developers unfamiliar with SQL schema.

That was the question I recently got from a customer: they had two systems which send comparable queries to the same SQL Server database, and the system using ADO.Net was 'significantly slower' than the other system.

In SQL 2005+, finding implicit conversions is straightforward with DMVs (and a not so straightforward query).

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

If you don’t want to rewrite your app, consider rewriting stored procedures (you do use stored procedures?) to convert mistyped variable before the query statement.

Why is ADO.Net so slow? - U2U Blog

This is also a good reason to avoid ORM frameworks (or learn how to use explicitly typed parameters) when dealing with complex SQL queries or large data sets.

More info around LINQ and implicit conversions.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/16/unexpected-side-effects-problems-from-implicit-conversions.aspx

http://www.hanselman.com/blog/ImprovingLINQCodeSmellWithExplicitAndImplicitConversionOperators.aspx

SSIS Community Tasks and Components

Useful set of components available for SSIS

Project Description
This is a community project for developing additional tasks and components for SSIS, and identifying other CodePlex projects that do the same. 51 Integration Services extensibility projects exist on CodePlex - find them and over a hundred more from around the 'net right here.
This list includes projects hosted on CodePlex, items that are available in source or binary-only form for free on other sites, or are commercially available items from third party companies. If there are errors or ommissions in this list, please contact me directly, or post in the Discussions

SSIS Community Tasks and Components

Why you should switch from SQL 2000 to SQL 2005/2008/2008 R2

Other than the support lifecycle and improvements with query performance and administration, there is another reason to upgrade your databases to 90 compatibility mode. 

Compatibility levels are only available for the last two versions. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).

Note that even if you have performed a SQL 2005 upgrade of your databases, the compatibility mode may still be set at 80 (SQL 2000) mode.

Best to run the upgrade advisor and have a disaster recovery plan in place before switching compatibility modes.

http://msdn.microsoft.com/en-us/library/ms144256.aspx

Deprecated Database Engine Features in SQL Server 2008 R2

All-In-One Code Framework

The All-In-One Code Framework from Microsoft provides most anything in any MS language you would need to start building an app.

Not only C++, includes samples for WPF/WCF, ASP.NET, Silverlight and even Azure.

All-In-One Code Framework

Buck Woody : Links for Patterns and Practices for the Data Professional

Some useful links here.

Patterns and Practices Developer Center: http://msdn.microsoft.com/en-us/practices/default.aspx

Microsoft Solution Accelerators: http://technet.microsoft.com/en-us/solutionaccelerators/default.aspx

Application Architecture Guides: http://apparch.codeplex.com/wikipage?title=Knowledge%20Base%20Index&referringTitle=Home&ProjectName=apparch

Application Architecture Guide Free Book: http://apparchguide.codeplex.com/

Microsoft Operations Framework: http://technet.microsoft.com/en-us/library/cc506049.aspx

Patterns and Practices Symposium: http://blogs.msdn.com/b/agile/archive/2010/08/25/the-patterns-amp-practices-symposium-redmond-2010.aspx

Patterns and Practices document from Microsoft, for data: http://msdn.microsoft.com/en-us/library/ms998446.aspx

Article by Brian Knight on Database Standards and Conventions: http://www.sqlservercentral.com/articles/Naming+Standards/sqlstandards/176/

SQL Server Best Practices: http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

Also - The information for making a "default template" for SSMS queries is here in this post: http://blogs.msdn.com/b/buckwoody/archive/2009/10/28/templates-for-new-query-window.aspx

Buck Woody : Links for Patterns and Practices for the Data Professional

DMVs for Query Plan Metadata

Excellent article around troubleshooting slow query performance.

This query also allows you to see historical queries executed.

SELECT TOP 100
        max(last_execution_time) LastExecutionTime,
        SUM(total_logical_reads) AS total_logical_reads ,
        COUNT(*) AS num_queries , --number of individual queries in batch
        --not all usages need be equivalent, in the case of looping
        --or branching code
        MAX(execution_count) AS execution_count ,
        MAX(execText.text) AS queryText
FROM    sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS execText
GROUP BY deqs.sql_handle
ORDER BY 1 DESC

DMVs for Query Plan Metadata

Loading ANSS Earthquake Data Into SQL Server 2008 - Spatial Ed - Site Home - MSDN Blogs

Relevant to those in Canada that experienced the earthquake, here is how to generate spatial reports using a free feed from ANSS.

The Advanced National Seismic System (ANSS) maintains a comprehensive catalog of worldwide seismic events (typically earthquakes) which is a good source of spatial data for SQL Server 2008.  The ANSS Composite Catalog (formerly called the CNSS Earthquake Catalog) provides a search capability which allows the download of seismic events, from 1898 to present, using various search terms. In this post I will cover the following topics:

Download ANSS seismic data

Prepare the downloaded ANSS data

Create a database table for the ANSS data

Load the ANSS data

Add, update and index the spatial data column

Sample spatial queries on the ANSS data

Loading ANSS Earthquake Data Into SQL Server 2008 - Spatial Ed - Site Home - MSDN Blogs

Jas Dhalliwal's Blog : Planetary Cloud: The Rise of the Billion-Node Machine

What if the whole planet was a grid computing effort?  Alternatively, what if the grid computing power of the planet was stored on a single chip?

Two thoughts that might just change the world.

Planetary IT, not just Public Clouds, may well be the next big thing. The ability to get the IT resources of the planet working on problems such as genome sequencing, climate challenges, simulation for nuclear fusion, spaceship design amongst others with a billion-node system composed each of 1000-cores is like getting a brain the size of the entire Earth working for a fraction of a second to find potential solutions to our most challenging concerns of today. This affects everyone. This is the Rise of the Billion-Node Machine – the billion-node IT nexus serving the planet.

Jas Dhalliwal's Blog : Planetary Cloud: The Rise of the Billion-Node Machine

Scale up, scale out.

QueryCell Review and License Giveaway

April 2010 Giveaway for Excel Power Users!

Sam Howley, founder of Oak Focus Software has been kind enough to provide me with some licenses for his Excel Add-In, QueryCell. QueryCell was released in June, 2009 by Oak Focus Software. Try it out with a few free queries, running directly in Excel.

Or open it up to unlimited queries with a license key. Just comment on this post (in English please!) with the most wanted feature you are looking for in Excel and your email address in email [at] email domain format. First 3 get a license for QueryCell.

Beware Sam! Third-party vendors of popular Microsoft plugins have the knack of being bought out by the mother ship, either in the monetary way or The Simpsons way. Good luck.

At first glance, the features from this look like a scaled-down version of PowerPivot, applying SQL instead of DAX.

The Install:

1. Files are placed in C:\Program Files\QueryCell by default. The build I got seemed to be from March 6, 2010. Quick and simple install.
2. Open Excel (I have 2003 on the PC I tested)

Notice the QueryCell button adding to the annoying Office Live button that I never use.

1. Time to fire up my sheet of stock ETFs.

Right-clicking to get rid of the Office Live bar, I notice a new CBarSSQL toolbar. The QueryCell add-in.

Clicking on the QueryCell icon hurts my brain at first. What do I do?

Time to check out the tutorials. Lots of info here. Would have liked to see some sort of MS Wizard interface to setting up a data source, with support for embedded, encrypted ole-db data sources, or perhaps connection strings stored on a server. But this is a developer tool, right? Right?!?

Wrong. This is a tool every power Excel user should have.

Select some cells, Right-Click QC-Add Region As Table. Now I get a data source called A and a list of fields to the right.

Ok, type some SQL. Intellisense! SQL Formatting, something Microsoft is still missing! As a SQL DBA, I miss the CTRL-E shortcut to execute SQL. So I click the blatantly obvious Run SQL button.

Run my query and the Query Results fall into the QueryResults worksheet. Cool!

Add another table. Join the two together. Wow.

Click Mark Rows. Run another query. Multi-rule Conditional formatting! In Excel 2003, no less. Complex, repeatable Auto-Filter!

Tried to add the A.Open column. No go, reserved word. Where's my quoted identifiers or square brackets? QueryCell converted the Open column to COLA. Renaming the column should do the trick but if targeting the financial sector with this, a different way of handling reserved words (or at least OPEN) should be on the feature list. No worries!

Clicked the Refresh Regions button in the top left. Automatically detected data sources!

Somehow I feel like I've just scratched the surface on what I can do here. In any case, this is a tool any Power Excel User should check out, even if they don't know SQL.

Some features I would look forward to seeing:

• Improved SQL support and templates
• Access-style Query View designer (for those lazy dba’s)
• Ability to undock the add-in (multiple monitors)
• Update statements to Excel, and back to SQL
• More data sources (Analysis Services cubes, Sharepoint Lists, Web Services)
• Ability to reset defaults

I can see this tool saving hours of time with complex VLOOKUP formulas, conditional formatting, and cut-paste jobs. For repetitive tasks, generating test data, and automated data refreshes from multiple sources, this tool looks a great helper.

VMware and SQL and Lock Pages in Memory - SQL Server with Mr. Denny

 

In other words, use Lock Pages in Memory with SQL/VMWare with great caution.

With the recent release of the ability for the Lock Pages in Memory setting to be used on SQL Server 2005 and 2008 Standard Edition I see more and more people shooting them selves in the foot with this setting when running under VMware.  I see this as becoming more of an issue now that this switch is available for Standard edition as I would assume that most virtualized SQL Server installations are done using SQL Server Standard Editions.

VMware and SQL and Lock Pages in Memory - SQL Server with Mr. Denny

How to connect Excel to SQL Server 2005 Analysis Services

Another trick to retrieve dimension data into an Excel table based on my previous posting.

with member [measures].[rowcount] as '1'
select [measures].[rowcount] on 0, [<Dimension>].[<LevelName>].members on 1
from [$<MyDimension>]

Substitute with the dimension you would like to pull data from.

How to connect Excel to SQL Server 2005 Analysis Services

Flattening Pivot Tables into Excel 2007 Tables

If you have an MDX query, you can run it directly in Excel outside of a pivot table.

The steps are:

1. Create a pivot table against an OLAP cube

2. Drag in a measure (not calculated) and rows/columns/filters.

3. Select Show Details on the measure.

This brings up an Excel drillthrough table.  Under Table Tools – Design, Connection Properties, you have access to Command Types SQL, table, Default.  Under this is a drillthrough query that can be converted to MDX.

Unfortunately it appears as though it returns key results instead of descriptive names for some columns, but it might be useful.  You can reference a column name in =Table_ExternalData_1[#Headers] so you could write a VLOOKUP to get the caption from another sheet if required.

You can go to Table Design – Summarize with Pivot table, to get a non-OLAP pivot table.

You can add row numbers, remove duplicates, and export the table to a Sharepoint List.

I like using Excel tables, and one of the things that has mildly annoyed me in the past is that you can’t bind the results of an MDX query to an Excel table in the way you can do with a SQL query. I mean, pivot tables are all very well, but sometimes a plain old table is a better fit – for example, think of the cool stuff the data mining addin works can do with tables. Luckily, with a bit of hacking, you can do it… and here’s how.

Chris Webb's BI Blog's Blog - Windows Live

You can do all of the wacky stuff available with Excel 2007 tables.

So now let’s finish our discussion of syntax, since some of you are probably asking questions like “how do I reference the entire table, headers and all?”  Referencing the entire table can be done this way: =Table1[#All].  Or if you just want to reference the headers: =Table1[#Headers].  To reference the entire column: =Table1[[#All], [Column1]].  To reference just the header value of a column: =Table1[[#Headers], [Column1]].  The special keywords can also be combined: =Table1[[#Headers],[#Data],[Column1]].  At this point I think you get the idea. 

http://blogs.msdn.com/excel/archive/2005/10/28/486604.aspx

Now if it didn’t involve hand-coding your own MDX this could be a bit more useful…

Next step would be to publish this to Excel Services in Sharepoint and see if it is refreshable.  You would end up with a live Reporting Services-style flattened rowset report that could be exported to Excel with live connectivity.

With the crummy key captions of course…

Implementing Most Recently Used Lookups - SQLServerCentral

Great idea for implementing adaptive list/drop boxes of lookup data, with the most commonly selected items first.

How many times have you had to scroll through 267 country names to get to the "United States" in a list box? We constantly force our users to do unnecessary work when it would be a breeze for us to implement adaptive lists ordered by most commonly selected foreign key values or by most recently selected foreign key values.

Implementing Most Recently Used Lookups - SQLServerCentral

Brian Hartman's Report Viewer Blog & How to Move the @*#^$! View Report button

Looking forward to implementations of SQL 2008 R2 and the new Visual Studio 2010.

One of the new features we added to the ASP.Net Report Viewer in Visual Studio 2010 is a JavaScript API to allow you to interact with the viewer on client.  In reading many of the posts on the report controls forum, we found that many people struggle when implementing a custom toolbar or replacing portions of the toolbar functionality.  The new JavaScript API is intended to make it easier for you to provide the same functionality available through the built-in toolbar with a minimum amount of effort.

Brian Hartman's Report Viewer Blog

With the Visual Studio 2005 / SQL 2005 Report Viewer, there is no easy way to manipulate the toolbar.

One common issue is the View Report button scrolls off the page for larger width reports.  Other than implementing your own custom parameters & toolbar functionality, here is another way to manipulate.

                   <rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote" Height="100%" Width="100%" ShowDocumentMapButton="false"  SizeToReportContent="true" AsyncRendering="false">
        </rsweb:ReportViewer>
        </td>
        <script language="javascript">
        function getRepViewBtn() {
  return document.getElementsByName("ReportViewer1$ctl00$ctl00")[0];
}
  var btn = getRepViewBtn(); 
  src = btn.outerHTML;
  btn.style.display = 'none';
  src = src.replace('View Report','View/Refresh Report');
  src = src.replace('style="','style="background-color:maroon;color:ffffff"');
  document.write (src);

</script>

This places the View Report button above the Report Viewer, and changes the text.

To fix the double scroll bar issue.

<head runat="server">
<title>Report Viewer</title>
<script type="text/javascript" language="javascript">

window.onload=function()
{

var viewer = document.getElementById("ReportViewer1");
var frame = document.getElementById("ReportFrame");
if (frame != null && viewer != null)
{
var reportDiv = eval("ReportFrame").document.getElementById("report").contentDocument.getElementById("oReportDiv");
viewer.style.height = reportDiv.scrollHeight;
viewer.style.width = reportDiv.scrollWidth;

  //var btn = eval("ReportFrame").document.getElementsByName("ReportViewer1$ctl00$ctl00")[0];
// btn.style.display = 'none';
}

}
</script>
<script type="text/javascript" language="javascript">

window.moveTo(0,0);
window.resizeTo(screen.width,screen.height);

</script>

</head>

It sounds like the Visual Studio 2010 Report Viewer just might solve these issues… but for those of us still stuck in the past…

SQL Server Helper & a helper script to redirect SQL to Excel

 

Useful site for lots of SQL Script examples.

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

SQL Server Helper

If you have Powershell installed, exporting to Excel is probably a non-issue for you.

However, here is a quick script that was useful to me and was fairly simple to implement.

1. Create a directory and copy the Excel shortcut to it.  Rename the shortcut to Excel.lnk
2. Create a command script countalltables.cmd

osql –S [sqlservername] -d [databasename] -E -I -s "," -Q "spcCountAllTables" > CountallTables.Csv
Excel countAllTables.Csv

3. Create a stored procedure in the database spcCountAllTables

create proc spcCountAllTables
as
set nocount on
SELECT  'myTable' as TableName, COUNT(*) as Total FROM MyTable UNION ALL

4. Run the proc countalltables.cmd

You should see a list of tables you defined with their counts.