sqlclr

Use optional arguments in SQLCLR

I have a stored procedure in MSSQL 2005 Server SQLCLR. Is it possible to define an optional argument to it? If so, how? ...

Deploy a CLR Trigger/Stored Procedure using WiX

As the founder's of stackoverflow talk so much about being able to deploy changes easy, I'm trying to come up with a solution to solve my issue. A quick background is that a client contacted me and needed something done on the database that is quite not so trivial, but isn't horribly difficult to do (took me about a day to get the proce...

Accessing TSQL created #temp tables from CLR stored procedure. Is it possible?

I have a TSQL Stored Procedure tsql__sp__A which does two things: (a) Creates a temp table #tempTable that has SELECT data from a complex SELECT query. (b) Calls a CLR managed Stored Procedure clr__sp__B for each row that does computation on row parameters. Question: Is it possible to access #tempTable from CLR procedure clr__sp_...

Is it ok to load .net dlls into SQL Server as UNSAFE?

When creating a SQL Server CLR stored procedure, I noticed that I couldn't reference anything in the .net framework as I would normally. After some reading around, I realised that assemblies needed to be loaded into the database first. Therefore, I loaded in the ones I need but due to P/Invoke had to use the UNSAFE permission set. I...

SQL CLR SqlBulkCopy from DataTable

We have an in memory DataTable in a CLR procedure. After significant processing the DataTable has a lot of data that we need to load into a table in the database. Unfortunately, since we are using a context connection SqlBulkCopy will not work (throws error: The requested operation is not available on the context connection outline of c...

Control SQL Server CLR Reserved Memory

I've recently enabled CLR on my 64 bit SQL Server 2005 machine for usage of about 3 procs. When I run the following query to gather some info on memory usage... select single_pages_kb+ multi_pages_kb + virtual_memory_committed_kb as TotalMemoryUsage, virtual_memory_reserved_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_...

SQL Server: An error occurred during the generation of the asymmetric key.

create asymmetric key asymmetrickey from executable file = 'c:\windows\microsoft.net\framework\v2.0.50727\system.windows.forms.dll' Why can't I use the above to generate an asymmetric key from framework libraries (for installing in SQL server as references for CLR functions). It gives an error: "An error occurred during the generation ...

CLR UDF returning Varbinary(MAX)

Is it possible for a SQL CLR User-Defined Function to return the data type varbinary(MAX)? In the documentation it mentions: "The input parameters and the type returned from a scalar-valued function can be any of the scalar data types supported by SQL Server, except rowversion, text, ntext, image, timestamp, table, or cursor." - they d...

why would a SQLCLR proc run slower than the same code client side

I am writing a stored procedure that when completed will be used to scan staging tables for bogus data on a column by column basis. Step one in the exercise was just to scan the table --- which is what the code below does. The issue is that this code runs in 5:45 seconds --- however the same code run as a console app (changing the conn...

Best way to run a nightly process that manipulates data and sends email via SQL Server 2005

I have an asp app that runs against SQL Server 2005 and I'd like to run processes that: query data, manipulate data, and send emails containing particular data and analysis What is the easiest way to do this in SQL Server 2005? Can I do this in an SSIS package? Should I use CLR procedures? ...

SQLCLR Community Extensions or common library

Having just finished writing a Regex replacement and match function and tvf for SQLCLR for the fifth time, I sat and pondered whether there was a set of common community extensions for SQLCLR for the most common things you want in a database but are never provided. Powershell for example has an excellent set of community extensions that...

SQL Server Create Function (CLR) - nested types

Is it possible to create a CLR function mapped to a method of the nested type? CREATE FUNCTION [dbo].[MyClrFunc] (@s NTEXT) RETURNS NTEXT AS EXTERNAL NAME [MyAsm].[MyNamespace.MyClass+MyInnerClass].MyFunc; the SQL Server keep feeding me with the same error: MyNamespace.MyClass+MyInnerClass ...

How to push SQLCLR updates to your (CI) server

I'm re-using some .NET code between SQL 2005 server and WinForm clients using SQLCLR. This code has dependencies beyond .NET 2.0 which means I have to add these assemblies on the server machine (e.g. CREATE ASSEMBLY). I'm trying to do this as part of a CI build and am wondering what methods have worked best. ...

Error When Creating CLF Function

I've created an C# class that contains 2 static method and I successfully created an Assembly in SQL 2005. When I try to create a table-valued function from that Assembly I get the following error: Msg 6509, Level 16, State 36, Procedure , Line 2 An error occurred while gathering metadata from assembly '' with HRESULT 0x80004005. W...

How do you get the sql server username of the currently executing command in a CLR Trigger?

I know that SqlContext.WindowsIdentity returns the current user that is executing the command (in a CLR trigger), however, this only works for windows authenticated users. How do I get the username if it's a sql server user instead? Any help is appreciated. ...

what are the possible problems with CLR integration in Sqlserver

I read an article about using CLR integration in sqlserver and was wondering what some of the potential problems might be, if any. My thought was using it for validating potentiality bad data in a legacy Database. an example is a persons name in the phone number column. Edit: I don't think there are any problems, but it's not somethi...

sql join problem with negatives

I need to select data from two table using a join. This is fairly simple and have no problems here. The problem occurs when the field I am joining is used as two separate foreign keys (I didn't design this). So the ID field that I join on is either a positive or negative number. If it's a positive number it relates to ID_1 on the table_...

Any Best practice of doing table record insert with SQL CLR store procedure?

Recently we turned a set of complicate C# based scheduling logic into SQL CLR stored procedure (running in SQL Server 2005). We believed that our code is a great SQL CLR candidate because: The logic involves tons of data from sQL Server. The logic is complicate and hard to be done using TSQL There is no threading or sychronization or ...

Cannot call scalar-valued CLR UDF from select ... from table statement

I have created a scalar-valued CLR UDF (user defined function). It takes a timezone id and a datetime and returns the datetime converted to that timezone. I can call it from a simple select without problems: "select dbo.udfConvert('Romance Standard Time', @datetime)" (@datetime is of course a valid datetime variable) But if I call it p...

Is there anyway to be able to reference xml/text columns in the "inserted" and "deleted" tables in a SQL Server 2008 (CLR) trigger?

I get an exception when I "Select * from deleted" in a clr trigger in SQL Server 2008. Is there anyway to be able to access xml/text columns from the deleted and inserted tables in a trigger? ...