Search code examples
sql-servert-sqlsqlclrsmo

Invoking SMO via T-SQL?

I'd like to use SMO to do several things that aren't easily done in T-SQL. However, all the examples I've found are for C# or Powershell. Is there an easy way to invoke SMO via T-SQL, as you could do with DMO and sp_OACreate? One way around that would be to invoke the SMO via PowerShell via xp_cmdshell - but that's going to be ugly. Suggestions?

The obvious example would be "script out a table using SMO", invoked via SQL.

Thanks.

Solution

  • Unfortunately no, SMO is not directly invoke-able via SQLCLR. Not only is it -- Microsoft.SqlServer.Smo.dll -- not in the Supported .NET Framework Libraries list, but it is specifically disallowed.

    If you try to manually import that DLL via CREATE ASSEMBLY, you get the following error:

    CREATE ASSEMBLY [SMO]
AUTHORIZATION [dbo]
FROM
N'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
WITH PERMISSION_SET = UNSAFE;

    Returns:

    Msg 6596, Level 16, State 1, Line 1
    CREATE ASSEMBLY failed because assembly 'Microsoft.SqlServer.Smo' is a system assembly. Consider creating a user assembly to wrap desired functionality.

    Ok, so try "creating a user assembly to wrap desired functionality".

    If your Assembly is marked with PERMISSION_SET = SAFE you get:

    Msg 6522, Level 16, State 2, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "SmoTest":
    System.Security.SecurityException: That assembly does not allow partially trusted callers.

    If your Assembly is marked with PERMISSION_SET = UNSAFE you get:

    Msg 6522, Level 16, State 2, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "SmoTest":
    System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application.
    System.Exception:
    at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor()
    at Microsoft.SqlServer.Management.Smo.Server..ctor()

    UPDATE:

    And, I just found this related thread from January 2007 on the MSDN forums:

    'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.