November 11, 2003 at 6:57 am
Dear All,
I am trying to start a DTS package from a Store procedure. It doesn't like the 'LoadFromSQLServer' command. Can anyone tell me what is wrong ?
Here is the code
DECLARE @hPkg int
DECLARE @hResult int
DECLARE @sPackageName varchar(255)
DECLARE @sServerName varchar(255)
DECLARE @sServerUserName varchar(255)
DECLARE @sServerPassword varchar(255)
DECLARE @sMethod varchar(255)
DECLARE @lFlags int
DECLARE @src varchar(40)
DECLARE @desc varchar(100)
SET @sPackageName = 'A'
SET @sServerName = 'INVESTMENTS1'
SET @sMethod = ''
SET @sServerUserName = ''
SET @sServerPassword = ''
SET @lFlags = 256
EXEC @hResult = sp_OACreate 'DTS.Package', @hPkg OUT
if @hResult <> 0
Print 'Cannot Create'
else
Print 'Created'
SET @sMethod = 'LoadFromSQLServer'
EXEC @hResult = sp_OAMethod @hPkg, @sMethod, NULL,
@sServerName, @sServerUserName, @sServerPassword, @lFlags, 'A'
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo NULL, @src OUT, @desc OUT
SELECT Info = 'Method - '+@sMethod, Source = @src, Description = @desc
END
ELSE
Print 'It Worked'
EXEC @hResult = sp_OADestroy @hPkg
Thanks
J
November 11, 2003 at 11:51 pm
I don't know what is wrong with this code. However when I choose to run a DTS package from a stored proc, I use xp_cmdshell to do it.
November 12, 2003 at 8:06 am
A thought,
I notice you are not supplying a Username / Password.
Try ommiting those 2 from the code.
I have fired a DTS from a proc many a times and I always use this example:
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
DECLARE @objPackage int
DECLARE @PackageName varchar(128)
DECLARE @rc int
DECLARE @ServerName varchar(128)
DECLARE @DatabaseName varchar(128)
SET@PackageName = 'XXX_xxxxxxxx'
SET@ServerName = @@ServerName
exec sp_OACreate 'DTS.Package', @objPackage output
exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("strFileString").value', @FileName
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("InfoID").value', @infoID
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("Fund").value', @Fund
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("GroupID").value', @GroupID
exec @rc = sp_OAMethod @objPackage, 'Execute'
exec @rc = sp_OADestroy @objPackage
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Justa thought to try.
Could you post the actual error message you are getting?
November 13, 2003 at 9:05 am
Thanks for both of help, it now works.
J
November 13, 2003 at 9:09 am
Curious,
What was it?
November 19, 2003 at 8:57 am
This is just the topic I was looking for! I've been tasked with something that (I think) could easily be solved by executing at DTS pkg via a stored proc, but I've never done it or seen it done.
I used one of the code examples above but am not sure what I'm doing wrong?? The code executes without printing errors, but nothing happens.
I put print statements to see what the value of the @rc variable. It printed a couple non zero values, which I know are errors but don't know what they mean Here is the code followed by the @rc values:
DECLARE @objPackage int
DECLARE @PackageName varchar(128)
DECLARE @rc int
DECLARE @ServerName varchar(128)
DECLARE @DatabaseName varchar(128)
SET @PackageName = 'create_dataset'
SET @ServerName = @@ServerName
exec sp_OACreate 'DTS.Package', @objPackage output
exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
print @rc
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("begin_date").value', '10/1/2003'
print @rc
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("end_date").value', '10/31/2003'
print @rc
exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("org_id").value', 2031
--exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("GroupID").value', '1'
print @rc
exec @rc = sp_OAMethod @objPackage, 'Execute'
print @rc
exec @rc = sp_OADestroy @objPackage
-2147286768
0
0
0
-2147220499
November 19, 2003 at 9:26 am
What is happening in your back in your DTS?
IS your DTS firing?
Remember you might need to appropriate permissions to run your DTS package from the proc.
Turn on Profiler and to help look at what is going on.
November 19, 2003 at 10:00 am
The package doesn't fire from running the stored proc. The package runs fine with I run it directly from DTS designer.
Permissions shouldn't be a problem the way I have things set up.
November 19, 2003 at 12:56 pm
Another good trick is to have the DTS save to an exceptions file.
If the exceptions file is empty after you run it from the proc, chances are the DTS is not actually running due to an error before or during the connection to the DTS.
If the DTS is transforming a file to table for example, the file must be properly permissioned for SQL server to access it.
November 20, 2003 at 1:39 pm
I used the sp_OAGetErrorInfo to get a desc of the error code. On the sp_oamethod calling LoadFromSQLServer line, I get the error "OLE32.DLL has been loaded at the wrong address." This Dll is loaded on c:\winnt\system32. Do you know if it should be somewhere else? I'm doing all this from the server with sys admin privileges.
November 20, 2003 at 2:04 pm
Cross,
I think the error message from the sp_OAGetErrorInfo is misleading.
I do not know if you can get accurate error on what is causing the package not to fire, can someone confirm this?
I utilize the package exceptions to get a feel of the problem.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply