Update: The issue has been solved!
For SQLA 16, reference to the .NET framework 3.5 version of iAnywhere.Data.SQLAnywhere when you use the default CLR environment dbextclr16.exe.
When you want to use framework 4.0, reference to iAnywhere.Data.SQLAnywhere.v4.0.dll and execute the following statement before you use the external method the first time:
For framework 4.5, reference to iAnywhere.Data.SQLAnywhere.v4.5.dll and use this version of the external environment:
I'd like to use the CLR external environment to retrieve data from various sources using the .NET framework and then represent it to the database as a result set.
The examples provided with SA show how to access the database from .NET code by using an internal connection accessable through SAServerSideConnection.Connection. And this is exactly where the samples throw an error:
Here's what I do. Versions are SA 22.214.171.1244 and Visual Studio 2012 Premium Update 4.
Now execute the example statements from datatype.sql in ISQL. Works like a charm.
OK, now for the data stuff:
Before you copy the dll again, close the CLR in ISQL
When I try to run the test statements from resultset.sql, the following fails
with the mentioned exception from SAServerSideConnection.Connection. Poor thing thinks its out although it isn't.
If i change the GetConnection() to
it works. But that's just a bad workaround, for obvious reasons.
This is a documentation bug, .NET 2.0 is not supported by the CLR external environment. .NET 3.5 or later is required to use SAServerSideConnection.
This page documents the support correctly. Other references will be updated accordingly.
answered 15 May '14, 13:28
thanks for the ultrafast reply. Yep, the CHM-version of the SA 16 docs still says "Only .NET version 2.0 is supported." under
SQL Anywhere Server - Programming » SQL Anywhere external environment support » The CLR external environment
Unfortunately, framework4 doesn't work for me, either. Same setup as in my initial question, this time targeting framework 4 and referencing "C:\Program Files\SQL Anywhere 16\Assembly\V4\iAnywhere.Data.SQLAnywhere.v4.0.dll"
This time, it doesn't even start the dll:
It seems like it doesn't find the System.Data.dll although it is present:
German Windows 8.1 with VS 2012 Premium. Framework 4 is there, no doubt. Same happens when I try Framework 3.5, btw.
You don't have a working version as a VS Project, do you?
answered 15 May '14, 14:22
drives me crazy. Nope, doesn't work.
Here is what I do: I copy your three files to a directory c:\clrtestbuild and run repro.bat. The dbisql call to repro.sql tells me, it doesn't find iAnywhere.Data.SQLAnywhere.v4.0
Right, it's not in my path. But it's in the GAC where the SA installation routine puts it.
I always thought .NET hosts would pull missing assemblies from there. Whatever, I change the repro.bat and add the missing path:
To my surpise: no change! It is still missing the iAnywhere data dll. What the heck, I copy the iAnywhere DLL to the destination directory c:\clrtest. Arrrgh, it's still missing.
Maybe if I copy the iAnywhere dll to the c:\clrtestbuild directory? Nope.
For some reason, I get an idea: I copy your three files to the c:\clrtest directory and run repro.bat from there. I even remove the command that extends the path to c:\clrtest from repro.bat. And ahhh, we are one step further: it doesn't complain about the missing dll. Unfortunately, now I get another error (see below).
Now I start the DB from a different location. In the command prompt, I move to the root directory and
Bam, iAnywhere dll missing.
Here is my theory: "When the external dll loaded into the SA CLR host is not located in the path from where the DB server was STARTED, links to other DLLs are not properly resolved." Haven't tried it with a SA db running as a service yet. I can reproduce the problem on a clean Win7 virtual machine with just SA 16 and framework 4 installed.
Back to the subsequent error. When the db and the dll are in the same directory and I start the db from there, I get the following error from your sample:
telling me in German that an object reference doesn't lead to an object instance. Here is some more information from the server log:
I'm still stuck.
answered 16 May '14, 07:24