How to manually deploy SQL Server CLR assembly

The following is the process of manually deploying the SQL Server CLR assembly. Here, we will make a record for you to read it at any time and provide reference for those who have no manual deployment experience, if you have any more convenient and intelligent methods, thank you!

Previously, deploying SQL Server CLR assembly with VS was simple and easy. Now the server is deployed on the Intranet, and you must manually update and deploy SQL Server CLR assembly.

Start to think of alter assembly [ASSEMBLY name] FROM 'G: \ AA \ XX. in this way, the dll is OK. I didn't expect an error and the more complicated it is to query the information. It seems that the manual deployment is far less as simple as I think, so I did a good research and finally succeeded. The specific steps are as follows:

1. If you have previously deployed the current Assembly, you must first delete all modules that reference the current Assembly (various functions and stored procedures) and then delete the current Assembly.

2. redeploy the current Assembly, and then create relevant modules (various functions and stored procedures ).

The following is an SQL template. You can set it as needed:

 
 

  
  
-- ====================================== Delete first ====== ===================================== --
  
  
-- MyCLRSqlserver indicates the Assembly deployment name.
  
  

  
  
If exists (SELECT * FROM sys. assemblies asms WHERE asms. name = n' MyCLRSqlserver 'and is_user_defined = 1)
  
  
BEGIN
  
  
-- Delete a set function.
  
  
If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [StrJoin] ') AND type = n' AF ')
  
  
Drop aggregate dbo. StrJoin;
  
  
-- Delete a scalar function.
  
  
If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [___ fun_CLR_ChangeSalarieScope] ') AND type in (n'fn', n'if', n'tf', n'fs', n'ft '))
  
  
Drop function dbo. ___ fun_clr_changesalari.pdf;
  
  
-- Delete other...
  
  

  
  
-- Uninstall the assembly.
  
  
Drop assembly [MyCLRSqlserver];
  
  
END;
  
  

  
  
-- ==================================== Recreate ===================================== --
  
  

  
  
-- Register the Assembly (if it is deployed on the server, copy the dll to the server and modify the file address ).
  
  
Create assembly [MyCLRSqlserver] FROM 'G: \ AA \ XX. dll ';
  
  

  
  
-------------------- [Dbo]. [StrJoin] ----------------------
  
  
Create aggregate [dbo]. [StrJoin]
  
  
(@ Value [nvarchar] (4000 ))
  
  
RETURNS [nvarchar] (4000)
  
  
External name [MyCLRSqlserver]. [MyCLRSqlserver. StrJoin]
  
  
GO
  
  

  
  
EXEC sys. sp_addextendedproperty @ name = n' autodeployed', @ value = n' yes ', @ level0type = n' SCHEMA', @ level0name = n' dbo ', @ level1type = n' aggregate', @ level1name = n' strjoin'
  
  
GO
  
  

  
  
EXEC sys. sp_addextendedproperty @ name = n' sqlassemblyfile', @ value = n' StrJoin. cs ', @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' aggregate', @ level1name = n' strjoin'
  
  
GO
  
  

  
  
EXEC sys. sp_addextendedproperty @ name = n' SqlAssemblyFileLine ', @ value = 14, @ level0type = n' SCHEMA', @ level0name = n' dbo', @ level1type = n' aggregate ', @ level1name = n' strjoin'
  
  
GO
  
  
-- Other function creation processes...
  
  

  
  
-- ===================================================== ===================================== --
 
 

The above is a difficult manual creation process. Of course, if there are not many modules, this is okay. If there are many, you can check the system view of the current database assembly_modules to obtain all CLR modules, then write the code to automatically generate the DROP and CREATE processes for the module.