How to use OpenRowSet to insert data into a blank file?

Question

How to use OpenRowSet to insert data into a blank file?

I need to insert into a txt file (say to D:\TDB) some select output (say select * from sys.tables) from the database

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=D:\TDB;HDR=Yes;', 'SELECT * FROM sys.tables.txt')
select * from sys.tables;

I get

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'sys.tables.txt'. Make sure the object exists and that you spell its name and the path name correctly.".

Msg 7350, Level 16, State 2, Line

1 Cannot get the column information from OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

What is wrong?

PS. please do not propose the bcp solution, cause already tested and does not work everytime, so I would test openrowset now..

When I've done it in the past I've created a template file with the appropriate column headers, then used xp_cmdshell to create a new copy of the template file and used the copy to dump into and finally renamed the populated file to something else (I used a timestamp since I wanted to keep the file each time) using xp_cmdshell to make room for the next execution. — Eric J. Price, Mar 27 '13 at 18:31
Are you sure, your SQL Server is able (has permissions) to write to D:\TDB? Maybe SQLCMD is an option for you: http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd — CeOnSql, Mar 09 '15 at 12:15
Although you could do it using xp_commanshell, probably you wouldn't want to but instead from a frontend language (ie: C#) write the content to a text file (and that doesn't require the ancient Jet driver to be installed and OpenRowSet be enabled). — Cetin Basoz, Mar 11 '21 at 13:07

score 0 · Answer 1 · edited Apr 07 '15 at 04:13

@serhio , I tested your sql below：

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=D:\TDB;HDR=Yes;', 'SELECT * FROM sys.tables.txt')
select * from sys.tables;

I got a few test results

The filename should not include "." in it. (sys.tables.txt→systables.txt)
HDR(Header Row) can not be used here.(Remove it)
The txt file must exist.(Create it)
The first line in the txt file should be the all column name of your source data.

sql

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=D:\TDB;', 'SELECT * FROM systables.txt')
select * from sys.tables;

systables.txt

name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published,lob_data_space_id,filestream_data_space_id,max_column_id_used,lock_on_bulk_load,uses_ansi_nulls,is_replicated,has_replication_filter,is_merge_published,is_sync_tran_subscribed,has_unchecked_assembly_data,text_in_row_limit,large_value_types_out_of_row,is_tracked_by_cdc,lock_escalation,lock_escalation_desc

How to use OpenRowSet to insert data into a blank file?

1 Answers1

Linked