2

I was contemplating the creation of a tool to load varbinary(max) fields in a SQL Server 2008 database with files as selected from a open file dialog. Is there any such tool or equivalent that I could use? My SQL server is in a hosted environment where I don't have physical access to the server so loading it with TSQL is not an option.

Matt Ruwe
  • 3,386
  • 6
  • 39
  • 77
  • 2
    Are the files on the Server itself? If so I normally use TSQL to do it. `SELECT BulkColumn FROM OPENROWSET(BULK N'c:\somefile.txt', SINGLE_BLOB) as i` – Martin Smith Mar 19 '11 at 18:57
  • No, my server is in a hosted environment (update post with this information). – Matt Ruwe Mar 19 '11 at 19:04
  • Ah right. I'm not aware of any tools to do this then. I know `bcp` can export binary data - I've never investigated whether it can be used to import it. – Martin Smith Mar 19 '11 at 19:20
  • How would the tool communicate with the SQL Server if it can't use TSQL? – Gabe Mar 20 '11 at 05:37
  • TSQL can't be used, because it only has knowledge of the local SQL server, I can't load a file that exists on my client PC to a server. – Matt Ruwe Mar 21 '11 at 12:00

1 Answers1

10

How about powershell?

# from: http://sev17.com/2010/05/t-sql-tuesday-006-blobs-filestream-and-powershell/
#
$server = "superfly\sqlexpress"
$database = "Yak"
$query = "INSERT dbo.FileStore VALUES (@FileData, @FileName)"
$filepath = "d:\yak.txt"
$FileName = get-childitem $filepath | select -ExpandProperty Name

$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
$command.CommandTimeout=120
$connection.Open()

$fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Open',[System.IO.FileAccess]'Read')
$buffer = new-object byte[] -ArgumentList $fs.Length
$fs.Read($buffer, 0, $buffer.Length)
$fs.Close()

$command.Parameters.Add("@FileData", [System.Data.SqlDbType]"VarBinary", $buffer.Length)
$command.Parameters["@FileData"].Value = $buffer
$command.Parameters.Add("@FileName", [System.Data.SqlDbType]"NChar", 50)
$command.Parameters["@FileName"].Value = $FileName
$command.ExecuteNonQuery()

$connection.Close()
nathan_jr
  • 9,092
  • 3
  • 40
  • 55