Very New to SQL SERVER--AUTOMATION OF REPORTS

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

SQL Server 2008 Forums

Transact-SQL (2008)

Very New to SQL SERVER--AUTOMATION OF REPORTS

Author

Topic

rahul_raj007
Starting Member

4 Posts

Posted - 2013-05-30 : 05:26:31

Hi all,
I am ORACLE PL/SQL Developer.
My problem:

I have 3 simple select statement in oracle
I want to execute the queries in SQL SERVER on 1st of every month
and generate 3 .xls (or .csv) files (1 .xls file for each select statement).The 3 excel files that will be genrate has to be placed into the clients locations-- \\10.75.67.00\Outputfolder.
Whole process has to be automated .

How can i do this in SQL SERVER 2008.
I cant use sp_makewebtask in 2008.So please tell me step by step how can i implement this in 2008.
In 2005 i have implemented like this


ALTER PROCEDURE [dbo].[sp_reports]

AS
BEGIN
	
	SET NOCOUNT ON;
	
declare @directory varchar(200)
set  @directory = '\\IPADDRESS OF THE CLIENT'S MACHINE\OUTPUTFOLDER\' 

declare @title1 varchar(200) 
set @title1 = ' Report' 


declare @yr varchar(20)
set @yr = datepart(year,getdate())

declare @filetype varchar(20)
set @filetype = '.xls'

declare @file1 varchar(200)
set  @file1 = @directory + 'Report'+@mth + '_' +@yr + @filetype

EXEC sp_makewebtask 
  @outputfile = @file1,
  @query = 'select * from openquery(DB_LINK_TO_ORACLE,'' SELECT * from table_name where column_name=''''yes''''
  '')',
  @FixedFont=0,@resultstitle=@title1

Thanks
Rahul Raj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 05:49:12

you just need to create a SQL Server agent job which calls a SSIS package that executes the query and exports result to xls or csv. The job can be scheduled for automated execution as per your convenience.
If you dont want to use SSIS Write a sql procedure using bcp to export query results to Excel or csv

see

http://beyondrelational.com/modules/2/blogs/70/posts/10846/export-to-excel-with-column-names.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rahul_raj007
Starting Member

4 Posts

Posted - 2013-05-30 : 06:00:48

quote:
Originally posted by visakh16

you just need to create a SQL Server agent job which calls a SSIS package that executes the query and exports result to xls or csv. The job can be scheduled for automated execution as per your convenience.
If you dont want to use SSIS Write a sql procedure using bcp to export query results to Excel or csv

see

http://beyondrelational.com/modules/2/blogs/70/posts/10846/export-to-excel-with-column-names.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi thanks for the reply.
I dont know anything about SSIS packages.

Can you just paste the working procedure with the following query


select col1,col2,col3,col4,col5 from table_in_oracle_DB
where col1='RAHUL' and col2 in (5,10,16);
--col3 is date  type,col4,col5 is character types

Thanks
Rahul Raj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 06:11:08

use CREATE PROC

see

http://msdn.microsoft.com/en-us/library/ms187926.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rahul_raj007
Starting Member

4 Posts

Posted - 2013-05-30 : 06:28:15

quote:
Originally posted by visakh16

use CREATE PROC

see

http://msdn.microsoft.com/en-us/library/ms187926.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I dont think your procedure will work because i am fetching data from
oracle table not sql server table

Thanks
Rahul Raj

rahul_raj007
Starting Member

4 Posts

Posted - 2013-05-30 : 06:52:46

quote:
Originally posted by visakh16

you just need to create a SQL Server agent job which calls a SSIS package that executes the query and exports result to xls or csv. The job can be scheduled for automated execution as per your convenience.
If you dont want to use SSIS Write a sql procedure using bcp to export query results to Excel or csv

see

http://beyondrelational.com/modules/2/blogs/70/posts/10846/export-to-excel-with-column-names.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Do you know how to use SSIS package?

Thanks
Rahul Raj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 08:26:39

quote:
Originally posted by rahul_raj007

quote:
Originally posted by visakh16

use CREATE PROC

see

http://msdn.microsoft.com/en-us/library/ms187926.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I dont think your procedure will work because i am fetching data from
oracle table not sql server table

Thanks
Rahul Raj

Nope it will still work so far as you're executing query in SQL Server which is what you told earlier
I want to execute the queries in SQL SERVER on 1st of every month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 08:27:08

You just need to use linked server to connect to Oracle tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources