October 17, 2007 at 8:17 am
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
October 17, 2007 at 2:30 pm
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.
October 18, 2007 at 5:22 am
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
October 18, 2007 at 6:54 am
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
October 18, 2007 at 7:10 am
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)
June 1, 2009 at 10:07 pm
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy