Getting the server to do the export (1 Viewer)

O

ontiK

Guest
Hi All,

This one is driving me nuts.

My App is an Access XP adp on a SQL backend. I have some querys that take an eternity to run and typically timeout when I try and run them from the Access client. I can however export the data via DTS which is good because a data export is what I want to achieve, but bad because it takes sometime 20mins to run.

However, what I need to do is have the user be able to push a button on the client that starts an export on the Server which then mails the exported data as an attachement to the user who executed it. The user needs to be able to coninute working while the server is doing its thing. I have played with xp_sendmail but the format of the data in the attachment it creates is inappropriate. (Needs to be csv at least or Excel at best. xp_sendmail attachements have a controlled line size via the @witdh param)

This functionality also needs to be dynamic so I need to be able to change the view or table name the export will come from and provide a WHERE clause. This rules out using bcp because it is limited as to how many sets of quotation marks (single and double) you can include in your SQL command param. I'm not really in a position to be able to build a DTS package for every possible export either so my options are getting slim.


Has anyone got any suggestions for paths to follow to try and meet this objective?


Regards,
ontiK.
 

SQL_Hell

SQL Server DBA
Local time
Today, 00:39
Joined
Dec 4, 2003
Messages
1,360
Hi there

I can think of a way, if you had your DTS scheduled in a job, then you could execute a stored procedure that makes a call to start the job using:

sp_start_job

then in another job step you could have the output attached to a sql server email. I realise that you cant have the query in the sql server amil cos of the size. but you could output it to a text file in the dts and then attach that to your email.
 
O

ontiK

Guest
Yeah thanks for that. sp_start_job will be what I need. What I can do is a set up a generic view that I can re-script in my main sp. IE ALTER VIEW vw_GenericExport which would just contain my SQL for the dataset as required. the DTS Package would always point to vw_GenericExport and just output whatever it represented at the time. The first DTS would output the job then schedule the second job to mail the output as an attachement.

Nice idea. Thanks again, I'm sure I can work with that. I think sp_start_job is the key I've been looking for.


Cheers,
ontiK.
 

SQL_Hell

SQL Server DBA
Local time
Today, 00:39
Joined
Dec 4, 2003
Messages
1,360
No problems mate glad I could help.

Good luck with it and Happy Christmas
 
O

ontiK

Guest
Ok I seem to have gotten ahead of myself there.

All that worked BUT!!!

The problem I now have is that when I create the DTS package that I was o include in the job, it's first step is to execute a CREATE TABLE command based on the structure of the view I first select when creating the package. When I then go and change the SQL of the pump view, the column names no longer match the CREATE TABLE statement and so consequently it fails. What I am therefore unable to do is change the CREATE TABLE statement that supplied the Pump to the export. Is there any way that you can tell the package to script the CREATE TABLE statement on the fly as the package runs? I'm guessing not.

I have determined that you can edit DTS packages in VB5 or later and I think C++ too but neither of these have anything to do with the app I'm working on as its an Access XP frontend. From what I can gather there is no way to do this via Transact SQL no? That would be my preference.

Am I missing something with regard to how to create the DTS package? I confess I don't have deep skills in this area and so have just used the wizard, saved the package and edited as necessary.


Thanks a ton for your help so far.

ontiK.
 

Users who are viewing this thread

Top Bottom