How To Generate the deployment script wiht the binary string?

• Lowell

  SSC Guru

  Points: 323524

  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!

• Tom257

  SSChampion

  Points: 11543

  More actions

  March 21, 2012 at 7:39 am

  #1462130

  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: 323524

  More actions

  March 21, 2012 at 7:58 am

  #1462146

  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

  March 21, 2012 at 8:05 am

  #1462152

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

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