Hi
I have been using the OLE objects to execute a package in a stored procedure, everything works fine for a while then I see myself having to reboot the server because the package is not working, the funny thing is that the procedure does not give me errors. In my procedure I first create an excel file(this part never fails, I see the file created), then I dynamically pass in the file path of the newly created excel file and set the connection object. The package should then export a view in the excel file(which it does not). The problem is that the stored procedure is not returning any errors, and the view is not being exported to the excel spreadsheet. Is it possible that if sometimes a user encounters an error, it locks up the OLE object?? Here is my code snippet. I have checked at each step for an @hr error but everything is 0, and I know I have data in my view???
An aside, when I execute the package from enterprise manager, all goes well, it fills my excel spreadsheet.
Thanks in advance.
Here is my code for the OLE automation
Exec @hr = sp_OACreate 'DTS.Package', @Pkg out
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Pkg
goto ExitError
END
-- Load the Package definition
-- Flags is for Trusted Connection
Exec @hr = sp_OAMethod @pkg, 'LoadFromSQLServer',
NULL, @Server, @PackageName = @PkgName, @Flags = 256
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @pkg
goTo ExitError
END
-- Get a handle to the ExcelFile connection
Set @propName = 'connections(' + @fileConnName + ')'
Exec @hr = sp_OAGetProperty @Pkg, @propName, @conn out
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Pkg
goTo ExitError
END
--set the datasource to the new file path
Exec @hr = sp_OASetProperty @conn, 'datasource', @filepath
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @conn
goTo ExitError
END
Exec @hr = sp_Oamethod @pkg, 'Execute'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @PKg
goTo ExitError
END
-- Cleanup
Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg
END
return 0
ExitError:
-- Cleanup
Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg
return 1
END
GO
I have been using the OLE objects to execute a package in a stored procedure, everything works fine for a while then I see myself having to reboot the server because the package is not working, the funny thing is that the procedure does not give me errors. In my procedure I first create an excel file(this part never fails, I see the file created), then I dynamically pass in the file path of the newly created excel file and set the connection object. The package should then export a view in the excel file(which it does not). The problem is that the stored procedure is not returning any errors, and the view is not being exported to the excel spreadsheet. Is it possible that if sometimes a user encounters an error, it locks up the OLE object?? Here is my code snippet. I have checked at each step for an @hr error but everything is 0, and I know I have data in my view???
An aside, when I execute the package from enterprise manager, all goes well, it fills my excel spreadsheet.
Thanks in advance.
Here is my code for the OLE automation
Exec @hr = sp_OACreate 'DTS.Package', @Pkg out
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Pkg
goto ExitError
END
-- Load the Package definition
-- Flags is for Trusted Connection
Exec @hr = sp_OAMethod @pkg, 'LoadFromSQLServer',
NULL, @Server, @PackageName = @PkgName, @Flags = 256
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @pkg
goTo ExitError
END
-- Get a handle to the ExcelFile connection
Set @propName = 'connections(' + @fileConnName + ')'
Exec @hr = sp_OAGetProperty @Pkg, @propName, @conn out
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Pkg
goTo ExitError
END
--set the datasource to the new file path
Exec @hr = sp_OASetProperty @conn, 'datasource', @filepath
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @conn
goTo ExitError
END
Exec @hr = sp_Oamethod @pkg, 'Execute'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @PKg
goTo ExitError
END
-- Cleanup
Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg
END
return 0
ExitError:
-- Cleanup
Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg
return 1
END
GO