The tag has no wiki summary.

learn more… | top users | synonyms (1)

2
votes
1answer
76 views

How to capture PRINT statements from TSQL called by CLR stored procedure

I have a CLR stored procedure that (among other things) calls a TSQL stored procedure. The TSQL stored procedure runs some dynamic SQL, and it prints the SQL before it runs for debugging purposes. ...
3
votes
2answers
116 views

Export a table and zip with password

I would like, from TSQL, to use a view to extract data from a table, output it with BCP, then use non 3rd party tools to ZIP it with a password and AES encryption. Optionnally, I could use a 3rd party ...
5
votes
1answer
64 views

Can CLR execution continue after 'THROW' is encountered in T-SQL?

I am calling a T-SQL stored procedure from a CLR stored procedure. and the THROW statement in T-SQL stops the execution of CLR without going into the catch block. Is there something I can change in my ...
2
votes
2answers
41 views

CLR/C# equivalent to XACT_STATE()

I am porting a set of T-SQL stored procedures to C#/CLR. In the T-SQL, we use XACT_STATE() to determine if an error requires rolling back to a savepoint or rolling back the transaction entirely. I ...
0
votes
2answers
33 views

Grant execute on xp_logevent to sa using sa user to do it

Running a SQL Server 2012 CLR stored procedure with this code: const string executeLog = "EXEC master..xp_logevent @errorNumber, @message, informational"; using (SqlCommand cmd = new ...
0
votes
1answer
47 views

Log debug messages in a SQL CLR C# Stored Procedure

I have developed a SQL CLR stored procedure in C#, for SQL Server 2012. I want to log messages to SQL Server registers. On a normal stored procedure I use xp_logevent What can I use to log a message ...
0
votes
1answer
89 views

CLR Assemblies and SQL Server Availability Groups

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. CREATE ASSEMBLY drawing FROM ...
10
votes
2answers
339 views

Crashing CLR on SQL Server 2014 (windows 2012R2)

I have this small CLR that does a RegEX function on a string in columns. When running on SQL Server 2014 (12.0.2000) on Windows Server 2012R2 the process crashes with Msg 0, Level 11, State 0, ...
1
vote
1answer
125 views

SQL Server to XML files alternatives

I am currently looking for an alternative to writing a large, complex XML file based on a known specification. Currently the file is written in t-sql as a stored procedure. The output is in a results ...
0
votes
2answers
402 views

Storing IP addresses - varchar(45) vs varbinary(16)

I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual ...
1
vote
1answer
50 views

SQL CLR very slow on Amazon EC2 Instance (SQL Server 2014 CU6)

I am running a SQL Server 2014 Standard Edition (Cumulative Updates 6) instance on an r3.large Amazon EC2 box. I noticed that the CPU usage of SQL Server skyrockets to 100% while executing pretty much ...
0
votes
1answer
111 views

SQL CLR in MySQL? [closed]

Can we call SQL CLR in MySQL Server using User Define Function? I want to create trigger in mysql. It will call user define function and this UDF will update the table in SQL Server.
1
vote
0answers
11 views

CLR UDT SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
1
vote
0answers
72 views

Update SQL Server .net version

I have used this article to detect the latest install .net version and here is the result: Its .net 4.0 and I want to know if the SQL Server is using the latest installed version? I want to ...
4
votes
1answer
314 views

GZipStream/DeflateStream compressions alternatives

I have created a simple CLR functions for compressing/decompressing NVARCHAR columns: [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)] public static SqlBinary Compress( string ...
1
vote
2answers
400 views

Security or performance risks using SQL CLR [closed]

Are there any particular security or performance risks in using the CLR in SQL Server ?
3
votes
1answer
2k views

resource wait SQLCLR - wait_type CLR_SEMAPHORE by SPID 0

UPDATE In the comments under Aaron's post is an extensive explanation why I want to find this out, please read it if you want to know. The system is running some ETL, reporting services and a ...
2
votes
1answer
230 views

In a SSDT project how to avoid redeploying the assembly?

I have a SSDT project that has a few sql clr elements. They rarely get altered but every time I redeploy my db project it decides it must drop the assembly and recreate it. I think its somewhat ...
0
votes
1answer
155 views

xp_logevent Invalid Parameter Type

I'm working with SQL Server 2012 Enterprise Edition SP2 on a Windows 7 32bits. I have this code inside a stored procedure. Begin try -- EXEC CLR Stored Procedure exec ...
4
votes
3answers
4k views

Assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog

I am trying to deploy a SQL CLR function using the HTTPUtility.UrlDecode method of System.Web but I can't get it to deploy. Error received: .Net SqlClient Data Provider: Msg 6503, Level 16, State ...
4
votes
1answer
176 views

How can “HierarchyID” type work when “CLR” is disabled?

The following statement sp_configure 'clr enabled' returns: name minimum maximum config_value run_value clr enabled 0 1 0 0 I assume this means there are no CLR ...
1
vote
0answers
618 views

“Failure occured while attempting to connect to the Principle server” from CLR procedure

I have a Windows Service running under a domain account which calls a stored procedure which then calls a CLR stored procedure. Initially I had a problem where database ownership chaining wasn't ...
3
votes
2answers
836 views

Migrating CLR Assemblies

I am migrating a SQL Server 2008R2 database to a new server. (also 2008R2) There are a number of CLR assemblies on the server. Will these automatically migrate with the database or will I have to ...
0
votes
0answers
64 views

I am trying to implement Scott Chamberlain's code in SQL Server 2008 R2 . Getting Assembly not present errors

I am trying to use Scott Chamberlain's code, here: http://dba.stackexchange.com/a/17466/45380 I was able to compile it and create the DLL, but now when I try to run SQL CREATE ASSEMBLY I get: Msg ...
3
votes
2answers
3k views

App domain is marked for unload due to memory pressure

In my sql server log (2005 SP4) I have this message every couple of seconds : "AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure" Our server : Windows Server ...
1
vote
1answer
305 views

Variable numbers of parameters in a SqlClr UDF or Stored Proc

Lets say I have the following signature for a CLR Stored Proc or UDF: [Public] public static void RaisError( string message, short severity, short state, params object[] args) ...
4
votes
1answer
1k views

Reference assembly in sqlproj not being deployed to server

I have a Visual Studio 2013 Database Project which takes a modified version of fastJSON as a reference. I selected Generate DDL in the reference properties as shown in this screenshot: And in the ...
2
votes
1answer
326 views

Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX)

I have a SQLCLR stored procedure with the following signature in Visual Studio 2013: [SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)] public static ...
0
votes
0answers
171 views

Comparing CLR Functions in Visual Studio SSDT

I'm comparing the CLR functions from a pair of SSDT projects to a database. The only difference shown in the comparison is on two properties: IsDeterministic and DataAccess The compare shows that the ...
2
votes
1answer
322 views

How to access external DLL's static methods that incorporate output parameters in SQL Server

I know how to load a CLR DLL in SQL Server and access to its static methods which does not incorporate output parameters in their method signatures but what I cannot realize is how I can use methods ...
6
votes
1answer
370 views

SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger?

On a thread today I posed a desire to get code to execute immediately when a record is inserted rather than resorting to polling. Triggers are powerful tools, yet dangerous places to put real code, ...
0
votes
0answers
212 views

XML Serialization in SQL Server 2005 CLR

I know this question has been asked before, but none of the answers I've tried seem to work for me. I'm trying to execute a CLR Stored Procedure in Sql Server 2005. The CLR generates a file, sends it ...
4
votes
1answer
383 views

Suggest better design pattern for reverse DNS lookups

I'm working on a project where I need to look up the hostnames associated with IP addresses who were logged making HTTP requests. The lookups currently happen as part of a daily ETL job. The current ...
2
votes
1answer
673 views

Counting the number of calls of a stored procedure

I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data). How can I let a counter ...
3
votes
1answer
243 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...
4
votes
1answer
508 views

Understanding MS SQL Server Date Types

Consider the following: declare @dt datetime, @dt2 datetime2, @d date set @dt = '2013-01-01' set @dt2 = '2013-01-01' set @d = '2013-01-01' select convert(varbinary, @dt) as dt, ...
2
votes
0answers
611 views

Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way

Consider a CLR stored procedure that accepts an XML parameter and calls another stored procedure in the same schema: [SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = false, ...
2
votes
0answers
336 views

Multithreading caching in SQL CLR

Is there any mechanism available to a SQLCLR assembly for caching that will still work when the assembly is registered as "safe"? The cache needs to be thread-safe, so it can be shared by multiple ...
1
vote
0answers
717 views

Asynchronous processing of SQL CLR results that can dynamically add more work [closed]

I have an application that consists of Web-services that make calls to a sharded SQL Server database. Each database node has a SQL CLR stored procedures, which given some number of records to process, ...
4
votes
1answer
914 views

How do I create a CLR UDF with external name specifying namespace, class, and subclass?

I am creating a CLR UDF using external name and second level nested class names seem not to be permitted. Is this the case? When using the following syntax and including a first level class name and ...
2
votes
2answers
413 views

Is there a SQL Server 2008 R2 specific target for SSDT projects?

I am making a SQLCLR stored procedure in Visual Studio 2012. I have these options for target platform: Is there not a SQL Server 2008 R2 specific option? Is this because SQL 2008 R2 shares the same ...
5
votes
1answer
8k views

How to grant a user UNSAFE ASSEMBLY permission

How can we assign a user , e.g.: the database owner (DBO) the UNSAFE ASSEMBLY permission? I'm asking it to enable a user to create an assembly and don't know the SA password, so EXEC sp_changedbowner ...
5
votes
1answer
864 views

Calling a .NET method in a SQL Server script

In the data migration process, I need to convert some columns values form the source system before writing to the target table. I've been provided with a Csharp code which reads a string and do the ...
5
votes
1answer
492 views

Is there anyway to make sql server search the master database for SQLCLR stored procedures before the current one?

For T-SQL stored procedures with the prefix sp_, SQL Server will search for and user a procedure in the master database before the current one. This does not appear to be the case with a SQLCLR stored ...
2
votes
2answers
179 views

Visual Studio DAC with Schemabound View containing hierarchyid::GetRoot() - Two part name needed?

In Visual Studio, when trying to create a DAC which has a schemabound view which contains hierarchyid::GetRoot() this error is generated and the project does not build. However, the t-sql is ok as if ...
8
votes
2answers
875 views

Other than restarting SQL Server, is there any way to force the SQLCLR AppDomain to be reset?

I want to force the AppDomain being used by SQLCLR to be reset. How can I do that besides restarting the SQL Server instance?
2
votes
2answers
487 views

SQL server job deployment

I have a SQL Server job which has got different steps, including running a SSIS package. Currently it is on my PC (my local instance). I should deploy it to UAT and assume some steps need to be ...
10
votes
2answers
3k views

Do SQL Server Express Editions support CLR procedures?

Do SQL Server Express Editions (2005, and 2008) have support for installing, enabling and running CLR Stored procedures and functions.