Openrowset query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Openrowset query

Author

Topic

dirs
Starting Member

14 Posts

Posted - 2010-05-06 : 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

638 Posts

Posted - 2010-05-06 : 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 - 2010-05-07 : 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

22864 Posts

Posted - 2010-05-07 : 03:53:09

Refer point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

dirs
Starting Member

14 Posts

Posted - 2010-07-19 : 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

22864 Posts

Posted - 2010-07-19 : 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 - 2010-07-19 : 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

22864 Posts

Posted - 2010-07-20 : 03:22:20

No. I dont think bcp has it

Madhivanan

Failing to plan is Planning to fail

dirs
Starting Member

14 Posts

Posted - 2010-07-20 : 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

22864 Posts

Posted - 2010-07-20 : 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 - 2010-07-21 : 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

22864 Posts

Posted - 2010-07-21 : 04:59:00

Use order by clause in bcp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources