WindowsPhone/Skydrive: “we’re having trouble downloading this document”

Since this week’s updates to SkyDrive, my Windows Phone Office Hub started having issues accessing documents.  Files I hadn’t downloaded to my Windows Phone from my SkyDrive worked fine, but any files I’d already downloaded and tried to access again consistently gave the error: “we’re having trouble downloading this document”.  Also, if I created a new folder on my SkyDrive on my laptop, it wouldn’t show up when browsing my SkyDrive on my phone.

The reason is that the URLs of the documents have changed with the latest updates to SkyDrive.  Since Office Hub is looking for these URLs, it is getting confused.

The fix is that you’ll need to reset Office on your Windows Phone.

To do this:

1. Open Office Hub on your Windows Phone, go to “Locations” and open “Phone”.  If you have any documents there that you want to save, open them, select the ellipsis (…), and select “Share”.  Select your email and then mail to yourself.  This way you have a copy since resetting office will remove any files saved to your Phone.  And any pinnings you have on your homescreen.
2. Go to “Settings” on your Phone.  Go to “Applications”.  Select “Office”
3. Hit the “Reset Office” button.  If it doesn’t work, reboot your phone and try again.

SkyDrive should now work fine.

Why I’m a Republican voting against Ohio Senate Bill 5

A non-technical entry from me this time, but I felt compelled to share my thoughts on Ohio’s Senate Bill 5 debate.

I’m currently about a third of the way through reading the text of the rather lengthy Bill.  My plan is to have it read cover-to-cover at least a couple days before election day.  As a point of information, I consider myself, politically, to be something of a Raging Moderate, though I do tend to vote Republican and did vote for Governor Kasich, who at the time seemed to me to be (marginally) the better option.  Kasich has spearheaded Senate Bill 5, a piece of legislation that significantly limits collective bargaining for public employees in the state of Ohio.  The Bill is now in referendum as Issue 2.  Putting political alignments aside and looking at what’s actually in Senate Bill 5, I can see some good things.  And I can see some VERY bad things.

Here is my analysis of the Bill and why the bad is outweighing the good.

The Intent of the Bill

Let’s talk for a second about the intent of the Bill.  Both sides of the argument surrounding the Bill are each suggesting ulterior motives from the opposing side’s stance.  But most will at least agree that part of the Bill’s intent is to try to help our current fiscal crisis in the state of Ohio. 

How the Bill attempts to help the budget

Some of SB5’s main attempts to control government spending come from enforcing health benefit contributions and the outlawing of what is known as “pension pick-ups”.  For the former, the Bill limits collective bargaining of health care benefits costs, locking them in at a 15% employee contribution.  Even though many public employees already pay this, I could see how this could help cut down the government spending.  And what about pension contributions?  You may have heard that SB5 increases how much public employees pay towards pensions.  In fact, it does not.  Current laws have public workers contribute up to 10% of their paycheck towards their pension and employers being required to pay 14%.  SB5 does not change this.  What it does do is outlaw the practice of “pension pick-ups”, by which employers agree to pay a portion of the employees’ 10% contribution.  Again, some money saved.

What are the specifics of the Collective Bargaining Restrictions?

The text of the Bill preserves the collective bargaining of wages, hours, and terms and conditions of employment.  There seems to be some confusion about if safety equipment is covered.  This is understandable.  When the original version of Senate Bill 5 narrowly passed the Ohio Senate on March 2^nd of this year, it did not allow for collective bargaining of safety equipment.  It was the revised Bill that came out of the Ohio House on March 31^st that added explicit coverage of safety equipment in this added line: “…equipment issues directly related to personal safety are subject to collective bargaining”  (SB5 4117.08 F).  So the current version of SB5 DOES allow police and firefighters to bargain for safety equipment.

So far, all of this sounds reasonable. 

Stay tuned.  This takes a hard left turn into over-the-line.

The bad stuff

Senate Bill 5, page 230, 4117.08 C: “nothing … impairs the right and responsibility of each public employer to:” 

“(5) Make any and all reasonable rules and regulations”.  It does not put any definition on what is “reasonable”.  This is unacceptably broad and vague.  This says that if public workers want to collectively bargain against something, the other side of the table can simply say, “I’m sorry, that’s a reasonable policy.  You can’t collectively bargain against it.” 

The Bill is taking too much power away from the workers who are in the best place to know what is best and safest for our communities.  Firefighters and nurses wouldn’t be able to collectively bargain for appropriate staffing levels.  Safety equipment is one thing, but if you don’t have enough people to do a job, how can that be safe?

Effect on Teachers

Teachers seem to be at the heart of the SB5 debate.  If SB5 is allowed to continue, they will lose the right to strike, collectively bargain for appropriate class sizes, indeed lose the right to collectively bargain on any matter concerning the education of our children which is dubbed “reasonable” by the people who spend almost no time in the classroom.  Republicans value personal responsibility.  Who else (besides parents, I hope) would feel a greater sense of responsibility about the education of our children than teachers?  We have to insure that teachers continue to have a voice in what is best for our children.  SB5 is denying them that.

SB5 also mentions performance-based pay for teachers.  I say “mentions” because it defines no specifics about how this is to be done or how performance is to be measured, though it gives some suggestions.  Some theorize that performance-based pay could be good for education.  I must say, we can theorize all we want.  But I’m an engineer.  I prefer facts, and study, and data, and evidence.  And the fact is I don’t know of any study performed that concludes that performance-based pay is good for education.  All of them either say it doesn’t work or are inconclusive.  Why this legislation would be pushing a policy which no study supports is mind-boggling.

The big one

If you thought the denying of bargaining on self-defined “reasonable” rules was bad enough, brace yourself, we’re not done.  The big bad item of this Bill:  Senate Bill 5 eliminates binding arbitration, in which a neutral third party, instead of the public employer, decides collective bargaining disputes.  Who will decide collective bargaining disputes now?  The employer’s legislative body.  That’s right; the employees lose.  It would be like us having a debate about, oh, let’s say, Senate Bill 5, wherein at the end, the winner is decided by me.  And I choose myself; I win.  Sound fair?

Isn’t it just common sense that any dispute should be decided by a neutral party?  With binding arbitration eliminated by Senate Bill 5, we can effectively forget any collective bargaining for the workers at this point.  It would be futile.

Conclusion

I’m all for fiscal responsibility, but one can’t sacrifice responsibility to the community or responsibility to equality to get it.  I believe in our system of government, particularly in the idea of checks and balances.  And when one side of the table gets sole rights to decide what is “reasonable” and sole rights to decide who wins, then there is no check, there is no balance. 

I sincerely hope there may be some future incarnation of legislation that takes some of the good from this Bill and combines it with a more fair and balanced policy toward the working class.  For now, please join me and urge others to join me in voting NO on Issue 2:  NO to Senate Bill 5.

Who’s Using SQL Azure? (… and why aren’t others?)

I gave a lunch n’ learn a couple days ago, as part of E&Y’s “SQL Thirstday” (sic) series.  I figured I’d summarize the hour talk into a blog post for anyone curious.  The topic was again SQL Azure, but this time I wanted to focus less on technical details and more on real world examples of SQL Azure adoption.  I wanted to address questions like:

• What companies are using SQL Azure?
• Is there a particular type of company using SQL Azure?
• Have there been big name adoptions?
• What are their experiences?
• Have they run into problems?
• What are some of the benefits/business drivers pushing companies to SQL Azure?
• What are reasons some companies don’t opt for it?
• Is it because of limited functionality? Scalability? Security Issues?
• What are the primary hurdles holding back adoption?

Let’s narrow the first question down to sizes.   So,

What sized companies are using SQL Azure?

You’d expect almost all to be small-to-medium sized companies, at least I did.  What’s surprising is that of the case studies I’ve seen, almost a third of them are Enterprise sized companies.  Looking at Windows Azure Case Studies specifically for SQL Azure, you get these percentages:

Small companies are still the clear majority, but it looks like larger companies are moving to SQL Azure faster than I would expect.  Though it seems in most of the Enterprise companies, they are using SQL Azure for new projects within the companies, rather than a migration of all of their applications (we’ll get to why this is in a minute;  it’s not because migration is hard)

 What type of company is using SQL Azure?

 I’ve been asked this question before and it’s very difficult to answer because there are many types of companies who are using SQL Azure.  It’s a lot easier to define what types aren’t.  These include Health Care, because of HIPAA restrictions, and Retail companies that have PCI DSS (Payment Card Industry Data Security Standards) restrictions.  Microsoft makes no claim regarding PCI standards for 3^rd party hosting.  Depending on where and how you store your data, there are ways to develop applications in the cloud to use 3^rd party PCI data processors that may keep the cloud application itself out of scope.  See the case study for Zuora.

 Are there Big Name adopters?

Oh yes.  I’ve compiled several case studies of recognizable, Enterprise sized companies that are currently using SQL Azure.  For each listed below I’ll cover what the company is, what the project they’re using Azure for is, what data they’re storing in SQL Azure, and what they loved about SQL Azure.

Case Study:  Xerox
The Company:  Industry Leader in, amongst other things, Managed Print Services (MPS)
The Project :  In order to provide printing services for smartphone business users, Xerox first created Xerox Mobile Print, which enabled users to print from their smartphone to printers within their company.  In 2010, Xerox wanted to expand this to allow smartphone users to print to any accessible printer, in or out of their company.  Thus, Xerox Cloud Print was born.  And it was born in Azure.
The Data in SQL Azure:  In building Xerox Cloud Print in Azure, Xerox stored User account information, job information, device information, and print job metadata in SQL Azure.  They used Windows Azure Worker Roles to convert documents to print ready format.  By their claim, they had data logically partitioned in 1 database for multiple tenants.  (Without knowing specifics, I assume this was done using separate schemas or a logical naming convention.)
What they loved about SQL Azure:  They loved that they could use a familiar skill set.  There developers were skilled in SQL Server, so it was very easy to make the transition to the cloud without having to learn a whole new data layer technology.  They also loved how easily they could scale up the pay-as-you-go model.  SQL Azure enabled them to implement and innovate faster because of its quick stand up time and lower cost risk.  After all, if you can easily scale back and pay for less (or bail out of failed project easily) this lowers the fear of taking risks on new initiatives for your customers.

Case Study:  Daimler
The Company:  Leading manufacturer of premium passenger cars and commercial vehicles.
The Project :  Daimler needed to quickly get an application to the market that would enable Smart Car drivers to learn the charge state of their car and quickly locate a nearby charging station.  This would be done in an online portal accessible from a laptop or smartphone.
The Data in SQL Azure:  They stored user and vehicle account information in SQL Azure.
What they loved about SQL Azure:  SPEED.  With SQL Azure, they could stand up SQL Server environments in minutes.  This enabled them to quickly get the application to the market in 3 months. 

Case Study:  3M
The Company:  Consumer and Industrial Products, Research and Development.
The Project :  3M has a lot of experience and research behind analyzing products for visual appeal.  They wanted to make their knowledge available as a service.  To do this, they created their Visual Attention Service (VAS).  This is a web tool for businesses that would allow users to upload potential product images and have them analyzed against 3M’s visual attention models.  The user would get fast feedback on what parts of the product the consumer would be most likely to look at and remember.
The Data in SQL Azure:  The used SQL Azure to manage uploaded images and provide analytical results from the image processing engine.
What they loved about SQL Azure:  The cost savings as opposed to managing regional data centers.  That, and automated management.  In their words:

