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
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
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.
![IC108955[1]](http://lh6.ggpht.com/_W25Un3JaOdk/TM2AHut--cI/AAAAAAAAAUg/9qWcaUxN8CA/s1600-h/IC108955%5B1%5D%5B3%5D.png "IC108955[1]")
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.
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://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/
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
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".
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
- Finish the nonproduction servers upgrade
- 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.
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.
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).
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://www.hanselman.com/blog/ImprovingLINQCodeSmellWithExplicitAndImplicitConversionOperators.aspx
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
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.
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.
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
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
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
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
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:
Notice the QueryCell button adding to the annoying Office Live button that I never use.
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:
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.
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
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.
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…
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.
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…
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.
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.
Not sure how common this scenario is, but something to be aware of if you are using DB snapshots (SQL 2005 & SQL 2008 feature) and a 3rd-party backup tool that acts on the database files.
SQL Server 2005 introduced snapshot databases and modified DBCC to create secondary snapshot streams for online DBCC operations. The online DBCC creates a secondary stream of the database files that is SPARSE. CSS has found that if a 3rd party backups and utilities or NT Backup is used against the database files the SPARSE setting may get incorrectly, propagated to the parent stream. In the case of DBCC this is the original database files(s).
Test-drive SQL 2008 R2 here, without the downloads.
What manner of lunacy is this?
No, we’re not insane (not criminally insane, anyway). We just decided to allow the most brilliant and creative Microsoft Administrators and Developers we know to have unfettered access into the highly protected core of the Microsoft® SQL Server® 2008 data platform. View all SQL Server editions, here.
Power Pivot is the new name for Microsoft Gemini, a user-based cube building and data analysis add-in for Excel 2010.
Not to be confused with Pivot Power.
A few years ago, one of my clients determined that they should centralize their real estate and shift around workers to better optimize the facilities. Read – downsizing. I found this out from a student who was working on the project much later. All I knew was as contractors we had to be out of the building, and we might want to bring our computer equipment with us (and chairs, which I found out later).
So our development team was shuffled off from one of the nicest penthouse offices in the city, attached to a huge mall, next to the largest assortment of restaurants in the city, to a 1960s-design nuclear bunker disguised as an office.
We found out quickly that our small team of people had the run of the floor. Every chair was broken, and designed before Herman Miller got out of high school. There was a doomsday clock attached to the ceiling in the centre of the floor. It didn’t work either. The decor was styled with vintage warning signs. “Danger due to acid.” “Diesel generators – do not open this door!”.
It was a great work environment that motivated me to work from home (and other client locations) much more often.
Phil Factor reminded me about this, and has a funny story about how location is sometimes more important than appearance or prestige.
My friend, with some exaggeration, explained that the management had decided that it seemed wrong to give any offices of a major retail bank the appearance of a deserted building, so all contractors were given offices near the windows.
Phil Factor's Phrenetic Phoughts : Moving Down in the World of Work
Simple Talk has a great article on the top 10 things you might have issues in dealing with SQL Reporting Services.
Challenges/Solutions
SSRS offers a range of different reporting techniques and technologies, to cater for the reporting needs of all levels of users, from the chief executives, to business analysts, to operational staff. Their reporting needs range from simple, tabular ad-hoc reports, to parameterized, linked or snapshot reports, to complex drill-down and drill-through multi-level reports.
Following is the list of some of the challenges I have encountered while developing such reports using Reporting Services 2000/2005. In the sections that follow, I will cover each challenge individually, providing insight into what may cause the difficulty, alongside a possible solution.
- Horizontal Tables: Calendar Reports
- Select "ALL" Query Parameter option
- Multiple Sheets in Excel
- Excel Merged Cell Issues
- Blank Pages
- Vertical Text
- Report Data in Header/Footer
- Are you missing XML/CSV data on your exports?
- Template Reports
- Using the Reporting Services database
A ZIP file containing samples of the reports detailed in this article is available to download, try out and amend to suit your own needs.
Ten Common SQL Server Reporting Services Challenges and Solutions
Microsoft has released a new version of the IIS Database Manager, providing the ability to manage SQL from a web page.
IIS Database Manager allows you to easily manage your local and remote databases from within IIS Manager. IIS Database Manager automatically discovers databases based on the Web server or application configuration and also provides the ability to connect to any database on the network. Once connected, IIS Database Manager provides a full array of management options including managing tables, views, stored procedures and data, as well as running ad hoc queries.
IIS Database Manager provides native support for SQL Server and is also fully extensible for developers to add support for other database systems. In addition, because IIS Database Manager is an extension of IIS Manager, administrators can securely delegate the management of databases to authorized local or remote users, without having to open additional management ports on the server.
Here are a few articles to get you started on using the IIS Database Manager:
Publisher:
VMwareLatest Revision:
Mar 18, 2009Download:
http://www.vmware.com/files/pdf/solutions/sql_server_virtual_bp.pdfDescription
Microsoft SQL Server is a very popular and widely deployed general purpose database server supported on Windows Sever operating systems. As customers embrace a “virtualize first” policy for all applications within their enterprises, they often need guidelines for deploying SQL Server in VMware Infrastructure. This paper should help you understand how to characterize your SQL Server databases for virtualization and the best practices for designing VMware Infrastructure to support SQL Server.
Intent
Sometimes when generating reports or text for web pages or form letters, it becomes handy to concatenate multiple column values from rows of data into a single value entirely within T-SQL. For reference, a problem I encountered recently involved selecting one or more department names and converting them to a comma-separated list to appear in a drop-down list.
The SQL language itself doesn't easily facilitate this but there are ways around this. Below I outline two alternate queries using SQL Server.
Open DBDiff for SQL Server 2008
By Muthusamy Anantha Kumar aka The MAKOpen DBDiff 1.0 is the utility provided by Microsoft via CodePlex. You may have used the Open DBDiff utility for SQL Server 2005 before. DBDiff utility compares two databases and generates a script that could be used to synchronize two databases. The Open DBDiff utility is in beta version 8. This article is going to illustrate the various use of the Open Diff utility.
Features Supported by the Editions of SQL Server 2008
Use the following tables to determine which features are supported by the different editions of SQL Server 2008.
Winding down availability for add-ins in earlier versions brings this add-in’s lifespan to a close. It sounds like customers were using this instead of upgrading to Excel 2007… which is still missing some of the features of the add-in.
”The Excel Add-in for SQL Server Analysis Services has been removed to avoid customer confusion about support for this component. As noted in the details that accompanied the release of this product, Microsoft does not provide any support for this add-in and has no plans to release future versions. Newer versions of Excel include most of the functionality that is provided by this add-in; these newer versions are supported according to the Microsoft Product Lifecycle.”
Farewell to the Excel 2003 addin and the BI Accelerator - Windows Live
What is the go-to path for Excel 2003 customers? Upgrade. It is unfortunate that they removed access to this tool rather than “open-sourcing” it or sending it off into MS Research land.
How about removal from MS downloads and addition into CodePlex?
http://www.codeplex.com/site/search?projectSearchText=analysis%20services
Same goes for tools like Proclarity. Leaving customers in the dark is not necessarily the correct approach.
Excel 2003 has ended mainstream support as of April 14, 2009.
http://office.microsoft.com/en-us/products/HA102294401033.aspx
Extended support ends January 14, 2014.
Pinal Dave comes through with another useful query.
Quite often, we need to know how many any particular objects have been executed on our server and what their execution plan is. I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.
SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'In result set, you will find the last column QueryPlan with XML text in it. Click on it, and it will open a new tab displaying the graphical execution plan.
A project that allow the execution of Unit Testing against a database (Relational or Multidimensional).
It uses NUnit (http://www.nunit.org/) )as unit-testing framework and does not require DBA or BI Developer to know anything about .NET: just write your SQL or MDX queries and test them. QueryUnit will take care of automatically create the assembly that will test your query for you, using NVelocity (http://nvelocity.codeplex.com/) to generate .NET classes and the CodeDom engine to compile them at runtime.
Compound Operators
SQL Server 2008 introduces the Compound Operators as a programmability enhancement in Transact-SQL. Compound operators execute some operation, such as +, -, * and /, and set an original value to the result of the operation. It is just a simpler syntax that you can use on commonly performed operation such as incrementing a variable by a certain number.
As an example, instead of doing the following:
SET @Index = @Index + 1
it can now simplified as follows:
SET @Index += 1
I was recently asked to investigate a problem with a server and the lack of space on a partition. After a quick look using Treesize I noticed a suspicious folder being used by an application for logging purposes.
There were over 700,000 files in it! When I tried to browse the folder using explorer it took an absolute age to open as you can imagine. To rectify the problem and recover the majority of the disk space being used by the logs I wanted to delete the contents of the logging folder that was older than 30 days. After a quick search I discovered a command line utility called forfiles.exe that is included with Windows Server 2003. Using forfiles.exe I was able to delete all files older than 30 days like so:
forfiles.exe /p (pathtofilestodelete) /s /m *.* /d -30 /c "cmd /c del /q @path"
A working example is:
forfiles.exe /p d:\logs /s /m *.* /d -30 /c "cmd /c del /q @path"
This will delete ALL files from d:\logs (and all sub folders it contains because /s has been used to force recursion) older than 30 days without prompting you to confirm deletion.
Here is an explanation of the switches I used:
/p = The path to search for the files you want to check the date of and remove
/s = Recurse subdirectories contained within the path specified using /p and check them as well
/m = The search mask to be used for the file type you want to check the date on (*.* being all files)
/d = The date to compare the files against. A standard date type can also be used (dd/mm/yyyy)
/c = The command to be used on a file that matches the /m and /d criteria
/q = Used within /c to instruct the del command to delete files quietly
JJClements.co.uk » Delete files older than certain number of days
You can tap into the self-education that comes from countless hours spent at home trying to locate information on the Web and turn it to your advantage in your own SQL Server-based search applications. With just a little bit of code you can help reduce your training costs and give your users an easy to use interface that will make them want to use your search applications. In this article I'll explain how to convert Google-style queries to SQL Server's full-text search CONTAINS predicate syntax
Some of the new features in Excel 2010 specific to Analysis Services
But anyway, now I’ve been given the go-ahead to blog I thought I’d list all of the new features I’ve found that are relevant to Analysis Services users. This does not include anything to do with Gemini, because Gemini isn’t part of the Technical Preview and I don’t have it yet unfortunately. I’m also not going to comment on bugs or things that don’t work in the way I’d want because, after all, this is not released software and things can and hopefully will change before RTM.
However, the recommendation fails when you get to the log portion of the equation. Why? It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style. On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on… Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file. You can only get a performance gain from multiple files on the data portion of a database.
Kevin Kline : Old Performance Tuning Recommendations Die Hard
Your database knows how tables are relationally connected. DbOctopus takes advantage of that data wherever it can. Do you need to quickly see related rows from other table, without manual searching? Or to see descriptive names from FK table instead of numeric IDs? It's just a tip of iceberg of what DbOctopus can do to help you be more productive, while making everything easier.
Multipurpose Row_Number() Function
One of the features available in SQL Server 2005 is Row_Number() function. It is not only used to generate row number for each row but also used for other purposes as well. I breifly explain how it can be used for various purposes
Shudder. If you need to, here’s the way automate Excel via PowerShell. Just don’t stick this on a server someplace…
How Can I Use Windows PowerShell to Automate Microsoft Excel?
Hey, Scripting Guy! How can I use Windows PowerShell to automate Microsoft Excel?
Hey, Scripting Guy! How Can I Use Windows PowerShell to Automate Microsoft Excel?
One of the custom log entries that I didn’t know about was the Execute SQL Task’s ExecuteSQLExecutingQuery and now having taken a look at it I wish I’d have known about it a lot sooner. The real power of this custom log entry is that it shows the SQL statement that gets executed against the data source which is especially valuable if the SQL statement is constructed using an expression.
In Part 4 of this series, I discussed Internet Explorer setup. To date, the series has been about steps needed to set up Kerberos Constrained Delegation with Windows Integrated Authentication. While the first parts of the series were not a step-by-step guide in how to set up Kerberos, it was meant to provide an overall checklist of things to make sure were properly set up. What happens if you have an issue with Kerberos after it is set up? That’s what this part of the series is about.
Unfortunately, many Kerberos setups have problems because:
Kerberos Constrained Delegation – Troubleshooting Tools (Part 5 of 6) - Windows Live
