April 19, 2005 at 6:33 am
Hi
I want to achieve the following, please help me:
We have a user that is working in a program which uses a sql databas.
Sometimes he want´s to copy that database to another database. When he want´s this done he calls us and we have to make it for him. So we made a DTS job that we run each time he asks us.
But now we want to create a solution so he can run this DTS job by himself.
How do we do that?
I´m a newbie to vb.net but I have read something about: EXEC master..xp_cmdshell 'DTSRun /S SQLServer /N "DTSPACKAGE" /E',that can be run.
How do you program the application to run this command on a specific server?
The user has no access to the server at this point.
Thanks in advance
April 19, 2005 at 2:04 pm
When I have the kind of situation where another person needs to load info on a scheduled basis...
I set it up so they have network permissions to move an OS file (like an MDB) to a specific staging directory that my server has access.
Then I create the DTS job that looks in that directory for the file and when it's there it runs the job that does what it needs to do to load it into the database, and when successful it uses a DOS MOVE kind of thing to relocate that file so the process wont be repeated.
(and us the SQL job scheduler to run this task periodically--it can be set to execute a DTS package.)
April 20, 2005 at 1:33 pm
I have a similiar issue.
I set up the DTS package as a job then use ADO to run the sp_start_job procedure on the msdb database.
Dim cnn As Object
Dim cmd As Object
Set cnn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
'Connect to the Server using Windows Authentication
cnn.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=SQLSERVERNAME;INITIAL CATALOG=msdb;INTEGRATED SECURITY=sspi"
cnn.Open
cmd.CommandTimeout = 100
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "EXEC msdb.dbo.sp_start_job @job_name='MyJobName'"
cmd.Execute
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply