MS SQL newbie question: extracting blobs to disk

[GWestphal]

I was recently given a MSSQL database that is about 80GB since it has data included as blobs in one of the columns. I've never worked with data actually "in" a database before, I've always passed filename paths and metadata.

So, my question is how do you extract the blobs to a file on my local disk? The blobs may be of several different types that may be different sizes.

Sorry if this is simple, but I've been messing around with an access front end for a while and I can't seem to execute any of the queries I've tried to write, it asks for the DSN which I'm not sure what to select because the database is on a cluster somewhere.

My knowledge of databases is simple things in mysql using mysql admin tools to execute queries. Never touched MSSQL or used access before.

 

[BoberFett]

http://agilebi.com/jjames/2011/12/09/how-to-export-blob-data-to-files-in-ssis/

 

[GWestphal]

I'm sorry even that is too high level for me to understand. I don't really understand what stored procedures are or where I would write any of that VB code. We're talking basics here.

 

[GWestphal]

This is my knowledge of SQL, I have zero knowledge of file i/o at all with regards to databases, I've only used them as relational metadata spreadsheets essentially.

SELECT ColumnID FROM DB WHERE ColumnID="Foo";

 

[KLin]

Is there a table that defines what filetype each blob is? That's important to know in order to use the proper extension when outputting to the file system.

Public Sub testfile()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=SQLNCLI11;Server=<SERVERNAME>;Database=<DBNAME>;Trusted_Connection=yes;"

Set rs = New ADODB.Recordset
rs.Open "select top 1 binarydata, document_desc, modify_timestamp from table1", cn, adOpenKeyset, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("binarydata").Value
mstream.SaveToFile "c:\test.doc", adSaveCreateOverWrite

rs.Close
cn.Close

End Sub

You'd have to change the connection string to include your server and dbname and also the query string to wherever the blob data is stored.

 

[GWestphal]

The files are all of the same filetype, just not of the same length, 20s-20min.
I don't have admin access to the server or a webserver to run PHP. I only have client access permissions to the database.

 

[GWestphal]

I've seen references to bcp command, but I don't know where I would run it from, since I don't have access to the machine just SELECT permissions for the database.

 

[KLin]

that code is actually vba

 

[Leros]

Blobs are just bytes. Files are just bytes. Read the bytes from the database and dump the bytes to a file.

 

[QuietDad]

1. Create a DSN that has the SQLserver user ID and password.

To do that go to the Control Panel/Administrative Tools/Data Source (ODBC). Click the System DSN and and follow the prompts.

2. Open a new access database. File/Get External Data/Link Tables. Leave everything blank and select ODBC from the bottom list box. You server should be listed there. If not, Step 1 failed

 

[ForumMaster]

perhaps this will help:
Working-with-Binary-Large-Objects-BLOBs-Using-SQL-Server-and-ADONET

i'm an oracle guy so i know how to do this easily in oracle, but don't know if there is a tsql alternative. this shouldn't be difficult.

 

[GWestphal]

I haven't figured out the initial problem with the blobs, but I have another problem. This DB is located remotely and I need to copy it locally for performance issues.

I need to copy it to a mysql DB, but the migration wizard connects it just presents a blank list for the schema. Anyone know if this is indicative of a bug in mysql's migration wizard or if I need more DB permissions (I think all I have is connect and select)?

 

[beginner99]

As always it would be helpful to know what exactly oyu are trying to achieve. First you wanted the binary data and know you actually want to copy the database to mysql. It's not the same thing.

And what are the performance reasons? what makes you think it will run faster locally?

Why not use sql server express instead of mysql? eg:

http://stackoverflow.com/questions/...erver-database-from-a-server-to-local-sql-exp

of course you need the dbas to give you such a "copy".

 

[GWestphal]

I need to use mysql because we don't have any licenses for sql server and the free versions of sql server express etc are limited to 10GB and the db is 80GB. The network infrastructure here is woefully bad. And doing the queries and pulling down 1 million files x a few hundred KB per entry would cripple the network here. It's easier just to have it local because its faster and closer to how we will implement the final software. But the person who worked on the project before me had no local copies of the data, it only exists as blobs in that remote database. So I want to move it locally and it would be nice to pull out each file for local easier accessibility of the data when not doing hugely automated tasks where the sql implementation might have some use.

 

[Jaepheth]

This is actually relevant to a side project I'm trying to do.

I think I have functional code, except I don't know how to get the correct server name/ data source

I have a separate program capable of running SQL queries on the database I'm trying to connect to. Is there a query I can run to return the server name? (I've already tried using "SELECT UTL_INADDR.get_host_address from dual;" to get an IP address, which the query works, but using the result as the data source doesn't work)