SQLsaturday #160

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Sep 22 2012 at Kalamazoo Valley Community College, 6767 West O Avenue, Kalamazoo Township, MI 49009.

Admittance to this event is free, but we do charge a lunch fee of 10.00 so that we can provide a lunch - not pizza! Please register soon as seating is limited, and let friends and colleages know about the event.

My Schedule: Build you own schedule HERE

8:00 AM Gathering Tchotchkes as fast as possible. (Reminder: Get your PASS bag first or wear cargo pants.)

 8:30 AM Opening Ceremonies

09:00 AM Brent Ozar

Building Faster SQL Servers

SQL Server speed boils down to how fast we can get data in and out of storage. Sooner or later, we're going to run out of memory to cache. In this session, we take a lesson from Microsoft's blazing-fast Fast Track Reference Architectures, look at why they work so well for the wildest queries, and explain how to build our own SQL Servers for quick queries. Brent Ozar shows you how to test your own servers and compares them to servers he's worked with in the field.

10:15 AM Luke Jian

Anatomy of a Join

A lot of database professionals today do not have a Computer-Science Degree so they never attended a “Introduction to Databases” course. In this session we are trying to fix at least one thing and we’ll look in depth at the three types of join operations, visualize how their algorithms work in order to understand how query plans are computed, explain why these join operations have very different performance characteristics and why the optimizer chooses a specific join operator to use in a query plan. Will demonstrate that empirical cost calculations are similar to what Query Optimizer actually outputs. Good understanding of join algorithms is vital in order to diagnose and remedy issues related to bad query plans.

11:30 AM - 1:30 Sweeping floors

01:30 PM Rob Kerr

BI1 Big Data Analytics - Hadoop and Microsoft BI

This session is a deep dive that will show you how to use the Hadoop platform to extend traditional Microsoft BI solutions. The first half of the session covers big data architectures, the MapReduce process and strategies for integration of Hadoop data with traditional BI tools. The second half of the session demonstrates how to combine Hadoop, SQL Server Analysis Services, Power View and SharePoint 2010 into an end-to-end solution.

02:45 PM Benjamin Nevarez

Dive into the Query Optimizer-Undocumented Insight

This 400 (or maybe 500) level session will focus on using undocumented statements and trace flags to get insight into how the query optimizer works and show you which operations it performs during query optimization. I will use these undocumented features to explain what the query optimizer does from the moment a query is submitted to SQL Server until an execution plan is generated including operations like parsing, binding, simplification, trivial plan, and full optimization. Concepts like transformation rules, the memo structure, how the query optimizer generates possible alternative execution plans, and how the best alternative is chosen based on those costs will be explained as well.

4:00 PM Norman Kelm

PowerShell, the New SQL Hammer

You've seen all the amazing scripts that use PowerShell, but writing your own is raising more questions. This session will help fill in the gaps by explaining all the moving parts of PowerShell 2.0, the integration with SQL Server and answer the following questions as well as others. Why is Invoke-Sqlcmd necessary? What is and why is there a Minishell for SQL Server? What makes the SQLSERVER: PS Drive so powerful? How does a remote SQL Server get added to the SQLSERVER: PS Drive?

5:15 PM Closing and Raffle

How to: MSSQL 2008 (R2) Edition Upgrade

How to: MSSQL 2008 (R2) Edition Upgrade

by Paul Olson

To upgrade to a higher edition of SQL Server 2008 (R2)  1.Insert the SQL Server installation media. From the root folder, double-click setup.exe or launch the SQL Server Installation Center from Configuration Tools. To install from a network share, locate the root folder on the share, and then double-click Setup.exe. 2.To upgrade an existing instance of SQL Server 2008 to a different edition, from the SQL Server Installation Center click Maintenance, and then select Edition Upgrade. 3.If Setup support files are required, SQL Server Setup installs them. If you are instructed to restart your computer, restart before you continue. 4.The System Configuration Checker runs a discovery operation on your computer. To continue, click OK. 5.On the Product Key page, select a radio button to indicate whether you are upgrading to a free edition of SQL Server, or whether you have a PID key for a production version of the product. For more information, see Editions and Components of SQL Server 2008 and Version and Edition Upgrades. 6.On the License Terms page, read the license agreement, and then select the check box to accept the licensing terms and conditions. To continue, click Next. To end Setup, click Cancel. 7.On the Select Instance page, specify the instance of SQL Server to upgrade. 8.The Edition Upgrade Rules page validates your computer configuration before the edition upgrade operation begins. 9.The Ready to Upgrade Edition page shows a tree view of installation options that were specified during Setup. To continue, click Upgrade. 10.During the edition upgrade process, the services need to be restarted to pick up the new setting. After edition upgrade, the Complete page provides a link to the summary log file for the edition upgrade. To close the wizard, click Close. 11.After installation, the Complete page provides a link to the summary log file for the installation and other important notes. To complete the SQL Server installation process, click Close. 12.If you are instructed to restart the computer, do so now. It is important to read the message from the Installation Wizard when you are done with Setup. For information about Setup log files, see How to: View and Read SQL Server Setup Log Files. MSDN source: http://msdn.microsoft.com/en-us/library/cc707783.aspx

