We need to be able to install an assembly to a remote SQL Server database during an MSI installation. In order to do this, we have a SQL script with a replacement tag where the assembly data (hex encoded with the 0x prefix) should go. Now I need to figure out how to make a custom action read the binary file data for the assembly from the MSI file, and put the hex content into a property that InstallShield's "SQL Scripts" action can substitute into the SQL script.
I'm stuck at the point where I need to read the binary file data from the MSI file. I could wait until the deferred context to read the file data from the file system after it's installed, but that doesn't seem to be the way MSI "wants" to operate seeing as how it makes accessing properties so difficult in the deferred action context. I'm not even sure I would still be able to modify the SQL script at that point - it would probably be too late. I think the "right" way to do this is to have the SQL script that will be executed already determined before the deferred action phase begins.
I'm looking for a solution better than my backup plan of having the build process replace the content in the SQL script at build time (because that means we essentially deliver two copies of the file, one for reference/maintenance purposes, and the other included in SQL script).
So how can I read the binary content of a file that MSI will install from a custom action, or is there a better way to install an assembly for a CLR to a remote SQL server?
I would recommend doing what you are trying to avoid doing ;-), and that is having the build process create the SQL script with the CREATE ASSEMBLY [name] FROM 0x.... in it.
I'm looking for a solution better than my backup plan of having the build process replace the content in the SQL script at build time (because that means we essentially deliver two copies of the file, one for reference/maintenance purposes, and the other included in SQL script).
I disagree with the conclusion here. You only need the script with the FROM 0x.... in it. You do not need to keep one that references a path to a DLL. That is not very portable, even outside of the MSI installer scenario. From an SDLC ("Software Development Life Cycle" for those wondering about that acronym) perspective, it is best to have a release script that is self-contained: no external dependency on a DLL somewhere on the file system. And from a "reference" perspective, there is not much benefit to having the DLL outside of possibly viewing its code using ILSpy or Red Gate's .NET Reflector, and that would just be to verify what was "shipped" since you have the actual source code.
The only times I ever bother with the DLL is for demos, presentations, and articles. When it comes to integrating something into a build process, I just read the DLL into a string of hex codes, broken at every Nth character with a back-slash (\) as the final character of the line since that is the T-SQL line-continuation character. An example of this is the following SQL script on Pastebin.com:
SQLCLR Meta-data function for cross-database IndexName()
That script installs an Assembly and creates a Function as a working example of the code I posted in this DBA.StackExchange answer:
Central stored procedure to execute in calling database context