deepdark.net - James Green's Blog - Database

Visual guide to the SQL Server 2008 setup experience

James Green — Tue, 12 Feb 2008 11:44:24 GMT

Previously I had been using the (formally "Katmai") SQL Server 2008 CTPs that were distributed through MSDN as VHD files ready for Virtual PC. But today with the arrival of the November CTP (CTP 5) DVD I actually got around to the installation.

I had no problem with the hardware requirements and the install went successfully. I screen caped the interesting bits as I went...

The Launcher. First up there are some improvements to the install launcher with all the important stuff easy to find.

The Configuration Check screen is as you would expect based on SQL Server 2005.

An interesting thing to note about the Feature Selection screen is as you add and remove features the remaining steps adjust accordingly

I've skipped the Instace Configuration screen because it is just as you would expect, however the Service Accounts page has been improved with some secure defaults and it guides you towards secure choices.

Again I have skipped the Instance Configuration because it is much as you would expect. The Database Engine Configuration screen is again good at guiding you towards good choices. Interestingly TempDB location is included which is nice. Always good to have TempDB on seperate IO if possible.

Analysis Services Configuration is much the same as Database Engine Configuration, but nice to see SharePoint integrated mode is available on the Reporting Services Configuration step. Historically this use to be some black magic. I'm looking forward to testing out this option in Katmai in greater depth later.

The rest of the steps are much as you would expect based on SQL Server 2005 and not too interactive, so I have saved you the bandwidth.

And there you have it! Play time again for me :-)

Listening To: Air - Premiers Symptomes

Removing SQL Server Embedded Edition after Windows Sharepoint Services 3.0

Mon, 19 Nov 2007 10:13:12 GMT

OK I'm wheeling out the Sharepoint Annoyances category for one last random show - until next time (kinda like the Rolling Stones)

The problem comes when you remove WSS 3.0 from a box and the instance of Microsoft SQL Server 2005 Embedded Edition (SSEE) does not get removed.

This is by design, however if the reason you are removing WSS 3.0 is because of a problem with the SSEE database you have a problem.

It turns out it is easy to uninstall after all - I found the answer via Jérémie Clabaut's blog.

The good news is it is a one liner to call msiexec. Quoting Jérémie:

msiexec /x {CEB5780F-1A70-44A9-850F-DE6C4F6AA8FB} callerid=ocsetup.exe

As is documented elsewhere - don't forget to move away / delete any errant Mdf/Ldf files as they can interfere with the reinstall.

Thanks Jérémie!

Listening To: The Velvet Underground and Nico, The Velvet Underground

Null comparison on SQL Down Under

Fri, 02 Nov 2007 07:32:16 GMT

So lots of people (18 or so) are interested in talking about nothing.

Not nothing per se, but null.

Looking at the 200-odd posts to the list in the last month or so, near half of them are on the topic of null comparison, that Greg Low started with regards to his connect item.

OK, so the discussion drifted all over the topic and my contribution, fair to say did not advance the start of the art...

At the end of the day however (is it done yet guys?), it turned into a really good discussion around null, the meaning of comparison and T-SQL semantics, so thanks guys.

Trouble with the SQL Server 2008 CTP 4 VHD on MSDN

Sun, 21 Oct 2007 02:48:20 GMT

The SQL Server 2008 ("Katmai") CTP 4 VHD that was published to MSDN Subscriber Downloads last week is made available as a 4-part self-extracting RAR archive.

It can seem to have an issue in while self-extracting. As shown in the screen cap below is it looking for en_sql_server_2008_ctp_4_vhd_part_1_of_5_.rar:

This is obviously wrong because a) part 1 is not a rar - it is in the exe, and b) the download is in 4 parts not 5. So if you see this prompt, browse for en_sql_server_2008_ctp_4_vhd_part_2_of_4_.rar.

Likewise when asked for en_sql_server_2008_ctp_4_vhd_part_2_of_5_.rar browse for en_sql_server_2008_ctp_4_vhd_part_3_of_4_.rar.