SQL Prime Philosophy

SQL Prime Philosophy

by Paul Olson

The SQL Prime philosophy is defined as a concept of high performance supported by an expanding knowledge that embraces the concept of the unity of the SQL structure and function. These are the four major principles of SQL Prime: 1. The SQL Server is a unit, an integrated unit of Resource, Design, and Function. 2. SQL possesses self-regulatory mechanisms, having the capacity to defend, repair, and optimize itself. 3. Structure and function are reciprocally inter-related. 4. Rational design is based on consideration of the first three principles. These principles are not held by dba’s to be empirical laws; they serve, rather, as the underpinnings of the philosophy on SQL Server administration and design.

The SQL Prime philosophy is defined as a concept of high performance supported by an expanding knowledge that embraces the concept of the unity of the SQL structure and function. These are the four major principles of SQL Prime: 1. The SQL Server is a unit, an integrated unit of Resource, Design, and Function. 2. SQL possesses self-regulatory mechanisms, having the capacity to defend, repair, and optimize itself. 3. Structure and function are reciprocally inter-related. 4. Rational design is based on consideration of the first three principles. These principles are not held by dba’s to be empirical laws; they serve, rather, as the underpinnings of the philosophy on SQL Server administration and design.

The SQL Prime philosophy is defined as a concept of high performance supported by an expanding knowledge that embraces the concept of the unity of the SQL structure and function.

These are the four major principles of SQL Prime:

1. The SQL Server is a unit, an integrated unit of Resource, Design, and Function.

2. SQL possesses self-regulatory mechanisms, having the capacity to defend, repair, and optimize itself.

3. Structure and function are reciprocally inter-related.

4. Rational design is based on consideration of the first three principles.

These principles are not held by dba’s to be empirical laws; they serve, rather, as the underpinnings of the philosophy on SQL Server administration and design.

SSRS: Dynamic Columns

SSRS: Dynamic Columns

Not knowing how many columns will be returned for any given report at runtime is a problem. At least you would think! Easy approach, make the column names dynamic!

Most of the time we lay out our reports with static columns and populate them with returned values. In this case we are populating the column names with returned values. The Matrix lay out is the the way to go because of the physical column row setup.

 In this case we are using QuestionDisplay as the column header and PrimaryAnswer as the value in the row for that column. The report will populate the number of columns based on the number of values returned for QuestionDisplay. We will then use SELECT DISTICT to manage this sprawl.

The DataSet Query:

SELECT

DISTINCT

vw_SurveySummary

.SurveyID

,

vw_SurveySummary.SurveyName

,

vw_SurveySummary.SectionOrder

,

vw_SurveySummary.SectionName

,

vw_SurveySummary.QuestionNumber

,

vw_SurveySummary.QuestionSubNumber

,

vw_SurveySummary.QuestionDisplay

,

vw_SurveySummary.QuestionRank

,

vw_SurveySummary.QuestionSummary

,

vw_SurveySummary.QuestionMultiAnswer

,

ISNULL(vw_AnswerDetail.UnitCode,'') AS UnitCode

,

ISNULL(vw_AnswerDetail.PrimaryAnswer,'') AS PrimaryAnswer

FROM

ICQsurvey2.dbo.vw_SurveySummary vw_SurveySummary

LEFT

OUTER JOIN ICQsurvey2.dbo.vw_AnswerDetail vw_AnswerDetail ON vw_SurveySummary.QuestionID = vw_AnswerDetail.QuestionID

WHERE

SurveyName='2011 ICQ' AND QuestionNumber IS NOT NULL

Results Returned:

Creating the Report:

I typically use the report wizard as a starting place for a new report.

The grouping will make all the differance here, follow these guidlines:

• Group the row by your Filter, UnitCode in this example.
• Group your Column by your header value, Display Question.

         
Results:
The values for your row under each column will coordinate to the line value that was returned.

Change Owner of an Object in SQL Server

So I have this custom app that was built by an unknown a few years ago and it is mission critical! Oh and great news; it assigns ownership to db objects with create login! Now a new user needs to have the account turned over to them and nobody knows who supports this app. I figured it out but it took some digging and it turns out that it is as simple as changing object ownership. Now, who has a valid script to do this in MSSQL 2000?  There are many scripts out there to change the object owner for MSSQL. I have found issues with each one that I have tried so I cleaned up some scripts for MSSQL 2005-2008 and 2000 and posted them here:

CREATE SP - Exchange Object Owner

IF exists (select * from sysobjects where id = object_id(N'[dbo].[sp_ExchangeOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_ExchangeOwner]

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

CREATE proc sp_ExchangeOwner( @usrName varchar(20), @newUsrName varchar(50))

as

-- @usrName is the current user

-- @NewUsrName is the new user

set nocount on

declare @uid int                   -- UID of the user

declare @objName varchar(50)       -- Object name owned by user

declare @currObjName varchar(50)   -- Checks for existing object owned by new user

declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'

set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static

for

select name from sysobjects where uid = @uid

open chObjOwnerCur

if @@cursor_rows = 0

begin

  print 'Error: No objects owned by ' + @usrName

  close chObjOwnerCur

  deallocate chObjOwnerCur

  return 1

end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0

begin

  set @currObjName = @newUsrName + "." + @objName

  if (object_id(@currObjName) > 0)

    print 'WARNING *** ' + @currObjName + ' already exists ***'

  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"

  print @outStr

  print 'go'

  fetch next from chObjOwnerCur into @objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

---

MSSQL 2000 Version

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_ExchangeOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_ExchangeOwner]

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

CREATE proc sp_ExchangeOwner( @usrName varchar(20), @newUsrName varchar(50))

as

-- @usrName is the current user

-- @newUsrName is the new user

set nocount on

declare @uid int                   -- UID of the user

declare @objName varchar(50)       -- Object name owned by user

declare @currObjName varchar(50)   -- Checks for existing object owned by new user

declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'

set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static for

select name from sysobjects where uid = @uid

open chObjOwnerCur

if @@cursor_rows = 0

begin

  print 'Error: No objects owned by ' + @usrName

  close chObjOwnerCur

  deallocate chObjOwnerCur

  return 1

end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0

begin

  set @currObjName = @newUsrName + "." + @objName

  if (object_id(@currObjName) > 0)

    print 'WARNING *** ' + @currObjName + ' already exists ***'

  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"

  print @outStr

  print 'go'

  fetch next from chObjOwnerCur into @objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

SSIS hates varchar(max) Error: 0xC002F309

  While attempting to populate a 
  SSIS string variable with the result 
  set from a stored procedure that 
  returns a varchar(max) value, 
  you will get this error:

Error: 0xC002F309 at GenerateVariableXML, Execute SQL Task: An error occurred while assigning a value to variable "xml_clob": "The type of the value being assigned to variable "User::xml_clob" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object

-----------------------------------------------------------------------------

Result syntax from stored procedure:

SET @ReturnXML=@v_xml

SELECT CAST(@ReturnXML AS Varchar(max)) as ReturnXML

-----------------------------------------------------------------------------

PROBLEM: Who knows? Some speculate that the max length of the string variable is 4000 however I have disproved this by dumping 15000 characters into a string variable. I think it has more to do with the fact that Varchar(max)) uses the normal data pages until the content actually fills 8k of data. When overflow happens, data is stored as old TEXT, IMAGE and a pointer is replacing the old content. SSIS must have an issue operating with that possibility.

-----------------------------------------------------------------------------

SOLUTION: Change your casting data type to Varchar(8000):

SET @ReturnXML=@v_xml

SELECT CAST(@ReturnXML AS Varchar(8000)) as ReturnXML

-----------------------------------------------------------------------------

MORE: You can add a Script Task (C#)to your Control Flow after loading the variable to display the value of the String Variable like this:

        public void Main()

        {

            DialogResult button =MessageBox.Show((string)Dts.Variables["User::xml_clob"].Value, (string)"xml_clob",MessageBoxButtons.OK);

            Dts.TaskResult = (int)ScriptResults.Success;

        }