I have a CLR stored procedure that references an assembly created in VS 2008 that uses Linq. Lets call this assembly 'MyLib'. I can't seem to get 'MyLib' into my SQL 2005 database. I do the following: CREATE ASSEMBLY [MyLib] FROM 'C:\MyLib\bin\Release\MyLib.dll' WITH PERMISSION_SET = UNSAFE GO But I get the error: Assembly 'My...
In the past I've been able to embed a sripting languate (like JScript) inside the SQLCLR, so scripts can be passed as parameters of functions, to perform certain calculations. Here is a simplistic example (the function ssScriptExecute returns a concatenation of all the print's in the script): select dbo.ssScriptExecute( 'print("Calculat...
I am using a clr procedure in one of my normal stored procs and i'm experiencing an odd error handling situation. Check the following code, it works as expected... BEGIN TRY create table #test(id varchar(2)) insert #test select '123' select * from #test END TRY BEGIN CATCH select 'an error occured but was handled' END CATCH Ho...
I have written an assembly which is integrated in sql server, providing some stored procedures written in C#. The assembly has a readonly static variable holding some configuration data. This data is manipulated via stored procedures, which are also provided by the assembly. Obviously I have to synchronize access to this static variable....
When making an HttpWebRequest within a CLR stored procedure (as per the code below), the first invocation after the Sql Server is (re-)started or after a given (but indeterminate) period of time waits for quite a length of time on the GetResponse() method call. Is there any way to resolve this that doesn't involve a "hack" such as havin...
I have written a CLR stored procedure that is in an assembly. I have a build system that can auto-build and deploy .net application from our source control repository. I want the two things to work together so I can redeploy the assembly that hosts the CLR stored proc. However it looks like, unlike IIS, simply replacing the binaries ...
I'm trying to access a Sql filestream from a CLR stored procedure. I've set up a very simple database with a single table which includes a filestream column. I can successfully read from the filestream using a simple console app. Here's some example code for the proc that fails: [SqlProcedure] public static void GetDataFromFileStream(st...
Hi, I am converting a console application to run from Service Broker in SQL Server 2005. The application makes heavy use of DataSet. Are DataSets supposed to work in SQLCLR? How do I define the connection to use? Is there any documentation about this? Thanks for your help! ...
Guys lease help me out I think I'm close, but I'm missing something. Background I'm rewriting a SQL CLR assembly (stored procedure), my assembly contacts another resource and gets back XML. I want to return this XML as a recordset, NOT a scalar value. From what I've seen, this is how to return a recordset: SqlContext.Pipe.Send(mySqlD...
I'm using SQLCLR in my project and, for the record, I'm very pleased with it. However, I can't find any good sources of information for good data access patterns. Usually I do communication between .net and SQL using stored procedures because I always want an API for my database. However, in this case the .net code is part of the API, s...
I have a table with a heavy load(many inserts/updates/deletes) in a SQL2005 database. I'd like to do some post processing for all these changes in as close to real time as possible(asynchronously so as not to lock the table in any way). I've looked a number of possible solutions but just can't seem to find that one neat solution that fee...
On SQL Server 2008, I'm attempting to register an assembly that seems to only reference the supported libraries. Here is the T-SQL code that I'm using to register the assembly: create assembly MySpatial from 'c:\Spatial.dll' This results in the following error: Msg 6509, Level 16, State 31, Line 1 An error occurred while gather...
Continuing on the hidden features theme, I havent found one for SQLCLR (Microsoft 2005 / 2008). For example, undocumented features, tricks to do things which are very useful but not documented enough? Also features that are very useful in general. ...
I am getting the following error message when I try install my .NET assembly int SqlServer 2005. My .NET assembly references 'ChilkatDotNet2.dll' assembly. Msg 6544, Level 16, State 1, Line 1 CREATE ASSEMBLY for assembly 'myassembly' failed because assembly 'chilkatdotnet2' is malformed or not a pure .NET assembly. Unverifiable PE Head...
I have a static class which loads a .NET assembly dynamically (using Assembly.LoadFile method) I get the following error message: Msg 6522, Level 16, State 2, Line 3 A .NET Framework error occurred during execution of user-defined routine or aggregate "MySQLCLRUDFFunction": System.TypeInitializationException: The type initializer for '...
Can I delete the DLL-File C:\PATH\TO\DLL\FILE.DLL after creating an assembly with CREATE ASSEMBLY MyAssemblyName FROM 'C:\PATH\TO\DLL\FILE.DLL' ? Everything seems to work fine after deleting the file. But SELECT * FROM sys.assembly_files shows the path of the file in the name column. Can this cause problems? (or is the content of...
It is well documented here that when you update an assembly (that is not on the approved list) in the GAC that happens to be inside SQL 2005/2008, the SQLCLR will complain that could not load file or assembly. This is also a problem when moving a database to a new machine (that could have different versions of the assembies). Is there a...
So I developed a web application in ASP.NET and SQL Server 2008. Part of the SQL code I implemented as a .NET assembly (instead of t-sql) because it is performing intense calculations that are better written in C#. Now I am ready to deploy my application, but I am stuck looking for a hosting company that allows this. Scratch out Discoun...
This has been bugging me for a while and I'm hoping that one of the SQL Server experts can shed some light on it. The question is: When you index a SQL Server column containing a UDT (CLR type), how does SQL Server determine what index operation to perform for a given query? Specifically I am thinking of the hierarchyid (AKA SqlHierar...
Is there a way to get a value from the registry using a CLR procedure that doesnt involve having to registry Microsoft.Win32 as an assembly in SQL Server? I am looking to get the DigitalProductId (I will need to decode it to a string) from the registry within a CLR proc and return it to SQL. Supported Namespaces CustomMarshalers Micro...