“SQL Azure is of great value to 3M because it takes the database management piece off our plate”
                – Jim Graham, 3M Technical Manager

Case Study:  Intuit
The Company:  Makers of TurboTax, Quicken, QuickBooks.
The Project :  Intuit was creating the Intuit Partner Program (IPP).  This would enable developers to build Software-as-a-Service applications that would integrate with Intuit’s QuickBooks data.  Applications that would then be posted to Intuit’s app center.
The Data in SQL Azure:  Intuit was providing SQL Azure for any relational data partners desired to store.
What they loved about SQL Azure:  They loved simplified provisioning and deployment of multiple relational databases.  In their own words:               

“Intuit is increasingly becoming a cloud-based company.  This year, 50 percent of our revenue will come from Connected Services. As we look into the future, we see the increased importance of delivering more cloud-based offerings and making them available to a variety of different connected devices.”
                – Alex Chriss, Director, Intuit Partner Platform at Intuit

Case Study:  Associated Press
The Company:  The world’s largest news organization
The Project :  The Associated Press wanted to take all their breaking news and make it more easily consumed by other applications.  They created a Breaking News API that developers across the world could integrate with.
The Data in SQL Azure:  The Associated Press stored their news story metadata in SQL Azure.
What they loved about SQL Azure:  They loved the lack of needed infrastructure management.  This enabled them to focus their time and money on other things.  The familiar toolset and quick time to market were also of great importance to them.

Case Study:  Siemens
The Company:  Electronic and Electrical Engineering.
The Project :   With over 80,000 devices worldwide, Siemens needed to streamline their management and system patching process.  For this they created their common Remote Service Platform (cRSP) in Azure.
The Data in SQL Azure:  Order processing and management data was stored in SQL Azure.
What they loved about SQL Azure:  Flexibility and cost were big for them.  But it was also very important to have the consistent interface that SQL Azure provided them.  This enabled them to easily switch between on-premise SQL Servers and SQL Azure, without having to manage 2 code bases for data access: 

“Developing the application management logic on SQL Azure  allowed fast and seamless migration and internal code reuse. So we could use the same data model in the cloud as we do in our on-premises repository“
                – Gerald Kaefer, Architect at Siemens

Case Study:  TicketDirect
The Company:  Major ticketing service for Australia and New Zealand.
The Project :   They needed to better handling elastic peak loads that are common for the ticketing industry.  Think about an application that largely sits idle until a flood of activity comes when Lady Gaga tickets go on sale.  The cloud would be perfect for them to migrate to.  But they had a lot of intense logic at the SQL stored procedure level (as many applications often do), and they needed a cloud data provider that would enable them to have such complex data layer logic.  Enter SQL Azure.
The Data in SQL Azure:  All their ticketing data was stored in SQL Azure.  They would spin up tens or hundreds of new SQL Azure databases and move data into them in peak loads.  (I’d be curious to hear more specifics about how this was managed technically if they’d like to share.)
What they loved about SQL Azure:  As I said they had intense TSQL logic in stored procedures.  That meant that no other cloud provider could give them what they wanted, except SQL Azure:

“Their Windows Azure application needs to have quite sophisticated logic running very close to the data layer …We looked at Amazon EC2, but to get the same functionality would have required much greater costs and management. With Windows Azure and SQL Azure, we get all of that cloud readiness right out of the box.”

“It is the shining light in this project. Without SQL Azure, we wouldn’t be able to do this.”

                – Chris Auld, Director of Strategy and Innovation

Case Study:  T-Mobile
The Company:  Leading provider of wireless services.
The Project :   T-Mobile was developing a social networking app for the Windows Phones which they called “Family Room”.  It was social networking functionality for small groups like families to post on a common bulletin board and share schedules.  i.e.  someone would post “We’re out of toilet paper.  Can someone pick some up?”, or a discussion could open about “How does everyone feel about such-and-such restaurant for dinner?”.  The important thing for T-Mobile was that the data for each family was secured and private.
The Data in SQL Azure:  All the social networking data was stored in SQL Azure.
What they loved about SQL Azure:  That they were able to get the level of security they needed.

So what are their experiences?

These adopters have had a mostly positive experience with SQL Azure.  They do find, as I stated earlier, that starting new projects in SQL Azure is better than migrating large existing ones.  This isn’t because migration is difficult.  Indeed, there are many good utilities for technically migrating on premise databases to SQL Azure (i.e. SQL Azure Migration Wizard, Red Gate SQL Compare and Data Compare that now support Azure).  These companies are starting new projects in Azure as a proof of concept and to build their confidence in the cloud.  Starting small is building their momentum to take more to the cloud.

Have they run into problems?

I’ll admit this was a mostly pro-Azure talk.  But it’s important to discuss the negatives.  Did these companies experience problems in the new technology?  As with any new technology, yes, of course there are gotchas. 

They found that scaling up was easy, but scaling out was hard.  This is due to a lack of federating capabilities or replication solutions in Azure.  This is at least the state of things for now, though SQL Azure Data Sync is providing some synchronization capabilities.  And when considering things like datasync, network unreliability can cause a problem.  The redundancy of a SQL Azure database requires a connection retry.  For .NET code, developers learned they had to switch to using the ReliableSQLConnection object in .NET to have this built-in retry functionality.

