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 can I always close a text file from a CLR Stored Procedure even when the process is killed?

I have a C# SQL 2005 .net stored procedure which scrubs a text file looking for characters not in a range of characters and replacing them with another character. This works fine except when the process is killed. When this happens the file handle of the file being scrubbed is not released. I use a try catch finally block when opening the file and the output file. The finally section fiushes the output file and closes all files and streams but still when I go to access the file again or use the file in explorer it says the file is still in use. Should I be handling this some other way? How do I know the files will always be closed correctly.

This behavior could happen because that when you kill the process, your finally section may not be called. Eventually, the file handle will be released when garbage collector kicks in.

Hosting SQL Server 2005 As a Runtime Host

I am trying to use the new Common Language Runtime (CLR) hosting feature to write stored procedures in C#
i have added
Microsfot.sqlserevr.server name space
and ia m trying to use sqlContext Object as below

using (SqlConnection connection = new SqlConnection(dbConn))
{
connection.Open();
SqlCommand sqlcmd = new SqlCommand("select @.@. version", connection);
SqlContext.Pipe.ExecuteAndSend(sqlcmd);
}

i get the below error when i execute (SqlContext.Pipe.ExecuteAndSend(sqlcmd);)

System.InvalidOperationException was unhandled by user code
Message="The requested operation requires a SqlClr context, which is only available when running in the Sql Server process."
Source="System.Data"

i checked if (SqlContext.IsAvailable) and it returnsfalse as well.

Please le me know how to make it work.

Thanks

THNQDigital

Try to use context connection in this way:

using (SqlConnection connection = new SqlConnection("context connection=true"))

You can find an example here:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlpipe(d=ide).aspx

And here is an article about context connection:

http://msdn2.microsoft.com/en-us/library/ms254981(d=ide).aspx

|||

Thank you Jay.

But "context connecion = true", means we are not providing any user credential to login to sql server. The managed code has to run in the same process as SQL server right?.

sqlContext.IsAvailable has to reurn true in order to confirm that managed code is running in the same process as sql server ( in process). For me 'sqlContext.IsAvailable' is returning false.

How do we acheive in process communication bewteen managed code ( c#) and sql server. In other words how do we make sqlContext.IsAvailable return true.

Please le me know Thanks for your help

THNQDigital

|||

THNQdigital:

But "context connecion = true", means we are not providing any user credential to login to sql server. The managed code has to run in the same process as SQL server right?.

Yes, I agree with you.

As I understand the sqlContext.IsAvailable should return true when the code is running inside SQL Server using common language runtime integration--that means in SQL you can create an assmebly pointing to the dll file compiled from your code, then create UDF or stored procedure to reference the code. You may take a look at this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp

|||

Hi Jay,

The Link you provided above is not taking me to the related topic. Could you please verify and re send me the correct link. I greatly appreciate your help. Thanks.

THNQDigital

|||

Sorry it's my fault, please try this one

http://msdn.microsoft.com/library/en-us/dnsql90/html/sqlclrguidance.asp?frame=true