Hi all,
I have a small dll that use the SQL Server 2005 SMO library. SQL Server 2005 has SP 1 installed. When I tried to register the assembly with the followin command, I get the error shown. If I remove all references to SMO, the assembly registered fine.
I really need help on this.
Thanks in advance.
CREATE ASSEMBLY ACDataBridge
FROM
'H:\User\Development\Projects\Applications\ACDataBridge\bin\Debug\Projects.Applications.ACDataBridge.dll'
WITH PERMISSION_SET = UNSAFE
Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'Projects.Applications.ACDataBridge' failed because assembly 'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.
Hi,
Unfortunately in SqlServer 2005 we do not support loading SMO in SQLCLR. If you want to build your assembly for dual use (i.e. both inside SQLCLR and on the client) you can load SMO via reflection. This works in a rather narrow set of scenarios though, for the next version of SqlServer we are looking at making sure that SMO loads and works inside SQLCLR.
Regards,
Ciprian Gerea, SqlServer SDE
|||Hi Ciprian,
Thank you so much for your repy. For a minute, I thought this post would never be answere. Your sggestion sounds good, but I have tried loading assebly dynamically in SQLCLR code and it was failing; here is one:
I use Assembly.LoadFrom("test.dll"); to dynamically load a dll in a SQL CLR pocedure method, but I get this error:
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
First method:
I have also tried to activate a managed COM, and it fails:
//Get IDispatch Interface
Type objMethodType = Type.GetTypeFromProgID("CLRServer.CServerObject");
//Create Instance
object objMethod = Activator.CreateInstance(objMethodType);
//Make Array of Arguments
object[] myArguments =
{
strKeySet, strTarget, strMetadata
};
//Invoke Add Method
strReturnParam = (String)objMethodType.InvokeMember(
"Process", BindingFlags.InvokeMethod, null, objMethod, myArguments);
Second method:
I tried to activate an unmanaged COM, which in turn, calls a method in the above managed COM:
The instance of the unmanaged COM was created succesfully, but the method call failed with COMExeption
//Get IDispatch Interface
Type objMethodType = Type.GetTypeFromProgID("CComNativeLink2.CComNativeLink.1");
//Create Instance
object objMethod = Activator.CreateInstance(objMethodType);
//Make Array of Arguments
object[] myArguments =
{
strKeySet, strTarget, strMetadata
};
//Invoke Add Method
strReturnParam = (String)objMethodType.InvokeMember(
"ProcessData", BindingFlags.InvokeMethod, null, objMethod, myArguments);
Any idea?
Can you give a sample of your suggestion?
Thanks in advance
|||I think that Assembly.LoadFrom fails to access the disk if you are registred as SAFE. The reason is that registering as SAFE only gives you only execution privileges, not file access. You should use Assembly.Load instead. But let me make something clear - this does not mean that you will be able to use SMO inside SQLCLR this way, it's just a way to load SMO only when you're not inside SQLCLR and keep the rest of your application code identical, as much as possible.
As for the COM objects, they are also not supported inside SQLCLR. You can of course use the sp_OACreate & Co. and load the COM stuff into the native space, but this has drawbacks as far as reliability and performance. If the native objects loads a managed assembly it will still be subject to the same restrictions.
Ciprian
|||Hi Ciprian,
Thank you so much for clarifying that. Unfortunately, I had already tried sp_OACreate and it failed also due to managed COM. I guess my only option left would be extended stored procedure and managed COM. Or is that going to fail also?
|||I'm afraid it will also fail. There is currently no way you can run SMO inside SQLCLR.
Ciprian
|||Is there another way to script out objects from within SQL Server?This is very disappointing.
