SQL Server forums: development, administration, security, performance tuning tips

SQL Server forums: development, administration, security, performance tuning tips

Huge - SQL Monster

Donald Farmer on SQL Server Integration Services : Oracle and SQL Server Integration Services

Donald Farmer on SQL Server Integration Services : Oracle and SQL Server Integration Services: "SQL Server to Oracle. What can I say? S*!& happens. "

Office Excel Add-in for SQL Server Analysis Services

Link to new version 1.5 available.

Brian Knight, SQL Server MVP

Speeding up the VS/SQL User Interface Load Time

One of the frustrating things about the new Visual Studio interface is the load speed. This applies to SQL Server 2005 as well. I have a very new computer which is quite speedy and the splash screen still takes 5-10 seconds to load. On my old machine, this is more like 10-15 seconds while the splash screen is loaded before SQL Server Management Studio would show the login dialog box. A remedy I stumbled across was to add the -nosplash switch at the end of the executables call in the shortcut. After killing the splash screen from displaying, my load time on my good machine was down to 1-2 seconds before being prompted for a login screen. I come in an out of tools so much, that this is quite useful.

To do this, right-click on the shortcut in the start menu for your tool like Business Intelligence Development Studio or SQL Management Studio and click Properties. In the target text box change the shortcut to point to the following for SQL Management Studio:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -nosplash

For Business Intellgience Managment Studio, the same logic applies:

"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" -nosplash

Also, this works for the Visual Studio environment in general. I personally hop in and out of these tools all day long and this solved a lot of frustration.

Slowly Changing Dimensions

Thomas Pagel BI Blog :: "One of the very nice enhancements of SQL Server 2005 Integration Services (SSIS) compared to DTS is the introduction of the wizard for slowly changing dimensions (SCD). It makes it easy to create dimension tables storing changing data and tracking these changes. There are some blogs about that, i.e. Jamie has a nice example for a package using the wizard? http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx"

Task Parameters

Task Parameters

OLEDB ?
Ado.net @param
ADO ?
ODBC ?

Thomas Pagel BI Blog :

Thomas Pagel BI Blog :: "So we?re getting closer and closer to RTM and we all hope that Microsoft will deliver a perfect product after such a long time of Betas (sorry, IDWs and CTPs). But you can be sure that this will not be a 100% finished product, just as Windows 2003 and other releases were not perfect on day one. I?m not one of guys telling you ?wait for Service Pack 1?, SQL 2005 is a too good product to spend more time without it. But I?m sure you have to expect some issues mostly related to performance.

One issue we got aware of is a performance flaw when you use NON EMPTY with ?non trivial? calculated members (well, this seams to be starting with already quite trivial calculations?). We had queries running for minutes and asked Microsoft why they take so long. They suggested exchanging the calculated member with the formula behind it. We had this with just a ParallelPeriod function we used in a calculated member. When we changed the query to use the base measure and put the ParallelPeriod in the query the performance was very good (some seconds for a quite complex MDX).

Remember that NON EMPTY is used very often, i.e. Reporting Service?s query designer uses it by default. So it?s quite likely that you get in touch with this issue.

It?s confirmed that this flaw will NOT be fixed in RTM.
And there are others I?m not aware of, yet?"

Scrollable checklists

Scrollable checklists: "An alternative to the select element with enabled multiple attribute.

This example is described in the Check it, don't select it article on C82. See a bare-bones example."

Sequence number in Reporting Services

Want a running total like Excel's row numbering?


RunningValue(1, Sum, Nothing) gives you a sequence number

Visual Studio Magazine ? Build Client-Side Reports Easily

Visual Studio Magazine ? Build Client-Side Reports Easily

Office Business Scorecard Manager 2005

Office Business Scorecard Manager 2005: "SharePoint Lists as Data Sources in Business Scorecard Manager
posted Wednesday, December 07, 2005 1:42 PM by Patrick Husting
SharePoint runs on SQL, SQL supports ODBC so of course it makes sense to be able to use SharePoint lists as data sources in Business Scorecard Manager as soon as you have ramped up on the SharePoint schema.

