Hot answers tagged

11

You can only add references to those assemblies which have been registered with Sql Server. If they are not registered, they will no show up in the Add References dialog. There are a number of steps you'll need to do register a DLL, firstly you'll need to reconfigure your database: ALTER DATABASE [MyDatabase] SET TRUSTWORTHY ON; sp_configure 'clr enabled', ...


10

Yes, the Express editions do support CLR integration. The link is for the latest version, but if you switch to 2008 you will see it is supported there too.


10

First off, just to be clear: SQLCLR / .NET / C# / VB.NET cannot query the database. Only T-SQL can query SQL Server. So in order for SQLCLR code to get data or interact with SQL Server in any way, it must establish a SqlConnection, like any other .NET app, and submit T-SQL, or execute a Stored Procedure. Yes, you can call a SQLCLR (whether it is C# or VB....


8

At our company we have that exact setup. When you create a CLR assembly a binary representation of the assembly is stored within the database that you create it in. This enables you to take it with you (and even script it out) should you move the database at any point in time. A couple of months back our data center got flooded - filling several servers ...


8

I played around a bit to figure out what these settings do... when I commented, I only mentioned what our settings are without understanding them; I'm not the project lead for our migration to Database Projects, so I wasn't familiar with the minutiae of this stuff. The Model Aware property needs to be set to True. This property is poorly documented, but ...


8

You can either use Uri.UnescapeDataString (in System), in which case you will also need to do a Replace('+', ' ') on the string before passing it to Uri.UnescapeDataString, or if you would rather not bother with it, this function is available in the Free version of SQL# (which I am the author of). Importing System.Web is probably more work than it's worth. ...


8

This appears to be a duplicate of this question: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question. There is no obvious choice ...


7

Do not do it in SQL. From the few things that can be categorized as 'stupid use of SQLCLR', making expensive lengthy network calls ranks as #1. At the very very very very least, make sure the CLR code calls Thread.BeginThreadAffinity() before it goes into waiting for the intertubez to respond (DNS lookup and reverse lookup included). The proper way to ...


7

Only users mapped to a login that's a member of the sysadmin fixed server role can create and alter an assembly with an UNSAFE permission set defined. Therefore, to achieve your desired result you would have to add that respective login to the sysadmin fixed server role. BOL reference on CREATE ASSEMBLY: Specifying UNSAFE enables the code in the ...


7

The clr enabled server configuration option only controls whether user assemblies can be run by the SQL Server instance. The hierarchyid, geometry and geography types are system CLR types. These are contained in system assemblies, so are available regardless of the clr enabled setting. Similarly, other system features that rely on CLR integration, like ...


7

As you have noted, System.Web is an unsupported library. In order to reference System.Web you will need to make a call to CREATE ASSEMBLY. It seems like you tried that, but how did you reference the location of System.Web.dll? Did you copy/paste it to a different location? SQL Server will try to locate dependent assemblies in the same location. In other ...


6

The assembly binary is stored as a blob in the database, so it's carried wherever the database goes. CLR is only enabled on the instance -- there are no database-specific settings for that. In any event, why are you trying to do this? (I'm not trying to be argumentative; I just want to hear the motives involved, because perhaps the problem could be solved ...


6

A 2008 and 2008R2 user database is schema compatible, hence one version for both in SSDT. In other words, there aren't any schema objects that you could add to a 2008R2 SSDT model that couldn't be created in 2008. This isn't the same as either database version or database compatibility level. Database version: The database version is a number stamped ...


6

There is a more elegant solution that won't affect all other assemblies: just change the PERMISSION_SET of one of the assemblies in the app domain (app domains are per user). ALTER ASSEMBLY [AssemblyName] WITH PERMISSION_SET = {1 of the 2 levels that this assembly is not current at} Just remember that ...


6

You might be able to use the service broker, though that is probably overkill, as you state. Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that ...


6

Can I host the DLL on a different server so it will get run there? How would this work? If you want to run code on a remote server, you would still need code on the local server to be able to make that remote call. Although, you could set up an instance of Express Edition to host the SQLCLR code (it would need to be a Stored Procedure(s) and/or Function(...


5

You need to specify a SqlFacet for the return value. This is the same as specifying a SqlFacet for an input parameter, except you prefix it with return: For (max) length, use MaxSize=-1 in the SqlFacet attribute, example follows: using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [SqlFunction ...


5

I am assuming you are asking about alternatives to installing SQL CLR assemblies from Visual Studio. Having the code in Visual Studio is not required. Deploying CLR Database Objects on MSDN details the options, including SQL statements and deployment scripts.


5

I know this is a bit brutal, but what about disabling the CLR and re-enabling it? sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 0; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO


5

It would be a tough call between a CLR assembly and using SSIS given that your current migration script is just a script. CLR Assembly: Must be installed into a database (both the assembly binary and the exposed wrapper method(s)). Requires setting up an external project in Visual Studio, something you may not be very familiar with. Very easy to integrate ...


5

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used. Regarding "Security": If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren't any issues that I have ever been able to find. And ...


5

how to stored the actual IP address - in text or bytes format. Which is going to be better? Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither. Given the following information (from Wikipedia article on IPv6): Address representation The 128 bits of an IPv6 address are represented in 8 groups of 16 bits ...


5

In order to capture messages (either from PRINT or RAISERROR('', 1, 10) or like it) in .NET (regardless of calling a Stored Procedure or ad hoc SQL), you need to set up a method that will get called by the SqlConnection.InfoMessage event. The basic implementation is as follows: [SqlProcedure()] public static void Print_CLR() { using (SqlConnection conn ...


5

SQLCLR is the ability to run .NET code within SQL Server. When people speak of SQLCLR they are usually referring to the ability to write custom .NET code (Stored Procedures, Functions, Triggers, User-Defined Types, and User-Defined Aggregates). In this case, this ability can be turned on and off via the server option of "clr enabled" within sp_configure, ...


5

A lot of the .NET source code is available, and much of it is Open Source, though under various licenses. Still other code is available, but only for reference purposes and cannot be used in your code. Given that the stated goal is: I would like to increase that limit for a complex existing application that is bound to hit this limit. I don't wish to ...


5

As srutzky pointed you to some locations in his answer where some of Microsoft's source code is available and the function you requested isn't listed, I think we can conclude the source code isn't available under any open source license, and it looks like your question was about it being open source, not about being able to see the source code. Given that ...


5

The difference is between CLR static methods (type_name::method()) and instance methods (instance.method()) Static methods are defined on the type itself and are generally utility methods that get everything they need to operate passed in as method parameters. Instance methods operate on an object of a particular datatype and are able to access the ...


4

The answer is to add sys. in front of the hierarchyId: sys.hierarchyid::GetRoot()


4

I don't know a solution (and I don't know if the CLR scaffolding was ever designed to mimic the functionality you're talking about), but one workaround could be to create a T-SQL stored procedure in master that serves as a wrapper to relay the call to the CLR version. Marking it as a system object shouldn't be necessary, as long as a stored procedure with ...


4

There is no such thing as a stored procedure trigger. If you want an absolutely accurate count, then yes, change your CLR procedure (or whatever wrapper(s) it is called from) to write an entry to a log somewhere, every time. If "close enough" is "good enough" then you can periodically poll the DMV sys.dm_exec_procedure_stats which will tell you the ...



Only top voted, non community-wiki answers of a minimum length are eligible