DTS Job - Import to excel - 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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Job - Import to excel

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-27 : 13:51:25

Guys,

I want to create DTS job to import data from a table on nightly basis to a excel spreadsheet.

select * from employee where deptid = 1

the results of this query should be imported to a excel spreadsheet as a daily night job.

any suggestions/inputs would be helpful

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 14:31:57

You could create the DTS package and then schedule its execution as a job, or you can use T-SQL code to do the export and schedule that as a job. See here for more about using T-SQL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-27 : 15:26:17

snsql,

All I want to do is the following

I have 2 select statements

select * from employee where daterecorded between '11/01/2006' and '11/31/2006'
select * from person where id in (select id from employee where daterecorded between '11/01/2006' and '11/31/2006')

The results of these 2 sql statements should be imported to excel spreadsheet with corresponding 2 tabs for results of each of this sql statement.

I plan to pass the dates to stored procedure for the select statements

Can this implemented with stored procedure and DTS job

any suggestions would help

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 17:43:39

Yes, or you can do it without DTS. Either way you'll use a scheduled job to execute it daily, it's just a case of whether you use the job to execute a DTS package, or use the job to execute Transact SQL. Do it whichever way you are more comfortable with.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-27 : 22:53:26

snsql,

Can you please show me how I can import the results of the 2 queries into 2 different tabs of the same excel file

is it possible to execute the below stored procedure and have results of both the queries into 2 tabs of a single spreadsheet.

alter procedure spExport (@date1 datetime, @date2 datetime) as
set nocount on
begin
select * from employee where daterecorded between @date1 and @date2
select * from person where id in (select empid from employee daterecorded between @date1 and @date2)
end
set nocount off

execute spExport '2006-11-01 00:00:00.000', '2006-11-31 23:59:59.000'

Please help me to export 2 results of above stored procedure into 2 tabs of the spreadsheet

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-28 : 02:08:03

You should read the link that snSQL suggested

Madhivanan

Failing to plan is Planning to fail

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-28 : 09:51:10

Madhivanan.

Thank you for your reply

I am trying to insert the below t-sql in the stored procedure
but the t-sql doesnt seem to work

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Testing.xls;',
'SELECT id FROM [SheetName$]') select id from employee

I get the following message

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'SheetName$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

And I am sure the path is right and so the tab name - '[SheetName$]'

Can anyone tell me what mistake I am doing here

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-28 : 10:55:18

SheetName is the worksheet name that you see on the tab at the bottom of the spreadsheet area in Excel. If I run your code with a worksheet named SheetName then it works fine (note that you must not put the $ in the spreadsheet).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 03:13:31

<<
The Microsoft Jet database engine could not find the object 'SheetName$'.
>>
The query I suggested is generalised. You need to replace file name and sheet name accordingly

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources