FAQ Database Discussion Community


SQL Server CLR hardcoded SQL statements

c#,sql-server,sqlclr
I am converting an existing T-SQL stored procedure into CLR C# .NET. It has been drilled into me that hardcoding SQL statements in .NET application source code is evil. Is a CLR stored procedure an exception to this rule? What other alternatives do I have? I can't very well call...

Storing WCF rest request data with SQL Server stored procedure

.net,sql-server,sqlclr
I have a simple interface in my WCF service with one method which gets a myRequest parameter public interface IService { [OperationContract] string MyOperation(myRequest request); } When I'm posting the data from the client, the content type is application/json, so the request body auto deserialise into myRequest object. myRequest is...

How to check for nested square brackets?

c#,.net,regex,tsql,sqlclr
I have the input string below: [text1][text2][text3]...[textN] and I want to apply the following validation rule using regular expression: The ] and [ cannot be included in other []. For example, the next input strings are not correct: [test1][test2[][test3] [test1][test2]][test3] [test1][test2[lol][test3] [test1][test2]lol][test3] I need to validate the input string because...

SQL CLR function returning error trying to use context to create table and insert rows

.net,sql-server,sqlclr
I am running on SQL Server 2012 and Visual Studio 2012 with .NET 4.5, trying to build a CLR function that creates a table and inserts results into it using the context connection. I'm getting the error: An error occurred in RunFetchXML: Data access is not allowed in this context....

CREATE ASSEMBLY in UNSAFE mode with File path does not create Functions in database

sql,sql-server,sql-server-2012,sqlclr
I am trying to register CLR assembly in SQL Server with following statement: CREATE ASSEMBLY SQLCLRTest AUTHORIZATION [dbo] FROM 'C:\MyApp\SQLCLRTest.dll' WITH PERMISSION_SET = UNSAFE; The CLR assembly is signed with a pfx key, and I have already created an Asymmetric key and a SQL login like to allow installing this...

Deploying SQL CLR Project fails when creating assembly on database

c#,sql,.net,sql-server,sqlclr
I have a folder with 3 dll files in the folder on the server that I'm using to create the assembly. I tried the following code at first and got an error that said it couldn't find the system.data.datasetextensions.dll file on the server and I copied and pasted the dll...

msbuild error building sql clr dll in TFS

tfs,msbuild,tfsbuild,tfs2013,sqlclr
We have SQL CLR dll that we developed quite a long time ago that we have been using. Unfortunately it wasn't ever in our normal daily builds.We are going to be upgrade our sql server from 2008 R2 to 2014. So i opened the project and upgrade it to a...

Can dynamic types be used in a CLR functions?

c#,sql-server,sql-server-2012,clr,sqlclr
I was tasked with writing a CLR function that would mass send emails using Mandrill.Net. After a lot of hurdle with assemblies and other issues I managed to patch most of the function. The only thing left was to add sending those emails (I already had code for that). Unfortunately...

Can I use SQLCLR stored procedure to update a column of a database table ( using some compiled dll)

c#,sql-server-2012,sqlclr
I wanted to update the values of a few columns of a database table, using queries or stored procedure, but wanted to use my C# library to alter the value. For eg, I want the columns A,B,C of table T to be replaced with Encrypt(A), Encrypt(B) and Encrypt(C) where Encrypt...

Cannot connect to an external database in SQLCLR

c#,sql,sql-server,sqlclr
I am trying to create a function that will connect to an external database and run a query. When I run my function I am getting this error: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read,...

The type or namespace name 'DataSetExtensions' does not exist in the namespace 'System.Data' (are you missing an assembly reference?)

c#,sql,.net,reference,sqlclr
I know this is a common error but I have the correct reference to the System.Data.DataSetExtensions.dll added to the project and my project is a SQL CLR project built for .net 4.5 and I'm getting the error at the following line: using System.Data.DataSetExtensions; I also checked the properties for the...

Creating a table method on a user defined type (like like 'nodes' on the XML data type)

extension-methods,sqlclr
I've created working CLR based user defined table functions as well as user defined types. What I want now is a method on a CLR-UDT that returns a table. Similar to the 'nodes' method on the XML data type. The TableDefinition and FillRowMethodName properties of the SqlMethod decoration seem to...

Sending HTTP POST request from SQL Server 2012 or SQL CLR C#

c#,.net,sql-server,asp.net-web-api,sqlclr
Is there a generally accepted standard way for sending HTTP requests from SQL Server 2012? What I am trying to do is to use a remote server to process a search query and then insert the results back into the SQL Server 2012 database. The remote server offers a web...

How do I add c# class library/ class dll in SQL CLR project?

c#,sqlclr
How do I add c# class library/ class dll in SQL CLR project ?. I add the class library dll/ class dll but got error in SQL server 2012 during assembly creation Assembly references assembly 'system.runtime.serialization, version=4.0.0.0, culture=neutral, which is not present in the current database. SQL Server attempted to...

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host

c#,sql,.net,multithreading,sqlclr
I'm using threading in my SQL CLR Project and I'm getting the following error when I try to run my user defined function. What can I do to enable threading things such as the Parallel library and await/async in my project? Msg 6522, Level 16, State 1, Line 4 A...

How to convince sql server 2008r2 to use clr v4.0 instead of v2.0?

.net,sql-server,sql-server-2008-r2,sqlclr
I have sql server 2008r2. According to my internet research it supports .net framework 4.0. I tried to install my assembly with sql clr functions and receved an error. CREATE ASSEMBLY for assembly 'MyAssembly' failed because the assembly is built for an unsupported version of the Common Language Runtime. Query...

Unable to execute stored procedure created in SQLCLR

sql,sql-server,sql-server-2008,visual-studio-2012,sqlclr
I have create a stored procedure in SQLCLR (SQL Server Database Project, VS2012). I publish the stored procedure successfully to the database. But when I run the stored procedure in the database, I get an error. Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is...