Getting the files stored in a database table?

• yasar2002

  SSC Rookie

  Points: 27

  More actions

  March 11, 2013 at 2:26 pm

  #269771

  Hi All,

  I am struggling to find a solution / guidelines / 3rd party or internal database tools that allows me to extract files stored in a database table. These files have multiple formats, PDF, Word, Excel, Images etc.

  Please advise me, I have limited or no knowledge on visual studio.

  Thanks

  Yasar

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  March 12, 2013 at 7:10 am

  #1595982

  The SSIS Export Column Transformation can do that for you.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• fmuzul

  Old Hand

  Points: 314

  More actions

  March 13, 2013 at 9:13 am

  #1596570

  Hello, you don't need any third party tool.

  You can extract binary files from SQLServer BLOB fields using VBScript.

  I am using a script (a simple text file with .vbs extension) like this:

  option explicit

  const adOpenKeyset = 1

  const adLockOptimistic = 3

  const adTypeBinary = 1

  const adSaveCreateOverWrite = 2

  dim cn, rs, mstream

  set cn = CreateObject("ADODB.Connection")

  cn.Open "Provider=SqlOLEDB;Server=myServerName;Database=myDbName;UID=myUserId;PWD=myPwd"

  set rs = CreateObject("ADODB.Recordset")

  rs.Open "select BlobFieldName from TableName where Code='xyz'", cn, adOpenKeyset, adLockOptimistic

  set mstream = CreateObject("ADODB.Stream")

  mstream.Type = adTypeBinary

  mstream.Open

  mstream.Write rs.Fields("BlobFieldName").Value

  mstream.SaveToFile "C:\FileSavedFromBlob.pdf", adSaveCreateOverWrite

  rs.Close

  cn.Close

  set rs = nothing

  set cn = nothing