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

how best to tackle logins with SQL authentication after automatic failover

In an sql authentication environment with an automatic failover in database mirroring how to you manage new logins which have been created on the principle since the start of mirroring? Since the master cannot be mirrored, and the mirror database cannot be read during mirroring (except as a snapshot) in order to find the missing logins, I assume that only after failover a script should run to create the new logins and then run sp_change_users_login . The qestions are:

1) should the script create a new login first and then run sp_change_users_login with option update_one , or should sp_change_users_login using option

Auto_Fix create the missing logins?

2) But what is the password of these users? is it initially NULL , as a consequence of sp_change_users_login? What about the SIDs?

3) Or should we bypass sp_change_users_login altogether and use

CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...as described in http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx

4) What is the event that would trigger this script to run after the aitomatic failover ?

Is there a definitive MIcrosoft agreed apon and recommended method to tackle this?

If you want your application users to connect after failover you better create those logins beforehand @. both the principal and @. mirror server @. the time of configuring mirroring so that they will be available to you but will be mismatched and after failover you can correct them using sp_change_users_login sp.

and for the less important ones you can make use of SSIS to transfer logins frequently so that after failover you can just restore the master syslogins table or master db and fix the mismatch by change users login sp......

|||

Thank you for your anwer. My question was about the new logins created after mirroring had begun. Of course before mirroring started the existing logins were recreated on the mirrorserver beforehand . The question was whether sp_change_users_login should be used with update_one or Auto_Fix or not used at all . The auto fix option creates a login if there isn't one. The update_one option does not .

Assuming rhat SSIS would transfer the new logins from the princiopal master to the mirror master while database mirroring would transfer the users from the principal to the mirror. Will the SIDs be in order? If a script has to be run it would have to be run automatically so what is the trigger that this script will be triggered by?

|||

Actually there are a few issues here:

Firstly SSIS will not help you because as of 2005 the Transfer Logins task does not transfer the passwords, it resets the passwords which to be honest wouldn't help especially if you are using the High Availability option.

Next, you need to take care of the SID's otherwise you will have to sync them. See this KB article

Lastly, In 2005 you cannot create a login which has a default database that is offline. So if an app needs to use Automatic Client Redirect in a High Availability setup and the account is created on the Principal after mirroring has been configured, you will not be able to add the login on the Mirror. The only workaround is to bring the mirror online periodically and sync the logins. I heard it may be fixed in 2008 however; I have not tried yet, maybe someone can clarify for me.