November 30, 2015 at 2:13 am
To make faster our client database solution we need to use the our c++ native dll-library from UDF. I read several articles about registering the UNSAFE Assemblies on MSDN and other sources (i skip references to avoid the spam links) - all instructions works fine on MS SQLSRV 2008 (x64) and NOT WORKS on MS SQLSRV 2014 (x64)! - on this version my UDF freeze if dll-imported function is included
1) To simplify example I include in our native C++ dll project the small test function "test_func":
test_func.cpp:
#include "stdafx.h"
double __stdcall test_func()
{
return 1.0;
}
in .def file also have added "test_func"
After compiling - the CoreLIB.dll is signed with our company digital key.
I have tested accessing to this function from .NET in c# Application - and it worked.
2) I create new Visual C# -> Class Libary project and in Class1.cs file and put this code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Runtime.InteropServices;
public static class CLRTest
{
#if DEBUG
[DllImport("D:\\Project\\Core_LIB\\x64\\Debug\\CoreLIB.dll")]
#else
[DllImport("C:\\SQLSRV_CLRFUNCS\\Core_LIB\\x64\\CoreLIB.dll")]
#endif
public static extern double test_func();
[SqlFunction()]
public static SqlInt32 TestFnc()
{
return (SqlInt32)test_func();
}
}
The project successfully compiled into CLRTest.dll and signed with company digital key. I copy it into C:\SQLSRV_CLRFUNCS\CLRTest.dll.
3) I create the TestDB database
4) I enable CLR:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'clr enabled', 1
RECONFIGURE
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
5) I prepare the TestDB database to use UNSAFE Assemblies:
use TestDB
go
alter database TestDB set trustworthy on;
go
6) I register assembly
CREATE ASSEMBLY CLRTest
AUTHORIZATION dbo
FROM 'C:\SQLSRV_CLRFUNCS\CLRTest.dll'
WITH PERMISSION_SET = UNSAFE
GO
7) Register th UDF:
CREATE FUNCTION TestFnc()
RETURNS int
AS EXTERNAL NAME CLRClaytonBeta.CLRTest.TestFnc
GO
All registration scripts finished successfully.
8) Test my UDF, I run this query:
select dbo.TestFnc();
this command freeze without any errors!!
if i replace the "return (SqlInt32)test_func();" string with "return 1;" - all works.
Please, help!
November 30, 2015 at 5:46 am
please, close this topic.. I found problem: in my native dll I have hard initialization code in DLLMain. after comment it - all works fine.
December 4, 2015 at 10:33 pm
There is no way to close a topic on this Forum. Thanks for posting back that you found the issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply