How To Generate the deployment script wiht the binary string?

Question

How To Generate the deployment script wiht the binary string?

Lowell

SSC Guru

Points: 323518
More actions
February 27, 2012 at 7:37 am

#257489

I've been fiddling around with some cutesy export functions via CLR;
I've been able to use Visual Studio to deploy my project, and I can also script stuff out and pass the path to the dll i created;
i've seen some other TSQL only deployment scritps that look something like this:
CREATE ASSEMBLY [RegEx]
AUTHORIZATION [dbo]
FROM 0x4D5A900{snip!}
WITH PERMISSION_SET = SAFE
How can you generate the suite of commands to deploy a dll that way?
all i can think of is the "hard" way, of loading the dll as a varbinary, then selecting it and copy pasting the value, and hten adding all the scripts for each procedure in the dll.
is there an easier way?
/****** Object: StoredProcedure [dbo].[CLR_ExportTableToHTML] Script Date: 02/27/2012 09:28:31 ******/
CREATE PROCEDURE [dbo].[CLR_ExportTableToHTML]
@TableName [nvarchar](4000),
@FilePath [nvarchar](4000),
@IncludeHeaders [int],
@FileName [nvarchar](4000),
@Title [nvarchar](4000),
@Summary [nvarchar](4000),
@HTMLStyle [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Lowell.FavoriteCLRS].[Lowell.FavoriteCLRS.FavoriteCLRs].[CLR_ExportTableToHTML]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CLR_ExportTableToHTML'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'FavoriteCLRs.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CLR_ExportTableToHTML'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=252 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'CLR_ExportTableToHTML'
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

If you select the Build...Deploy option in VS (at least in VS 2010), then Visual studio creates the deployment .SQL in the bin/debug or bin/release directory along with your DLL. Just edit that file to create your deployment script.

However you do need a local database where the deployment will work.

Or you can load the assembly into a local database as a DLL file, then select the binary from sys.assembly_files - the content field is the binary you are looking for.

Lowell SSC Guru Points: 323518 More actions · Answer 2

Tom Brown (3/21/2012)
If you select the Build...Deploy option in VS (at least in VS 2010), then Visual studio creates the deployment .SQL in the bin/debug or bin/release directory along with your DLL. Just edit that file to create your deployment script.
However you do need a local database where the deployment will work.
Or you can load the assembly into a local database as a DLL file, then select the binary from sys.assembly_files - the content field is the binary you are looking for.

Thanks Tom;

I also found out after this post that you can right click and script the Assemblies in SSMS;

Embarssingly, I was creating the scripts like that manually....loading the dll into a varbinary, selecting it to script....copy paste into my model...

i learned a little bit after this one.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tom257 SSChampion Points: 11543 More actions · Answer 3

I should have figured. The post was a month old.

Just getting into CLR stuff myself, all sorts of complexities to keep me occupied 😀