From Sql Column to file, OPENROWSET?

Hi There

I am using OPENROWSET to import a file from disk to a varbinary(max) column in sql server.

However as far as i can see OPENROWSET is only to import into Sql Server. However i need to reverse this as well, by that i mean export a varbinary(max) column data to a file on disk. How can i do this ? (The file is a compress text file).

How does this work for file types, i mean you can import any file as binary but when you export it back to disk will the file typr still work, will a .xls or a.exe imprted and exported like this still function 100%.

Thanx?

OPENROWSET

Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

you can use it with insert or update.

if you are handling blobs however like images, pdfs,docs etc. i recommend streaming it out to a file using ado or ado.net

here are some usefull links:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q258038

http://support.microsoft.com/kb/308042/EN-US/

|||

Hi joeydj

I would really like to stay away from .net, my last option would be to write a clr stored proc to output the file from the database, are you 100% this cannot be dine in TSQL? What about TEXTCOPY? This seems to only work for Text and image data. However somethig like this is what i am looking for.

Also it is not documented in BOL obviously this makes me nervous as it may be depricated in future?

Thanx

|||

I stand corrected seems textcopy was only for Sql Server 6.5 and 7.

It just seems od to me that Sql Server would provide a feature to get data from disk into sql server but not vica versa?

|||

You could use DTS (in SQL2000) or SSIS (in sql2005) to import/export the data.

|||

Thanx for the feedback

Let me elaborate.

This is at a distributed site running SQL Express and only Sql express(No ssis avalable). There is a column in a table that is in a varbinary(max) format, this column was populated from a file using OPENROWSET at a central location and sent to to site via service broker, now i need to write this file back at the remote site from the varbinary(max) column?

Thanx

|||

As far as I know (and I might be wrong on this) the openrowset allows importation from file in order for us to have bulk inserts - it was not created as a primary mechanism to communicate with the file system.

In your situation I would go with CLR . SQL Server 2005 ships with a sample of a CLR SP that shows how to do it (check on <<your sql server installation directory>>\90\Samples\Engine\Programmability\CLR\HandlingLOBUsingCLR) so you wouldn't have to code much...

|||

Thanx Miguel

Yes i am sure OPENROWSET was not made for that , just thought there was a function or something i did not know about.

Thanx i will check out the sample, just out of interest if everyone has reccomended CLR then i am guessing there is no way to do this in SS2000?

Thanx

|||In SQL2000 I would recommend DTS's.