Category Archives: 2679

How To Add And Remove SQL Server 2008 / 2008R2 Instances

After installing Visual Studio 2010 I, inadvertently ended up with SQL Server 2008 Express having already installed SQL Server 2008 Developer. Only when I was going to upgrade to SQL Server 2008R2 I found out I had two instances installed.

I looked everywhere for a place to remove the SQLEXPRESS instance but couldn’t find it.

Only when I decided to uninstall the entire SQL Server suite and start over I found how to do it.

On Windows 7, go to Programs and Features and choose Microsoft SQL Server 2008 R2 (64-bit) (or whatever your version of SQL Server you want to add or remove and instance from).

Choose Remove (or Add, if you want to add a new instance).

Follow the steps and choose which instance you want to remove.

Select the features of that instance you want to remove (select all to remove the instance).
Note: Don’t remove shared features if you don’t want to remove any other instances.

It’s probably something I should have known, but I didn’t and it wasn’t easy to find.

Playing With SQL Server CLR Integration – Part IV (Deploying To SQL Server 2005)

With all developed and tested on my laptop using SQL Server 2008, it’s time to deploy to the company’s test machine running SQL Server 2005.

The first thing I ran into when executing:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO



was:



Msg 10327, Level 14, State 1, Line 1
Assembly 'MyAssembly' references assembly 'system.xml.linq, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.',
which is not present in the current database.
SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from,
but that operation has failed (reason: 2(The system cannot find the file specified.)).
Please load the referenced assembly into the current database and retry your request.


Looks like SQL Server 2005 doesn’t know about .NET Framework 3.5. I’d just load the assemblies being used: System.Core and System.Linq.Xml:



CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = SAFE
GO


Not so easy:



Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Diagnostics.Eventing.EventProvider::EtwRegister][mdToken=0x600003b][offset 0x0000003D][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::EncodeObject][mdToken=0x6000046][offset 0x00000000] Unmanaged pointers are not a verifiable type.
[ : System.Diagnostics.Eventing.EventProvider::WriteMessageEvent][mdToken=0x6000047][offset 0x0000003C][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x0000012E] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x00000030] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004a][offset 0x0000005F][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004b][offset 0x00000010][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x0000007D] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x00000309][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004d][offset 0x0000001B][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Security.Cryptography.CapiNative::ImportSymmetricKey][mdToken=0x60007c2][offset 0x00000071][found address of Byte] Expected numeric type on the stac...


Ok. I’d just load it with PERMISSION_SET = UNSAFE:



CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = UNSAFE
GO



Not yet:



Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server.
If not, use sp_changedbowner to fix the problem.


Solved:



ALTER DATABASE MyDatabase SET TRUSTWORTHY ON
GO


Only than I was able to load the .NET 3.5 assemblies:



CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [System.Xml.Linq]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll'
WITH PERMISSION_SET = UNSAFE
GO


With all dependencies in place, I tried to load the assembly with PERMISSION_SET = SAFE with no luck:



Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ShortPropsToXml' on type 'ShortProps'  in safe assembly 'Esi.SA.Encyclopedia' is storing to a static field.
Storing to a static field is not allowed in safe assemblies.


It had to be with PERMISSION_SET = UNSAFE.



After successfully loading the assemblies, I was finally able to create the Transact-SQL definitions for the functions (see Part I and Part II).



Now the DBAs won’t definitely let me use this, but it was fun to build it.

Playing With SQL Server CLR Integration – Part III

You might have noticed that I used LINQ in my last Playing With SQL Server CLR Integration posts (Part I, Part II).

I couldn’t make it work with the standard Visual Studio 2008 SQL CLR project template. Changing the Target Framework to .NET Framework 3.5 wasn’t enough. I had to edit the .csproj file by hand:

<?xml version="1.0" encoding="utf-8"?> <Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <!— ... -->

<ItemGroup> <!— ... --> <Reference Include="System.Core"> <RequiredTargetFramework>3.5</RequiredTargetFramework> </Reference> <Reference Include="System.Xml.Linq"> <RequiredTargetFramework>3.5</RequiredTargetFramework> </Reference> </ItemGroup> <!— ... --> </Project>


Playing With SQL Server CLR Integration – Part II

On my last post, I showed how to convert a property bag stored in text to a CLR Table-Valued Function.

I started thinking that I could retrieve the property values, but I couldn’t change them or add new properties.

Passing a table as a parameter is still not possible in SQL Server 2005. And that would force me to load the table into a variable, change it and load it back into the property bag.

Or I could create functions to Create, Update and Delete properties from the property bag.

Or I could just use XML. All it takes is creating a CLR Scalar-Valued Function to convert the property bag to an XML representation and another to convert the XML back to the property bag format.

It still makes me load the property bag into a variable if I want to change it, but it’s usable in SQL Server 2005, which was the targeted platform.

Converting the property bag to an XML document is easy using the previously created enumerator:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToXml",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlXml ShortPropsToXml(string shortPropsText)
{
    var xml = new XElement("ShortProps",
        from shortProp in ShortPropsEnumerable(shortPropsText)
        select new XElement("p",
            new XAttribute("n", shortProp.Key),
            new XCData(shortProp.Value)));

    using (var buffer = new MemoryStream())
    {
        using (var xmlWriter = XmlWriter.Create(buffer, new XmlWriterSettings { CheckCharacters = false }))
        {
            xml.WriteTo(xmlWriter);
        }

        buffer.Position = 0;

        using (XmlReader xmlReader = XmlReader.Create(buffer, new XmlReaderSettings { CheckCharacters = false }))
        {
            return new SqlXml(xmlReader);
        }
    }
}


Converting the an XML document back into the property bag format is also easy:



