Notice: I am a DBA. I am not a developer and have very little experience in tsql besides the basic stuff so far so please keep it simple or use comments in your code PLEASE!
Problem: We turned on an interface from one system to a different system after 6 months of uptime. Now we need to go back and get the first 6 months of data and move it to the other system. The database has a field called document that is of data type 'image' / blob. I have the query to get the facility,firstname, lastname, episode, account number, doctitle, doctype (pdf), and the documents binary blob.
I need to copy/move the .pdf to a fileshare while renaming the pdf based on the facility, firstname, lastname, episode, account number, etc.
How do I rename the file based on the other fields? How do I create a .pdf from a blob?
asked Aug 01, 2012 at 06:31 PM in Default
On SQL Server 2005+ you can create a CLR function for writing the BLOB data back to files. Below is a code which creates a very simple scalar CLR function which handles the writing.
For example using a C# Express you crate a new Class Library. Put the above code as the source of any class. Then compile it and register it as Assembly in your database. The assembly needs External Access permission set and therefore your database have to be set as TRUSTWORTHY
Also CLR needs to be enabled on the server:
Once CLR is enabled and TRUSWORTHY SET, you can register your assembly:
After that you can register the Scalar function:
Once registered, you can use a simple selects to write the blob data to the files:
answered Aug 03, 2012 at 06:16 AM
hey Vertigo...not sure if this will help at all. I've created an 2005 SSIS package with a VB.net script to export images to file.
Here is the script first,
Option Strict Off Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.io Imports System.Drawing.Imaging Imports System.Drawing
Public Class ScriptMain Inherits UserComponent
Then in the package, just pass the image and the length of the image into the script. The script is a destination, not a transformation.
answered Aug 03, 2012 at 03:19 AM