SQL 2008 Limitations

Handy to have around...
http://msdn.microsoft.com/en-us/library/ms143432.aspx

Adding assemblies at run time to rehosted workflow designer

When creating a rehosted solution for Workflow Foundation 4 you need to make whichever assemblies available at run time in the "Imports" section - but how to do this?  Either you can hard code each in your code behind, which works if you have a relatively static environment and/or access to the code, but in my case where we distribute it out to many clients I would like for them to be able to modify the list at will.  Enter custom config sections.

First, the entry in our .config file.  There are many good articles out there on the meaning behind configSections and how to do it, this is meant as a copy/paste helper. :)

  <configSections>
    <section name="DesignerImportedAssemblies" type="RehostedWorkflowDesigner.Configuration.DesignerImportedAssembliesConfigSection, RehostedWorkflowDesigner"/>
  </configSections>
 
  <DesignerImportedAssemblies>
    <ImportedAssemblies>
      <add assemblyName="mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <add assemblyName="PresentationCore, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      <add assemblyName="PresentationFramework, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      <add assemblyName="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <add assemblyName="System.Activities, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      <add assemblyName="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <add assemblyName="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <add assemblyName="System.Data.DataSetExtensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <add assemblyName="System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      <add assemblyName="System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <add assemblyName="WindowsFormsIntegration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    </ImportedAssemblies>
  </DesignerImportedAssemblies>

Then the configSection handler:
using System.Configuration;

namespace RehostedWorklowDesigner.Configuration
{
	// Config section
  public class DesignerImportedAssembliesConfigSection : ConfigurationSection
  {
    [ConfigurationProperty("ImportedAssemblies")]
    public DesignerIncludedAssembliesCollection DesignerImportedAssemblies
    {
      get { return (DesignerIncludedAssembliesCollection)base["ImportedAssemblies"]; }
    }
  }
 
  // Collection to hold all imported assemblies
  public class DesignerIncludedAssembliesCollection : ConfigurationElementCollection
  {
    protected override ConfigurationElement CreateNewElement()
    {
      return new ImportedAssemblyElement();
    }
 
    protected override object GetElementKey(ConfigurationElement element)
    {
      return ((ImportedAssemblyElement)(element)).AssemblyName;
    }
 
    public ImportedAssemblyElement this[int idx]
    { get { return (ImportedAssemblyElement)BaseGet(idx); } }

}
 
  // and the assembly information itself.
  public class ImportedAssemblyElement : ConfigurationElement
  {
    [ConfigurationProperty("assemblyName", IsKey = true, IsRequired = true)]
    public string AssemblyName
    {
      get { return (string)base["assemblyName"]; }

set { base["assemblyName"] = value; }
    }
  }
}

And lastly putting the two together and joining them to the workflow designer control via the AssemblyContextControlItem object :

AssemblyContextControlItem acci = new AssemblyContextControlItem();
acci.ReferencedAssemblyNames = new List<AssemblyName>();

acci.LocalAssemblyName = Assembly.GetExecutingAssembly().GetName();
 
 
// Pull all the referenced assemblies from the .config file.
 
Configuration.DesignerImportedAssembliesConfigSection section = (Configuration.DesignerImportedAssembliesConfigSection)ConfigurationManager.GetSection("DesignerImportedAssemblies");
 
foreach (Configuration.ImportedAssemblyElement element in section.DesignerImportedAssemblies)
{
  acci.ReferencedAssemblyNames.Add(new AssemblyName(element.AssemblyName));
}

How to see object definition in sql server

Found this neat function today - it shows you the internals of what tables a view is hitting, or the definition of a stored proc (ie sp_who2).  For example running the following will return you the definition of the service broker view:

SELECT object_definition(OBJECT_ID('sys.service_queues'))

Yields:

CREATE VIEW sys.service_queues AS  

SELECT o.name

       ,o.object_id

       ,o.principal_id

       ,o.schema_id

       ,o.parent_object_id

       ,o.type

       ,o.type_desc

       ,o.create_date

       ,o.modify_date

       ,o.is_ms_shipped

       ,o.is_published

       ,o.is_schema_published

       ,convert(smallint, o.property) AS max_readers

       ,convert(nvarchar(776), v.value) COLLATE database_default AS activation_procedure

       ,e.indepid AS execute_as_principal_id

       ,o.is_activation_enabled

       ,o.is_receive_enabled

       ,o.is_enqueue_enabled

       ,o.is_retention_enabled  

FROM sys.objects$ o  

  LEFT JOIN sys.sysobjvalues v

    ON v.valclass = 12 AND v.objid = o.object_id AND v.subobjid = 0 AND v.valnum = 0 -- SVC_QUEUEACTIVPROC  

  LEFT JOIN sys.syssingleobjrefs e

    ON e.depid = o.object_id AND e.class = 22 AND e.depsubid = 0 -- SRC_OBJEXECASOWNER  

WHERE o.type = 'SQ'

 

 Very cool stuff.

Find out last backup time for all databases

This is merely a re-post of a script done on SQL Authority that I placed on my site so I can easily find it in the future.


If you have not read any of Pinal Dave's blogs (SQL Authority), you should - he posts some very good information and knows a vast amount about SQL and it's inner workings.


 This script will display by database the last time it was backed up.

SELECT sdb.Name AS DatabaseName,

COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime

FROM sys.sysdatabases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

GROUP BY sdb.Name

Order by 2

Multi Column Report in Reporting Services

Reporting Services in all of its awesomeness is lacking in one area- multi-column (or newspaper style) reports.  It can do it, but only certain rendering formats support it.  One way around it in RS 2008+ is to use the oh so handy tablix control and the TSQL row_number() function.  Here’s how it works:
   

      1.  Create a query that returns the information you want.  Include 2 additional columns: Row Number and Column Number.  The purpose of these is to show the x/y coordinates in the tablix group.  The example query I use will pull a list of tables and columns from the information_schema view:

declare @numberOfColumns int = 3

select table_name

      ,column_name

      ,[row_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1 ) / @numberOfColumns

      ,[column_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1) % @numberOfColumns

from INFORMATION_SCHEMA.COLUMNS
     

     2. Create a new report, place a matrix (tablix) on the report body, and create the groups as follows:

3.  For the column title I set the value of the expression to show the column number returned by the query: ="Column " & Fields!column_number.Value

4.  Add some alternating background coloring to make the report more readable : =iif(RunningValue(Fields!table_name.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")

And presto!  When you view your report it will show you a nicely formatted multiple column report.  For fun, you can make the @numberOfColumns a parameter for the report and allow dynamic column counts.

Forms Authentication against Active Directory

While trying to authenticate against AD I ran into some problems and was wondering if it was my ldap connection string.  Found a great way to verify it with the Group Policy Result tool (http://www.microsoft.com/windowsxp/using/setup/expert/gpresults.mspx)

gpresult.exe /r

It lists all sorts of items about the currently logged in user - look for the "User Settings" section, and the first line shows you the exact path to your entry.  In my case:

CN=John Hennesey,CN=Users,DC=domain,DC=com

and there is an entry "group policy was applied from" - there's your server.domain.com.

and the web.config entries (no magic here - this is on thousands of web pages - this is for easy reference in the future)

<connectionStrings>

      <add name="ADConnectionString" connectionString="LDAP://server.domain.com/CN=Users,DC=domain,DC=com"/>

</connectionStrings>

<authentication mode="Forms">

      <forms name=".ADAuthCookie" timeout="10"/>

      </authentication>

<authorization>

      <deny users="?"/>

<allow users="*"/>

</authorization>

<membership defaultProvider="MyADMembershipProvider">

      <providers>

      <add name="MyADMembershipProvider" type="System.Web.Security.ActiveDirectoryMembershipProvider, System.Web, Version=2.0.0.0,         Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="ADConnectionString" attributeMapUsername="sAMAccountName"/>

      </providers>

</membership>

Couple this with the asp Login control, and all set.  Easy!

Nolocks on a view - does it nolock all the tables in the view?

I received this question from a colleague this week:

"If there is a view that is not using nolock [in the from clause] but you have a stored procedure that uses nolock when it calls the view then do all the FROMs in the view run with nolock?"


The simple answer : Yes, the nolock hint on the view should propagate down to the tables queried in the view*.


A brief visit to our favorite resource, Books Online, reveals the following:

In SQL Server 2005/2008, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

 And the asterisk:

*If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

And my (rather simple) test proved it out:

use master

go

create database lockTest

go

use lockTest

go

create table t1 (f1 int, f2 int)

go

insert into t1 values (1, 1)

insert into t1 values (2, 2)

go

create view v1 as

select *

from t1

go

set transaction isolation level serializable

go

begin tran

select * from v1

insert into v1 values (3,3)

-- go into a new window and run the following:

/*

-- Works

select *

from v1 with (nolock)

-- Hangs

select *

from v1

*/

/*

-- cleanup

use master

go

drop database lockTest

go

*/