Some other problems:  Unsupported data types, like CLR datatypes, can cause a problem for how some companies like to do things.  A lack of SSAS in SQL Azure is currently preventing companies from taking their cubes to the cloud.  And a lack of Cross Database Joins in SQL Azure is a big one.  Ideally in good design you shouldn’t have 2 databases talking to each other at the database level .  This is a rule that I break not-infrequently.  Sometimes in just makes sense to have cross-database queries.  But since a SQL Azure database is, in actuality, an abstraction of a database, this means no crossing databases for you.  If you have this implemented heavily in an application, you should know that this means moving to SQL Azure will require some significant refactoring.

What are the benefits/business drivers for going to SQL Azure?

The greatest business driver of them all:  COST.  Companies are noticing on average a 30-40% reduction in operating cost (in some cases as much as 90% like in the case of internet startup MyWebCareer.com).  When you talk about cost savings for a company using SQL Azure, you’re talking about two main points largely:

 1)      High availability.  The fact that you get 99.9% availability in a SQL Azure database for as little as $10/month is huge.  Can you get 3, 4, or 5 9s availability on your own?  Yes.  Can you do it for so cheap?  Not likely.  We spend a lot of time and resources on high availability and disaster recovery plans for on-premise applications.

 2)      Speed of deployment.  When you’re talking about getting infrastructure ready in minutes instead of weeks, this means you’ve got an application deployed in weeks instead of months.

Ultimately, this brings IT to the masses as almost a democratization of IT.  This allows smaller companies who could be intimidated by the high cost of IT management to compete with the larger companies.

What are the hurdles/reasons some companies don’t opt for SQL Azure?

Back to the negatives.  Hurdles?  You’ll find it’s not because of functionality limitations.  I’d say 80% of what you do in a SQL Server database is provided for you in SQL Azure.  Tables, FKs, Indexes, Procs, Views, the meat-and-potatoes is all there.  Some hurdles are things like a 50 GB limit.  Even though this has grown from 10 GB, Microsoft still has a cap right now in order to meet their SLA (recovering a much larger database in the event of an issue would take a long time).  This 50 GB limit on a single database could be a hard pill for some companies to swallow.  Especially since database federations are not easily implemented in SQL Azure.

Another hurdle is some countries’ restrictions on data in response to the Patriot Act.  At E&Y, we often have to deal with data that can’t leave a country’s borders.  Since there are only 6 Azure datacenters currently, this is a flag that such data cannot go to SQL Azure.   And again, if you have HIPAA or PCI restrictions, this is another indication that Azure might not be for you.

And ultimately, there’s a shift in paradigms that people have to go through when they go to the cloud.  A fellow developer described Cloud storage as being like banking.  Many of us had grandparents who didn’t trust banks and kept their money in their house.  For them, the question would be:  “why have your money in a bank when you can keep it at home?”  For us, the younger generation, the question is:  “why do you have thousands of dollars in a sock drawer, and not in a secure bank?”  You can see the analogy to the cloud.  Right now, the question is: “why have your data in the Cloud and not securely on premise?”  Perhaps one day the question will be:  “why have your data on premise and not securely in the cloud?”  We’ll see.  The cloud is only currently beginning to earn our trust.

For some, a shift in paradigms means the fear of the disappearance of true management (DBAs, infrastructure engineers, etc.).  This Dibert cartoon reminds me of that.  The truth is that we should never be without the Database Administrator.  The cloud will always need intelligent engineers to make sure that it is being utilized properly.

More humor about a shift in paradigms can come from this early interpretation of a horseless carriage: 

People often can only think of new things in terms of what they know.  Engineers designed whip holders into automobiles for the first 6-7 years, even though there was no horse…

The Cloud’s going to take some getting used to.

Final Thoughts

For me, I think Azure is going to be very appealing for the startups and the younger generation.  The small companies are now going to be able to be more Agile and scale up more easily if they’re lucky enough to have their ideas take off.  Also, consider that there are a lot of go-getter programmers who come out of college and then hit a snag when they realize there’s all this other infrastructure and management required to get their ideas out the door to the world.  Azure’s going to look very good to them.  Now they can just worry about programming an application and then having it hosted.  So who knows what other Mark Zuckerbergs are going to be unleashed and empowered now that the cloud is here.

 For More Information

 Check out Microsoft’s suite of Azure case studies:  You can easily filter down to SQL Azure as you like:

      http://www.microsoft.com/WindowsAzure/evidence

Analyze an Estimated Execution Plan at RunTime

There are many reasons why one might want to analyze an Estimated Execution Plan for a query. Of course you can analyze it in the graphical Execution Plan display tab in SSMS, but I wanted to analyze the more-complete XML plan.
And, (here’s the kicker) I wanted to do it at RUNTIME.
This post outlines my journey in finding a way to do this. The eventual solution was implemented as a CLR procedure with a wrapper SQL procedure to do the analysis.

The Problem

I was working on an application whose frontend allowed an advanced user to write queries against underlying views.  Even thought the user was restricted to just SELECT access against certain views to prevent him/her from wreaking havoc, there were still actions we wanted to take based on how they were writing the query.  For example, we might want to prevent the query from executing if (a) the query were written in a way that would return too much data, or (b) if the query would have run slowly, or (c) if the user were SQL-experienced and was trying to execute something like sp_help, which in some cases we weren’t able to deny execute to for the user (for example: in SQLAzure, you’re currently not able to deny execute to this).

The (Possible) Solution

I thought that getting the Execution Plan in XML and analyzing it would be a good way to get the information we needed.  Indeed, we could query the XML for all we wanted.

For pedagogical purposes, let’s just use a select from sys.objects for an example of a statement to analyze:

SELECT * FROM sys.objects

