Tag Info

Hot answers tagged

9

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.


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

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 ...


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

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

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 ...


5

You can either use Uri.UnescapeDataString (in System.Net), 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 ...


5

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 ...


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

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 ...


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

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 ...


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 ...


4

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


4

How are you migrating the database? Copying a database to another server via Backup/Restore or Detach/Attach will include the Assemblies as well as the T-SQL wrapper objects that point to the code in the Assemblies. Using a tool that lets you select object types might require that you at least verify that Assemblies have been selected to migrate. If all of ...


3

From the xp_logevent topic in the documentation: ' message ' Is a character string with a maximum of 2048 characters. So, I would change this: Declare @message nvarchar(max); To this: Declare @message nvarchar(2048); Or possibly even non-Unicode (the docs aren't clear): Declare @message varchar(2048); Also, severity should be delimited as a ...


3

Microsoft has a KB article about this behavior. You got a 32-bit OS with 32-bit application, so the process' address space is 4 GiB. AWE can be used to access more memory, but it is limited only for buffer pool usage. As per the documentation: The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be ...


3

That depends. If the assemblies were created and live in the database that is being migrated, then they will go with the database as they are already objects inside of it. If the assemblies were created in other databases that aren't being migrated then they will need to be re-created in the source instance somewhere. For example, if the assembly was made ...


3

SQLCLR assemblies can be installed with three levels of security access: SAFE | EXTERNAL_ACCESS | UNSAFE. This is amply documented, refer to CREATE ASSEMBLY and Designing Assemblies: Managing Assembly Security You can control how much an assembly can access resources protected by .NET Code Access Security when it runs managed code. You do this by ...


3

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 ...


3

Here are some thoughts on this: Do you know that you should get better compression on the strings that you have tested with? Have you tested those same strings by gzipping outside of .NET? Such as on Linux / CygWin -- DOS port of UNIX utilities / PHP / etc? If you have updated your system with .NET 4.5, then you are using the updated GZipStream. This is ...


3

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 ...


3

It appears that this behavior is specific to connections using "Context Connection = true;". I have tried to get around this by writing out the try-catch-finally structure instead of using the using macro, but that had no effect. A Microsoft Connect bug was filed almost 3 months ago regarding this behavior. In that Connect bug it was speculated that THROW ...


3

Yes, stay away from the sp_OA* OLE Automation procedures. If you want / need to handle this purely within SQL Server then you can do so using SQLCLR, which replaces the sp_OA* OLE Automation procedures. And if you aren't using xp_cmdshell for anything else, then no need to enable it just for this (although to be fair, if using SQL Agent, then a CMD step ...


2

What have you tried and what error are you getting? The following works for me on SQL Server 2008 R2. C#: namespace CLRTest { public static class ParamsTest { [SqlProcedure] public static void Test(out int p) { p = 5; } } } SQL: CREATE PROCEDURE dbo.Test(@p int OUTPUT) AS EXTERNAL NAME ...


2

Yes, this is supported. A note of caution, though: to support 2005, you have to be extremely careful to thoroughly test your application on 2005, because some of the functionality is limited compared to 2008+, and the differences are buried in the documentation. For example, we had to deploy a user-defined aggregate function for our database, which is ...


2

There are a couple of prerequisites to use the SCHEMABINDING option: · You cannot use * in the SELECT clause in the query, you have to specify column names · You have to use two-part naming convention when referring to objects (which is in general a good practice) So, you need to use schema name always when referring to objects when you ...



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