fire event from Access to run a DTS Job on Serv.

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)

fire event from Access to run a DTS Job on Serv.

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-08-18 : 23:16:17

Hello,

I want a button in access which, when clicked sends a command to the SQL server, telling to run a specific scheduled job (the job is a DTS Package).

I have to do it like that becuase the in the DTS Job is scheduled every morning to download new data from a Sybase Server into SQL Sever Tables.

However, the user must be able to manually get the updated data during the day.

It would be great if someone here could tell me how the command to maually fire a scheduled job looks like, also how to put it behind a button (never relly used VB)

Thanks a lot!

The Package is called TravelDownload

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-19 : 02:04:56

You can control DTS packages and their execution via the Microsoft DTSPackage Object Library, which is available in Access. You should be able to execute the package with a couple of lines of code.
I believe that you can also run a DTS package via T-SQL, but I can't be sure of the syntax to use.

HTH,
Tim

vux
Starting Member

45 Posts

Posted - 2004-08-19 : 02:20:44

ok thanks for the hint, I will see if I understand it...

vux
Starting Member

45 Posts

Posted - 2004-08-19 : 04:45:04

the MS help is always crappy, did not find anything about the libary...

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 05:25:00

You could create a trigger on a table that starts the job and insert a row into the table from access. (The table could contain a log of execution times and users - probably a good thing to have anyway)

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-19 : 19:41:37

If you want to pursue the DTSPackage object path, the methods are reasonably intuative. All you need to do is:
declare the DTSPackage2 ojbect and instantiate it
call the LoadPackageFromServer method
Call the Execute method

vux
Starting Member

45 Posts

Posted - 2004-08-19 : 22:15:40

Hi,

yes I thought about triggers.
but the problem is that each time the user adds, deletes, updates an entry in the specific table the whole dts would be restarted (I still do not know the query/command to do that) - which is too much efforts... access would not wait until all changes in the table are made and send the final query to the server only once when the user closes the table.

@Timmy
thanks for your posting, but I do not know how and where to declare DTSPackage2 ojbect and the call it and so on - i am very new to this, sorry...

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 23:41:32

[code]
Here is some sample code:

CREATE TRIGGER StartDTSTrigger
ON mytable
AFTER UPDATE
if object_id('tempdb..##sync_dts') is null
exec msdb..sp_start_job @job_name = 'StartDTSTask'
END

Create a SQL Agent job with Enterprise Manager named 'StartDTSTask'
that executes the statements below to launch the DTS package. Enable
it, but do not schedule it.

DECLARE @hr INT
DECLARE @oPKG INT
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSQLServer("(local)", "", "", 256, , , , "MyDTSPackage")', NULL
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr = 0
EXEC @hr = sp_OADestroy @oPKG

Add to the beginning of the DTS Package MyDTSPackage

create table ##sync_dts (c1 int)

-- The rest of your task here
[/code]
If you do this Access can insert into mytable. It can insert 10,000
rows and it will return to the application instantly.

Only one instance of your package will launch for the entire 10,000
row insert.

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

vux
Starting Member

45 Posts

Posted - 2004-08-20 : 03:17:56

thanks for taking the time to put together the sample code

but thats too high for me...

and as i said i think triggers might not be the best solution

would be good to knwo how to work with the DTS object from access

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources