6

I know how to script a stored procedure using PowerShell and SMO:

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  | out-null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" 
$srv.databases['MyDatabase'].StoredProcedures['MyProc'].TextBody

But since I want to use the procedure text within a query, I wonder whether I can call SMO in some way within pure T-SQL.

Must only run in development environment.

BTW: A function returning the definition of a stored procedure with a given name would solve my current problem but not answer my question.

Edit:

I want to find which procedures reference a given table when I use this query:

SELECT p.name --, definition 
FROM sys.sql_modules m
join sys.objects p on m.object_id = p.object_id
where p.type = 'P'
and definition like '%SearchForThis%'

That is exactly the same information I want to get using SMO.

8

You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005.

It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.

This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess one reason why you asked this. Again, CLR or sp_OA% will do it.

One thing to note is that almost every method and property in SMO maps to a SQL command or query. So using T-SQL to call SMO which is effectively T-SQL is circular.

And to get the stored procedure definition you'd use OBJECT_DEFINITION... to get the other properties available in SMO you'd use OBJECT_PROPERTY or query the system objects.

  • Could you elaborate please? The SMO assemblies don't seem to be available from inside (from stored CLR procedures). Can I add them in some way? Is it a safe thing to do? – GSerg Jan 24 '12 at 8:59
  • @GSerg: what are you trying to do? You don't need to use SQL to call CLR to call SMO? Everything available via SMO is already in raw T-SQL – gbn Jan 24 '12 at 11:30
  • Alas, it's not. I've got a CLR stored procedure, from inside of which I must figure the default value of the given parameter of the given SQL stored procedure. This information is not available via SQL, because sys.parameters only contains default values for CLR objects; for SQL objects documentation suggests parsing the result of OBJECT_DEFINITION. On contrary, SMO returns the default value of an SQL object's parameter just fine. – GSerg Jan 24 '12 at 12:04
  • 1
    Well. I've finally tricked my SQL Server project into allowing me to reference SMO. And what do I get when I execute my CLR function on the server: System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application. (at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor(SqlConnection sqlConnectionObject)). Apparently your advice to employ CLR for this particular purpose is not valid after all :( – GSerg Jan 24 '12 at 15:16
  • 1
    Hey there. @GSerg is correct: the SMO library is specifically blocked if it detects that it is being called from within SQL Server's CLR host. I answered a duplicate of this question on S.O. last year with the same findings: Invoking SMO via T-SQL?. There is no work around that will allow SMO to work in SQLCLR. – Solomon Rutzky Oct 7 '16 at 6:19
0

As it relates to the question as stated (and not the desire as stated in the "Edit"):

NO, it is not possible to call SMO from T-SQL. It cannot be done via the OLE Automation stored procedures (i.e. sp_OA*), nor via SQLCLR.

OLE Automation

I'm not sure what the OLEComponent name would be, but you can create objects by specifying their clsid. Of course, finding that for SMO isn't easy either, but I did find two references out in the inter-webz:

  1. what is the CLSID and PROG ID of Microsoft.sqlserver.Management.Smo (on Stack Overflow)
  2. To create.NET object from simple JScript. (on Programmer's Town)
DECLARE @ObjectToken INT, @HResult INT;
DECLARE @Source VARCHAR(500), @Description VARCHAR(500);

EXEC @HResult = sp_OACreate '{603F0A84-5631-3CA0-BCE0-C96597C2C1A0}', @ObjectToken OUT;
--EXEC @HResult = sp_OACreate '{755F7A20-E39C-31E0-8174-2E430C2C4E7A}', @ObjectToken OUT;

IF (@HResult <> 0)
BEGIN
   EXEC sp_OAGetErrorInfo @ObjectToken, @Source OUT, @Description OUT;
   RAISERROR('OLE Automation error. HResult: %x; @Source: %s; @Description: %s',
             16, 1, @HResult, @Source, @Description);
   RETURN;
END;

EXEC sp_OADestroy @ObjectToken;

Both values return:

Msg 50000, Level 16, State 1, Line 14
OLE Automation error. HResult: 80040154; @Source: ODSOLE Extended Procedure; @Description: Class not registered

Even though SMO is COM-visible, I suspect that it not working here is related to the following requirement, which is stated in the MSDN page for sp_OACreate

The specified OLE object must be valid and must support the IDispatch interface.

The SMO library does not appear to implement the IDispatch interface.

Even if someone does manage to find a way to get this to work, it is still ill-advised considering that the OLE Automation stored procedures have been deprecated since the release of SQL Server 2005.

SQLCLR

While SQLCLR would seem like the natural way to go with SMO (since SMO is also .NET), this definitely does not work as the SMO library specifically detects if it is being used within SQL Server, and if so, aborts with the following error message:

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()

There is no way around this (well, unless you are Microsoft ;).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.