Tag: SQL 2012

SQL, SQL 2012

SQL2012 It’s The Small Things Pt3–End Of The Month Function

If you work with financial data or have to do regular reporting type functions then I’m sure you currently use some kind of funky function/case statement to figure out what the last day of the month is and something even more funky to find the last day of next month or the month after.

SQL Server 2012 fixes that for you with the introduction of the EOMONTH function.

It’s very simple to use:

SELECT EOMONTH('2012/03/16')

image

 

Even better, the EOMONTH function can accept an additional parameter where you can specify a number of months to add or remove to get relevant results:

SELECT EOMONTH('2012/03/16', -1)

image

 

It even handled the leap year correctly.

Simplicity itself. A great addition.

SQL, SQL 2012

SQL2012 It’s The Small Things Pt2–Unified Security Experience

Back when SQL Server 2005 came out there was a big change to the way that logins were handled. No more stored procedures to create a SQL login or to allow a Windows user or group to connect to SQL. It also brought the advent of allowing SQL logins to have the same kind of policy restrictions as you had in Active Directory.

Despite these huge changes for some reason Microsoft did not implement the same kind of changes when it came to roles. You still had to use stored procedures to grant role membership, this lead to an experience where you had to manage the syntax differently:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

 

USE tempdb;

CREATE USER [sirsqlDBAs];

 

CREATE ROLE RoleTest;

exec sp_addrolemember 'RoleTest', 'sirsqlDBAs';

exec sp_droprolemember 'RoleTest', 'sirsqlDBAs';

 

With SQL 2012 this old stored procedure way of managing things has finally gone by the wayside and we now manage roles in a similar fashion to logins:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

 

USE tempdb;

CREATE USER [sirsqlDBAs];

 

CREATE ROLE RoleTest;

ALTER ROLE RoleTest ADD MEMBER [sirsqlDBAs];

ALTER ROLE RoleTest DROP MEMBER [sirsqlDBAs];

 

This doesn’t just work with database roles. Also gone is the sp_addsrvrolemember syntax (which for some reason had you specify the login first then the role, opposite how sp_addrolemember did).

ALTER SERVER ROLE DiskAdmin ADD MEMBER [sirsqlDBAs];

ALTER SERVER ROLE DiskAdmin DROP MEMBER [sirsqlDBAs];

 

Managing roles this way makes a lot more sense than trying to do things via stored procedures. In an effort to ensure that old code still works Microsoft have left the old stored procedures in, but deprecated them, so don’t expect them to work in the next release. Anyway, wouldn’t you rather work to retool your code so that your security experience is more unified?

SQL, SQL 2012

SQL2012 It’s The Small Things–Default Schemas For Groups

If you’re reading this then I’m sure you know all about SQL 2012 which just went RTM and will be available for general release on April 1st. I’m sure you’ve also heard all about some of the big new changes such as AlwaysOn with readable secondaries, ColumnStore indexes, and FileTables. Missed in all the big announcements are some of the smaller things that make a huge difference in the day to day running of SQL Server. I’m going to put together a few posts outlining some of the smaller, but to me equally significant, changes that are in the new version.

 

Back in the day there was no facility to provide a default schema to a Windows Group, you would actually get an error were you to try:

USE Master;

CREATE LOGIN [sirsqlDBAs] from windows WITH DEFAULT_DATABASE = TempDB;

 

USE tempdb;

CREATE USER [sirsqlDBAs];

 

ALTER USER [sirsqlDBAs] with DEFAULT_SCHEMA = dbo;

 

image

 

This script now works in SQL 2012 and defines the default schema

image

 

If we check out the properties of the user we can see that the default schema has been set to dbo:

image

 

 

This is a very welcome addition. Prior to SQL 2012 you would have to create a separate login and user for each user in a windows group and then set the default schema for each, that’s a lot of administrative hassle eliminated.

SQL, SQL 2012, SSRS

Please Don’t Use Deprecated Data Types

I know that a lot of vendors like to write for the lowest common denominator (i.e. SQL 2000) but really folks it’s gone too far. I’m sick of cracking open vendor code that’s certified for SQL 2008 and seeing things like IMAGE and TEXT data types. Microsoft deprecated these things back when they released SQL 2005 (see http://msdn.microsoft.com/en-US/library/ms143729(v=SQL.90).aspx under Textpointers). Why are you persisting these things six years later?

I bring this up because I’ve come across further egregious usage of these data types in vendor code yet again. The vendor in question? Microsoft.

Yes, that’s right, the folks that deprecated the data type six years ago is still using it to a large extent within the ReportServer and ReportServerTempDB databases that support SQL Server Reporting Services. Seriously Microsoft? Can you please get with the plan and fix this nonsense?

The following query, run against the ReportServer database will show 14 different tables (31 columns) using a variety of NTEXT and IMAGE data types.

select st.name as TableName, t.name as DataType, sc.name as ColumnName 

    from sys.types t 

        inner join sys.columns sc

            on t.system_type_id = sc.system_type_id

        inner join sys.tables st

            on sc.object_id = st.object_id

where 

    t.name in ('image', 'text', 'ntext')

order by 

    st.name, t.name

 

I have filed a Connect item asking Microsoft to fix this. Please go vote for it at https://connect.microsoft.com/SQLServer/feedback/details/714117/ssrs-using-deprecated-data-types-in-its-databases and help us rid the world of this old stuff.

SQL 2012

SQL 2012 – MDS IIS Issue

It’s funny, I thought my first SQL 2012 post would be something based around Clustering, AlwaysOn or admin fun but something cropped up today as regards Master Data Services (MDS) that I felt I should throw up there.

To say I’ve barely dealt with MDS is an understatement. I’ve never installed it, never worked with it, in fact I barely know what it does. As happens a request came in for it to be deployed for the dev folks to have a play around. The install appeared to have gone without any issues but then came the need to configure things using the MDS Configuration Mapper.

 

Upon launching the Configuration Mapper an error popped up as regards IIS and stopped everything in it’s tracks.

“The required .svc handler mappings are not installed in IIS”

Well gee, thanks Microsoft, that’s a really helpful error you’ve got there. Add on to that a lack of errors in the event logs and I figured it would be a dog to track down the problem.That’s Bing, not Ting – Image from http://www.flickr.com/photos/thecolourmill/3739742771/in/photostream/

First things first, I figured let’s try searching on the error, so I Bing’d it (yes people Bing, that’s my default search engine). The second result was promising, it led me to a Connect entry about the self same issue.

In the comments someone at Microsoft kindly thanked the submitter for finding the issue and stated “the fix for this issue will not be able to make this release”. Huh, never seen that before.

Fortunately there were a couple of post workarounds. The first one caught my attention. It mentions missing .svc handler mappings in IIS. A-ha, this looked promising I thought. I looked at the list of handlers, they all had 4.0 in them. Looks like a .NET thing I thought to myself.

 

I cracked open the IIS Manager and took a look at the installed Handler Mapping features. I could see .NET 2.0 components but not .NET 4.0 even though .NET 4.0 was installed on the system. This seemed like a case of .NET not being registered with IIS, something that was a serious pain back when .NET first came about and I had to deal with IIS installations (not something that I miss let me tell you).

I cracked open an elevated command prompt and drilled down to the .NET Framework 4.0 folder (cd %windir%Microsoft.NETFramework64v4.0.30319) and ran

aspnet_regiis.exe –i

which installs that version in IIS (there were no other sites on the server so I wasn’t concerned about impacting existing sites by changing their .NET version, read more about the switches before you do this).

With that complete, I did a reboot (I always like to try turning it off and back on again) and brought up the MDS Configuration Tool again. This time it was all happy and the configuration was able to continue.

 

Who said Connect was useless?