Finally when prompted for en_sql_server_2008_ctp_4_vhd_part_3_of_5_.rar browse for en_sql_server_2008_ctp_4_vhd_part_4_of_4_.rar. You will not be prompted for a part 5.

Once you have done that, fire up VPC or Virtual Server 2005 and enjoy!

Also, don't forget: You can check out the LiveMeeting events on the new features in SQL Server 2008 available through the Connect site.

Listening to: Aphex Twin, Selected Ambient Works, Volume II

The Zen of T-SQL - My SQL Down Under Code Camp slides

Sun, 14 Oct 2007 12:25:08 GMT

It was great being able to have the opportunity to elucidate my thoughts on this matter formally at SQL Down Under Code Camp '07.

For completeness, here are my slides: The Zen of T-SQL SDU CC.ppt (128.5 KB)

I really enjoyed meeting so many nice and very clever folks. Thanks to Greg and all involved in putting on the Code Camp, what a really great event!

For the record, the image is of Kasprowicz catching Pietersen at NatWest Challenge Final, The Oval, 2005.

:-)

The Zen of T-SQL, an example

Fri, 21 Sep 2007 13:44:04 GMT

I came across the following example in Inside T-SQL Programming, which I am digesting at the moment. Actually it is credited to Steve Kass (technical editor):

SELECT OrderId, OrderDate
FROM dbo.Orders
WHERE DATEDIFF(day, '19000102', OrderDate) % 7 = 0

This query is to answer the question "Which orders were placed on a Tuesday?". The query isn't particularly magic, but the idea behind it is as close to the zen of T-SQL as I can remember seeing:

There are 7 days between any two Tuesdays. If we know one Tuesday (like the 2nd Jan 1900) we have all the Tuesdays since.

What a great mindset to take with querying.

Listening To: Places like this, Architecture In Helsinki

Enforcing AD Groups Only on SQL Server 2008

Thu, 09 Aug 2007 22:14:44 GMT

So I was talking with Rob Farley at Tech.Ed (Rob, you’re my hero ;-)) and the topic he raised the topic of what if we did not grant *users* access to our SQL Servers, but instead only allowed groups.

Not something I had really thought about much before but This is a really good idea™.

The question that naturally arises is: How do we enforce this with something stronger than “because I said so”.

The good news is that it appears that SQL Server 2008’s Declarative Management Framework (a.k.a. “policies”) can be of assistance here.

Background on the Declarative Management Framework:

A Facet is a piece of data about the system that you can use in your policies. 23 Facets are included in the current Katmai CTP.
Each Facet can have 2 or 10 or more Properties. It is the properties that are used in your conditions. Facets are logically groupings of properties by SQL Server
A Condition is a logical expression about a property or set or properties
A Policy is a named instance of a specified Condition with a rule on how it will be enforced (on a schedule, on changes etc).

Step 1: Create a new Condition and call it GroupsExist. Choose the Login facet and the LoginType property. Set it to equal WindowsGroup.

In the case that there are user accounts like .\ASPNET or the ##MS_PolicyExecutionLogin## login that have a legitimate right to be there, list those in the expression as illustrated below.

Step 2: Create a new Policy based on the GroupsExist condition and cal it OnlyAllowGroupsNotUsers. Apply it to Server/Login and set it to Enforce as illustrated below.

One of the really cool things is that you can create your policies in a not enabled state, and use the built-in testing harness to get the policy right before enforcing it.

To test a policy in SQL Server Management Studio, right-click on it and choose Test… This will run the policy against the current state of the server and report the details of success or failure.

Now when the policy is disabled, the following statement will do as it is designed.

CREATE LOGIN [MYLAPTOP\SampleUserAccountAccess] FROM WINDOWS GO

However when the policy is Enabled, I am getting the following error, which I assume means I am am on the right track :-)

Msg 10314, Level 16, State 11, Procedure sp_syspolicy_execute_policy, Line 25
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.DmfSqlClrWrapper, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.

