powershell to drop functions

Question

HI

I have requirement to drop the assembly functions and recreate it

$selfunc = invoke-sqlcmd -server $Instance -Database $db.Name -Query  "SELECT   schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] FROM sys.objects o INNER JOIN sys.module_assembly_usages ma  ON o.object_id = ma.object_id INNER JOIN sys.assemblies a ON ma.assembly_id = a.assembly_id WHERE a.name=MYASSEMBLY"

foreach($sf in $selfunc)

{

drop function $sf.CLRObjectName

}

IT IS NOT WORKING .

Kindly help how can I achive this

SUpersent

Answer 1

$selfunc = invoke-sqlcmd -server $Instance -Database $db.Name -Query  "SELECT   schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] FROM sys.objects o INNER JOIN sys.module_assembly_usages ma  ON o.object_id = ma.object_id INNER JOIN sys.assemblies a ON ma.assembly_id = a.assembly_id WHERE a.name=MYASSEMBLY"

Did you check the value of $selfunc variable? Does it list the required values? 

I guess you are missing the single quotes around MYASSEMBLY

SELECT schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName]
FROM sys.objects o
INNER JOIN sys.module_assembly_usages ma ON o.object_id = ma.object_id
INNER JOIN sys.assemblies a ON ma.assembly_id = a.assembly_id
WHERE a.name = 'MYASSEMBLY' --> MYASSEMBLY in single quotes 

---

Regards,
Vaibhav

Answer 2

drop function $sf.CLRObjectName

Because you drop only the function, not the assembly: Dropping an Assembly

---

Olaf Helper

[ Blog] [ Xing] [ MVP]