| Author |
 Topic  |
|
|
dirs
Starting Member
14 Posts |
 Posted - 05/06/2010 : 08:59:37
|
I am using Openrowset to export data from log table to a .csv file. We are using MS SQL 2005.
Insert into OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\test;Extensions=CSV',
'SELECT * FROM [output1.csv$]')
select * from log
When I run the above mentioned query I get the error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".
When I try to import from .csv file to database it goes fine. I use the query:
select *
into log
from
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\test;Extensions=CSV;',
'SELECT * FROM [output.csv]')
Any help would be appreciated :-) |
|
|
DBA in the making
Aged Yak Warrior
636 Posts |
Posted - 05/06/2010 : 10:13:32
|
You can do it like this:
DECLARE @SQL VARCHAR(8000)
SELECT @SQL = 'bcp "SELECT * FROM ' + DB_NAME() + '..log" queryout C:\Test.csv -c -t, -T -S'+ @@SERVERNAME
EXEC master..xp_cmdshell @SQL
------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
dirs
Starting Member
14 Posts |
Posted - 05/07/2010 : 03:32:09
|
| Thanks for the reply. It worked :-) The only problem is that I am not getting column headings. What can I do to get the column headings as well?? |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
dirs
Starting Member
14 Posts |
Posted - 07/19/2010 : 09:56:14
|
quote:
Originally posted by madhivanan
Refer point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Madhivanan
Failing to plan is Planning to fail
Thanks for reply Madhivanan:) We would like to avoid creating procedures. Do I have any other option ? Any other solution to get headings as well in the excel file?
Thanks in advance :) |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/19/2010 : 10:10:30
|
quote:
Originally posted by dirs
quote:
Originally posted by madhivanan
Refer point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Madhivanan
Failing to plan is Planning to fail
Thanks for reply Madhivanan:) We would like to avoid creating procedures. Do I have any other option ? Any other solution to get headings as well in the excel file?
Thanks in advance :)
You can use the code without a procedure
Just declare all the parameters outside
Madhivanan
Failing to plan is Planning to fail |
|
|
|
dirs
Starting Member
14 Posts |
Posted - 07/19/2010 : 10:31:54
|
quote:
Originally posted by madhivanan
quote:
Originally posted by dirs
quote:
Originally posted by madhivanan
Refer point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Madhivanan
Failing to plan is Planning to fail
Thanks for reply Madhivanan:) We would like to avoid creating procedures. Do I have any other option ? Any other solution to get headings as well in the excel file?
Thanks in advance :)
You can use the code without a procedure
Just declare all the parameters outside
Madhivanan
Failing to plan is Planning to fail
Thanks again. Don't we have any option we can use with bcp command to get colum heading as well?
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/20/2010 : 03:22:20
|
No. I dont think bcp has it
Madhivanan
Failing to plan is Planning to fail |
|
|
|
dirs
Starting Member
14 Posts |
Posted - 07/20/2010 : 07:11:35
|
Madhivanan: I tried using the procedure that you recommended. But when I run
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
I get the error 'User name not provided, either use -U to provide the user name or use -T for Trusted Connection'
Tried
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path' -T
got error msg
'Msg 102, Level 15, State 1, Line 1'
ANy suggestions?
Incorrect syntax near '-'. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/20/2010 : 10:01:53
|
quote:
Originally posted by dirs
Madhivanan: I tried using the procedure that you recommended. But when I run
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
I get the error 'User name not provided, either use -U to provide the user name or use -T for Trusted Connection'
Tried
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path' -T
got error msg
'Msg 102, Level 15, State 1, Line 1'
ANy suggestions?
Incorrect syntax near '-'.
You should alter the procedure to include it
ie
Change the line
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
to
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T'''
Change wherever applicable and call the procedure
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
Madhivanan
Failing to plan is Planning to fail |
|
|
|
dirs
Starting Member
14 Posts |
Posted - 07/21/2010 : 03:55:44
|
| Thanks. The output file that I am getting now is ordered alphabetically. I want to order the output by timestamp( one of the columns in table). Thanks once again for help Madhivanan:) |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/21/2010 : 04:59:00
|
Use order by clause in bcp
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Openrowset query
