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

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

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


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

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


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

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

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


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


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

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

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


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

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


2

As far as I can tell (I did some testing of my own), this is not supported. The documentation hints at this: If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, ... Since there's no mention of a class-qualified name, I'm assuming SQL Server will traverse the hierarchy only by namespaces, with the last name ...


2

It is not just the compatibility level. Also the .NET options themselves are the same against both 2008 and 2008 R2. In a word: Is there not a SQL Server 2008 R2 specific option? No Is this because SQL 2008 R2 shares the same compatibility level of 100 with SQL Server 2008? Not exactly, but it doesn't really matter.


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

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


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


1

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



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