Here's an example connection string:

Driver={SQL Server};Server=localhost;Database=STS_MACHINENAME_1;Trusted_Connection=yes;

Here's a sample query that does a count:

select count(*)
from userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'SOME_LIST_TITLE' where nvarchar3 = 'SOME_COLUMN_HEADER_1' and nvarchar2 = 'SOME_COLUMN_HEADER_2'

Here's one that does a ratio:

declare @nApples decimal
declare @nOranges decimal
declare @ratio decimal

set @nApples = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'RED') or(nvarchar3='REDISH')))

set @nOranges = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'ORANGE') or(nvarchar3= 'ORANGEISH')))
"

Office Business Scorecard Manager 2005 : BSM05 Sample Project Plan

Office Business Scorecard Manager 2005 : BSM05 Sample Project Plan

64 Advantage

64 Advantage

Here's training for the future of 64-bit computing.

Visual Studio Magazine ? Build Client-Side Reports Easily

Visual Studio Magazine ? Build Client-Side Reports Easily

PromptSQL: SQL Intellisense for MS Query Analyzer, VS.NET 2003, Management Studio and VS 2005

Autocomplete for SQL

daclark's WebLog

daclark's WebLog: "Comparing machines in SMS
Here's a query for comparing Services on two machines. Just Clone the 'Compare software inventory on two computers' report and replace the SQL with below.

select (case when inv1.Name0 is not null then @name1 else @name2 end) as Netbios_Name0,
(case when inv1.Name0 is not null then inv1.DisplayName0 else inv2.DisplayName0 end) as DisplayName0,
(case when inv1.Name0 is not null then inv1.Name0 else inv2.Name0 end) as Name0,
(case when inv1.Name0 is not null then inv1.StartMode0 else inv2.StartMode0 end) as StartMode0,
(case when inv1.Name0 is not null then inv1.StartName0 else inv2.StartName0 end) as StartName0

from ( select s.ResourceID, s.DisplayName0, s.Name0, s.PathName0, s.StartMode0, s.StartName0
from v_GS_Service s join v_R_System sys on s.ResourceID=sys.ResourceID where sys.Netbios_Name0 = @name1 ) as inv1

full outer join ( select s.ResourceID, s.DisplayName0, s.Name0, s.PathName0, s.StartMode0, s.StartName0
from v_GS_Service s join v_R_System sys on s.ResourceID=sys.ResourceID where sys.Netbios_Name0 = @name2 ) as inv2

on inv1.Name0=inv2.Name0 and inv1.StartMode0=inv2.StartMode0
where (inv1.Name0 is NULL) or (inv2.Name0 is NULL) order by DisplayName0, Name0, Netbios_Name0


Here's a query for comparing AddR"

Creating Outlook Add-ins with Visual Studio 2005 Tools for Office

Creating add ins for Office with new tools.

calculated sum - MSDN

calculated sum - MSDN: "Not sure if this solution will work for you, but you can add a Calculated Field to the Data Set, say: 'FilteredQty', defined as (assuming Quantity is an integer):

CInt(iif(Fields!Country.Value = 'USA', Fields!Quantity.Value, 0))

Then in your report you can use this expression:

Sum(Fields!FilteredQty.Value), wich should return 4 rather than 10

To make it dynamic, 'USA' can be replaced by a report parameter."

Download details: Microsoft KPIUtil.exe tool for Microsoft Office Business Scorecard Manager 2005

Download details: Microsoft KPIUtil.exe tool for Microsoft Office Business Scorecard Manager 2005: "Microsoft KPIUtil.exe tool for Microsoft Office Business Scorecard Manager 2005
Download files belowQuick InfoVersion:1.0
Date Published:11/28/2005
Language:English
Download Size:38 KB - 216 KB*
*Download size depends on selected download components. "