Registering assembly in SQL Server 2005

Hi,

I have created a C# stored procedure that has a typed dataset (XSD). When I try register that assembly in SQL Server 2005, I am getting the following error.

CREATE ASSEMBLY failed because method "add_InvoiceDTRowChanging" on type "InvoiceDTDataTable" in external_access assembly "SS.StoredProc" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies. (Microsoft SQL Server, Error: 6213)

I am using SEP CTP for both VS and SQL Server. I do not have any app.config file in my project. Can anyone help me resolve this?

Thanks,
Prakash

Exactly as the error messsage says, the assembly you try to catalogue contains something that is not allowed under the permission set you try to do it under. In this case it looks to be the method add_InvoiceDTRowChanging in the class for one of the tables. If you look at that method you should see it has (probably) a HostProtectionAttribute with a property taking about synchronization. And, as the error mesage says, this is not allowed in a assembly set for EXTERNAL ACCESS. Change the permission set to be UNSAFE, and you shoudl be OK.

Notice that when you set UNSAFE you may have to mark your database as TRUSTWORTHY and assign the owner of the database UNSAFE PERMISSION rights.

Niels
|||Hi Neil,

Thanks for your insight. The problem I am having is, in the class I dont have any method that reads as add_InvoiceDTRowChanging. Also I did checked for the word 'Host' through out my solution and I dont find this word.

Thanks,
Prakash|||Hi Prakash,
I believe that in your stored procedure you have a CLR Event of some kind. The add_InvoiceDTRowChanging method is created automatically for you as part of the Event Handler. Unfortunately, the way that the CLR currently handles Event synchronization is not allowed under SAFE or EXTERNAL_ACCESS assemblies. Hopefully this will be fixed in a future release. Your assembly should work under the UNSAFE permission set.
Steven
|||

Hi Steven,

When registering with UNSAFE, I successfully added the Assembly. When I try to create a instance of the typed dataset in my c# stored procedure, I am getting the following error:

Msg 6522, Level 16, State 1, Procedure InsertInvoice, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'InsertInvoice':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boo
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlAttributeOverrides overrides, Type[] extraTypes, XmlRootAttribute root, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlRootAttribute root)
at System.Data.Common.ObjectStorage.ConvertXmlToObject(XmlReader xmlReader, XmlRootAttribute xmlAttrib)
at System.Data.XmlDataLoader.LoadColumn(DataColumn column, Object[] foundColumns)
at System.Data.XmlDataLoader.LoadTable(DataTable table, Boolean isNested)
at System.Data.XmlDataLoader.LoadData(XmlReader reader)
at System.Data.DataSet.ReadXml(XmlReader reader, Boolean denyResolving)
...

When I tried to create a new class and just copy pasted the same code that was generated by the typed data set, I was able to create a new instance of that class. Why I cannot create instance of a typed dataset?

Thanks,
Prakash

|||This just works like a charm with the Visual Studio RTM bits

Thanks,
Prakash|||I'm glad you got it to work. As you found, Visual Studio RTM deployment takes care of this automatically for you.
However, if you're doing this outside of Visual Studio or with beta bits, then you need to generate the Xml Serialization assembly yourself using sgen. How to do this is described in the following blog posting:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx
Thanks,
Steven|||Hi Steven,

I got into another trap now. When I try to create a new instance of StringReader class in C# stored proc I am getting the same error. I am passing a XML string and I am trying to use the StringReader Class to populate the typed dataset. When I use the following code, I am running into the error.

StringReader sr = new StringReader(data);

Can you please help?

Thanks,
Prakash|||Actually this turns to be a BIG issue for me now. After careful examining, I found out that its all because of Generic type in my typed dataset. I have created a AddressCollection generics of type Address. The typed dataset has a column of datatype AddressCollection.

When I load the XML file that I get as parameter from UI, it cannot understand that type and its throwing exception. But I did created the AddressCollection assembly into the SQLServer with UNSAFE permission.

Anyone has any idea of dealing TypedDataset with Generics inside SQL Server stored procedure?

Thanks,
Prakash|||

Hi Prakash,

Generics are not currently supported under SQLCLR. However if you're not using them on the SQL side of things (eg, as a return value or parameter for your stored proc) then it shouldn't be throwing an error. If you attach your code or send it to me ( stevehem at microsoft dot com) I'll take a look.

Steven

|||<Steven Hemingray - MSFT@.discussions.microsoft..com> wrote in message news:381fd38d-a93c-49fd-8f28-c4680a22993c@.discussions.microsoft.com... Generics are not currently supported under SQLCLR. However if you're not using them on the SQL side of things (eg, as a return value or Hi Steven, Can you clarify this statement? Do you mean that absolutely no use of generics is supported within SQLCLR routines? Including use of generic collections, etc? Or am I taking this too far out of context? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||

What I meant was that using generics inside SQLCLR is fine as long as they're not visible to the T-SQL side. This is the same restriction as on many .NET objects (arrays, regex, etc.) where you can use them all you want on the CLR side, but in order to interface with the T-SQL side you need to wrap them in constructs that T-SQL can understand.

In response to Prakash, I was trying to determine why the use of generics would throw a .NET serialization exception and not one of the 'generics are not supported' error messages .