Can I use OPENROWSET to simple export a one column table to a txt file?

Question

Can I use OPENROWSET to simple export a one column table to a txt file?

d_george

SSCrazy

Points: 2462
More actions
October 17, 2007 at 8:17 am

#183053

Hi
I just want to export the values in a one column table to a txt file.
I would just go back to bcp out but I want to avoid fmt files and try the new style.
A simple code snippet would be love.
Thanks
Since no one has responded, I thought I should give more detail.
I don't even care about OPENROWSET anymore. I just want it to work so I went to BCP.
Now the BCP is giving me crap about the fmt file. The following is the code:
CREATE TABLE DBO.ObjectData ([ObjectText] VARCHAR(MAX)) ON [PRIMARY]
SELECT @Command = 'BCP Server.DBO.ObjectData out C:\ObjectData.sql /f C:\AutoDTS.fmt /UMe /PMe /SMyServer'
EXECUTE master.dbo.xp_cmdshell @Command
File:
8.0
1
1 SQLCHAR 0 0 "\r" 1 ObjectText SQL_Latin1_General_CP1_CI_AS

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Run the query, with the data you need,in SSMS and send results to file. Name the file: "filename.txt" and make sure to click "All File Types" or it will put a .rpt extension on the file. If you need the file delimited you can right click on the grid and save the results to csv.

** If you need column headers you have to go to tools --> options --> query results --> results to grid and choose include header when saving or copying.

My blog: http://jahaines.blogspot.com

d_george SSCrazy Points: 2462 More actions · Answer 2

Hi

Although that is correct, it is simply manual.

I need to eventually wrap this in a proc.

I finally was able to generate a working format file with the bcp.

I have no idea why it did not work the 1st 3 times.

I would still like to know if anyone knows about the OPENROWSET option.

Thanks

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

If you need automation, you can choose either openrowset or SSIS. If you choose to do SSIS you can create a package and schedule it. If you choose openrowset you must first create the textfile with the column headers in it. Below is an example of openrowset.

Note: openrowsetmust be enabled via Surface area config.

Use AdventureWorks

go

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Text;Database=C:\',test#txt)

SELECT FirstName

FROM Person.Contact

Where ContactID < 50

My blog: http://jahaines.blogspot.com

d_george SSCrazy Points: 2462 More actions · Answer 4

Thanks a lot.

That was the code snippet I was loking for.

I am dissapointed that the file must be created first.

BCP is superior to the OPENROWSET method in this respect.

So I will have to use that.

SSIS would be great but this needs to be self contained in a proc. No calls.

Thank you for your help (and code)

sanarien25 Valued Member Points: 65 More actions · Answer 5

am trying to code it thru vb 6 receving a error that given is not a valid path. although it runs in sql analyzer smoothly exporting rows to txt file.

insert openrowset ('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\documents and Settings\sarathn.LGXTST\Desktop',test#csv) Select item, on_hand_qty as onhand from location_inventory

any solutions

thanks