SQL Server 2008 SSIS Cribsheet

Great intro to the new features in SSIS 2008.

Key takeaways:

• c#/web services within scripts
• better performance and caching
• Data Profiling
• Intelligent mapping of data types

Things I don’t get:

“The biggest advantage to this change is that you can have BIDS 2005 and BIDS 2008 installed on the same machine”

Doesn’t seem like an advantage to me to have more disk space used up, instead of a backwards-compatible development environment.

Here’s a link to the article.

SSIS 2008 Crib sheet

For things you need to know rather than the things you want to know

SQL Server 2008 SSIS Cribsheet

SSAS: Query Performance Tuning Whitepaper

 

For those of you with the assembly from the Analysis Services Stored Procedure project installed you can go one step further and use the following.

CALL ASSP.ClearCache("Adventure Works")
GO
SELECT {} ON 0
FROM [Adventure Works]

SSAS: Query Performance Tuning Whitepaper

Jesse Orosz: SQL Server Analysis Services Blog

Jesse demonstrates how to overcome the ‘double hop authentication issue’ in Analysis Services.

As you know we have run into a problem with querying Analysis Services from another computer (our desktops for example) after connecting to a secondary server.

After working on this case with MS, we have an understanding of the problem and we have a solution.

The problem is the desktops not requesting Kerberos tickets from the domain controller. This is a bug in Kerberos dll and is fixed with a hotfix by Microsoft.

Solution:

1)     A Kerberos hotfix must be installed in our desktops.

Kerberos Hotfix canbe installed from (hotfix for IA64, x64, Win2003 also exist)

2)     The linked server from secondary server to target server (cubes) must be defined with Fully Qualified domain name.

I would like to specify a couple of more points here, since they are prerequisites for this double hop authentication to work.

1)     We need to define the servers with "Trust this computer for delegation to any service (Kerberos only)" in Active Directory as opposed to “Do not trust this computer for delegation”.

2)     We need to register the OLAP service with SPN as follows for the target server

a.     Setspn.exe -A MSOLAPSvc.3/CUBESSERVER USER\svc_db

b.    Setspn.exe -A MSOLAPSvc.3/CUBESSERVER USER\svc_db

Jesse Orosz: SQL Server Analysis Services Blog

SQL 2005 Mirroring - Automatic Failover « Brad Marsh’s Weblog

 

This post is a follow on from a previous post ‘Complete Guide to SQL 2005 mirroring using the GUI‘

SQL 2005 Mirroring - Automatic Failover « Brad Marsh’s Weblog

Removing Duplicate Records using SQL Server 2005 | Servers and Storage | TechRepublic.com

 

Duplicate Records

Duplicate records can occur numerous ways, such as loading source files too many times, keying the same data more than once, or from just bad database coding. Having a primary key on your table (and you always should have one) can will in the removal of the duplicate records, but even w/ a primary key it is never a fun task to have handed to you to complete.

Removing Duplicate Records using SQL Server 2005 | Servers and Storage | TechRepublic.com

T-SQL 2005: One-to-many comma separated

Efficient string concatenation in SQL?

We have 2 tables one-to-many. How can we fetch parent table field, and the second field is its children comma separated ? In MSSQL 2000 we could use the following function. But in MSSQL 2005 with the help of FOR XML PATH feature it is a lot easier and the performance of string concatenation is amazing.

SELECT CustomerID
      ,(SELECT CAST(OrderID AS VARCHAR(MAX)) + ',' AS [text()]
          FROM dbo.Orders AS O
         WHERE O.CustomerID = C.CustomerID
         ORDER BY OrderID
           FOR XML PATH('')) AS Orders
  FROM dbo.Customers AS C;

T-SQL 2005: One-to-many comma separated

BizTalk 2006 Comparison

A low-cost alternative to BizTalk?  Sounds useful…

Generally we consider BizTalk to be over-engineered and awkward to use.  It's well placed to solve your problem, and will ultimately deliver a reliable solution - however development is complicated (requiring specialist personnel), debugging is difficult and it can't easily be used straight out of the box.  Additionally, relative to SmartsIntegrator, BizTalk is very expensive!

BizTalk 2006 Comparison

Microsoft OLAP by Mosha Pasumansky : Analyze MDX with MDX Studio

Best practice design tool for MDX queries is now available in MDX Studio.

One last thing to mention: Best practices are exactly what they are – best practices. It means, that most of the time using them you will be better off. However, they are absolute rules to be followed always. Sometimes there are exceptions from the rules. AS is a complex, feature-rich product, and MDX engine is probably the most sophisticated piece in it. Sometimes features interact in such a way, that some best practices might result in worse results. For example, one of the advices that MDX Studio will give is when it sees Filter(CrossJoin(…)), it will recommend using CrossJoin(Filter(…)) instead. It is common sense to reduce the size of the set before crossjoin’ing. But not only this is not always possible, but also there are rare cases where it would result in worse performance. So while you are encouraged to follow the best practices, always use them as a guide, and measure before and after applying the rules to make sure things do improve.

MDX Studio 0.4.0 is released with desktop and online versions simultaneously.

Desktop version can be downloaded from: http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.4.0

Online version can be accessed at: http://mdx.mosha.com

As usual – please post your comments, suggestions, bug reports etc at MDX Studio forum.

Microsoft OLAP by Mosha Pasumansky : Analyze MDX with MDX Studio

Lazy developer – Powershell for SSIS

I’m sure this could be applied to a package in order to build a custom solution for SSIS ETL against multiple tables, without manually creating ETL for each table.

Not to be one to admit defeat (and being too lazy to edit 120 columns by hand) I pulled together some C# to work with the interfaces that I need and then exposed this as PowerShell cmdlets. This both simplified the script and got around the issue of PowerShell not casting to the interfaces I needed.

The following script opens a package, grabs a reference to the columns property of a given csv connection and then loops through the columns, changing all the datatypes and then finally saves the package.

add-PSSnapin powerSSIS

$pkg = get-ssisPackage "C:\temp\Test Package.dtsx"
$fileCon = $pkg.Connections["CSV file"]
$col = $fileCon.Properties["Columns"].GetValue($fileCon)

 
for ($i = 4; $i -lt $col.Count; $i++)
{
  $name = get-ssisflatfileColumnName $pkg "CSV File" $i
  if ($name.startsWith("Column"))
  {
    set-ssisflatfileColumnName $pkg "CSV File" $i "Mth$($i-2)"
    $c = get-SSISFlatFileColumn $pkg "CSV File" $i
    $c.DataType = [Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType]::DT_Numeric
    $c.DataPrecision = 18
    $c.DataScale = 5
  }
}

set-SSISPackage $pkg "C:\temp\Test Package.dtsx" 

Darren Gosbell [MVP] - Random Procrastination

Andrew Fryer's Blog : Microsoft SQL Server Community Utilities

 

Microsoft SQL Server Community Utilities

As well as the independent SQL communities, there is also a Microsoft run one, the Microsoft SQL Server Community Worldwide ( You can tell it’s the Microsoft one by the catchy title).  They do tend to assume worldwide is the United States wide or even the Seattle area so you can drop in to Redmond for SQL school.  However don’t let that put you off, there is some really good stuff there including a list of utility scripts here  as long as your arm for probing the dark corners of your installations:

• Get a list of databases that are not being backed up
• Logins Permissions Report
• FileGroup Listing - Object (Table/Index) listing in filegroup/files
• SQL Server 2005 - Backup, Integrity Check and Index Optimization
• Find out which SQL Agent jobs are currently executing
• Get facts on whether statistics need to be updated
• Run SQL (T-SQL Statements or Stored Proc) on multiple Servers
• SQL Server data/log usage for each drives
• Find out if your local SQL Server is configured to use /3GB switch or not?
• Identify orphan SQL Server user accounts from your local SQL Server.
• See what's happening behind the scenes of TempDB database.
• Script to find out which login has what server role permissions.
• Create Audit Table
• Drop all foreign keys and tables in a database
• Control Database Mirroring Failover and Failback thru script.
• Defrag indexes from one or all databases.
• Auto Failover SQL Jobs during Database Mirroring Session.
• Backup all User and System Databases excluding Mirror Databases.
• How to determine the space used by Tables in the database.
• Quickly get database information
• Detect highest CPU bound queries
• Get SQL Server Agent job configuration information
• Rebuild all indexes in a user database
• How to get current date and time of a system.
• Changing the owner of DTS Packages
• Get a snapshot of My Computer in SQL Server
• Get details about your database
• Get the Facts about your Backups
• SQLAgent.out Size Alert
• Unrestrict database growth for one and for all
• To Index or to Defrag (Smart INDEX)
• Detect Real-time Deadlocks and Blocks
• Rebuild Indexes Online
• Are your indexes useful?
• Check the status of database mirroring session
• Checking Integrity and Consistency of Databases
• Track daily data growth
• Attach and Detach SQL Server Databases

Andrew Fryer's Blog : Microsoft SQL Server Community Utilities

Charlie Maitland’s Blog

Charlie has a huge amount of links on BI, Axtapa & Dynamix.

Charlie Maitland’s Blog

Jason Morales' Microsoft BI Update

 

Enterprise & Standard Edition Feature Comparisons:

- Microsoft SQL Server 2008

- Microsoft SQL Server 2005

Business Intelligence features of SQL Server Enterprise Edition:

(that are not in the Standard Edition)

Advanced Analytics

- Account intelligence

- Linked measures and dimensions

- Perspectives

- Semiadditive measures

- Writeback dimensions

Scalability and Performance

- Proactive caching

- Partitioned cubes and distributed partitioned cubes

- Auto parallel partition processing

Integration Services

- Data mining query transformation

- Data mining model training destination adapter

- Fuzzy Grouping transformation

- Fuzzy Lookup transformation

- Term Extraction transformation

- Dimension processing destination adapter

- Partition processing destination adapter

Reporting Services

- Data-driven subscriptions

- Report scale-out deployment

- Infinite clickthrough in ad-hoc reports

- Scale-out operational report configuration**

Data Warehousing

- Data compression**

- Star join query optimizations**

- Change data capture (CDC)**

- Scalable data marts and reporting**

Data Mining

- Parallelism for model processing & prediction

- Advanced Data Mining algorithms with algorithm plug-in API

- Advanced configuration and tuning options for data mining algorithms

- Time series capabilities**

** double-star denotes feature of SQL Server 2008

Introduction to New Data Warehouse Scalability Features in SQL Server 2008

Jason Morales' Microsoft BI Update

Mark Malakanov poorly maintained blog: July 2007

One workaround for the notoriously slow insert times from SSIS into Oracle.

SSIS 2005. How to avoid Autocommit when load into OLE DB destination.

An example with using of Oracle OLE DB driver on a destination side.

I have heard from my friend that he is experiencing very slow load into Oracle database. He used SSIS 2005. Which is funny, because Oracle Warehouse Builder is available. Anyway.
The cause of slowness of SSIS, when it loads into non-Microsoft OLE DB target, appeared to be the commit after every insert. Since the ‘Autocommit’ is set to be ‘ON’ by OLE DB standard, a driver propagates this mode to the database. The only way to turn it off, is to switch OLE DB datasource into a Transactional mode by issuing a BeginTransaction() call.

Mark Malakanov poorly maintained blog: July 2007

Error Handling in SSIS - SQL Server Central

Borrowing liberally from Jack Corbett's scripts will get you a great custom error handler.  No more creating and maintaining error tables for SSIS data flows!

Anyone who has used SSIS error handling has been frustrated that it returns an Error Code with no description. I decided that I needed a solution. Googling the problem led me to this blog by Jamie Thomson which had a link to a Script Component that gets the Error Description. Excellent! While this was I good start, I also wanted to see the data that caused the error. I started by including the data and outputting to a text file, but, being a database pro, this did not sit well with me. I started looking for a solution that would allow me to store the data in a table and be useable in each dataflow task, and, once again, the internet provided a solution, this article by jwelch on agilebi.com which I highly recommend reading.

My solution is uses the Input0_ProcessInputRow method and reflection, as shown by jwelch’s article, to loop through the columns, building an XML string (name-value pairs) which I insert into a table. I can then query the table to see the errors and the data. I can then verify that I fixed the errors from earlier loads.

