A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

re: A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

Jason Shantz — Tue, 14 Dec 2004 18:13:00 GMT

What does the 'Deploy Solution' command do behind the scenes? Deploying from VS is great when developing, but in production I'll need a scripted approach of some kind.

Also, which files must be deployed and which are optional?

Thanks!

re: A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

G. Man — Tue, 14 Dec 2004 20:21:00 GMT

A great introductory article and I second Jason's questions.

I also want to see a detailed discussion not on HOW you do this, but WHY. The code same you posted is traditionally part of the "data layer" of a web application. What are exactly the benefits and drawbacks to physically moving the code to the database server itself?

RE: A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

GisSharpBlog — Wed, 15 Dec 2004 13:11:00 GMT

A Simple Walkthrough for deploying a SQLCLR Stored Procedure

Raymond Lewallen — Wed, 15 Dec 2004 16:32:00 GMT

A Simple Walkthrough for deploying a SQLCLR Stored Procedure

re: A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

Eric Newton — Thu, 16 Dec 2004 07:19:00 GMT

G Man: it would take a lot longer article to detail benefits versus drawbacks of the idea... ie, i've seen requests for asp.net inside the procedure... um, hello? (not to G Man)

Another stipulation that needs to be mentioned is that CLR sprocs REQUIRE the static keyword. Since sprocs cant have state... its a good way to guarantee no state. (Good job)

Walkthrough of building a SQLCLR stored proc with VB

Christa Carpentiere — Fri, 17 Dec 2004 01:32:00 GMT

Walkthrough of building a SQLCLR stored proc with VB

re: A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

Doro — Thu, 16 Dec 2004 23:05:00 GMT

thank you and what about parametrised SP

re: A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

Brad — Tue, 04 Jan 2005 18:18:00 GMT

1. As expected, it is possible to deploy manually. If you have any SQL Server 2005 documentation, the details should be contained there. The Deploy command essentially is as follows:

CREATE ASSEMBLY statement:

CREATE ASSEMBLY [ assembly_name ]
[ AUTHORIZATION owner_name ]
{FROM { client_assembly_specifier |
assembly_bits [,…]
}
}
[ WITH option_list ]
option_list ::=
option_spec [,…]
option_spec::=
PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

client_assembly_specifier::=
'\\machine_name\share-name\[path\]manifest-file-name'

assembly_bits::=
{ varbinary_literal | varbinary_expression }_

Conversely:
DROP ASSEMBLY assembly_name [,…]
[WITH drop_option]
drop_option::=

The CREATE PROCEDURE (and CREATE FUNCTION) statments have been altered to support SQLCLR. The following is the syntax for creating a procedure:
CREATE PROCEDURE
proc_header
[ WITH {proc_attribute} [,…] ]
[ AS ]
body_definition
proc_header::=
proc_name ( [ {param_declaration} [,… ] ] )

proc_name::=
[ proc_schema_name. ]proc_object_name

param_declaration::=
@param_name param_type [ = default_value ]
[ COLLATE collation_name ] // might be cut
[OUTPUT]
body_definition::=
{ [BEGIN] procedure_body [END] }
| { EXTERNAL NAME method_specifier }

method_specifier::=
[assembly_schema.]assembly_name:
external_routine_name

external_routine_name::=
/* as defined in CREATE FUNCTION */

proc_attribute::=
ENCRYPTION
| EXECUTE AS { SELF | CALLER | USER = user_name }

2. The only file that is required to be deployed is the assembly. That said, other files may be deployed with the assembly. Visual Studio, by default, deploys the code file w/ the assembly. This facilitates SQLCLR debugging. There is an option on the property page of the SQLCLR project to turn off deploying of code files as well.

Odds and Ends

roy ashbrook — Sun, 01 Jul 2007 05:10:05 GMT

Stuff I've been intending to post a meaningful post about, but haven't: If you have ever wondered