I need to write a boolean logic parser which will translate the boolean logic language to a SQL WHERE clause. The order of the operands will always be in the correct order (with value on the right). Here is a relatively simple example. There could be nested parentheses and the use of NOT operators, etc. (CACOUNT=01 OR CACOUNT=02 OR CA...
I have a SQL CLR dll I want to deploy, but have found you can embed the byte stream/varbinary_literal/ varbinary_expression/assembly bits into a text file to get around the messy hassle of packaging a DLL and making sure it's accessible for the CREATE ASSEMBLY command. But what I have yet to find is how to get that byte stream/varbinary...
I'm trying to write a SQLCLR function that takes a DateTime2 as input and returns another DateTime2. Based on this post I altered the parameter to be the C# type DateTime giving me the level of precision I require. However because the input can be null I would like it to be DateTime?; the return type as well. using System; using Micros...
I need to import records stored in a MySQL Database that I do not maintain into my Sql Server 2005 database (x64) We should import the records at an interval basis (probably 1 hour). What would be the best solution to perform the regular import? Windows Service (using reference MySql.data dll) Windows Client (could make it automated)...
I am porting an old 32-bit COM component that was written in VB6 for the purpose of reading and writing to an Active Directory server. The new solution will be in C# and will use SQL CLR user functions. The assembly that I am trying to deploy to SQL Server contains a reference to System.DirectoryServices. The project does compile wi...
I must have hit an unusual one, because I can't find any reference to this specific failing anywhere... Scenario: I have a legacy SQL function used to transform(encrypt) data. This function is called from within many stored procedures used by multiple applications. I say this, because the obvious answer of 'just call it from your cod...
We have a third-party DLL that can operate on a DataTable of source information and generate some useful values, and we're trying to hook it up through SQLCLR to be callable as a table-valued UDF in SQL Server 2008. Taking the concept here one step further, I would like to program a CLR Table-Valued Function that operates on a table of ...
Hi, I have the business logic of an workflow-like application in a C# class library, but at end of each month certain process in the library needs to be invoked, i thought that the ideal mechanism to invoke this scheduled execution is using the Sql server agent (sql job preferably, althought i'm open to even go into SSIS hell if its abs...
Question: In SQL Server 2005, how can I list all SQL CLR-functions/procedures that use assembly xy (e.g. MyFirstUdp) ? For example a function that lists HelloWorld for query parameter MyFirstUdp CREATE PROCEDURE HelloWorld AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].HelloWorld GO after I ran CREATE ASSEMBLY MyFirstUdp FROM ...
Question: If I add IF not exists to a create procedure as external name statement, I get a syntax error... why? Both statements work fine if I run them separately... IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PriceSum') AND type in (N'P', N'PC') ) CREATE PROCEDURE PriceSum(@sum int OUTPUT) ...
Hi I am trying to make a clr stored procedure for a sql 2005 database that uses .net 3.5 assemblies So first I had to change sql 2005 to recognize system.core as unsafe what I am not too happy about(I rather have had it say SAFE). Now I get this error Msg 6522, Level 16, State 1, Procedure StoredProcedure1, Line 0 A .NET Framework e...
Hello all, I have an after trigger implemented in SQLCLR assembly. Within it I'd like to know, what columns have been really updated (and their values have been changed). Unfortunately, SqlContext.TriggerContext.IsUpdatedColumn returns true, even when a column value is still the same. I guess, it's just because an SQL query, prepared b...
I have a trigger written in sql clr (sql 2005, .net 3.5) and I need to get at username, hostname and app_name. In TSQL I would simply use select suser_name() select host_name() select APP_NAME() In my .Net code I tried System.Security.Principal.WindowsIdentity.GetCurrent().Name System.Environment.MachineName System.AppDomain.Current...
I want to use CLR table-valued function in SQL Server 2008, which accesses a database from inside itself. I've got a permission exception. I am trying to execute function as the same user as under which it was created. So cause of the problem is not clear.. Here is the function: public partial class MyClass { [SqlFuncti...
Are there any benefits to call CLR SQL stored procedure from regular trigger rather then deploy CLR trigger straight away? I need to be notified on specific column change (StatusID) in very large table. Currently a number of windows services is used. Each monitors its own StatusID, i.e. quires db for specific StatusID: SELECT a,b,c FRO...
Hello all, I'd like to turn trigger recursion on/off in my SQL CLR trigger. According to http://www.devx.com/tips/Tip/30031, I have to call EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'/'false' Is there a way to get to know what the current DB name is? When creating trigger, I ask users to choose one, but I don't want...
I'm working with some SQL 2005 CLR code written in C#. We have recently altered a few of the functions to allow NULL parameters. We did this by changing parameters from 'double' to 'SqlDecimal' types. We successfully tested the changes in development and have moved to deploy the updates to the production server. We're using a SQL script ...
hi I have created a function to implement the regex.split in sql. Here's the code: private static IEnumerable<IndexedValue<T>> ToIndexedValue<T>(IEnumerable<T> list) { int idx = 1; foreach (T value in list) yield return new IndexedValue<T>(++idx, value); } private struct IndexedValue<T> { public int Index; public T Value; ...
I have an interface in CLR between SQL Server and the Exchange Web Services to Sync and send emails between applications. In testing this it works(ed) without any problems; we are seeing sporadic issues in the production environment where longer web service task appear to be have overlapped. My question is very simple and I cant decide ...
When attempt to debug a SQL CLR Function the debugger hangs. Using VS2010 and SQL 2005. ...