[Microsoft.SqlServer.Server.SqlFunction(
    Name = "XmlToShortProps",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlChars XmlToShortProps(SqlXml shortPropsXml)
{
    var xml = XDocument.Parse(shortPropsXml.Value);

    var textBuilder = new StringBuilder();
    foreach (var item in xml.Document.Element("ShortProps").Elements("p"))
    {
        textBuilder.AppendFormat("[[[{1}]]]{0}{2}{0}", Environment.NewLine, item.Attribute("n").Value, item.Value);
    }

    return new SqlChars(textBuilder.ToString().ToCharArray());
}


Now it’s just uploading the new version of the assembly to the database:



ALTER ASSEMBLY [MyAssembly]
FROM '...\MyAssembly.dll'
GO


And defining the Scalar-Valued Functions in Transact-SQL:



CREATE FUNCTION [dbo].[ShortPropsToXml](@shortPropsText [nvarchar](4000))
RETURNS [xml] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[ShortPropsToXml]
GO

CREATE FUNCTION [dbo].[XmlToShortProps](@shortPropsXml [xml])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[XmlToShortProps]
GO



And it’s all set to go.



Now, given this property bag definition:



declare @text nvarchar(max)='[[[name1]]]
value1
[[[name2]]]
value2
[[[name3]]]
value3
'


I can change it into XML:



DECLARE @xml [xml] = dbo.ShortPropsToXml(@text)


  • Update:
    set @xml.modify('replace value of (/ShortProps/p[@n="name2"]/text())[1] with "new value2"')
    
  • Insert:
    set @xml.modify('insert <p n="name4">Value4.1
    Value4.2</p> after (/ShortProps/p[@n="name2"])[1]')
  • Delete
    set @xml.modify('delete (/ShortProps/p[@n="name3"])[1]')
  • Convert back to the property bag format:

    print dbo.XmlToShortProps(@xml)
    [[[name1]]]
    value1
    [[[name2]]]
    new value2
    [[[name4]]]
    Value4.1
    Value4.2


  • Query as a table:

    select T.C.value('./@n', 'nvarchar(max)') as Name, T.C.value('.', 'nvarchar(max)') as Value from @xml.nodes('/ShortProps/p') T(C)
    Name Value
    name1 value1
    name2 new value2
    name4 Value4.1
    Value4.2


Unfortunately, the application uses characters that are invalid to SQL Server as XML characters and I can’t use it on that application. I’ll have to understand those values better and add other fields to the table and XML.

Playing With SQL Server CLR Integration – Part I

I’m currently working with an application that stores a property bag in a SQL Server column like this:

[[[name1]]]
value1
[[[name2]]]
value2.1
value2.2
[[[name3]]]
value3



Don’t ask me why it’s done like this. It just is.



The application decodes this property bag into its inner structures and all works fine.



Sometimes I would like to query the database directly or do some reporting on those properties and just can’t.



So, I thought this was a good use case for SQL Server CLR Integration. I decided to build a CLR Table-Valued Function that would return the property bag as a two column table.



Parsing the property bag text can easily be achieved with a simple regular expression:



new Regex(
        string.Format(@"(?<Name>(?<=\[\[\[).*(?=\]\]\]{0}))\]\]\]{0}(?<Value>(([\s\S]*?(?={0}\[\[\[))|([\s\S]*?(?={0}$))))", Environment.NewLine),
        RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.CultureInvariant | RegexOptions.Compiled);


Ultrapico’s Expresso was a big help when developing this regular expression.



In case you don’t know, the way CLR Table-Valued Function are built is using an initial method to take the input and return an IEnumerable and row filler method the receives the enumerator item and outputs the column values.



Since these are really key-value pairs of strings, I decided to use )>) Structure" href="http://msdn.microsoft.com/library/5tbh8a42.aspx" target=_blank>KeyValuePair<string, string> instances to store each item and the enumerator became simply this:



private static IEnumerable<KeyValuePair<string, string>> ShortPropsEnumerable(string shortPropsText)
{
    return from Match m in shortPropsRegex.Matches(shortPropsText)
           select new KeyValuePair<string, string>(m.Groups["Name"].Value, m.Groups["Value"].Value);
}


And the implementation of the CLR Table-Valued Function is as simple as this:



[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToTable",
    FillRowMethodName = "ShortPropsToTableFillRow",
    TableDefinition = "Name NVARCHAR(4000), Value NVARCHAR(4000)",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static IEnumerable ShortPropsToTable(string shortPropsText)
{
    return ShortPropsEnumerable(shortPropsText);
}

public static void ShortPropsToTableFillRow(object item, out SqlChars name, out SqlChars value)
{
    KeyValuePair<string, string> shortProp = (KeyValuePair<string, string>)item;

    name = new SqlChars(shortProp.Key);
    value = new SqlChars(shortProp.Value);
}



To use this in SQL Server a few simple steps are need:



  1. Load the assembly into the database:
    CREATE ASSEMBLY [MyAssembly]
    AUTHORIZATION [dbo]
    FROM '...\MyAssembly.dll'
    WITH PERMISSION_SET = SAFE
    GO
  2. CREATE FUNCTION [dbo].[ShortPropsToTable](@shortPropsText [nvarchar](4000))
    RETURNS  TABLE (
        [Name] [nvarchar](4000) NULL,
        [Value] [nvarchar](4000) NULL
    ) WITH EXECUTE AS CALLER
    AS 
    EXTERNAL NAME [MyAssembly].[ShortProps].[ShortPropsToTable]
    GO
    
  3. Enable CLR Integration:
    EXEC sp_configure 'clr enabled', 1
    GO
    RECONFIGURE
    GO
    


And all is set to go.



Now I can just query the property bag as a table:



SELECT
    e.[ID],
    e.[Name],
    e.[Class],
    e.[Type],
    p.[Name],
    p.[Value]
FROM
    dbo.Entity as e
    CROSS APPLY dbo.ShortPropsToTable(e.[ShortProps]) as p


Just for curiosity, for a little over 50000 entities (that can be retrieved on about 1 second on my laptop), I got a little under 630000 properties in les then 40 seconds.



40 seconds might seem a lot compared to the 1 second, but I would like to see better times using T-SQL. And develop and test the TVF in just a couple of hours.



Now, if only he DBAs would allow me to use it. It doesn’t fit into the company’s “security policy”, you know.

Tip: Connecting To A Database Using Windows Authentication With Different Credentials Using SQL Server Management Studio

It is a good security practice to use Windows Authentication to connect to SQL Server because you don’t need to write the password on some configuration file or registry entry.

This practice also brings governance benefits. Managing users becomes part of domain administration and not part of each SQL Server instance’s administration.

But this becomes an hassle to users (in this context, the user is someone that needs to perform administration task of some kind – a SQL Server Management Studio user) who need to connect to databases using different credentials.

One workaround is using the runas command:

runas /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"



But if you are working on an environment were there are several domains and your machine does not belong to the domain of the ser account you want to use, you’ll get the folloing error:




RUNAS ERROR: Unable to run - C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
1787: The security database on the server does not have a computer account for this workstation trust relationship.



But not everything is lost. Because you want to make a remote connection, you can use the /netonly switch, and it works just fine:




runas /netonly /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"



SQL Server Management Studio still shows all the databases in the server (unlike what happens if you connect from a machine logged in as the user you want to access to the SQL Server instance), but ApexSQL Edit will only show the databases that account has access to.


The Architecture Tool Space Keeps Growing

The tool space for software architecture has been growing. There are new tools and continuous improvement in the existing ones.

Framework Design Studio

Last month Krzysztof Cwalina from Microsoft released the Framework Design Studio at MSDN Code Gallery.

The tool allows comparing two versions of an assembly to identify API differences: API additions and removals. Comparing versions of APIs comes very handy during API design process. Often you want to ensure that things did not get removed accidentally (which can cause incompatibilities), and as APIs grow, you want to review the addition without having to re-review APIs that were already reviewed. The tool, called Framework Design Studio (FDS) supports these scenarios. – from Krzysztof’s blog post

Lattix

Lattix keeps improving its Architecture Management System composed of two tools:

The Lattix tools can be used in the following environments:

The site has lots of resources on its getting started section that can be used to evaluate the tools:

  • Download a trial version of Lattix LDM
  • Take the tour
  • See a live Demonstration
  • Assess your software
  • Try a JumpStart project

The latest release (Lattix 4.1) was released on April 21, 2008.

NDepend

NDepend, from fellow C# MVP Patrick Smacchia, targets only the .NET Framework (stay tuned for more) is also in continuous development. Its UI is more familiar for Microsoft Visual Studio and Microsoft Office users than Lattix’s UI and a really cool feature:

The site has lots of videos (both on the Home page and Getting Started page) and documentation on all the concepts used by the tool.

The latest release (NDepend 2.8.1) was released on April 26, 2008.

ApexSQL Enforce

ApexSQL Enforce, from ApexSQL, targets SQL Server databases allows to:

  • Ensure adherence to design standards in "real time"
  • Facilitate upgrades to new versions of SQL Server by identifying potential conversion problems before the database is ported
  • Automatically reverse or fix detected violations using generated "FixSQL" scripts
  • Enforce Naming conventions and ensure that SQL reserved words and special characters are not used

The site has lots of product information:

The latest release (2008.02) was released on Marsh 26, 2008.

Techdays Portugal 2008 – For A World 2.0

Microsoft Portugal will be hosting Techdays 2008 from Mars 12nd to 14th, right after the Portuguese launch of Microsoft Visual Studio 2008, Microsoft SQL Server 2008 and Microsoft Windows Server 2008 on Mars 11th.

This is the biggest and greatest tech event in Portugal and there will be lots of national and international speakers and contests.

I’ll be there. Come visit us.