Add-Type error Meta File could not be found

Question

I've create a basic C# class that implements Microsoft.Data.Schema.ScriptDom and Microsoft.Data.Schema.ScriptDom.Sql interface. These two assemblies are part of Visual Studio Database Edition (VSDB) and are the parsing/scripting API's. You can parse SQL text and output a format SQL script. For more information about VSDB assemblies, see this blog post . Since they are redistributable, I've included both assemblies and the PowerShell script here:

#requires -version 2

add-type -path .\Microsoft.Data.Schema.ScriptDom.dll
add-type -path .\Microsoft.Data.Schema.ScriptDom.Sql.dll

$Source = @"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;
using System.IO;

    public class SQLParser
    {
        private IScriptFragment fragment;

        public SQLParser(SqlVersion sqlVersion, bool quotedIdentifier, string inputScript)
        {
            switch (sqlVersion)
            {
                case SqlVersion.Sql80:
                    SQLParser80 (quotedIdentifier, inputScript);
                    break;
                case SqlVersion.Sql90:
                    SQLParser90 (quotedIdentifier, inputScript);
                    break;
                case SqlVersion.Sql100:
                    SQLParser100 (quotedIdentifier, inputScript);
                    break;
            }
        }

        private void SQLParser100 (bool quotedIdentifier, string inputScript)
        {
            TSql100Parser parser = new TSql100Parser(quotedIdentifier);
            Parse(parser, inputScript);
        }

        private void SQLParser90 (bool quotedIdentifier, string inputScript)
        {
            TSql90Parser parser90 = new TSql90Parser(quotedIdentifier);
            Parse(parser90, inputScript);
        }

        private void SQLParser80 (bool quotedIdentifier, string inputScript)
        {
            TSql80Parser parser80 = new TSql80Parser(quotedIdentifier);
            Parse(parser80, inputScript);
        }

        private void Parse(TSql100Parser parser, string inputScript)
        {
            IList<ParseError> errors;

            using (StringReader sr = new StringReader(inputScript))
            {
                fragment = parser.Parse(sr, out errors);
            }

            if (errors != null && errors.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (var error in errors)
                {
                    sb.AppendLine(error.Message);
                    sb.AppendLine("offset " + error.Offset.ToString());
                }
                throw new ArgumentException("InvalidSQLScript", sb.ToString());
            }
        }

        private void Parse(TSql90Parser parser, string inputScript)
        {
            IList<ParseError> errors;

            using (StringReader sr = new StringReader(inputScript))
            {
                fragment = parser.Parse(sr, out errors);
            }

            if (errors != null && errors.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (var error in errors)
                {
                    sb.AppendLine(error.Message);
                    sb.AppendLine("offset " + error.Offset.ToString());
                }
                throw new ArgumentException("InvalidSQLScript", sb.ToString());
            }
        }

        private void Parse(TSql80Parser parser, string inputScript)
        {
            IList<ParseError> errors;

            using (StringReader sr = new StringReader(inputScript))
            {
                fragment = parser.Parse(sr, out errors);
            }

            if (errors != null && errors.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (var error in errors)
                {
                    sb.AppendLine(error.Message);
                    sb.AppendLine("offset " + error.Offset.ToString());
                }
                throw new ArgumentException("InvalidSQLScript", sb.ToString());
            }
        }

        public IScriptFragment Fragment
        {
            get { return fragment; }
        }


    }
"@
$refs = @("Microsoft.Data.Schema.ScriptDom","Microsoft.Data.Schema.ScriptDom.Sql")
add-type -ReferencedAssemblies $refs -TypeDefinition $Source -Language CSharpVersion3 -passThru

I'm using PowerShell V2 add-type to create a runtime type. I've tested the script on 3 different machines. On one machine the script works as expected on the other two machines the following error is produced. Both referenced assemblies are placed in the same folder as the PowerShell script. Any ideas on what I'm doing wrong?

PS C:\Users\u00\bin> .\SQLParser.ps1
Add-Type : (0) : Metadata file 'Microsoft.Data.Schema.ScriptDom.dll' could not be found
(1) : using System;
At C:\Users\u00\bin\SQLParser.ps1:125 char:9
+ add-type <<<<  -ReferencedAssemblies $refs -TypeDefinition $Source -Language CSharpVersion3 -passThru
    + CategoryInfo          : InvalidData: (error CS0006: M...ld not be found:CompilerError) [Add-Type], Exception
    + FullyQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands.AddTypeCommand

Add-Type : (0) : Metadata file 'Microsoft.Data.Schema.ScriptDom.Sql.dll' could not be found
(1) : using System;
At C:\Users\u00\bin\SQLParser.ps1:125 char:9
+ add-type <<<<  -ReferencedAssemblies $refs -TypeDefinition $Source -Language CSharpVersion3 -passThru
    + CategoryInfo          : InvalidData: (error CS0006: M...ld not be found:CompilerError) [Add-Type], Exception
    + FullyQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands.AddTypeCommand

Add-Type : Cannot add type. There were compilation errors.
At C:\Users\u00\bin\SQLParser.ps1:125 char:9
+ add-type <<<<  -ReferencedAssemblies $refs -TypeDefinition $Source -Language CSharpVersion3 -passThru
    + CategoryInfo          : InvalidData: (:) [Add-Type], InvalidOperationException
    + FullyQualifiedErrorId : COMPILER_ERRORS,Microsoft.PowerShell.Commands.AddTypeCommand

Answer 1

If you put the VSTSDB assemblies in the same dir as the script then you don't want to use "." in a relative path. "." will be relative to the directory where the script is invoked. Try something like this instead:

$ScriptDir = Split-Path $MyInvocation.MyCommand.Path -Parent
Add-Type -Path "$ScriptDir\Microsoft.Data.Schema.ScriptDom.dll"

Answer 2

Pretty simple, once you know ;-)

Max's example works because those assemblies are in the GAC so they can be referenced by name. Your assemblies aren't, so they need to be referenced by path.

You don't need the Add-Type references at the top either, at least, not for that script -- just change your last couple of lines to this:

$PSScriptRoot = (Split-Path $MyInvocation.MyCommand.Path -Parent)
$refs = @("$PSScriptRoot\Microsoft.Data.Schema.ScriptDom.dll","$PSScriptRoot\Microsoft.Data.Schema.ScriptDom.Sql.dll")
add-type -ReferencedAssemblies $refs -TypeDefinition $Source -Language CSharpVersion3 -passThru

Answer 3

I have a sample I've use during our PS track. It's kinda basic but works. Here's the code using SMO:

$Assem = ("Microsoft.SqlServer.Smo","Microsoft.SqlServer.ConnectionInfo")
$Source = @"
public class MyMSSql
{
       public static string getEdition(string sqlName)
       {
           string sqlEdition;
           Microsoft.SqlServer.Management.Smo.Server sname = new Microsoft.SqlServer.Management.Smo.Server(sqlName);
           sqlEdition = sname.Information.Edition;
           return sqlEdition;
       }
       public string getSqlEdition(string sqlName)
       {
           string sqlEdition;
           Microsoft.SqlServer.Management.Smo.Server sname = new Microsoft.SqlServer.Management.Smo.Server(sqlName);
           sqlEdition = sname.Information.Edition;
           return sqlEdition;
       }

}
"@;
Add-Type -ReferencedAssemblies $Assem -TypeDefinition $Source
[MyMSSql]::getEdition("MAX-PCWIN1")
#Developer Edition (64-bit)

$MySQLobj = New-Object MyMSSql
$MySQLobj.getSqlEdition("MAX-PCWIN1")

Hope this will give you a hint.

Max