Scheduling data extraction to excel

Question

Post reply

Scheduling data extraction to excel

Ahmad Osama

SSCertifiable

Points: 7676
More actions
March 12, 2008 at 10:17 pm

#71280

Hi All,
I want to schedule a data extraction to excel and to save that excel file with a new name in a folder.Which method should I use: A stored procedure or a DTS package.
Also want to know , how to save a file from sql server .
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Karel Walda Valued Member Points: 51 More actions · Answer 1

Karel Walda

Valued Member

Points: 51

March 13, 2008 at 3:32 am

#788939

My guess / suggestion would be DTS Package;

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Ahmad, you can do either. I'm currently using stored procedures, in a process which flows as follows:

Start with a formatted Excel template file placed in an accessible folder

Copy to a working directory and rename using the current date as a suffix

Create a server link to it

Update it

Delete the server link

Copy it again using "_current" as a suffix

You gain a great deal of flexibility by having a "data" tab and a "display" tab in your Excel file, all data writes are to the "data" tab which has row/column identifiers in place which would otherwise mess up the appearance.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 3

Thanks Chris,

I had to generate daily sales report and store as new file every day...Is it possible thru ur technik

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

Sounds like a perfect situation for a Reporting Services Fileshare Susbscription. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Ahmad Osama (3/14/2008)
Thanks Chris,
I had to generate daily sales report and store as new file every day...Is it possible thru ur technik

Yes it is, Ahmad. I'm using it for something very similar.

I can post the main stored procedures I use for this in their entirety, and fragments of other working code to help you - however, I'd strongly encourage you to study and achieve as much as you can first.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Hi Jason

I guess it does, having read a few bits and bobs about Reporting Services. We don't have it here, we're stuck on 2k for a while yet. I didn't even know it worked with 2k!

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

Yes, it works with 2000 and I believe it's on the SQL Server media. Oh, it's FREE and not too dificult to set up. Search these forums, or the web and you'll get more info.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 8

Yes it is, Ahmad. I'm using it for something very similar.

I can post the main stored procedures I use for this in their entirety, and fragments of other working code to help you - however, I'd strongly encourage you to study and achieve as much as you can first.

Cheers

ChrisM[/quote]

well..I've already started searhing for it.....I came thru sp_OAcreate functions to create excel file thru sql .... I am still working and will be able to do it.....Am I on right track.....

Thanks

Ahmad

Regards,
[font="Verdana"]Sqlfrenzy[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Ahmad Osama (3/15/2008)
Yes it is, Ahmad. I'm using it for something very similar.
I can post the main stored procedures I use for this in their entirety, and fragments of other working code to help you - however, I'd strongly encourage you to study and achieve as much as you can first.
Cheers
ChrisM

well..I've already started searhing for it.....I came thru sp_OAcreate functions to create excel file thru sql .... I am still working and will be able to do it.....Am I on right track.....

Thanks

Ahmad[/quote]

Almost certainly Ahmad, and well done for going to the trouble. You will find this article useful...

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

... but you should also consider Reporting Services, as Jason suggests.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 10

Thanks chris..

Where will I get an article on reporting services....

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 11

http://www.sqlreportingservices.net/BookSrs2000/default.aspx

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 12

Hi Chris,

I copied and executed the code that was at the specified url (shown below), I am getting the error "Msg 50000, Level 16, State 1, Procedure spExecute_ADODB_SQL, Line 53 Error while Executing DDL "Create table CambridgePubs (Pubname Text, Address Text, Postcode Text)", Operation is not allowed when the object is closed."

create procedure spExecute_ADODB_SQL

@DDL varchar(2000),

@DataSource Varchar(100),

@Worksheet varchar(100)=null,

@ConnectionString varchar(255)

= 'Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=%DataSource;

Extended Properties=Excel 8.0'

as

Declare

@objExcel int,

@hr int,

@command varchar(255),

@strErrorMessage varchar(255),

@objErrorObject int,

@objConnection int,

@bucket int

Select @ConnectionString

=replace (@ConnectionString, '%DataSource', @DataSource)

if @Worksheet is not null

Select @DDL=replace(@DDL,'%worksheet',@Worksheet)

Select @strErrorMessage='Making ADODB connection ',

@objErrorObject=null

EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT

if @hr=0 Select @strErrorMessage='Assigning ConnectionString property "'

+ @ConnectionString + '"',

@objErrorObject=@objconnection

if @hr=0 EXEC @hr=sp_OASetProperty @objconnection,

'ConnectionString', @ConnectionString

if @hr=0 Select @strErrorMessage

='Opening Connection to XLS, for file Create or Append'

if @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open'

if @hr=0 Select @strErrorMessage

='Executing DDL "'+@DDL+'"'

if @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute',

@Bucket out , @DDL

if @hr<>0

begin

Declare

@Source varchar(255),

@Description Varchar(255),

@Helpfile Varchar(255),

@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,

@Description output,@Helpfile output,@HelpID output

Select @strErrorMessage='Error whilst '

+coalesce(@strErrorMessage,'doing something')+', '

+coalesce(@Description,'')

raiserror (@strErrorMessage,16,1)

end

EXEC @hr=sp_OADestroy @objconnection

go

Thanks,

Ahmad

Regards,
[font="Verdana"]Sqlfrenzy[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Hi Ahmad

This isn't the method I use for reading / writing spreadsheets using TSQL, however I created the stored procedure using your code above and executed the following:

EXEC spExecute_ADODB_SQL

'Create table CambridgePubs (Pubname Text, Address Text, Postcode Text)',

'CambridgePubs',

'Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=%DataSource;

Extended Properties=Excel 8.0'

... which created 'CambridgePubs.xls' in directory 'C:\WINDOWS\system32' on the server, which is what I was expecting to see. I'd suggest checking your calling syntax of the stored procedure, but I suspect it's a security issue.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 14

try this ..

spExecute_ADODB_SQL @DDL='Create table CambridgePubs

(Pubname Text, Address Text, Postcode Text)',

@DataSource ='C:\CambridgePubs.xls'

I have excel installed on my server ....I am unable to understand what's the problem...

Regards,
[font="Verdana"]Sqlfrenzy[/font]