'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.

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.

'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.

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.

Microsoft sql server smo

Im trying to use

Microsoft.SqlServer.Management.Smo

to load a dropdown of available sql server databases.

On my computer it works fine but when i try and run the application on my virtual it throws and unhandled exception that it couldn't load SMO assembly. I know this is because 2005 isn't installed on my virtual but i want to be able to catch this error and just ignore it and load a blank dropdown. I tried putting try catch blocks around the load but it still errors. I then tried instead of importing it i tried to reference smo by using the whole name inside of my try catch block.

Does anyone know how to catch this error

Try' Create a new connection to the selected server nameDim srvConnAs New Microsoft.SqlServer.Management.Common.ServerConnection(txtServerName.Text)Dim srvSqlAs Microsoft.SqlServer.Management.Smo.Server' Log in using SQL authentication instead of Windows authentication srvConn.LoginSecure =False' Give the login username srvConn.Login = txtUserID.Text' Give the login password srvConn.Password = txtPassword.Text' Create a new SQL Server object using the connection we created srvSql =New Microsoft.SqlServer.Management.Smo.Server(srvConn)' Loop through the databases listFor Each dbServerAs Microsoft.SqlServer.Management.Smo.DatabaseIn srvSql.Databases' Add database to combobox txtDatabase.Items.Add(dbServer.Name)Next Catch exAs Exception MsgBox(ex.Message)End Try

I know this is because 2005 isn't installed on my virtual but i want to be able to catch this error and just ignore it and load a blank dropdown

smo object should work on a sql 2000 server, I have done it before. What is th exact error message your getting?

|||

What i have read tells me that smo is new in 2005 and does not work in 2000. But any case i want to ignore this error and just populate the dropdowns as blank if no sql server is installed at all.

The exact error message is:

Could not load file or assembly Microsoft.SqlServer.SMO
Version 9.0.242.0, Culture=neutral
PublicKey Token=89845dcd8080cc91 or one of its dependencies. The system cannot find the file specified