USE GateStore
GO
if @@ERROR=0 SET NOEXEC OFF else SET NOEXEC ON
GO
exec sp_configure 'clr enabled', 1
reconfigure
GO
ALTER DATABASE CURRENT SET TRUSTWORTHY ON
GO
if @@ERROR=0 SET NOEXEC OFF else SET NOEXEC ON
GO
set nocount on
declare @AssemblyName varchar(100) = 'SQLfuncs'
declare @objId int
declare @objName varchar(100)
declare @objType varchar(2)
declare @script varchar(2014)
declare @restable table(OperationResult varchar(4096))
insert @restable values('delete of objects on DataBase "'+DB_NAME()+'" from assembly "'+@AssemblyName+'"')
declare CLR_Deleter cursor local forward_only
for select
SO.id as [InnerID]
,SO.name as [InnerName]
,SO.[type] as [InnerType]
from dbo.sysobjects SO
join sys.assembly_modules AM on AM.object_id = SO.id
where AM.assembly_id in (select assembly_id from sys.assemblies where name=@AssemblyName)
open CLR_Deleter
fetch next from CLR_Deleter into @objId, @objName, @objType
while @@FETCH_STATUS = 0
begin
-----------------------------------------------------------------------------------------------
set @script=''
if @objType in ('FS', 'FT') set @script = 'IF OBJECT_ID ('''+@objName+''', '''+@objType+''') IS NOT NULL DROP FUNCTION [dbo].['+@objName+']' else
if @objType in ('PC') set @script = 'IF OBJECT_ID ('''+@objName+''', '''+@objType+''') IS NOT NULL DROP PROCEDURE [dbo].['+@objName+']'
if @script<>''
begin
begin try
EXECUTE (@script)
insert @restable values('successful deleteing of the object: '+@objName+' ('+@objType+', '+CAST(@objId as varchar)+')')
end try
begin catch
insert @restable values('Error deleteing of the object: '+@objName+' ('+@objType+', '+CAST(@objId as varchar)+')')
end catch
end
else insert @restable values('Unknown object: '+@objName+' ('+@objType+', '+CAST(@objId as varchar)+')')
-----------------------------------------------------------------------------------------------
fetch next from CLR_Deleter into @objId, @objName, @objType
end
close CLR_Deleter;
deallocate CLR_Deleter;
----select OperationResult from @restable
print 'Удаление объектов для сборки "'+@AssemblyName+'" завершено'
GO
if Exists(SELECT * FROM sys.assemblies where Name='SQLfuncs.XmlSerializers')
DROP ASSEMBLY [SQLfuncs.XmlSerializers]
GO
if Exists(SELECT * FROM sys.assemblies where Name='SQLfuncs')
DROP ASSEMBLY [SQLfuncs]
GO
if Exists(SELECT * FROM sys.assemblies where Name='newtonsoft.json')
DROP ASSEMBLY [newtonsoft.json]
GO
if Exists(SELECT * FROM sys.assemblies where Name='System.Runtime.Serialization')
DROP ASSEMBLY [System.Runtime.Serialization]
GO
if Exists(SELECT * FROM sys.assemblies where Name='System.Net.Http')
DROP ASSEMBLY [System.Net.Http]
GO
print '---- закончено удаление функций и сборок -----'
GO
if not Exists(SELECT * FROM sys.assemblies where Name='System.Net.Http')
begin
CREATE ASSEMBLY [System.Net.Http] AUTHORIZATION [dbo] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Net.Http.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка System.Net.Http успешно создана'
end
else
BEGIN TRY
ALTER ASSEMBLY [System.Net.Http] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Net.Http.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка System.Net.Http успешно обновлена'
END TRY
BEGIN CATCH
print '--> сборка System.Net.Http идентична предыдущей и не требует обновления'
END CATCH
GO
if not Exists(SELECT * FROM sys.assemblies where Name='System.Runtime.Serialization')
begin
CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION [dbo] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка System.Runtime.Serialization успешно создана'
end
else
BEGIN TRY
ALTER ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка System.Runtime.Serialization успешно обновлена'
END TRY
BEGIN CATCH
print '--> сборка System.Runtime.Serialization идентична предыдущей и не требует обновления'
END CATCH
GO
if not Exists(SELECT * FROM sys.assemblies where Name='newtonsoft.json')
begin
CREATE ASSEMBLY [newtonsoft.json] FROM 'D:\CLR\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка Newtonsoft.Json успешно создана'
end
else
BEGIN TRY
ALTER ASSEMBLY [newtonsoft.json] FROM 'D:\CLR\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка Newtonsoft.Json успешно обновлена'
END TRY
BEGIN CATCH
print '--> сборка Newtonsoft.Json идентична предыдущей и не требует обновления'
END CATCH
GO
if not Exists(SELECT * FROM sys.assemblies where Name='SQLfuncs')
begin
CREATE ASSEMBLY [SQLfuncs] FROM 'D:\CLR\SQLfuncs.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка SQLfuncs успешно создана'
end
else
BEGIN TRY
ALTER ASSEMBLY [SQLfuncs] FROM 'D:\CLR\SQLfuncs.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка SQLfuncs успешно обновлена'
END TRY
BEGIN CATCH
print '--> сборка SQLfuncs идентична предыдущей и не требует обновления'
END CATCH
GO
if not Exists(SELECT * FROM sys.assemblies where Name='SQLfuncs.XmlSerializers')
begin
CREATE ASSEMBLY [SQLfuncs.XmlSerializers] FROM 'D:\CLR\SQLfuncs.XmlSerializers.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка SQLfuncs.XmlSerializers успешно создана'
end
else
BEGIN TRY
ALTER ASSEMBLY [SQLfuncs.XmlSerializers] FROM 'D:\CLR\SQLfuncs.XmlSerializers.dll' WITH PERMISSION_SET = UNSAFE
print '--> сборка SQLfuncs.XmlSerializers успешно обновлена'
END TRY
BEGIN CATCH
print '--> сборка SQLfuncs.XmlSerializers идентична предыдущей и не требует обновления'
END CATCH
GO
CREATE FUNCTION [dbo].[CLR_HttpGetBlobNew](@url [nvarchar](max))
RETURNS varbinary(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[ReadBlob]
GO
IF OBJECT_ID ('CLR_HttpGetBlobNew', 'FS') IS NOT NULL
print 'CLR_HttpGetBlobNew ready'
GO
CREATE FUNCTION [dbo].[CLR_HttpGet](@url [nvarchar](max), @headers [nvarchar](max))
RETURNS varbinary(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[HttpGet]
GO
IF OBJECT_ID ('CLR_HttpGet', 'FS') IS NOT NULL
print 'CLR_HttpGet'
GO
CREATE FUNCTION [dbo].[CLR_HttpGetBlobMethod](@url [nvarchar](max),@method [nvarchar](max),@headers [nvarchar](max),@parameters [nvarchar](max),@contenttype [nvarchar](max))
RETURNS varbinary(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[ReadBlobMethod]
GO
IF OBJECT_ID ('CLR_HttpGetBlobMethod', 'FS') IS NOT NULL
print 'CLR_HttpGetBlobMethod ready'
GO
CREATE FUNCTION [dbo].[CLR_HttpGetBlobMethodEx](@url [nvarchar](max),@method [nvarchar](max),@headers [nvarchar](max),@parameters [nvarchar](max),@contenttype [nvarchar](max))
RETURNS TABLE (result varbinary(max), debug nvarchar(max), header nvarchar(max))
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[ReadBlobMethodEx]
GO
IF OBJECT_ID ('CLR_HttpGetBlobMethodEx', 'FT') IS NOT NULL
print 'CLR_HttpGetBlobMethodEx ready'
GO
-- Для совместимости. Новая AsWin1251 ------------
CREATE FUNCTION [dbo].[CLR_UnicodeToUTF8](@source [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsWin1251]--[UnicodeToUTF8]
GO
IF OBJECT_ID ('CLR_UnicodeToUTF8', 'FS') IS NOT NULL
print 'CLR_UnicodeToUTF8 ready'
GO
-- Для совместимости. Новая AsUTF8 ------------
CREATE FUNCTION [dbo].[CLR_UTF8ToUnicode](@source [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsUTF8]--[UTF8ToUnicode]
GO
IF OBJECT_ID ('CLR_UTF8ToUnicode', 'FS') IS NOT NULL
print 'CLR_UTF8ToUnicode ready'
GO
CREATE FUNCTION [dbo].[CLR_AsWin1251](@source [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsWin1251]
GO
IF OBJECT_ID ('CLR_AsWin1251', 'FS') IS NOT NULL
print 'CLR_AsWin1251 ready'
GO
CREATE FUNCTION [dbo].[CLR_AsUTF8](@source [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsUTF8]
GO
IF OBJECT_ID ('CLR_AsUTF8', 'FS') IS NOT NULL
print 'CLR_AsUTF8 ready'
GO
CREATE FUNCTION [dbo].[CLR_JSONtoXML](@source [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[JSONtoXML]
GO
IF OBJECT_ID ('CLR_JSONtoXML', 'FS') IS NOT NULL
print 'CLR_JSONtoXML ready'
GO
CREATE FUNCTION [dbo].[CLR_XMLtoJSON](@source [nvarchar](max)) --------------------------------------------------------
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[XMLtoJSON]
GO
IF OBJECT_ID ('CLR_XMLtoJSON', 'FS') IS NOT NULL
print 'CLR_XMLtoJSON ready'
GO
CREATE FUNCTION [dbo].[CLR_URLEncode](@source [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[URLEncode]
GO
IF OBJECT_ID ('CLR_URLEncode', 'FS') IS NOT NULL
print 'CLR_URLEncode ready'
GO
CREATE FUNCTION [dbo].[CLR_URLDecode](@source [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[URLDecode]
GO
IF OBJECT_ID ('CLR_URLDecode', 'FS') IS NOT NULL
print 'CLR_URLDecode ready'
GO
CREATE FUNCTION [dbo].[CLR_EmarsysRequest](@Method [nvarchar](4), @URI [nvarchar](1024), @json [nvarchar](max))
RETURNS TABLE (result nvarchar(max), error nvarchar(max))
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Request]
GO
IF OBJECT_ID ('CLR_EmarsysRequest', 'FT') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_EmarsysRequest ready'
GO
CREATE FUNCTION [dbo].[CLR_EmarsysRequestEx](@Method [nvarchar](4), @User [nvarchar](256), @Password [nvarchar](256), @URI [nvarchar](1024), @json [nvarchar](max))
RETURNS TABLE (result nvarchar(max), error nvarchar(max))
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[RequestEx]
GO
IF OBJECT_ID ('CLR_EmarsysRequestEx', 'FT') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_EmarsysRequestEx ready'
GO
CREATE FUNCTION [dbo].[CLR_SaveToFile](@Data [nvarchar](max), @Filename [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[SaveToFile]
GO
IF OBJECT_ID ('CLR_SaveToFile', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_SaveToFile ready'
GO
-- select dbo.CLR_SaveToFile('Текст для сохранения','C:\tmp\FileName.txt')
CREATE FUNCTION [dbo].[CLR_SaveBinaryToFile](@Data [varbinary](max), @Filename [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[SaveBinaryToFile]
GO
IF OBJECT_ID ('CLR_SaveBinaryToFile', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_SaveBinaryToFile ready'
GO
CREATE FUNCTION [dbo].[CLR_GZipToString](@Data [varbinary](max))
RETURNS varbinary(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[GZipToString]
GO
IF OBJECT_ID ('CLR_GZipToString', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_GZipToString ready'
GO
CREATE FUNCTION [dbo].[CLR_Levis_PingPong]()
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Levis_PingPong]
GO
IF OBJECT_ID ('CLR_Levis_PingPong', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_Levis_PingPong'
GO
CREATE FUNCTION [dbo].[CLR_Levis_GetPackage](@package [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Levis_GetPackage]
GO
IF OBJECT_ID ('CLR_Levis_GetPackage', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_Levis_GetPackage'
GO
CREATE FUNCTION [dbo].[CLR_Levis_PutPackage](@package [nvarchar](max))
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Levis_PutPackage]
GO
IF OBJECT_ID ('CLR_Levis_PutPackage', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_Levis_PutPackage'
GO
print 'в данной сборке CLR_GetTextHTML отсутствует (закомментарен)'
GO
CREATE FUNCTION [dbo].[CLR_HttpGetBinary](@url [nvarchar](max))
RETURNS varbinary(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[HttpGetBinary]
GO
IF OBJECT_ID ('CLR_HttpGetBinary', 'FS') IS NOT NULL
print 'CLR_HttpGetBinary ready'
GO
CREATE FUNCTION [dbo].[CLR_HMACSHA1Encode](@base [nvarchar](max), @key [nvarchar](max))
RETURNS varbinary(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[HMACSHA1Encode]
GO
IF OBJECT_ID ('HMACSHA1Encode', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'HMACSHA1Encode ready'
GO
CREATE FUNCTION [dbo].[CLR_TableReturnFunction](@params [nvarchar](max))
RETURNS TABLE (DateCreate datetime, Body varbinary(max), Message nvarchar(max))
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TestFunctions].[TableReturnFunction]
GO
IF OBJECT_ID ('CLR_TableReturnFunction', 'FT') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_TableReturnFunction ready'
GO
-- select [DateCreate],[Body],[Message] from dbo.CLR_TableReturnFunction('')
CREATE PROCEDURE [dbo].[CLR_GetUsedDB]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[GetUsedDB]
GO
IF OBJECT_ID ('CLR_GetUsedDB', 'PC') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_GetUsedDB ready'
GO
--exec dbo.CLR_GetUsedDB
CREATE PROCEDURE [dbo].[CLR_TryClearMemory](@before bigint out, @after bigint out)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[TryClearMemory]
GO
IF OBJECT_ID ('CLR_TryClearMemory', 'PC') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_TryClearMemory ready'
GO
CREATE PROCEDURE [dbo].[CLR_WriteLog](@message [nvarchar](max))
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[WriteLog]
GO
IF OBJECT_ID ('CLR_WriteLog', 'PC') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_WriteLog ready'
GO
CREATE FUNCTION [dbo].[CLR_WriteLogFunc](@message [nvarchar](max))
RETURNS BIT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[WriteLogFunc]
GO
IF OBJECT_ID ('CLR_WriteLogFunc', 'FS') IS NOT NULL -- ATTENTION on Type of function --
print 'CLR_WriteLogFunc ready'
GO
GRANT EXECUTE ON [dbo].[CLR_Levis_GetPackage] TO [nav_gate]
GRANT EXECUTE ON [dbo].[CLR_Levis_PingPong] TO [nav_gate]
GRANT EXECUTE ON [dbo].[CLR_Levis_PutPackage] TO [nav_gate]
GRANT EXECUTE ON [dbo].[CLR_HttpGetBlob] TO [mo2]
GRANT EXECUTE ON [dbo].[CLR_HttpGetBlobNew] TO [mo2]
GRANT EXECUTE ON [dbo].[CLR_HttpGetBlobMethod] TO [mo2]
set nocount on
declare @before bigint
declare @after bigint
exec dbo.CLR_TryClearMemory @before out, @after out
grant exec on CLR_HttpGetBlobMethod to public
Comments
0 B
|👍
/👎
0 B
|👍
/👎