Problem to Run DTS from Stored Procedure

Cesco

Hallo to everybody. I have a DTS in SQL Server 2000 and I need to
execute it from stored procedure.
I know that there are various method fot does this but they doesn't
work.
The first method that I try to use is with the stored procedure
"xp_cmdshell".
If I write in DOS prompt
dtsrun /F c:\DTS1.dts
the DTS1 will execute well

I write in Query Analizer the command:
exec xp_cmdshell 'dtsrun /F c:\DTS1.dts'
the DTS1 doesn't execute.
Also, if I use the DOS Prompt command:
dtsrun /S localhost /U sa /P sa /N DTS1
the DTS1 work well
but if I use this command with xp_cmdshell it doesn't work:
exec xp_cmdshell 'dtsrun /S localhost /U sa /P sa
/N DTS1'

Where is the problem?

I also use a stored procedure that use sp_OA method:

CREATE procedure p_rundts as
declare @hr1 int, @hr2 int, @hr3 int, @hr4 int, @oPKG int

--creating package
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT
print @hr1

--loading package
EXEC @hr2 = sp_OAMethod @oPKG, 'LoadFromSQLServer("(local)", "",
"",256,,,, "DTS1")', null
print @hr2

--Execute package
EXEC @hr3 = sp_OAMethod @oPKG, 'exec', NULL
print @hr3
IF @hr3 <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr3
RETURN
END
else print 'Esecuzione package'

--EXEC @hr3 = sp_OAMethod @oPKG, ' EXEC (@oPKG)'

--Pulizia dell'ambiente
EXEC @hr4 = sp_OADestroy @oPKG
print @hr4
GO

but also this procedure doesn't work...

Someone can help me?

Jul 23 '05 #1

John Bell

Hi

Instead of using localhost have you should use the name of the SQL Server or
instance. You may want to check out

http://www.sqldts.com/default.aspx?210
http://www.databasejournal.com/featu...le.php/1459181
http://msdn.microsoft.com/library/de...tsrun_95kp.asp

If specifying the file name then make sure that it will be in the correct
location.

John

"Cesco" <fr***************@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Hallo to everybody. I have a DTS in SQL Server 2000 and I need to
execute it from stored procedure.
I know that there are various method fot does this but they doesn't
work.
The first method that I try to use is with the stored procedure
"xp_cmdshell".
If I write in DOS prompt
dtsrun /F c:\DTS1.dts
the DTS1 will execute well

I write in Query Analizer the command:
exec xp_cmdshell 'dtsrun /F c:\DTS1.dts'
the DTS1 doesn't execute.
Also, if I use the DOS Prompt command:
dtsrun /S localhost /U sa /P sa /N DTS1
the DTS1 work well
but if I use this command with xp_cmdshell it doesn't work:
exec xp_cmdshell 'dtsrun /S localhost /U sa /P sa
/N DTS1'

Where is the problem?

I also use a stored procedure that use sp_OA method:

CREATE procedure p_rundts as
declare @hr1 int, @hr2 int, @hr3 int, @hr4 int, @oPKG int

--creating package
EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT
print @hr1

--loading package
EXEC @hr2 = sp_OAMethod @oPKG, 'LoadFromSQLServer("(local)", "",
"",256,,,, "DTS1")', null
print @hr2

--Execute package
EXEC @hr3 = sp_OAMethod @oPKG, 'exec', NULL
print @hr3
IF @hr3 <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr3
RETURN
END
else print 'Esecuzione package'

--EXEC @hr3 = sp_OAMethod @oPKG, ' EXEC (@oPKG)'

--Pulizia dell'ambiente
EXEC @hr4 = sp_OADestroy @oPKG
print @hr4
GO

but also this procedure doesn't work...

Someone can help me?

Jul 23 '05 #2

Cesco

Thank you for the reply. I change the name of server from localhost to
the name of my machine and i sure that the dts is in correct location
but the stored procedure doesn't work...
There is something to set to run stored procedure?

Jul 23 '05 #3

John Bell

Hi

Not really if it works at the command prompt then it should work from
xp_cmdshell. The tip on SQLDTS.com was to let SQL Server create a job for
your package (right click the package and choose the schedule menu) and that
will give you a valid DTSRUN command line.

John

"Cesco" <fr***************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Thank you for the reply. I change the name of server from localhost to
the name of my machine and i sure that the dts is in correct location
but the stored procedure doesn't work...
There is something to set to run stored procedure?

Jul 23 '05 #4