SQL ORDER BY within OVER clause incompatible with CLR aggregation?
As an answer: Officially from Microsoft, No. http://connect.microsoft.com/SQLServer/feedback/details/611026/add-support-for-over-order-by-for-clr-aggregate-functions It doesn't appear to have made it into 2014... no mentions of many transact-sql changes: http://msdn.microsoft.com/en-us/library/bb510411.aspx#TSQL...
Deploying SQL CLR Project fails when creating assembly on database
First off, only a short list of .NET Framework libraries are "supported". You can find that list on the MSDN page for Supported .NET Framework Libraries. System.Data.DataSetExtensions is not one of them. That is why you got the first error. The second thing posted is a warning, not an error....
Use MSSQL CLR to retrieve data as json
there are 2 separate issues in your post that are unlikely to be solved by a CLR solution: timeout there are no details about where the timeout actually occur (on the rdbms while performing the selection, on the client side while retrieving the data, in the report engine while actually...
Can I use SQLCLR stored procedure to update a column of a database table ( using some compiled dll)
You can use SQLCLR to call encryption from C#, though this is the wrong approach. If you need to do a custom algorithm, you should encapsulate that into a SQLCLR function so that it can be used in an UPDATE statement or even an INSERT or SELECT or anywhere. Something...
How to convince sql server 2008r2 to use clr v4.0 instead of v2.0?
.net,sql-server,sql-server-2008-r2,sqlclr
You can't. In an article posted by Doug Holland in 2010 it is explained that older versions of SQL Server (up to and including 2008 R2) use the LockClrVersion call to restrict the .NET version that can be loaded to the latest 2.0 version. To use .NET 4.0 you will...
Creating a table method on a user defined type (like like 'nodes' on the XML data type)
Sadly, it is not possible to create a method on a UDT that returns a table (i.e. returns IEnumerable). Your confusion regarding the two properties of the SqlMethod attribute are quite understandable as they are misleading. If you take a look at the MSDN page for SqlMethodAttribute Class, you will...
Cannot connect to an external database in SQLCLR
You will need to add an annotation to your method along the lines DataAccessKind.Read.
SQLCLR custom aggregate with multiple parameters
c#,sql-server,.net-assembly,sqlclr,user-defined-aggregate
There's no need to store a list of all the records - you only need to store the details of the oldest record you've seen so far. Something like this should work: [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToOrder = true, IsInvariantToNulls = true, IsInvariantToDuplicates = true, MaxByteSize = -1)] public struct sOlder...
Can dynamic types be used in a CLR functions?
c#,sql-server,sql-server-2012,clr,sqlclr
Based on the answer I got here, likely I cannot use dynamic types in CLR or at least not to the extent that can allow me to use Mandrill.Net. My guess is, I can actually use dynamic types, as long as I keep it to a single namespace. Dapper can...
What are IBinarySerialize Interface methods used for?
Any particular instance of the User-Defined Aggregate (UDA) is not guaranteed to exist throughout the entire lifetime of the query. It needs to have a storable representation. When you only use value types (as noted in the "enumeration format" link in the question), the provided Read and Write methods understand...
SQL CLR Trigger, How to make an assembly trusted due to transparent code call critical code?
Why is SQLCLR code only trusted partially? By default, CLR code running inside of SQL Server (i.e. "SQLCLR") is highly restricted so as to not degrade security or stability of SQL Server. How do you make SQLCLR fully trusted? What the CLR code within an Assembly can do is...
Storing WCF rest request data with SQL Server stored procedure
There's no way you can pass a complex object to the db, and it does not matter if you use a CLR stored proc, an old plain stored proc or something else. You'll need to transform your request into something that can be passed to the db. An XML serialisation...
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host
c#,sql,.net,multithreading,sqlclr
This error, as noted in your other question, Deploying SQL CLR Project fails when creating assembly on database, requires the following: ALTER ASSEMBLY [AssemblyName] WITH PERMISSION_SET = UNSAFE; ...
Unable to execute stored procedure created in SQLCLR
sql,sql-server,sql-server-2008,visual-studio-2012,sqlclr
Are you using WCF within your SQLCLR assembly (or some other web services client)? If so, you need to use the XML Serializer Generator tool to create the serialization assembly manually and then add it to SQL Server along with your original assembly. There are more detailed instructions at the...
CREATE ASSEMBLY in UNSAFE mode with File path does not create Functions in database
sql,sql-server,sql-server-2012,sqlclr
why the assembly gets registered properly through the DLL binaries, but not when I use DLL's path? The issue is not the creation (what you are referring to as getting registered) of the assembly. If you do not get an error when running CREATE ASSEMBLY then it did get...
Error creating CLR Assembly for SQL Server 2012
.net,sql-server,visual-studio-2012,sqlclr,clrstoredprocedure
I've been able to solve the problem. I had missed a single step when I made the reference from the Startup project to the Web Service project. All I had to do was expand the References node in the Project Explorer for the Startup project and select the Project Reference....
The type or namespace name 'DataSetExtensions' does not exist in the namespace 'System.Data' (are you missing an assembly reference?)
System.Data.DataSetExtensions is an assembly, not a namespace. You just need to add a reference to System.Data.DataSetExtensions.dll (as you say you already have) and then a using directive for the System.Data namespace: using System.Data; That will pull in all the extension methods in the classes in that namespace, e.g. DataRowExtensions. When...
How do I add c# class library/ class dll in SQL CLR project?
You have to register unsupported libraries before you can use them. -- You will have to use the Runtime Serialization from .NET v3 ALTER DATABASE [<<Database Name>>] SET TRUSTWORTHY ON; CREATE ASSEMBLY AnyName_You_Want FROM --'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Runtime.Serialization.dll' 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE; Excerpt: Unsupported libraries can still be called from...
I cannot use WCF in SQL CLR 2012
No, SQL-Server only allows a rather limited subset of C#, for security reasons. For example, adding a reference to System.Web is sufficient that it doesn't work. Perhaps you can rip the part of System.ServiceModel that you need out of the mono sourcecode and get it to work that way. With...
SQL CLR function returning error trying to use context to create table and insert rows
Functions, whether T-SQL or SQLCLR, cannot modify state of the server. There is a whole list of things that cannot be done, such as any SET commands, calling NEWID(), etc. Check out the MSDN page for Create User-defined Functions (Database Engine) for the list of "Limitations and Restrictions". The only...
Sending HTTP POST request from SQL Server 2012 or SQL CLR C#
c#,.net,sql-server,asp.net-web-api,sqlclr
Like Joe suggested using HttpWebRequest instead of HttpClient works without having to use unsupported assemblies: [Microsoft.SqlServer.Server.SqlProcedure] public static void SendRequest (string query, string table) { string address = "http://localhost:9000/api/search"; HttpWebRequest request = (HttpWebRequest) WebRequest.Create(address); request.ContentType = "application/json; charset=utf-8"; request.Method = "POST"; using (var streamWriter = new StreamWriter(request.GetRequestStream())) { string json...
How to check for nested square brackets?
If you really want a regexp here is a quick one : ^(\[[^\[\]]+\])*$ Works on your examples The principle here is for each bracket pair (\[.*\])* to contain any text that does NOT contains a bracket [^\[\]]+ In case you need to be able to have [test1][test2][][test3] working change the...
SQL Server CLR hardcoded SQL statements
I can't very well call a T-SQL stored procedure instead... I"m not sure what logic you need to be held inside your CLR component, however you can certainly call stored procedures from the CLR component to retrieve the data you want for processing. You can also call stored procedures...