Here is the solution:

Error Handling in SSIS - SQL Server Central

Call stored procedure inside SSIS Script task

Useful way to build custom data flow scripts, with increased performance benefits in Oracle, and access to other custom data providers and APIs.

I would like to call stored procedure inside Script task.
Any example out there?

Call stored procedure inside SSIS Script task

BI Thoughts and Theories : Address Columns Generically In a Script Component

Also useful when building a custom error script in SSIS.

Address Columns Generically In a Script Component

When writing script components in SSIS, it's often useful to be able to process the columns without knowing exactly what they are. For example, you may want to loop through all the columns to check for a conditional, like NULL values in the columns. Or you may want to take a set of columns and concatenate them into a single string for output, or output each one as an XML element. In any of these cases, you don't necessarily care about the individual column details.

One option is to use Reflection. This is fairly easy to set up, but is not the fastest performing option. To use Reflection, add this to the top of the Script class:

BI Thoughts and Theories : Address Columns Generically In a Script Component

BI Thoughts and Theories : XML Transformations Part 2

After a bit of frustrations, I got this to work with a SqlClient rather than an OLE DB client by parsing out the connection string and doing a string replace to remove the provider and other invalid information.

I'm using something similar to create a custom error handler that exports to an XML column.  No more error tables for each dimension table.

The script is very similar to the one from the previous article, though it is a bit simpler. The XML values are prepared by iterating through the Input Collection metadata, and using the Reflection classes to read the Row object dynamically. The resulting value is put into the XML column on the output.

The OLEDB Destination simply maps the XML column to an XML column on a database table.

This is a good alternate approach to the previous one, particularly if you need to send the XML to a database instead of a flat file.

BI Thoughts and Theories : XML Transformations Part 2

Magic Quadrant for Business Intelligence Platforms, 2008

Gartner has released the 2008 Magic Quadrant for BI Platforms.

Looks like BI has come of age, according to Gartner.  We have leveled off and are coasting rather than innovating.

Note that for the 2008 Magic Quadrant, given the maturity of the market, the Innovation criterion was not rated separately. Instead, it was factored into the Market Understanding and Offering (Product) Strategy criteria.

Magic Quadrant for Business Intelligence Platforms, 2008

SAS is the visionary leader, not much surprise here.  Microsoft and Cognos are the overall leaders.  Panorama, arcplan, and newcomer Board International are all niche players. 

Truly the last two years have been one of consolidation.  The next few years should see another round of innovation and new product offerings, if the cycles continue.

One interesting product that IBM returned to the mothership was Cognos.  It's technology that came out of IBM now returning to the fold.

The one player that's missing from this puzzle?  Google.

Companies such as Panorama are now promoting integration with Google technologies, though this article from 2006 seems to promote the idea of Google itself as a BI tool.  When I think of BI in terms of the quadrant I think of reporting rather than searching, so Google really doesn't fit into this space just yet - unless you want to count Google Analytics.

It will be interesting to see what the next year brings, with the launch of SQL 2008, PerformancePoint improvements, Microsoft's MDM tools, integration of Cognos and Applix with IBM software, and whatever happens with SAP and their BO.

My first pick would be an easy-to-use tool that brings relational, unstructured and multidimensional cube data together under one umbrella with point & click or easily maintainable scripts for ETL consolidation and data scrubbing, and a place to put planning numbers and napkin-style forecasts.  Preferably with some fancy charts, graphs, dashboards, and output to PDF, Powerpoint & Excel (and Visio?).  Without any installation or security hassles, java virtual machines, memory leaks, bloatware, or incompatibilities with other software.

What about using the OCZ NIA as an interface and the NVidia Tesla GPU for calculations?  Why not add a little e-Ink to help save some trees?

Too much to ask?

Microsoft Certified Master Product Overviews and Requirements

There are 3 new certifications out of Microsoft... the "Masters" series.

Microsoft Certified Master Product Overviews and Requirements

Published: June 10, 2008

IT professionals who hold Microsoft Certified Master technology-based certifications are recognized as holding the highest level of technical certification for Microsoft server products. Find out if you have what it takes to become a Microsoft Certified Master by reading the program overviews and prerequisites.

Microsoft Certified Master Product Overviews and Requirements

Deprecated Database Engine Features in SQL Server 2008

A few of the more common things that will probably break your SQl 2008 upgrade if you have not run the upgrade advisor for SQL 2005 & corrected them:

Database compatibility < 80

*= & =* joins

SET ROWCOUNT for INSERT, UPDATE, DELETE

RAISERROR syntax

More here...

Deprecated Database Engine Features in SQL Server 2008

Changes that may break SQL 2000 - 2005 upgrade.

http://msdn.microsoft.com/en-us/library/bb510680(SQL.100).aspx

Missing Date Ranges- the Sequel

Time intelligence - a missing component of SQL - is available with some scripts like these.

Missing Date Ranges- the Sequel

Introduction to the Transaction Log - SQL Server Central

Excellent article about transaction logs in SQL.  My best tip:  Always backup the transaction log and clear unused entries.  If your log hasn't been backed up before, back it up twice to be able to shrink it.  Never use the TRUNCATE command. It is much safer to use the backup facility to maintain the log.

Introduction to the Transaction Log - SQL Server Central

.NET HITMAN: Difference between LINQ to SQL and the Entity Framework

Some good scalability points on using LINQ in Enterprise applications.

So while there is a lot of overlap, LINQ to SQL is targeted more toward rapidly developing applications against your existing Microsoft SQL Server schema, while the Entity Framework provides object- and storage-layer access to Microsoft SQL Server and 3rd party databases through a loosely coupled, flexible mapping to existing relational schema.

.NET HITMAN: Difference between LINQ to SQL and the Entity Framework

Mark Malakanov poorly maintained blog: SSIS 2005. Fast load into non-MS OLE DB destination

Speed up SSIS to Oracle loads... without a 3rd-party component... but not quite as easily.

Great Article. This helped me speed up my Load. It is 8 times faster this way.

Mark Malakanov poorly maintained blog: SSIS 2005. Fast load into non-MS OLE DB destination

Are you only getting 1GB of RAM out of SQL? SQL Server memory configurations for procedure cache and buffer cache

One reason to switch to 64-bit SQL Server.

The 32-bit platform

The 32-bit platform (x86) has the least amount of buffer cache. Buffer cache cannot reside within the Address Windowing Extension (AWE)-controlled memory space; it has to be in the first 2 Gigs of RAM allocated to the SQL Server instance. On the 32-bit platform SQL Server 2000 and SQL Server 2005, both use the same base calculation to find the amount of procedure cache to use. SQL Server will use up to 1 GB of memory, or 50% of the memory, whichever is lower.

SQL Server memory configurations for procedure cache and buffer cache

Radim.NET - SQL SP3 TBA... in 2008?

Looks like Cumulative Update 8 just isn't cutting it.

After lot of demands from user community (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575) MS finally decided to create SP3 for SQL Server 2005. It will be shipped after official launch of SQL2008, but still we should see SP3 in 2008. That's a good news, because all Cumulative Updates (new CU7 was published two days ago http://support.microsoft.com/kb/949095/en-us) were not recommended to install and SP2 brought a lot of problems with it.

Radim.NET

OCZ NIA setup and help thread - OCZ Forum

Some good feedback so far.... I still want one of these!

This is by far the coolest thing since sliced bread, it takes some getting used to but I am getting better fast. I was having trouble at first with moving forward in UT3 and had to ask my GURU for help. The fix was easy, I clench my teeth a lot so he told me to lower the movement to 3.0 on the joystick 1 profile and wala I am unstoppable. OCZ ROCKS. The more I play the more I see how slow my keyboard is, I had to set my mouse sensitivity up so it could keep up with the advanced profile movements. 

OCZ NIA setup and help thread - OCZ Forum

OLAP PivotTable Extensions - Home

 

OLAP PivotTable Extensions is an Excel 2007 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel 2007 API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality. It can be launched from the following menu option in the right-click menu for PivotTables:

OLAP PivotTable Extensions - Home

Creating Connection Managers & You may be unable to execute SQL Server 2005 Integration Services packages that contain script tasks or script components & 64 bit

Setting the Project Properties to Run64BitRuntime = false resolves this.... and while scheduling you'll need to use the x86 version of dtexec.

Both versions of the provider have the same ID. To specify whether the Integration Services runtime uses an available 64-bit version of the provider, you set the Run64BitRuntime property of the Integration Services project. If the Run64BitRuntime property is set to true, the runtime finds and uses the 64-bit provider; if Run64BitRuntime is false, the runtime finds and uses the 32-bit provider. For more information about properties you can set on Integration Services projects, see Integration Services Considerations on 64-bit Computers and Integration Services in Business Intelligence Development Studio.

Creating Connection Managers

Poor Man's Proclarity

 

Day after day we are working with Cube Browser but when the project or cube designer has closed we lost our query, sometime it takes a long time to reorder again all our dimensions.

This add-in is giving us the ability to save personal views and run it as a new request.

I develop it for SSAS 2005 & 2008 (SSAS 2008 with VS 2008).

BeI - Microsoft Business Intelligence

Sync All Logins on a Server in a single click using SP_MSForEachDB - SQL Server Central

Sync'ing logins with Log Shipping.

One of the problems restoring a database to a new server is dealing with out of sync logins. The link between a database user name and a server user name is the SID that is generated when the login is added to SQL Server. There are stored procedures to sync the SID's but they have to be run for each user that is out of sync in each database. If you are bringing up a DR server that houses 60 log shipped databases, this can be a very tedious task.

Sync All Logins on a Server in a single click using SP_MSForEachDB - SQL Server Central

Eliminating Cursors - SQL Server Central

One way to eliminate cursors scraping across your tables like fingers on a chalkboard.

 

Eliminating Cursors - SQL Server Central

Show content differences between two tables/views - SQL Server Central

Useful script.

Show content differences between two tables/views

Show content differences between two tables/views - SQL Server Central

SharePoint Live Document Libraries

Softartisans Officewriter has some interesting products with SQL & Sharepoint integration.

Live Document Libraries

SharePoint Document Libraries are a convenient and increasingly popular way to share files across an organization. SharePoint adds powerful features, such as access control and versioning, on top of basic file availability. However, Document Libraries can only serve static documents. For example, if you wanted to store a spreadsheet with sales data, you would need to constantly edit the document to insure that the employees who download the file are getting the latest data. This limitation does not allow a company to take advantage of the fact that these documents are being stored on a server with ready access to the latest corporate data.

Publish data from:

SQL Server

ADO.NET

Business Data Catalog

OfficeWriter turns SharePoint Document Libraries into Live Document Libraries, service the most current data from any ADO.NET or Business Data Catalog data source. Every time a user requests the document, you can return the latest data.

SharePoint Live Document Libraries

Creating a Sequential Record Number field - SQLTeam.com

When does Something = Something = Something?  Only in SQL....

This little piece of code will run through a table and sequentially number the field you specify. The only drawback is that it will determine the order based on the physical order of the table.
declare @intCounter int
set @intCounter = 0
update Yaks
SET @intCounter = YakSequenceNumber = @intCounter + 1

Creating a Sequential Record Number field - SQLTeam.com

MDX | The Frog-Blog

 

Convert MDX fields to SQL

Saturday, December 15th, 2007

A number of our customers have reporting systems that use both MDX and SQL, retrieving data from both OLAP and SQL Server databases. This generates the problem of converting an MDX field ([Dimension].[Hierarchy].&[Attribute]) into SQL Server field value (Attribute). The following code is a Reporting Services custom code section that will rip off the MDX and leave you with the value.

    Public Function MDXParamToSQL(Parameter As String, All As String) As String

        Dim Val As String
        Val = Parameter

        If Val.Contains(”[”) Then
            If Val.ToLower().Contains(”].[all]”) Then
                Return All
            Else
                Val = Val.Substring(1, Val.LastIndexOf(”]”) - 1)
                Val = Val.Substring(Val.LastIndexOf(”[”) + 1)
                Return Val
            End If
        Else
            Return Val
        End If

    End Function

MDX | The Frog-Blog

Chris Webb's BI Blog - Why Linq to MDX isn't worth the ROI

In my opinion, MDX is a complex query language that isn't intuitive, and most people I work with cringe when they need to write an MDX query.  Performance is unpredictable and it is really easy to shoot yourself in the foot.  Service packs with Analysis Services make a difference in both performance and results of a query.

If you disagree with the above statement, see Mosha's latest entry on Subselects, SCOPEs, and hierarchy navigation MDX functions.

With standardized T-SQL, and the query plan generator or plan explanation tools in modern databases, it is much easier to build and adjust SQL code for performance and maintainability.  SQL, though, doesn't have the time intelligence and other BI features that make Analysis Services shine.  Sure, you can hack them in there, but relational databases aren't really designed to answer the same questions as cubes.

Code developers seem to be of a different mindset than SQL DBAs.   Rather than using stored procedures, many people unfamiliar with databases prefer to use the 'dynamic SQL' approach that is causing so much of the SQL injection problems of today.  Maintaining classes with SQL strings is about as much fun as writing MDX.  If you're going to do something like that, at least put them outside your compiled assemblies.

So what's the solution for both of these?  SQL has tons of tools that remove the dynamic SQL approach from the hands of developers and put it into automated ORM generation and CRUD generation frameworks.  Tools for offline code generation, like MyGeneration and CodeSmith.  Tools for "on-the-fly" generation, like NHibernate, and LINQ.  What does MDX have?

Excel.

And LINQ to MDX still looks like it's pretty far off.

Seeing that Marco Russo has released his book "Programming Microsoft LINQ" reminded me of a conversation I had with him a while ago about something I've heard various people ask about over the last year - will there be a LINQ to MDX?

Chris Webb's BI Blog

What happened to Natural Language Query? Training queries so that Business Analysts could access their data with "friendly" terminology seemed like a good idea at the time...

Unfortunately, (or fortunately, depending on if you were the one doing the vocabulary training) it went the way of the Dodo.

This blog posting just about sums it up.

Even if I could talk to my computer (an idea that's never particularly appealed to me, this Mac is supposed to be able to do it but I've never turned it on), would I want to speak to it in full sentences stuffed with subordinate clauses and prepositional phrases? I think I'd want to grunt things like “Yahoo, Berlin weather” or “break line 238” or “spam!”.

http://www.tbray.org/ongoing/When/200x/2003/05/16/NLQuery

Grunting out a LINQ select statement.

public void Linq7() {
    List products = GetProductList();
    var productNames =
        from p in products
        select p.ProductName;
    Console.WriteLine("Product Names:");
    foreach (var productName in productNames) {
        Console.WriteLine(productName);
    }
}

Grunting out a SQL statement.

SELECT * FROM Product

Grunting out an MDX Statement

SELECT [Product].[Product].members on 0

FROM $Product

Grunting out an MS Access select statement.

Double-click on the table dummy!

I still prefer the SQL approach, since double-click doesn't seem to scale as well. :)

How about Dynamic SQL in Cobol?  It's all Portugese to me...

         1         2         3         4         5         6         7   
123456789012345678901234567890123456789012345678901234567890123456789012

000001*----------------- I N I C I O   D O   C O D I G O --------------*
000002 IDENTIFICATION DIVISION.                                         
000003*-----------------------
000004 PROGRAM-ID.     SELECT01.                                         
000005
000006* Sistema      : EXEMPLO                      
000007* Programa     : SELECT01                                           
000008* Objetivo     : Listar os dados da CONTA corrente    
000009* Analista     : CARLOS ALBERTO DORNELLES                                                   
000010* Desenvolvedor: CARLOS ALBERTO DORNELLES                           
000011* Data         : 31/12/2002                                         
000012* Linguagem    : COBOL / DB2 / CICS                                 
000013* Manutencoes  :                                                    
000014*----------------------------------------------------------------*
000015* Desenvolvedor              Responsavel                   Data   
000016* -------------              -----------                   ---- 
000017*
000018* xxxxxxxxxxxxxxxxxxxxxxxxx  xxxxxxxxxxxxxxxxxxxxxxxxxx xx/xx/xxxx
000019* descrição xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
000020*----------------------------------------------------------------*
000000
000021 ENVIRONMENT DIVISION.                                            
000022*---------------------
000023 CONFIGURATION SECTION.                                           
000024*---------------------
000025 SPECIAL-NAMES. 
000026     DECIMAL-POINT IS COMMA.                           
000027
000028 DATA DIVISION.                                                   
000029*-------------
000030 WORKING-STORAGE SECTION.                                         
000031*-----------------------
000032 77  WS-SQLCODE-EDT              PIC  ----9.                      
000033 01  AREAS-DE-TRABALHO.                                           
000034     03 WS-TAM                   PIC  9(005).                     
000039 01  WS-VARIAVEIS-DB2.                                            
000040     03 WS-GR-CURSOR.                                             
000041        49 WS-ID-TAMANHO-CURSOR  PIC S9(004) COMP   VALUE ZEROES. 
000042        49 WS-DE-CURSOR          PIC  X(2000)       VALUE SPACES. 
000035 01  WS-AREA-ENTRADA.                                               
000039        05 PRM-NU-CONTA-E        PIC  9(004).                     
000035 01  WS-AREA-SAIDA.                                               
000036     03 PRM-QTDE-CONTA           PIC  9(004).                     
000037     03 PRM-ARRAY-SAIDA          OCCURS 1 TO 100 TIMES            
000038                                 DEPENDING ON PRM-QTDE-CONTA.                  
000039        05 PRM-NU-CONTA          PIC  9(004).                     
000040        05 PRM-NO-CONTA          PIC  X(040).                     
000040        05 PRM-NO-ENDERECO       PIC  X(070).                     
000040        05 PRM-NO-CIDADE         PIC  X(050).                     
000039 01  WS-AREA-ERROS.                                               
000040     03 PRM-QTDE-ERROS           PIC  9(003).                     
000041     03 PRM-ARRAY-ERROS          OCCURS 1 TO 094 TIMES            
000042                                 DEPENDING ON PRM-QTDE-ERROS.           
000043        05 PRM-NUMERO-MENSAGEM   PIC  X(004).                     
000044        05 PRM-PROGRAMA          PIC  X(008).                     
000045        05 PRM-INFORMACOES       PIC  X(200).   
000046
000047*              Definicao de tabelas e areas na DCLGEN            *
000048*----------------------------------------------------------------*
000049     EXEC SQL INCLUDE SQLCA      END-EXEC.                        
000050     EXEC SQL INCLUDE TABELA01   END-EXEC. 
000051
000052 LINKAGE SECTION.                                                 
000053*---------------
000054 01  DFHCOMMAREA.                                                 
000055     03 LKS-EXCECAO.                                               
000056        05 LKS-ERRO-CICS         PIC  9(003).                     
000057        05 LKS-NU-MENSAGEM       PIC  9(004).                     
000058        05 LKS-NO-MENSAGEM       PIC  X(078).                     
000059        05 LKS-NU-SQLCODE        PIC  9(004).                     
000060     03 LKS-IDENTIFICACAO.                                         
000061        05 LKS-IN-NOME-PGM       PIC  X(008).                     
000062        05 LKS-IN-CO-USUARIO     PIC  X(008).                     
000063        05 LKS-IN-CO-FUNCAO      PIC  X(002).                     
000064     03 LKS-ENTRADA-SAIDA.                                         
000065        05 LKS-CONTEUDO-TAM      PIC  9(005).                     
000066        05 LKS-CONTEUDO.                                           
000067           07 FILLER             PIC  X(001) OCCURS 1 TO 20000    
000068                                 DEPENDING ON LKS-CONTEUDO-TAM.    
000069
000070 PROCEDURE DIVISION USING DFHCOMMAREA.                            
000071*------------------------------------
000000
000072     PERFORM R000-PROCED-INICIAIS      THRU R000-FIM.                
000073     PERFORM R100-PROCED-PRINCIPAIS    THRU R100-FIM.                
000074     PERFORM R999-PROCEDIMENTOS-FINAIS THRU P999-FIM.                
000075
000076 R000-PROCED-INICIAIS.                                            
000077*--------------------
000000
000105     MOVE LK-CONTEUDO(1:LK-CONTEUDO-TAM) TO WS-AREA-ENTRADA.      
000078     INITIALIZE     LKS-EXCECAO.                
000079     MOVE SPACES TO LKS-CONTEUDO(1:20000).      
000080     MOVE ZEROES TO LKS-CONTEUDO-TAM            
000081                    PRM-QTDE-CONTA           
000082                    PRM-QTDE-ERROS.            
000083 R000-FIM.                                                        
000084     EXIT.                                                        
000085
000086 R100-PROCED-PRINCIPAIS.                                          
000087*----------------------
000000
000088     PERFORM R200-ABRE-CONTA THRU R200-FIM.             
000089     PERFORM R210-LE-CONTA   THRU R210-FIM.             
000090     IF SQLCODE EQUAL +100                                            
000091        MOVE 1       TO LKS-ERRO-CICS                       
000092        ADD  1       TO PRM-QTDE-ERROS                     
000093        MOVE SPACES  TO PRM-INFORMACOES (PRM-QTDE-ERROS)
000094        MOVE SQLCODE TO LKS-NU-SQLCODE                      
000095        MOVE SQLCODE TO WS-SQLCODE-EDT                      
000096        STRING 'Nenhum registro encontrado na tabela TABELA01.'                  
000102                     DELIMITED BY SIZE                                
000103                               INTO PRM-INFORMACOES (PRM-QTDE-ERROS)
              END-STRING
000104        MOVE '0001'     TO PRM-NUMERO-MENSAGEM (PRM-QTDE-ERROS)
000105        MOVE 'SELECT01' TO PRM-PROGRAMA (PRM-QTDE-ERROS)
000106        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000107     END-IF.                                                      
000108     PERFORM UNTIL SQLCODE = +100                                 
000109             PERFORM R220-MONTA-CONTA THRU R220-FIM              
000110             PERFORM R210-LE-CONTA    THRU R210-FIM              
000111             IF PRM-QTDE-CONTA = 100                                 
000112                MOVE +100 TO SQLCODE                                   
000113             END-IF                                                    
000114     END-PERFORM.                                                 
000115     PERFORM R230-FECHA-CONTA THRU R230-FIM.             
000116     MOVE LENGTH OF WS-AREA-SAIDA TO LKS-CONTEUDO-TAM.                                
000118     MOVE WS-AREA-SAIDA TO LKS-CONTEUDO (1:LKS-CONTEUDO-TAM).                
000119                                    
000120 R100-FIM.                                                        
000121     EXIT.                                                        
000122
000123 R200-ABRE-CONTA.                                               
000124*---------------
000000
000136     INITIALIZE WS-GR-CURSOR.                                    
000137     MOVE 1 TO WS-ID-TAMANHO-CURSOR. 
000000     MOVE PRM-NU-CONTA-E TO NU-CONTA.                            
000138                                                                         
000139     STRING                                                      
000140        'SELECT NU_CONTA , NO_CONTA , NO_ENDERECO , NO_CIDADE '
000000        'FROM DCL.TABELA01_CONTA '                 
000141                DELIMITED BY SIZE INTO WS-DE-CURSOR                
000142                WITH POINTER WS-ID-TAMANHO-CURSOR
000000     END-STRING.
000000
000000     IF NU-CONTA NOT EQUAL ZEROES
000139        STRING                                                      
000140           'WHERE NU_CONTA >= ' :NU-CONTA 
000141                   DELIMITED BY SIZE INTO WS-DE-CURSOR                
000142                   WITH POINTER WS-ID-TAMANHO-CURSOR
000000        END-STRING
000000     END-IF.
000000       
000225     EXEC SQL                                                     
000226          PREPARE CONSULTA FROM :WS-GR-CURSOR                         
000227     END-EXEC.                                                     
000228                                                                         
000229     IF SQLCODE NOT EQUAL +0                                          
000230        MOVE 1 TO LK-ERRO-CICS                                    
000231        MOVE SQLCODE TO LK-NU-SQLCODE                             
000232        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000233     END-IF.                                                       
000234                                                                         
000235     EXEC SQL                                                     
000236          DECLARE CUR001 CURSOR FOR CONSULTA                          
000237     END-EXEC.                                                    
000238                                                                         
000239     IF SQLCODE NOT EQUAL +0                                          
000240        MOVE 1 TO LK-ERRO-CICS                                    
000241        MOVE SQLCODE TO LK-NU-SQLCODE                             
000242        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000243     END-IF                                                       
000244                                                                         
000245     EXEC SQL                                                     
000246          OPEN CUR001                                                 
000247     END-EXEC.                                                    
000248                                                                         
000133     IF SQLCODE NOT EQUAL +0                                          
000134        MOVE 1       TO LKS-ERRO-CICS                       
000135        ADD  1       TO PRM-QTDE-ERROS                     
000136        MOVE SPACES  TO PRM-INFORMACOES (PRM-QTDE-ERROS)
000137        MOVE SQLCODE TO LKS-NU-SQLCODE                      
000138        MOVE SQLCODE TO WS-SQLCODE-EDT                      
000139        STRING 'Erro de acesso a base de dados. SQLCODE: '                  
000140                WS-SQLCODE-EDT ' ErrMc: ' SQLERRMC                                      
000143               ' - Tabela utilizada -> TABELA01'                        
000145                DELIMITED BY SIZE                                
000146                          INTO PRM-INFORMACOES (PRM-QTDE-ERROS)
000147                MOVE '0001'     TO PRM-NUMERO-MENSAGEM (PRM-QTDE-ERROS)
000148                MOVE 'SELECT01' TO PRM-PROGRAMA (PRM-QTDE-ERROS)
000149                PERFORM R999-PROCEDIMENTOS-FINAIS   
              END-STRING                             
000150     END-IF.
000000                                                      
000151 R200-FIM.                                                        
000152     EXIT.                                                        
000153
000154 R210-LE-CONTA.                                                 
000155*-------------
000000
000156     EXEC SQL                                                     
000157          FETCH  CUR001                                                    
000158          INTO  :NU-CONTA                                              
000159          ,     :NO-CONTA                                              
000159          ,     :NO-ENDERECO                                           
000159          ,     :NO-CIDADE                                             
000160     END-EXEC.  				  
000000                                                  
000161     IF SQLCODE NOT EQUAL +0 AND +100                                 
000162        MOVE 1       TO LKS-ERRO-CICS                       
000163        ADD  1       TO PRM-QTDE-ERROS                     
000164        MOVE SPACES  TO PRM-INFORMACOES (PRM-QTDE-ERROS)
000165        MOVE SQLCODE TO LKS-NU-SQLCODE                      
000166        MOVE SQLCODE TO WS-SQLCODE-EDT                      
000167        STRING 'Erro de acesso a base. SQLCODE: '                  
000168                WS-SQLCODE-EDT ' ErrMc: ' SQLERRMC                                     
000171               ' - Tabela utilizada -> TABELA01'                          
000173                DELIMITED BY SIZE                                
000174                          INTO PRM-INFORMACOES (PRM-QTDE-ERROS)
              END-STRING
000175        MOVE '0002'     TO PRM-NUMERO-MENSAGEM (PRM-QTDE-ERROS)
000176        MOVE 'SELECT01' TO PRM-PROGRAMA (PRM-QTDE-ERROS)
000177        PERFORM R230-FECHA-CONTA THRU R230-FIM               
000178        PERFORM R999-PROCEDIMENTOS-FINAIS                                
000179     END-IF.  
000000                                                    
000180 R210-FIM.                                                        
000181     EXIT.                                                        
000182
000183 R220-MONTA-CONTA.                                              
000184*----------------
000000
000185     ADD  1 TO PRM-QTDE-CONTA.                                  
000186     MOVE NU-CONTA    TO PRM-NU-CONTA    (PRM-QTDE-CONTA).        
000187     MOVE NO-CONTA    TO PRM-NO-CONTA    (PRM-QTDE-CONTA). 
000186     MOVE NO-ENDERECO TO PRM-NO-ENDERECO (PRM-QTDE-CONTA).        
000187     MOVE NO-CIDADE   TO PRM-NO-CIDADE   (PRM-QTDE-CONTA). 
000000
000188 R220-FIM.                                                        
000189     EXIT.                                                        
000190
000191 R230-FECHA-CONTA.                                              
000192*----------------
000000
000193     EXEC SQL                                                     
000194          CLOSE CUR001                                                
000195     END-EXEC.   			 
000000                                                 
000196 R230-FIM.                                                        
000197     EXIT.                                                        
000198
000199 R999-PROCEDIMENTOS-FINAIS.                                              
000200*-------------------------
000000
000201     IF LKS-ERRO-CICS = 1                                          
000202        MOVE LENGTH OF WS-AREA-ERROS TO LKS-CONTEUDO-TAM           
000203        MOVE WS-AREA-ERROS TO LKS-CONTEUDO (1:LKS-CONTEUDO-TAM)              
000205     END-IF.                                                      
000206     EXEC CICS                                                    
000207          RETURN                                                    
000208     END-EXEC.  
000000                                                  
000209 P999-FIM.                                                        
000210     EXIT.                                                        
000211*----------------- F I M   D O   C O D I G O --------------------*

Microsoft Dynamics CRM UK Blog : Resizing A Virtual PC Hard Drive

 

Resizing A Virtual PC Hard Drive

Holy Diver...

The other day I was working on one of my Virtual PC demo images, when I noticed I was running out of disk space. When I originally created the Virtual Hard Drive (.VHD) I just accepted the default size of 16GB without thinking, but more and more often I find I need to install other products such as SharePoint Server, BizTalk Server, PerformancePoint Server, which inevitably require extra disk space.

Microsoft Dynamics CRM UK Blog : Resizing A Virtual PC Hard Drive

SQL Excel freeware add-in - Home

SQL Excel is just that, a simpler and easier way to get SQL results into Excel directly from SQL.

Seems really promising...

May 21st - new much improved version is available (compatible and tested on Excel 2000, 2002/XP, 2003 and 2007).    This is an Excel for Windows add-in so it wont work on a mac

SQL Excel freeware add-in - Home

Windows XP (SP2): 3 ways to optimize performance with NTFS

Could be useful for optimizing SQL bootup times.  Note that a large number of databases will also increase bootup times... be sure to detach databases not in use and keep sizes as small as possible.

Microsoft Bootvis is a good tool for troubleshooting bootup times.

Resize the Master File Table and prevent fragmentation
The Master File Table (MFT) contains specific information about each folder and file on your hard disk (such as date of creation, parameters etc.). To prevent fragmentation, the NTFS reserves exactly 12.5% of your hard disk capacity for the MFT. However, when your free disk space is low, XP writes those files directly into the MFT. Additionally, small files are automatically stored in the MFT. Both factors lead to a certain degree of fragmentation which is why we recommend to increase the MFT:

Windows XP (SP2): 3 ways to optimize performance with NTFS

Quick and Dirty SSIS Variable Export

If you have a lot of variables in your SSIS package and you want to document the contents, one way is to do this:

1. Create a package configuration

2. Enter a name for the config file (variables.xml)

3. Select the variables to include.

4. Click OK to create the config file.

This XML can then be opened in Excel 2007 as an XML table.

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays - SQL Server Central

Another few uses for the numbers table, and a new SQL 2005 CTE example without a numbers table.
http://www.sqlservercentral.com/articles/T-SQL/63003/

SSIS Trick - Setting multiple variables at once

There are custom components out there that set a single variable for you.  You can also set variables using script tasks. 

One way I found to set multiple variables in a package at the same time is to execute a 'fake' sql command. 

There is a tiny performance hit, however this could also be considered a way to include 'profiling' of your app variables with SQL Profiler.

1. Drag a SQL Command Task into the package.
2. Set the command connection.  Set the command text to SELECT 'Myvalue' as MyVariableValue, 'myvalue2' as My2ndVariableValue

Notice no FROM statement.  This also works for other databases, using DUMMY tables.
3. Set the resultset to 'Single Resultset'
4. Set the Results tab to map the variables to the select statement results.

Test Run: Testing SQL Stored Procedures Using LINQ

Using LINQ to create unit tests for SQL.

Test Run

Testing SQL Stored Procedures Using LINQ

Dr. James McCaffrey

Test Run: Testing SQL Stored Procedures Using LINQ

Kimberly L. Tripp: Improving *my* SQL skills through your questions! http://www.SQLskills.com/blogs/kimberly

 

If you add more than 1GB then you'll add 16VLFs. In general, most transaction logs will only have 20 or 30 VLFs - even 50 could be reasonable depending on the total size of the transaction log. However, in many cases what happens is that excessive autogrowths can cause an excessive number of VLFs to be added - sometimes resulting in hundreds of VLFs. Having an excessive number of VLFs can negatively impact all transaction log related activities and you may even see degradation in performance when transaction log backups occur. To see how many VLFs you have solely look at the number of rows returned by DBCC LOGINFO. The number of rows returned equals the number of VLFs your transaction log file has. If you have more than 50, I would recommend fixing it and adjusting your autogrowth so that it doesn't occur as fequently. To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log:

Kimberly L. Tripp: Improving *my* SQL skills through your questions! http://www.SQLskills.com/blogs/kimberly

Andy Leonard : SSIS Design Pattern - Incremental Loads

Incremental loads without a CDC tool.

SSIS Design Pattern - Incremental Loads

Introduction

Loading data from a data source to SQL Server is a common task. It's used in Data Warehousing, but increasingly data is being staged in SQL Server for non-Business-Intelligence purposes.

Andy Leonard : SSIS Design Pattern - Incremental Loads

Steve Fibich : Passing a values back from a child package to a parent package in SSIS

 

I found this process very useful to pass metadata about versions of data imported into a master data management system from a child package back to a parent package.  You can pass any data types from a child to a parent using this method.  Normally I put the script that passes the value from the child to parent package in the post execute event handler.  The only reason I put the task in the event handler is for style, as I feel it’s better to put this variable handling code separate from the specific package logic itself.

Steve Fibich : Passing a values back from a child package to a parent package in SSIS

Andy Leonard : SSIS Design Pattern - Read a DataSet From Variable In a Script Task

Andy has more SSIS goodness.

This script uses an OLEDbDataAdapter (oleDA) to fill a DataTable (dt) with the contents of the dsVar SSIS package variable, then iterates each row and column to build a string containing the data in the row. It then pops up a messagebox for each row displaying the row's contents before moving to the next row.

Andy Leonard : SSIS Design Pattern - Read a DataSet From Variable In a Script Task

Andy Leonard : Introducing Change Data Capture, SSIS, and SQL Server 2008 CTP5 (Nov 2007)

I'm currently working with an AS/400-based CDC system.  MS has jumped into the pond with built-in CDC in 2008.

Introducing Change Data Capture, SSIS, and SQL Server 2008 CTP5 (Nov 2007)

Introduction

On Thursday, 24 Jan 2008, I presented New Features In SSIS 2008 to the Richmond SQL Server Users Group.

Most of the presentation was dedicated to demonstrating Change Data Capture (CDC) interacting with SQL Server 2008 Integration Services. I started seriously working on this demo the first week of January, thinking I'd put 2 - 6 hours into it to get it running using the detailed instructions in Books Online. Things were going relatively well working through the demo until I hit calls from SSIS to table-valued functions created by CDC.

Andy Leonard : Introducing Change Data Capture, SSIS, and SQL Server 2008 CTP5 (Nov 2007)

Multiple NULL values in a Unique index in SQL Server/DB2 LUW « Systems Engineering and RDBMS

 

Yesterday, when helping out a friend who was working on a project that required porting an application from Oracle v9.2.0.5 to SQL Server 2005, he ran into the same UNIQUE index issue as we had blogged before. Since that was a major requirement by the client, this project needed to support having multiple NULL values in the column and still have a UNIQUE constraint. That is allowed by Oracle but not in SQL Server and DB2 LUW. There is a way to make this work in SQL Server and DB2 LUW also but that requires a work-around. Consider this table:

Multiple NULL values in a Unique index in SQL Server/DB2 LUW « Systems Engineering and RDBMS