Tag Info

Hot answers tagged

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

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

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


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

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


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


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

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


1

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



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