0

How to store data from an openquery in a .CSV OR EXCEL FILE?

select * from openquery(DB_LINK,'SELECT col1,col2,col3,col4 from oracle_table_name')

select * from openquery(DB_LINK,'SELECT col1,col2,col3,col4,col5 from oracle_table_name2')

The result has to be stored in 2 different files.

I have scheduled the open query such that it runs on 1st of every month. And the 2 files that are generated have to be kept in client's windows specific folder.

This is the first time I'm using SQL SERVER. So please explain step by step.

I am using this query at the moment.

declare @title varchar(55) 
set @title = 'Test'   

EXEC sp_makewebtask 
  @outputfile = 'D:\Test11.xls', 
  @query = 'select CIRCUIT_DESIGN_ID as [Account Number] from openquery(MSP_PROD,'select CIRCUIT_DESIGN_ID from circuit')',
  @FixedFont=0,@resultstitle=@title
Pondlife
  • 15,992
  • 6
  • 37
  • 51
Pale Blue Dot
  • 67
  • 2
  • 12
  • `sp_makewebtask` was removed in SQL Server 2008, are you sure your version is 2008? And do you have to implement your solution in this way, or can you just write a small script that connects to Oracle directly and saves the results as a .csv file? It isn't obvious why you need to use SQL Server, and it makes the solution much more complex. But anyway, OPENQUERY is actually irrelevant because you're just asking how to save the results of *a* query to file and that has been asked many times already, e.g. [here](http://stackoverflow.com/questions/1461510/exporting-sql-server-data-to-csv). – Pondlife May 16 '13 at 16:00

1 Answers1

0

Create an Export Data... task using SQL Server Management Studio (by right clicking the database), specify that you want to output the data to CSV, and that your datasource is to be a SQL statement.

Use your select * from openquery... statement as the datasource.

Save the package, then create a SQL Server Agent job that will execute it on your desired schedule.

paul
  • 21,653
  • 1
  • 53
  • 54