SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Openrowset query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dirs
Starting Member

14 Posts

Posted - 05/06/2010 :  08:59:37  Show Profile  Reply with Quote
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

638 Posts

Posted - 05/06/2010 :  10:13:32  Show Profile  Reply with Quote
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.
Go to Top of Page

dirs
Starting Member

14 Posts

Posted - 05/07/2010 :  03:32:09  Show Profile  Reply with Quote
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??
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 05/07/2010 :  03:53:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Refer point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dirs
Starting Member

14 Posts

Posted - 07/19/2010 :  09:56:14  Show Profile  Reply with Quote
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 :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/19/2010 :  10:10:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

dirs
Starting Member

14 Posts

Posted - 07/19/2010 :  10:31:54  Show Profile  Reply with Quote
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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/20/2010 :  03:22:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
No. I dont think bcp has it

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dirs
Starting Member

14 Posts

Posted - 07/20/2010 :  07:11:35  Show Profile  Reply with Quote
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 '-'.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/20/2010 :  10:01:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

dirs
Starting Member

14 Posts

Posted - 07/21/2010 :  03:55:44  Show Profile  Reply with Quote
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:)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/21/2010 :  04:59:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Use order by clause in bcp

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000