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.


8

The typical way to do this in SQL Server 2005 and up is to use a CTE and windowing functions. For top n per group you can simply use ROW_NUMBER() with a PARTITION clause, and filter against that in the outer query. So, for example, the top 5 most recent orders per customer could be displayed this way: DECLARE @top INT; SET @top = 5; ;WITH grp AS ( ...


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


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

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


6

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

In DBMS, like MySQL, that do not have window functions or CROSS APPLY, the way to do this would be to use standard SQL (89). The slow way would be a triangular cross join with aggregate. The faster way (but still and probably not as efficient as using cross apply or the row_number function) would be what I call the "poor man's CROSS APPLY". It would be ...


5

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


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

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


4

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


4

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


4

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


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


3

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


3

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


3

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


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

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


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

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

I typically use a combination of CTEs and windowing functions. You could achieve this answer using something like the following: ;WITH GiveMeCounts AS ( SELECT CustomerID ,OrderDate ,TotalAmt ,ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY --You can change the following field or sort order to ...


2

Stop worrying about timer/queue waits like CLR_SEMAPHORE. These waits always accumulate wait time, but this is not "bad" wait time in 99.999% of all cases. You should be filtering them out of your analysis and focusing on the ones that matter and that you can do anything about anyway. Use Paul Randal's NOT IN list as a guide: Wait statistics, or please ...


2

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


2

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


2

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


2

I can manually write DDL to reference the stored procedure with any number of variables, defaulting them all to null. However, is there a T-SQL way to say "this stored procedure has a variable number of arguments" in the same way as sp_executesql does? No, there's no built-in way to do this. Is my only option to use an extended stored procedure? ...


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

SQL Server does not rely on the binary order for its "own" data types. For CLR datatypes you could use the iComparable interface, but as @MattJohnson mentioned, SQL Server ignores it: http://connect.microsoft.com/SQLServer/feedback/details/252230/sqlclr-provide-the-ability-to-use-icomparable-or-a-similar-mechanism-for-udts Microsoft does not publish the ...



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