April 22, 2002 at 11:48 am
Dears,
I want to know if it is possible to run a DTS packet using Stored Procedures.
Actually, for my domain users to be enable to update their data, they need to run a DTS from time to time. I want to faciliate the way for them. FOr example, if they can do that DTS with an SP, they can update their data from other programs like ACCESS too, without entering to SQL management itself.
Any help will be greatly appreciated,
Mohammed
April 22, 2002 at 12:24 pm
April 22, 2002 at 12:28 pm
You can use the DTSRun Utility with xp_cmdshell in an SP.
-JG
-JG
April 22, 2002 at 12:55 pm
quote:
You can use the DTSRun Utility with xp_cmdshell in an SP.Dear JG and Steve Jones,
Thanks for your reply. But how can I use xp_cmdshell in my SP? Sorry, but as I'm a new user, explain more please.
Thanks again from you both,
Mohammed
-JG
April 24, 2002 at 2:10 am
I use this little utility procedure which I can call from any stored procedure. It gets round two problems.
1 If your users try to call DTS from their client machines they will nbeed to have all the relevant DLL's on their client as DTS will run in the client address space. Calling this utility via a stored procedure ensures that DTS runs in the Server's address space.
2. It gives you a consistent method of calling DTS from within a stored procedure.
The utility uses the OLE object method of calling DTS and you can add/alter properties fed to it to suit.
The call from a stored procedure would be
EXEC prc_util_execute_dtspackage [servername], [username], [password], [dts package name]
I usually create the DTS packages using SQL authentication and the same username and password. I store that name and password in a small utilities table and collect those values in my calling sp prior to the exec of this utility.
Enjoy
Graham
IF EXISTS (SELECT 1 from dbo.sysobjects
WHERE id = Object_id('dbo.prc_util_execute_dtspackage') AND (type = 'P' or type = 'RF'))
BEGIN
DROP PROC dbo.prc_util_execute_dtspackage
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*******************************************************************************
Now For The Procedure Proper
*******************************************************************************/
CREATE PROCEDURE prc_util_execute_dtspackage
@ServerName sysname,
@ServerUserName sysname,
@ServerPassword sysname,
@PackageName sysname
AS
DECLARE
@ErrorValue INT,
@Object INT,
@ErrorDescription VARCHAR(255)
/*********************************
Create A Package Object
*********************************/
EXEC @ErrorValue = sp_OACreate 'DTS.PACKAGE', @Object OUTPUT
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/********************************
Load The Method With Required Params
********************************/
EXEC @ErrorValue = sp_OAMethod @Object,
'LoadFromSqlServer',
NULL,
@ServerName = @ServerName,
@ServerUserName = @ServerUserName,
@PackageName = @PackageName,
@Flags = 0,
@PackagePassword = '',
@ServerPassword = @ServerPassword
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/**********************************
Execute The Method
**********************************/
EXEC @ErrorValue = sp_OAMethod @Object, 'Execute'
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/*********************************
Empty The Package Object
*********************************/
EXEC @ErrorValue = sp_OAMethod @Object, 'UnInitialize'
/********************************
Destroy the Object
********************************/
EXEC @ErrorValue = sp_OADestroy @Object
RETURN
I have found it is easy to please a great many people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
February 12, 2003 at 4:28 pm
Like many others in the forums I am trying to find a secure method for a non sysadmin to execute a DTS Package.
xp_cmdshell appears to be out since I cannot put them in the Sysadmin group, nor can I open up the proxy issues for SQLAgent.
I tried the COM object recommendation by 'crosspatch' but unfortunately, I always get 'Login failed for user 'username' ' Regardless if I passed a sysadmin account and password or passed nulls for it to default to the SQLAgent account.
Does anyone know why the sp_OACreate may be failing with the Login failed or some other method to accomplish this task?
March 11, 2003 at 7:19 am
I am trying to use the sp_oacreate "DTS.Package" method for running DTS package from a stored procedure. However when I get to the sp_oamethod line I get the following error:
ole32.dll is loaded at the wrong address.
I only get this on our live server (the same script runs fine in developement and test). Does anybody know how to solce this?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply