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.