| Author |
Topic |
|
Limuh
Yak Posting Veteran
94 Posts |
 Posted - 2008-07-10 : 21:46:35
|
| Hello guys,im wondering,in SQL 2005 is it possible to transfer column records to a file without using SSIS?Please adviceThanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Limuh
Yak Posting Veteran
94 Posts |
Posted - 2008-07-11 : 01:50:06
|
| is there anyone here can give an example how to do bcp.exe or bulk insert? syntax i mean? thanks |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-11 : 02:02:06
|
quote:
Originally posted by Limuh
Hello guys,im wondering,in SQL 2005 is it possible to transfer column records to a file without using SSIS?Please adviceThanks!
Thread titlequote:
Transferring Column records to an excel file
You want to export data to a text file or excel file ?BCP can't give you excel format. Bulk Insert is for inserting data into table from text file.see this thread for exporting data to excelhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Limuh
Yak Posting Veteran
94 Posts |
Posted - 2008-07-11 : 05:14:35
|
| thanks Khtan,but i got this error..Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-11 : 05:24:13
|
quote:
Originally posted by Limuh
thanks Khtan,but i got this error..Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Make sure the file is closed when you execute the queryGo thru the suggested linkMadhivananFailing to plan is Planning to fail |
|
|
|
Limuh
Yak Posting Veteran
94 Posts |
Posted - 2008-07-11 : 05:40:19
|
| i also got this error.Msg 7357, Level 16, State 2, Line 1Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.by the way what [Sheet1$] specifically? A:1 - something? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-11 : 14:18:18
|
quote:
Originally posted by khtan
BCP can't give you excel format.
Yes it can. My customers are happy with receiving their csv files that I created with bcp as all I need to do to achieve simple Excel formatting is specify comma as the separator. They can then just double-click on the csv file and it opens in Excel.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
kkuhns
Starting Member
7 Posts |
Posted - 2008-07-11 : 16:46:31
|
| A quick and simple way that I do this is to right click on the results which will give you the option to save the data as .csv file. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-12 : 05:02:12
|
quote:
Originally posted by Limuh
i also got this error.Msg 7357, Level 16, State 2, Line 1Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.by the way what [Sheet1$] specifically? A:1 - something?
It means that the EXCEL sheet doesnt have column namesCreate them and then tryMadhivananFailing to plan is Planning to fail |
|
|
|
|
Transferring Column records to an excel file