Strong name validation failed. (Exception from HRESULT: 0x8013141A) ---> System.Security.SecurityException: Strong name validation failed. (Exception from HRESULT: 0x8013141A) System.Security.SecurityException: System.IO.FileLoadException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
The statement has been terminated.

I’ll research this message next week, as well as re-do the sameple when back home and connected to AD, and if the results differ I’ll re-post.

...Topic for future blogging: Ensuring consistent policies across several instances of SQL Server. Stay tuned.

Listening To: The dodgy motor in the fridge in my apartment.

T-SQL: dmy, mdy, oh my! dateformat confusion

Thu, 12 Apr 2007 00:52:12 GMT

This script came out of a discussion here at the office around a product that isn't flexible or location aware in how it writes datetime values into the database.

This is also not paying attention to storing the date data in UTC or localtime or any of those concerns, just the semantics of storing whatever the date value happens to be.

What are your experiences with this? Comments as always most welcome :-)

Example script follows:

/*
    You can use the following proc at the start of your app to see
    what the current date format SQL Server is expecting from you
    based on the default language selection set on your login.

    Look at the dateformat field in the resultset of this proc.

    Use this if you want to honour the regional selection that has
    been setup on a per-user basis on the SQL Server
    (login properties of each user)
*/
exec sp_helplanguage @@language

/*
    Otherwise, you may also override the language settings if your
    application code can only format dates one way.

    This will avoid message 241 at runtime:
        "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

    The 15th of Feb is a good example because there is no 15th month:
*/

SET LANGUAGE us_english
go -- dateformat is now = mdy

select cast('2/15/2003' as datetime) as US_1
go
select cast('15/2/2003' as datetime) as US_2 --Expect Msg 242
go

SET LANGUAGE british
go -- dateformat is now = dmy

select cast('2/15/2003' as datetime) as British_1 --Expect Msg 242
go
select cast('15/2/2003' as datetime) as British_2
go

You can grab the script here: >20070412.deepdark.net_dmy_mdy.sql (1.14 KB)

Intent Insurance part II: Checks and balances

Wed, 07 Mar 2007 22:47:03 GMT

It is probabbly wise to remember domain rules in the database when considering Intent Insurance.

A common scenario for a check constraint is to ensure the range of values in a field in cases where no foreign key constraint is available to constrain the values. The typical example is the case of a [State] field, where a check constraint on the table may look like:

[State] IN ('NSW', 'QLD', 'VIC', 'ACT', 'TAS', 'NT', 'SA', 'WA')

To contrast this with the foreign key constraint, you may be guided by personal taste or circumstance to not create a lookup table of States since it is not expected to change any time soon and would then require a JOIN every time we needed to see the State with any address.

Now to the point of the post, I just came across an example that is worth mentioning. Consider the following simple and neatly anonymous table of clients:

Imagine a business rule exists as follows:

If this client is known to us by an online referal, they must have an email address
Any client, online referal or not, can have an email address

You would naturally expect this rule to exist programatically in the presentation tier. The Intent Insurance mindset would then be to also add a check constraint to the table:

([online_referal] = 0) OR ([online_referal] = 1 AND [email_address] IS NOT NULL)

And there you have it, in one line another little parachute in the application asserting your intent. Sleep a little easier tonight :-)

More different ways to script database objects a different way

Wed, 21 Feb 2007 07:32:28 GMT

Rob Farley posted a very interesting codegen post on scripting objects this morning, and as a reforming codegen junky I just couldn't let it go without comment :-)

Firstly, I modified his query as so:



select quotename(si.name) as "@IndexName", quotename(ss.name) AS "@SchemaName", quotename(so.name) AS "@ObjectName",

    stuff((select ',' + quotename(sc.name)

            from sys.index_columns sic

            join sys.columns sc

                on sc.column_id
= sic.column_id

            where so.object_id =
sic.object_id


                and sic.index_id
= si.index_id

                and sc.object_id =
so.object_id


            order
by sic.key_ordinal

            for xml
path('')),1,1,'') as "@IndexColumns"

from sys.indexes si

join sys.objects so

    on so.object_id = si.object_id


join sys.schemas ss

    on ss.schema_id = so.schema_id

where so.type = 'U' and si.type =
1

for xml path('index'), root('indexes')

Things I'll point out:

Adding the extra for xml clause, this time specifying a better row element name than 'row' and also adding a document node
Once the outter for xml clause is in place, we can alias the columns using @ to have them come out as elements in the results

We can feed the results of that query straight into the following XSLT:

<?xml version="1.0" encoding="iso-8859-1"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output method="text"/>

    


    <xsl:template match='/'>

    <xsl:apply-templates select ='indexes/index'/>

    </xsl:template>



  <xsl:template match='index' >

create index <xsl:value-of select='@IndexName'/> 


  on <xsl:value-of select='@SchemaName'/>.<xsl:value-of select='@ObjectName'/>
(<xsl:value-of select='@IndexColumns'/>)

  </xsl:template>

  


</xsl:stylesheet>

I'm not claiming this method is superior, just different, which I think is in keeping with the spirit of Rob's post :-) and I have learned more about the improvements of the for xml clause in SQL Server 2005 in the process.

I'll leave final judgment on the utility of this approach as an exercise to the reader; my instinctive reaction is to include it as a build step to help you snapshot schema changes between check-ins, or as Rob suggests to take objects from one database and create slightly different objects in another database – there are no wrong answers and if you think of something interesting please leave a comment :-)

Grab the source files here: ScriptObjects.zip (.78 KB)

Code Generation in T-SQL

Mon, 11 Dec 2006 22:35:10 GMT

OK, so I was trying to kick this Code Generation bent I have been on of late, but… opportunity knocked yesterday for a query where one of the possible solutions involved codegen and I was weak :-)

So imagine we have some convieniently breif and neatly anonymized canonical example like the following table:

SELECT customer_id, order_id

FROM OrderCustomer

customer_id	order_id
1	32
1	33
1	34
2	821
2	831
2	851
2	861
2	871
2	911
3	1
3	2
3	3
3	4
3	5

Now imagine the project is to remove all the rows from this table, except the lowest number order for each customer. The first step is to write a query to exceptionalize these rows:

SELECT customer_id, min(order_id) AS AS LowestOrderID

FROM OrderCustomer

GROUP BY customer_id

customer_id	LowestOrderID
1	32
2	821
3	1

The next step is to select some string literals with the original query so that the result is valid T-SQL

SELECT 'DELETE FROM OrderCustomer WHERE customer_id = ', customer_id,

'AND order_id > ', min(order_id) AS LowestOrderID

FROM OrderCustomer

GROUP BY customer_id

Tangentally the little unit of joy in this whole experience for me is that the <Ctrl-T> keyboard shortcut is the same in SQL Server 2005 Management Studio as it was back in Query Analyzer.

So, hit <Ctrl-T> to output the results window as Text, then F5 to return the results to get something like:

DELETE FROM OrderCustomer WHERE customer_id = 1 AND order_id > 32

DELETE FROM OrderCustomer WHERE customer_id = 2 AND order_id > 821

DELETE FROM OrderCustomer WHERE customer_id = 3 AND order_id > 1

(2 row(s) affected)

(5 row(s) affected)

(4 row(s) affected)

Select all the DELETE FROM statements and copy and paste them into a new query window, then F5 to remove the rows.

Look at the table to test the result:

SELECT customer_id, order_id

FROM OrderCustomer

customer_id	order_id
1	32
2	821
3	1

Custom Australian Data Generator for Visual Studio Team Edition for DB Pros

Thu, 31 Aug 2006 23:16:57 GMT

Hows that for a long title?

So there is a new version of Visual Studio (in CTP at time of writing) called Visual Studio Team Edition for Database Professionals - its aimed at people who might associate themselves with the title Database Professional. I gotta say, it looks like a really good solution to some problems.

I attended a cabana session at Tech.Ed (Tech.James?) this year hosted by Greg Low on data generation in the product, and during the presentation it came up that it would be nice to have custom generator for Australian-specific data... so I thought I'd take a stab at it.

Below is a screencap of the kind of random data my generator will spit out. Handy for columns where a check constraint may be forcing a given format for the data. The format of the phone numbers is configurable by a property and the rows are conistant for a location so that if we generate a Vic address it will have a valid Vic phone number etc.

The good news is its really easy to do. Even at this early stage the product has some pretty reasonable doco on what to do to write your own generators.

So my custom generator is available for download by clicking on this link

To install it on your copy of vsdbpro:

Copy the DLL from that zip into the following path on your PC:
[Program Files]\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
Edit the config file at:
[Program Files]\Microsoft Visual Studio 8\DBPro\Microsoft.VisualStudio.TeamSystem.Data.Extensions.xml with a suitable XML editor and add the following line:
<type>deepdark.net.PersonalDetails, AustralianDetails, Version=1.0.0.0, Culture=neutral, PublicKeyToken=341d0bd35c6f7d28</type>
Restart Visual Studio, you should see a new generator called Personal Details in the list.

The source code is hardly going to start a revolutiuon, but I can make it available if there is any interest - just let me know.

How to tell if a SQL Server trigger is enabled? ...answer!

Mon, 08 May 2006 13:50:31 GMT

I posted before about trying to determine if a SQL Server trigger is enabled or disabled.

The answer came via SQLJunkies and of course comes to us by way of OBJECTPROPERTY function. The property is: ExecIsTriggerDisabled.

I learnt all this back in the SQL 7.0 days and my fav trick is to dive into the sysobjects & syscolumns tables for all kinds of metadata. I never caught the OBJECTPROPERTY and the Information Schema views.

My homework is to now get busy learning the OBJECTPROPERTY function so I can keep up with being 1 version behind :-(

XPath pre-post MkII

Tue, 25 Apr 2006 23:41:25 GMT

So I mentioned earlier that I am excited about using XPath against XML data stored in SQL Server (v2000 at the moment) but I keep coming up against the same problems, like text/ntext data types are invalid for local variables in a batch and I can't fit the documents I want inside varchar(8000).

The result with the most Google Juice on this points to the solution we all want. There needs to be a version of sp_xml_preparedocument that accepts a pointer to a text/ntext column. There are various solutions around, none of which are kind on server resources (like creating an undetermined number of varchar(8000) variables in scope) and none of which are pretty.

The chances of getting this went from slim to none last Nov. Oh well. I've yet to sink my teeth into SQL Server 2005 and I know they have done a lot with XML in that release, but Microsoft: Do you think every system and team cuts over to the new version of your products the day after launch? I understand you're excited but the same happened to VB6 when VS.NET 1.0 came out.

The project I have in mind involves taking the XML out of Excel documents and working with it using XPath and XSLT. So for now this dosen't live in the data tier. Oh well. The sun will rise on the morning.

Oh and a nod to the brainy and beautiful Anina for "google juice" links.

How to tell if a SQL Server trigger is enabled? hmmm

Mon, 17 Apr 2006 23:30:21 GMT

Maybe it's a hangover from a public holiday but I can't seem to find out from the system tables or sprocs. Man it can't be that hard!

Google here I come :-(

Stay tuned for update if I find out...

License to develop databases

Thu, 02 Mar 2006 23:01:55 GMT

I know what makes me sick and what makes me well, but that dosen't make me a doctor.

Knowledge of a specific business domain dosen't qualify you through some holy invocation to be a database designer.

Sounds like I'm taking a pretty hard line on this :)

Really it all stems from seeing people put in way too much effort to cope with bad ideas. Take for example this pretty simple rule of normalization:

Every time a row and column meet, that cell should hold one and only one bit of data.

Break that rule and you will be forever doomed to write hard to maintain queries. I was just discussing a scenario that needed to regularly update part of a field for a large number of rows. Luckily that scenario isn't seeing the light of day <phew>

So, what criteria do you think should be on the test that issues licenses to develop databases?