If we just wanted to use the IDE, we could get the estimated plan by just clicking the “Display Estimated Execution Plan” button in the toolbar.

From there, you can get view the Plan.

Now Graphical Plans are nice to look through, but they mask detailed data for the plan. The XML plan is far more complete. You can right click and select “Show Execution Plan XML”. And that can give you an XML file you can view and save:

All this is well and good, but what if you wanted to do it at RUNTIME, like I want to?  This brings up a series of issues.

Options and Respective Issues

Obviously, the IDE isn’t going to work for runtime.  So the question becomes: how can we store this XML in a variable at runtime on which we can do an XQuery?  Can we do this programmatically?

Option:  Tracing

You could use a SQL trace to capture the showplan XML, as outlined in this post:

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2007/06/01/store-statistics-xml-in-database-tables-using-sql-traces-for-further-analysis.aspx

The issue: the query would still have to be executed.  I want to get the estimated execution plan, and then decide whether or not to run the query.

Option:  Query DMVs

You could query dynamic management views and dynamic management functions, as in the snippet below.  (The example XQuery to analyze the XML from the previous link)

DECLARE @PlanXML xml;
DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT * FROM sys.objects';

SELECT @PlanXML = [QP].[query_plan]
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE [ST].[text] = @SQL;

SELECT @PlanXML AS PlanXML;

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)
SELECT
	 ro.relop.value('@NodeId', 'int')					AS NodeId
	,ro.relop.value('@PhysicalOp', 'nvarchar(200)')		AS PhysicalOp
	,ro.relop.value('@LogicalOp', 'nvarchar(200)')		AS LogicalOp
	,(ro.relop.value('@EstimateRows', 'float')
		* (ro.relop.value('@EstimateRewinds', 'float')
		+  ro.relop.value('@EstimateRebinds', 'float')
		+ 1.0))											AS EstimateRows
	,CAST(ro.relop.exist('*/sql:RelOp') AS bit)			AS IsNotLeaf
FROM @PlanXML.nodes('//sql:RelOp') AS ro(relop)

The issue: again, the query would have had to have been kicked off and cached first. If the plan were cached, we’d get this nice output:

However, we can’t count on the text of that query having been cached.

Option:  SET SHOWPLAN_XML

You’ll find that behind the scenes when you click “Display Estimated Execution Plan”, what Management Studio is actually doing is this:

SET SHOWPLAN_XML ON
GO

SELECT * FROM sys.objects
GO

SET SHOWPLAN_XML OFF
GO

Provided the user has proper rights,:

GRANT SHOWPLAN TO [username]

you’ll get what you want.

The issue:  good luck trying to save this off as an XML variable.  Because after you have SHOWPLAN_XML on, anything executed after that will not actually be executed.  The XML for the Plan of anything you try to do (including saving to a variable) will just be returned in the results tab.  We’re still getting nowhere.

Option: Dynamic SQL

Not a bad idea.  We could execute everything in dynamic SQL and insert it into a table.   Let’s try it:

IF object_id('tempdb..#EstimatedPlans') IS NOT NULL
BEGIN
   DROP TABLE #EstimatedPlans
END

CREATE TABLE #EstimatedPlans
(
   PlanXML xml
)

DECLARE @SQL nvarchar(max)
SET @SQL =
'
	SET SHOWPLAN_XML ON
	GO

	SELECT * FROM sys.objects
	GO

	SET SHOWPLAN_XML OFF
	GO
'

INSERT INTO #EstimatedPlans
EXEC sp_executesql @SQL

You may have already noticed the problem with this. “GO” is for separating batches in Management Studio. You can’t use it in a batch of dynamic SQL. You’ll get:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘GO’.

Ok, let’s take out the “GO”s then:

IF object_id('tempdb..#EstimatedPlans') IS NOT NULL
BEGIN
   DROP TABLE #EstimatedPlans
END

CREATE TABLE #EstimatedPlans
(
   PlanXML xml
)

DECLARE @SQL nvarchar(max)
SET @SQL =
'
	SET SHOWPLAN_XML ON

	SELECT * FROM sys.objects

	SET SHOWPLAN_XML OFF
'

INSERT INTO #EstimatedPlans
EXEC sp_executesql @SQL

The issue:

Msg 1067, Level 15, State 1, Line 0
The SET SHOWPLAN statements must be the only statements in the batch.

Arrrg! We have to execute multiple batches on the same connection to turn SHOWPLAN_XML on and pass in our query. But we can’t execute multiple batches on the same connection in Dynamic SQL!

But wait! What about this:

DECLARE @SQL nvarchar(max)

SET @SQL = 'SET SHOWPLAN_XML ON'
EXEC sp_executesql @SQL

SET @SQL = 'SELECT * FROM sys.objects'
EXEC sp_executesql @SQL

SET @SQL = 'SET SHOWPLAN_XML OFF'
EXEC sp_executesql @SQL

Sorry, all you get now is the output of SELECT * FROM sys.objects, since each dynamic SQL execution is in its own scope that returns back to the calling scope. Results:

So how do we execute multiple batches on the same connection?

Option:  CLR to the rescue!

Perhaps if we created a CLR procedure and pass in the SQL, it could execute “SET SHOWPLAN_XML ON” first, and then the input SQL on the same open connection, and return the XML results.  Huzzah!

I’ll spare you the pain of the code samples from my CLR failures, and just summarize the issues I had to address along the way:

1. You can usually return data in a dataset from a CLR procedure using

   SqlContext.Pipe.Send(sqlDataRecord);
   

   The issue with this is that this requires a SQLConnection with the connection string of “context connection=true” to run on the same database.  Unfortunately, there are some limitations to this connection that will prevent us from doing what we want to do:

   http://msdn.microsoft.com/en-us/library/ms131101.aspx

   Ultimately, we need to pass in the server and database name and open up a new connection.

2. I tried to build a connection string and open up that connection, get the XML, and then open up a context connection to pass the data out.  This will work in some cases, but will error sometimes if you try to do an EXEC INTO (output the data of the CLR proc into a temp table)

   Long story short: I needed use an output parameter.

3. Tried the output type of SQLString from the CLR procedure (since working with strings seemed easier than XML), but SQLString has a max length of 4000 and thus wouldn’t work.

   I had to have a SqlXml output parameter.

4. Packaging up a SqlXml datatype took me a bit of searching to figure out.  Here’s how to do it:

//Package XML as output
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.InnerXml = //The XML in a string here//
System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
PlanXML = new SqlXml(xnr);

The Final Solution

Here’s the final CLR procedure I created to solve my problem. Note I had to prepare a connection string from passed-in parameters. Next, I opened up a single connection and executed two batches: the first to set SHOWPLAN_XML ON, the second to run the input SQL. I then pulled out the result into a dataset, took the first column of the first row of the first table and packaged it up as a SqlXml type.

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetShowPlanXML
    (
          SqlString SQL
        , out SqlXml PlanXML
        , SqlString server
        , SqlString database
        , SqlBoolean isIntegratedSecurity
        , SqlString loginIfNotIntegrated
        , SqlString passwordIfNotIntegrated
    )
    {
        //Prep connection
        string strConnectionString;

        if ((bool)isIntegratedSecurity)
        {
            strConnectionString = @"Data Source="
                + server.ToString()
                + @";Initial Catalog="
                + database.ToString()
                + @";Integrated Security=True";
        }
        else
        {
            strConnectionString = @"data source="
                + server.ToString()
                + @";initial catalog="
                + database.ToString()
                + @";Persist Security Info=True;User ID="
                + loginIfNotIntegrated.ToString()
                + @";Password="
                + passwordIfNotIntegrated.ToString();
        }

        SqlConnection cn = new SqlConnection(strConnectionString);

        //Set command texts
        SqlCommand cmd_SetShowPlanXml = new SqlCommand("SET SHOWPLAN_XML ON", cn);
        SqlCommand cmd_input = new SqlCommand(SQL.ToString(), cn);

        if (cn.State != ConnectionState.Open)
        {
            cn.Open();
        }

        //Run SET SHOWPLAN_XML ON
        cmd_SetShowPlanXml.ExecuteNonQuery();

        //Run input SQL
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        da.SelectCommand = cmd_input;
        ds.Tables.Add(new DataTable("Results"));

        ds.Tables[0].BeginLoadData();
        da.Fill(ds, "Results");
        ds.Tables[0].EndLoadData();

        if (cn.State != ConnectionState.Closed)
        {
            cn.Close();
        }

        //Package XML as output
        System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
        //XML is in 1st Col of 1st Row of 1st Table
        xmlDoc.InnerXml = ds.Tables[0].Rows[0][0].ToString();
        System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
        PlanXML = new SqlXml(xnr);
    }

Below is the example wrapper TSQL procedure I created around the CLR procedure. The purpose of this wrapper is twofold. One, I wanted it to assign default connections for server and database. Two, I’ve put in an example XML analysis that I might do.

CREATE PROCEDURE [dbo].[GetEstimatedExecutionPlanInfo]
(
	 @SQL nvarchar(max)
	,@server nvarchar(4000)						= NULL
	,@database nvarchar(4000)					= NULL
	,@isIntegratedSecurity bit					= 1
	,@loginIfNotIntegrated nvarchar(4000)		= NULL
	,@passwordIfNotIntegrated nvarchar(4000)	= NULL
)
AS
BEGIN

	--Use current connection as default
	IF @server IS NULL
		SET @server = (SELECT @@SERVERNAME)

	IF @database IS NULL
		SET @database = (SELECT DB_NAME())

	--Get XML from CLR Proc
	DECLARE @PlanXML xml

	EXEC [dbo].[GetShowPlanXML]
		 @SQL
		,@PlanXML OUTPUT
		,@server
		,@database
		,@isIntegratedSecurity
		,@loginIfNotIntegrated
		,@passwordIfNotIntegrated

	--Return Plan
	SELECT @PlanXML AS PlanXML

	--Return info about Plan
	;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)
	SELECT
		 ro.relop.value('@NodeId', 'int')					AS NodeId
		,ro.relop.value('@PhysicalOp', 'nvarchar(200)')		AS PhysicalOp
		,ro.relop.value('@LogicalOp', 'nvarchar(200)')		AS LogicalOp
		,(ro.relop.value('@EstimateRows', 'float')
			* (ro.relop.value('@EstimateRewinds', 'float')
			+  ro.relop.value('@EstimateRebinds', 'float')
			+ 1.0))											AS EstimateRows
		,CAST(ro.relop.exist('*/sql:RelOp') AS bit)			AS IsNotLeaf
	FROM @PlanXML.nodes('//sql:RelOp') AS ro(relop)

END
GO

Here’s a test execution:

DECLARE @SQL nvarchar(max);
SET @SQL = 'SELECT * FROM sys.objects'

EXEC [dbo].[GetEstimatedExecutionPlanInfo] @SQL

Yay! The output we wanted, it’s during runtime, and we didn’t have to execute the query! Again, this is an example XML analysis that I might do; this could be used later on in code to decide whether or not to execute the user’s query.

