The Bit Bucket

Hi Folks,

Hope this helps someone. After upgrading to IE9 and SQL Server 2008 R2 CU7, I found that I couldn't access Report Manager anymore. I found that even though I was in the BUILTIN\Administrators group, that I had to specifically add myself to the Content Managers role in Reporting Services for the root folder of Report Manager. (I have no idea why as it used to work ok) NOTE: I had to do this with UAC turned off, otherwise, Site Settings, etc. were not visible. Remember to turn UAC back on if you did this temporarily.

But the bigger issue was that Report Builder 3.0 would not launch, even after I got the button back on the screen. The message was that I needed to install the .NET Framework 3.5, even though I already had .NET Framework 4.0 installed. Nothing I did seemed to fix the problem.

In the end, the issue is that Report Builder 3.0 will not launch from Report Manager in IE9 unless you enable Compatibility Mode (normally used for older web sites that don't render properly in IE9) for the Report Manager web site. Once I set Compatibility Mode, Report Builder 3.0 launched as expected.

Hope this helps save someone else a bunch of time.

Regards,

Greg

Hi Folks,

I've published another SQL Down Under podcast. This time it's Mark Tabladillo discussing SQL Server data mining. It's available now at: http://www.sqldownunder.com.

Enjoy!

Regards,

Greg

At our company we teach quite a lot of classes and that means we do a lot of printing. So, we decided to move up to a really serious printer. Whenever we go to a high-end print shop, they all use Fuji Xerox Docucenter printers. So we thought we should get one.

These are truly amazing printers/copiers. The print quality is the best available. The print speed is awesome (and just based on your budget). The capabilties are exactly what we need. The printers are renowned for their reliability and the price of the printers reflects their high-end status.

However, given a company claims to have the best product available, charges a price that matches those claims and also says that it almost never breaks down, how long would you imagine they would warrant it for? 12 months? 24 months? 36 months? 5 years?

I was a little stunned to see that they cover only the cost of parts for 3 months. Yes, that's no misprint. It's 3 months.

I am assured this is "normal" for this segment of the industry. Am I alone in finding this more than a little odd? A small car manufacturer would warrant their similarly-priced product for 3 or 5 years today.

I understand that most people buy these with some sort of ongoing service agreement but is there any other industry where you sell items that cost as much as a car and only warrant it for 3 months? What message does that really send about the manufacturer's confidence in their own product?

I had a day of mixed emotions today.

I (and I assume most of the world) have been horrified by this "religious" massacre in Afghanistan: http://www.theblaze.com/stories/two-beheaded-florida-quran-burning-triggers-massacre-at-un-office-in-afghanistan/ 

Today, however, I also had the reverse experience and a seriously uplifting one. This afternoon I drove through Marysville. For those that don't know it, it's a town that was wiped off the face of the map by bushfires in Victoria a while back. Hundreds of people died. I was roughly in the area and so I made a point of popping into Marysville to see how they were coping. In general, I find that the people in those towns don't want hand-outs so much as business. So I try to visit them and spend some cash in their shops, etc.

But it was the people in the bakery cafe that impressed me today. I saw a tin-can for donations on the counter. I wandered up to look at it. I thought it was a good idea to contribute to helping the locals. What impressed me most was that the collections were for flood victims in Queensland and Victoria, not for Marysville at all. Given what a mess they are currently dealing with themselves, that's seriously impressive and uplifting to see.

There was a discussion on our internal mailing list today about how to get a list of reserved words for SQL Server. It strikes me that there should be a system view that returns this. It could also return details of the version of the product where the word was added and an indication of if the use of the word is deprecated.

If you agree, you know the drill. Vote once, vote often 🙂

https://connect.microsoft.com/SQLServer/feedback/details/653455/system-view-that-lists-reserved-words

This one is a bit annoying. When you apply SP1 for Visual Studio 2010 (VS2010), one of the side effects seems to be that you lose Intellisense in SQL Server Management Studio 2008 R2 (SSMS).

If Intellisense matters to you, you might want to wait for a cumulative update to fix it.

Details here: https://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1

Great to see Jacob and the guys updating their T-SQL Quiz. It's free and it's fun. Get involved:

http://beyondrelational.com/quiz/SQLServer/TSQL/2011/default.aspx

Since the removal of the 8KB limit on serialization, the ability to define new data types using SQL CLR integration is now almost at a usable level, apart from one key omission: indexes.

We have no ability to create our own types of index to support our data types. As a good example of this, consider that when Microsoft introduced the geometry and geography (spatial) data types, they did so as system CLR data types but also needed to introduce a spatial index as a new type of index. Those of us that need to work with the product as it's supplied can't just create our own new types of index objects.

What would have been far preferable would have been for the ability to create user-defined indexes to have been added to the product and for spatial indexes to have been one instance of that.

Other database engines (such as Oracle) have this capability. This makes it impossible to migrate applications that use Oracle Data Cartridges to SQL Server in an effective way. It also just makes the creation of data types in SQL Server that much more limiting than it could be.

One alternative is to promote properties of CLR data types via persisted calculated columns and then index those but that's somewhat awkward and more importantly, doesn't really do the same thing.

If you'd like to see user-defined index types be considered in the future, you know what to do. Vote once, vote often 🙂

https://connect.microsoft.com/SQLServer/feedback/details/650268/introduce-user-defined-index-types

 

Just posting this blog tonight hoping it might save someone else a bunch of time. For call recording on Skype, I use a program called Pamela. Lately, when I'd first installed it, it would work fine. Later, however, it would come up and say:

"Another application (Pamela.exe) is attempting to access Skype, but we are unable to respond".

You just have to love these sorts of messages that don't give you the slightest clue about what the problem is.

While I saw the problem with Pamela, it can happen with any Skype add-in. The problem actually is related to UAC. If Skype is running as non-admin (startup app in windows) and you launch the other app as admin, you'll get this error message. Amazingly, running the add-in without admin privileges makes it work. The irony is that I was trying it as admin because I thought that would make sure it works.

Hope that helps someone else as I certainly wasted endless hours trying to work out what was wrong. And a big raspberry to the Skype guys for the "useful" error message.

I've had a number of people over the years ask about whether or not a particular type of object is a good candidate for SQL CLR integration. The rules that I normally apply are as follows:

Database Object	Transact-SQL	Managed Code
Scalar UDF	Generally poor performance	Good option when limited or no data-access
Table-valued UDF	Good option if data-related	Good option when limited or no data-access
Stored Procedure	Good option	Good option when external access is required or limited data access
DML Trigger	Good option	Rarely a good option as most perform substantial data access
DDL Trigger	OK option if only limited processing of XML EVENTDATA	Good option for extensive processing of XML EVENTDATA
Aggregate	Not possible	Good option
User-defined Data Type	Only alias types	Good option

 Scalar UDFs written in Transact-SQL are well-known for causing performance problems in SQL Server environments. Managed code is often a good option (and generally a much faster option) for implementing scalar UDFs as long as the functions do not depend on heavy data access.Table-valued Functions that are data-oriented are likely to be best implemented in Transact-SQL. A common use case for managed code in table-valued UDFs is for functions that need to access external resources such as the file system, environment variables, registry, etc.

 

Stored procedures have traditionally been written in T-SQL. Most stored procedures should continue to be written in T-SQL.  There are very few good use cases for managed code in stored procedures. The exceptions to this are stored procedures that need to access external resources or perform complex calculations. There should be consideration, however, about whether code that performs these tasks should be implemented within SQL Server at all.

 

Almost all DML triggers are heavily-oriented towards data access and are written in T-SQL. There are very few valid use cases for implementing DML triggers in managed code.

 

DDL triggers are also often data-oriented. Some DDL triggers though need to do extensive XML processing, particularly based on the XML EVENTDATA structure passed to these triggers by SQL Server. The more that extensive XML processing is required, the more likely the DDL trigger would be best implemented in managed code. Managed code would also be a better option if the DDL trigger needed to access external resources but this is rarely a good idea within any form of trigger.

 

Transact-SQL offers no concept of user-defined aggregates. These need to be implemented in managed code. A common use case for user-defined aggregates is to create functionality that exists in other database engines while migrating to SQL Server. For example, if a database engine provides a RANGE function, a replacement could be written in managed code to avoid the need to rewrite the application code. As a further example, the SQL Server Migration Assistant uses managed code to ease Oracle migration. SQL Server 2005 limited user-defined aggregates to those that could be serialized in 8KB of data. SQL Server 2008 changed this limit to 2GB and also introduced the ability to create multi-column aggregates.

 

Transact-SQL offers the ability to create alias data types but these are not really new data types. They are more like subsets (or subclasses) of existing built-in data types. Managed code offers the ability to create entirely new data types and determine not only what data needs to be stored but also the behavior of the data type. It is important to understand however that the intention of user-defined CLR data types is not to convert SQL Server into an object-oriented database. Data types should relate to storage of basic data, not to objects such as employees or customers.