how to deploy SQL Server Database Project w/o Visual Studio.

i need a regex support in SQL 2005 server.

i've downloaded SQL regex support project fromhttp://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx?loc=en.

but in order to enable regex support i needed to open VS 2005. compile the project and deploy it to SQL server. now i can use Regexp-s in SQL queries.

However this is not elegant way to deploy everytime SQL Database project via Visual Studio to every MS SQL server i want.

my question is : how can i deploy without using Visual Studio?

As long as the assembly is compiled into a DLL, you can use the CREATE ASSEMBLY and then the CREATE FUNCTION commands to install the SQLCLR assembly.

If it isn't compiled, you can download the free .NET Framework 2.0 distribution from Microsoft and use the command line compilers to create the DLL.

This will take a little work and will require learning a few things about SQLCLR code. Are you familiar with any of this?

Don

|||

no, i'm not familiar with CLR. :(

|||

Okay, let's take this a step at a time. Do you have a DLL file for the library you want to use?

Don

|||

i have a dll.

i can create an SQL assembly:

USE [myDB];

EXECsp_configure @.configname='Show Advanced Options', @.configvalue= 1;

RECONFIGUREWITHOVERRIDE;

EXECsp_configure;

CREATEASSEMBLY RegexFROM'C:\Msdn.SqlRegex.dll'WITHPERMISSION_SET=SAFE ;

what is next?

PS: thanks :)

|||

Cool. You're most of the way there. The last step is to use CREATE FUNCTION to create the function you can call from T-SQL. Unlike when you create T-SQL user-defined functions, this time you're going to create a function name linked to a function in the regex assembly. The syntax will be something like this:

CREATE FUNCTION dbo.MyRegExFunc (@.myString nvarchar(4000))
RETURNS nvarchar(4000)
AS EXTERNAL NAME Regex.MyRegExFunc
GO

That's the basic syntax, but you'll have to adjust it for your actual code. A couple of things to note: The name of the function in T-SQL doesn't have to match the name of the function in your assembly. Here I called them both MyRegExFunc, but you can make the T-SQL name any legal name. It's is convenient and easiest to make them the same, however. If you use namespaces in your .NET code, the EXTERNAL NAME might be a bit different. And you have to match the input parameter and return data types, more or less.

Make sense?

Don

|||

thanks alot!!!

i've found the full syntax for creating the functions (just calling the MODIFY in SQL on already imported function from assembly) :

CREATEFUNCTION [dbo].[exportedFunctionName](@.input [nvarchar](max), @.pattern [nvarchar](4000))

RETURNS [bit]WITHEXECUTEASCALLER

AS

EXTERNALNAME [assemblyName].[classInDLL].[functionToExport];

again , thanks alot for helping