quote:

---

Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail

---

insert into OpenRowset('msdasql', 
  'Driver={Microsoft Text Driver (*.txt; *.csv)};
  Database=\\server\folder\Test.TXT;Server=server; UID=user;PWD=password')
  Select * FROM ClientData WHERE (Client = '???')

quote:

---

Originally posted by juvethski

hi,
the export to excel works fine... but it truncates the data to 255..

i hv a description field defined as text, and most of the values for this field exceeds 255. when i export it to excel thru the openset method, it truncates the values to 255 characters. is there an option in openrowset that will not truncate the data?

thanks!!

---

quote:

---

Originally posted by maborcha

I am trying to use the following to export a database to a text file.

insert into OpenRowset('msdasql', 
  'Driver={Microsoft Text Driver (*.txt; *.csv)};
  Database=\\server\folder\Test.TXT;Server=server; UID=user;PWD=password')
  Select * FROM ClientData WHERE (Client = '???')

Or is there a better way to export to a text file. I need to be able to specify the text file name (full path) and the client to be exported.

Thanks,

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by juvethski

hi,
the export to excel works fine... but it truncates the data to 255..

i hv a description field defined as text, and most of the values for this field exceeds 255. when i export it to excel thru the openset method, it truncates the values to 255 characters. is there an option in openrowset that will not truncate the data?

thanks!!

---

See if this helps you
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q189897

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by madhivanan

>>one of the columns of the table is defined as text so this creates problem

Did you try DTS method?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by madhivanan

>>\\clientmachine\d$\testing.xls but it doesn't work.

What is the error you got?
Make sure you have permissions to access file from client machine

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by madhivanan

See if this helps
http://www.kbalertz.com/kb_818182.aspx

Madhivanan

Failing to plan is Planning to fail

---