I was reviewing some code that a consultant checked in and notice they were using SQLCLR. I don't have any experience with it so thought I would research what it was about. I noticed that they used Dim cn As New SqlConnection("server=LOCALHOST;integrated security=yes;database=" & sDb) instead of DIM conn As New SqlConnection("contex...
My current view is no, prefer Transact SQL stored procedures because they are a lighter weight and (possibly) higher performing option, while CLR procedures allow developers to get up to all sorts of mischief. However recently I have needed to debug some very poorly written TSQL stored procs. As usual I found many of the problems due t...
Receiving the following error when attempting to run a CLR stored proc. Any help is much appreciated. Msg 10314, Level 16, State 11, Line 1 An error occurred in the Microsoft .NET Framework while trying to load assembly id 65752. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNA...
I have a SQL CLR function in SQL Server 2005. I want to profile this function to make sure there are no memory leaks. Any recommendations on how to determine if my function is behaving properly? ...
When I try my CLR UDF, I am getting this error: Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "getFileSize": System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture...
For some reason, new projects set the version number of the assembly to 1.0.*, which then assigns what appears to be a random number once compiled. Why is this not set to 1.0.0.0 like all other projects. Thanks. ...
For this xml (in a SQL 2005 XML column): <doc> <a>1</a> <b ba="1" bb="2" bc="3" /> <c bd="3"/> <doc> I'd like to be able to retrieve the names of the attributes (ba, bb, bc, bd) rather than the values inside SQL Server 2005. Well, XPath certainly allows this with name() but SQL doesn't support that. This is my chief complaint wi...
I've got a Clr user defined type that takes a string of values sepertated by a comma. I'm losing a little bit of precision when converting to this type, I've narrowed it down to this line of code: cast(cast(nlinkjt as nvarchar(100)) + ',' +cast(avglrwf as nvarchar(100)) + ',' + cast(avglrwfjt as nvarchar(100)) as dbo.CLRJourneyTime) as...
I want to write a service (probably in c#) that monitors a database table. When a record is inserted into the table I want the service to grab the newly inserted data, and perform some complex business logic with it (too complex for TSQL). One option is to have the service periodically check the table to see if new records have been in...
What advantages does SQLServer CLR offer over T-SQL? Is using .NET syntax easier than T-SQL? I see that you can define user types, but I'm not quite clear on why that's better. For example, you could define an email type and it would have a prefix property and a domain property. You could then search on domain or prefix or both. However...
I'm investigating some of the newer technologies available in SQL Server 2005/2008. Most of my applications are written in C# and generally have a database component. Most of what I find on Google are the basic, 'This is how you set up a CLR UDT'. I have a few general questions on their real-world application and use. Are CLR hosted...
Hi, we are trying to create C# CLR Stored Procedure on a SQL Server 2005 machine. The C# code connects to an external datasource via an OLEDB driver to get a datatable. It has been tested on a separate console application, and it runs without problems. (The OLEDEB Driver is a proprietary driver connecting to a proprietary DB...) On SQL...
I know it's not supported, and I know it's not even a terribly good idea. But, I want to have a WCF client inside a SQL Table Valued Function. I've (seemingly) registered the correct assemblies, but when running my client - I get a WCF error: Msg 6522, Level 16, State 1, Line 1 System.ServiceModel.CommunicationObjectFaultedException: ...
Is there an equivalent of PRINT 'hello world' which can be called from CLR (C#) code? I'm trying to output some debug information in my function. I can't run the VS debugger because this is a remote server. Thanks! ...
Are there any inherent problems with calling an ole object from tsql? I have been posed with a problem that I would like to solve solely using a stored procedure, is there anything I need to worry about with using a ole object as opposed to writing it in a clr based language? ...
How do you raise a SQL Server error event (similar to RAISERROR) from within a SQL CLR routine? ...
This is the situation: I have a Trigger.dll and a Trigger.XmlSerializer.dll. I use CREATE ASSEMBLY to register them in MSSQL. Now, I have compiled new versions of both. I want to use ALTER ASSEMBLY to update them, however you can only update one at a time. If you try to update one that has a dependency, it complains. What's the tr...
I've got a SqlServer project with a very simple test for a Table-Valued-Function:- [SqlFunction(TableDefinition = "forename nvarchar(50)", FillRowMethodName = "TestFillRow", DataAccess = DataAccessKind.Read)] public static IEnumerable TestConn(int ID) { using (SqlConnection con = new SqlConnection("context connection=true")) { //con...
I am considering accessing some CLR code from a UDF in Sql Server 2005. I've heard stories that unhandled exceptions thrown in CLR code can bring down the server. Obviously my function would contain a try-catch block. However, certain exceptions (stackoverflow) can skip the catch. Does anyone have a set of guidelines that can comp...
I'm trying to convert some existing C# code into a CLR stored procedure. The obvious connection string to use is a context connection ("context connection = true"). The problem I'm running into is some things apparently don't work on this connection type. The latest thing is the SqlConnection.GetSchema method fails. The error just says ...