How Can I automatic create all that Objects in assembly in simple way ?

Hi

I use SQL code snippet to attach the CLR assembly (dll) to the SQL Server Express

CREATE ASSEMBLY [DatabaseAndImages]

AUTHORIZATION [dbo]

FROM 'C:\CLR_File.dll'

WITH PERMISSION_SET = SAFE

That file content CRL Codes for 10 Stored Procedures & 3 UDT & 5 Functions & 6 Triggers

As you can see it's content large amount of DB objects !!

My Question is ..

Is there any simple way can I use it to extract or automatic create all that Objects in the Database without use separate SQL Statement for each one ?

By Example , I will use this SQL statement to create the sp_AddImage that already located inside the CLR dll file

CREATE PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName [nvarchar](max),

@.Image [varbinary](max)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

But as you know .. I have many objects .. and I am in development phase and I will do change to that object many time and also may I will add much more …

I thing it's not good to write SQL Statement for each object and do changes every time when I change the object definition

Is there any one line of SQL statement can I use it to automatically create and extract all the objects inside the assembly ?

Or is there any way to do that Issue by simple operation ?

And thanks with my best regarding

Fraas

If you use Visual Studio 2005; it has this new project type: the Sql Server Project which allows you tol do automatic deployment of the assembly as well as creation of the objects. It has it's limitations though - only for VS 2005 Professional and above, and it can not do ALTER (it always re-deploys everything), and it doesn't script out the objects. But as I said, it does automatic deployment.

If you want the ability to script out the objects, etc you can try a project type (and deployment mechanism) I wrote as an add-in to Visual Studio. You can find more info here.

Niels|||

Hi Fraas,

This blog post: http://blogs.msdn.com/sqlclr/archive/2005/11/21/495438.aspx details a ddl trigger that will create all the objects in your assembly when you create it. This hopefully will solve your problem without requiring Visual Studio.

Steven

sql