Now CLR isn’t the only solution. I imagine the front end application could do the work that the CLR procedure is doing to get the plan. Indeed, this might have to be the option if we’re in SQLAzure, where CLR isn’t currently supported. And yes, there’d be a lot of analysis of the XML we’ll have to decide on and develop. But we’re on our way.

My talk at SQLSaturday: Introduction to Developing with SQL Azure

Come check out my talk on SQL Azure at the Chicago and Boston SQLSaturdays on March 26th and April 2nd, respectively:

Introduction to Developing with SQL Azure
The Cloud is fast becoming the latest hot topic in the technology arena. We need to understand how we as SQL Developers and Administrators can utilize SQL Azure, Microsoft’s platform for relational data storage in the Cloud, to meet our needs. This talk will be a step-by-step demo of how to obtain an Azure subscription, create and administer a SQL Azure database, and work with it from your own SQL Server Management Studio AddIn. You’ll find that Azure is so easy to work with, you’ll get two for the price of one in this talk: you’ll learn Azure and learn how to create a Management Studio AddIn!

SQLSaturday is a one day training event for SQL Server professionals.
Check it out:
http://www.sqlsaturday.com/
Here’s my session, with slides:
http://www.sqlsaturday.com/viewsession.aspx?sat=67&sessionid=3773

Hope to see you there!

“RowGUID is neither a DataColumn nor a DataRelation for table summary. (System.Data)”

We had merge replication in SQL Server 2005 set up that was working fine, except when trying to view conflicts, the Conflict Viewer would give this error: “RowGUID is neither a DataColumn nor a DataRelation for table summary. (System.Data)”.

The source of this it turns out was actually a period in the Publication name, as we had a “6.1” as part of the name. Other special characters would probably also cause trouble.

The only way to fix this was to drop the replication, resolve known conflicts manually with a data compare, recreate the replication, and reinitialize. After that, we could use the Conflict Viewer to resolve conflicts successfully.

This was a frustrating issue since the replication was allowed to be created with the period and worked completely fine, only the Conflict Viewer experienced trouble and it gave an error that did not accurately describe the issue.

The moral of the story is: don’t create a publication with a period in the name.

Aggregate to CSV in TSQL

Sometimes we need to rollup string values into lists of comma separated values. Using “FOR XML PATH” per column that you want to rollup can provide a way to do this.

Let’s say you have a data set like so:

id	fruit	city
101	Apple	Cleveland
101	Apple	Pittsburgh
101	Banana	Pittsburgh
102	Grape	Cleveland
102	Melon	Cleveland
103	Melon	Pittsburgh
103	Melon	Cleveland

and for each distinct “id”, you want a list of its distinct fruits and cities.

If you put a FOR XML PATH statement for each column you want to rollup like so:

...
----------fruit---------
,STUFF((SELECT DISTINCT ', ' + NULLIF(fruit,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS fruit
...

and then GROUP BY id, you’ll have a nice aggregated set. It will even put it in alphabetical order and remove NULL and empty string values for you.

Complete code example:

;WITH tableToCSVRollup (id,fruit,city)
AS 
(
			  SELECT 101, 'Apple', 'Cleveland'
	UNION ALL SELECT 101, 'Apple', 'Pittsburgh'
	UNION ALL SELECT 101, 'Banana','Pittsburgh'
	UNION ALL SELECT 102, 'Grape', 'Cleveland'
	UNION ALL SELECT 102, 'Melon', 'Cleveland'
	UNION ALL SELECT 103, 'Melon', 'Pittsburgh'
	UNION ALL SELECT 103, 'Melon', 'Cleveland'
)
SELECT
id
----------fruit---------
,STUFF((SELECT DISTINCT ', ' + NULLIF(fruit,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS fruit
----------city----------
,STUFF((SELECT DISTINCT ', ' + NULLIF(city,'')
FROM tableToCSVRollup t2
WHERE t1.id = t2.id
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
,1,2,'') AS city
------------------------
FROM tableToCSVRollup t1
GROUP BY id
ORDER BY id

Results:

id	fruit	city
101	Apple, Banana	Cleveland, Pittsburgh
102	Grape, Melon	Cleveland
103	Melon	Cleveland, Pittsburgh

——————————————

GROUP BY CUBE Example

The GROUP BY CUBE statement was introduced with SQL Server 2008 and is pretty sweet. Here’s an example.

First, let’s create our table and add data:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GroupByCubeExample]') AND type in (N'U'))
	DROP TABLE [dbo].[GroupByCubeExample]
GO

CREATE TABLE [dbo].[GroupByCubeExample]
(
	 [Region] [nvarchar](50) NOT NULL
	,[Product] [nvarchar](50) NOT NULL
	,[Salesman] [nvarchar](50) NOT NULL
	,[Amount] [money] NOT NULL
)
GO

INSERT INTO dbo.GroupByCubeExample
(Region,Product,Salesman,Amount)
		  SELECT 'America'	,'Bike'	,'Bean, Adam'		,10
UNION ALL SELECT 'America'	,'Bike'	,'Stanford, Matt'	,20
UNION ALL SELECT 'America'	,'Car'	,'Bean, Adam'		,10000.0000
UNION ALL SELECT 'America'	,'Car'	,'Stanford, Matt'	,20000.0000
UNION ALL SELECT 'Canada'	,'Bike'	,'Bean, Adam'		,10
UNION ALL SELECT 'Canada'	,'Bike'	,'Stanford, Matt'	,20
UNION ALL SELECT 'Canada'	,'Car'	,'Bean, Adam'		,10000.0000
UNION ALL SELECT 'Canada'	,'Car'	,'Stanford, Matt'	,20000.0000 

Then, we can SUM using GROUP BY CUBE for some nice automatic rollups.

SELECT
	 Region
	,Product
	,Salesman
	,SUM(Amount) AS Amount
FROM dbo.GroupByCubeExample
GROUP BY CUBE
(
	 Region
	,Product
	,Salesman
)
ORDER BY 
	 Region
	,Product
	,Salesman

Results (NULLs mean ALL):

Region	Product	Salesman	Amount
NULL	NULL	NULL	60060.00
NULL	NULL	Bean, Adam	20020.00
NULL	NULL	Stanford, Matt	40040.00
NULL	Bike	NULL	60.00
NULL	Bike	Bean, Adam	20.00
NULL	Bike	Stanford, Matt	40.00
NULL	Car	NULL	60000.00
NULL	Car	Bean, Adam	20000.00
NULL	Car	Stanford, Matt	40000.00
America	NULL	NULL	30030.00
America	NULL	Bean, Adam	10010.00
America	NULL	Stanford, Matt	20020.00
America	Bike	NULL	30.00
America	Bike	Bean, Adam	10.00
America	Bike	Stanford, Matt	20.00
America	Car	NULL	30000.00
America	Car	Bean, Adam	10000.00
America	Car	Stanford, Matt	20000.00
Canada	NULL	NULL	30030.00
Canada	NULL	Bean, Adam	10010.00
Canada	NULL	Stanford, Matt	20020.00
Canada	Bike	NULL	30.00
Canada	Bike	Bean, Adam	10.00
Canada	Bike	Stanford, Matt	20.00
Canada	Car	NULL	30000.00
Canada	Car	Bean, Adam	10000.00
Canada	Car	Stanford, Matt	20000.00

And if you don’t like the NULLs, you can use a WHEN GROUPING() statement to better format:

SELECT
	 CASE
		WHEN GROUPING(Region) = 1	THEN 'ALL REGIONS'
		ELSE Region
	 END AS Region
	,CASE
		WHEN GROUPING(Product) = 1	THEN 'ALL PRODUCTS'
		ELSE Product
	 END AS Product
	,CASE
		WHEN GROUPING(Salesman) = 1	THEN 'ALL SALESMAN'
		ELSE Salesman
	 END AS Salesman
	,SUM(Amount) AS Amount
FROM dbo.GroupByCubeExample
GROUP BY CUBE
(
	 Region
	,Product
	,Salesman
)
ORDER BY 
	 Region
	,Product
	,Salesman

Results:

Region	Product	Salesman	Amount
ALL REGIONS	ALL PRODUCTS	ALL SALESMAN	60060.00
ALL REGIONS	ALL PRODUCTS	Bean, Adam	20020.00
ALL REGIONS	ALL PRODUCTS	Stanford, Matt	40040.00
ALL REGIONS	Bike	ALL SALESMAN	60.00
ALL REGIONS	Bike	Bean, Adam	20.00
ALL REGIONS	Bike	Stanford, Matt	40.00
ALL REGIONS	Car	ALL SALESMAN	60000.00
ALL REGIONS	Car	Bean, Adam	20000.00
ALL REGIONS	Car	Stanford, Matt	40000.00
America	ALL PRODUCTS	ALL SALESMAN	30030.00
America	ALL PRODUCTS	Bean, Adam	10010.00
America	ALL PRODUCTS	Stanford, Matt	20020.00
America	Bike	ALL SALESMAN	30.00
America	Bike	Bean, Adam	10.00
America	Bike	Stanford, Matt	20.00
America	Car	ALL SALESMAN	30000.00
America	Car	Bean, Adam	10000.00
America	Car	Stanford, Matt	20000.00
Canada	ALL PRODUCTS	ALL SALESMAN	30030.00
Canada	ALL PRODUCTS	Bean, Adam	10010.00
Canada	ALL PRODUCTS	Stanford, Matt	20020.00
Canada	Bike	ALL SALESMAN	30.00
Canada	Bike	Bean, Adam	10.00
Canada	Bike	Stanford, Matt	20.00
Canada	Car	ALL SALESMAN	30000.00
Canada	Car	Bean, Adam	10000.00
Canada	Car	Stanford, Matt	20000.00

Work-around for 2005 Maintenance Plan bug

In working with SQL 2005 Maintenance Plan backup cleanup tasks, a frustrating bug was found. It seems that when the ability to select “hours” as a time criterion was added in SP2, the door was opened for misinterpretation of time units. For example, you can, in your local Management Studio, set the Plan to delete files greater that 4 days old, and when run on the server the Plan will delete files only greater than 4 WEEKS old. This was the full mapping I found:

SET ACTUAL
Hours -> Days
Days -> Weeks
Weeks -> Months
Months -> Years
Years -> Years

I don’t know yet if this is fixed in 2008. For 2005 at least, I suggest refraining from using the built-in “Maintenance Cleanup Tasks” in Maintenance Plans, and instead use a T-SQL Statement task with the snippet below. This will truly delete greater than 4 days. Set your time criteria however you like.

DECLARE @DateOlderThan datetime
SET @DateOlderThan = DATEADD(day,-4,GETDATE())

EXEC master.dbo.xp_delete_file
	 0						-- delete files
	,N'H:\SQLBackup\INST3'	-- full path to the main directory
	,N'bak'					-- file extension
	,@DateOlderThan			-- delete files created before this timestamp
	,1						-- Including first-level sub

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.”

Management Studio 2008 has a default that prevents saving table changes from the designer when the table will need to be rebuilt. I’m constantly being asked about this error by developers:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.”

To be able to make table changes go to Tools->Options->Designers and uncheck “Prevent saving changes that require table re-creation”.

It’s a good idea to do this as soon as you install 2008. Tell everyone around you. I’m asked about it quite often.