Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Generate the deployment script wiht the binary string? Expand / Collapse
Author
Message
Posted Monday, February 27, 2012 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1258245
Posted Wednesday, March 21, 2012 7:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:50 AM
Points: 1,234, Visits: 1,435

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.

Post #1270159
Posted Wednesday, March 21, 2012 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1270178
Posted Wednesday, March 21, 2012 8:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:50 AM
Points: 1,234, Visits: 1,435
I should have figured. The post was a month old.

Just getting into CLR stuff myself, all sorts of complexities to keep me occupied
Post #1270184
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse