DTS with ActiveX called from Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS with ActiveX called from Stored Procedure

Author

Topic

isanlu
Starting Member

7 Posts

Posted - 2004-11-24 : 15:45:14

OK this may sound complex but the idea is actually quite good.

I have a stored procedure that feeds a Crystal Report in an application. The user requires that the information on the report is exported and cleaned up in excel for email purposes. The information for the report is stored in a table used for this purpose. It is a semi temp table, it gets truncated at the beginning of the report and populated at the end.

I created a DTS package with basically two steps

1.- Creates a text file based on the select * from table
2.- I have an Excel document called Master.xls with a Macro that opens the text document created in step 1 and formats and saves the document the way the users wants it. The macro in the Excel document is called by the following ActiveX script in the DTS Package:

Function Main()
dim xl_app
dim xl_Spreadsheet
SET xl_app = CREATEOBJECT("Excel.Application")
SET xl_spreadsheet = xl_app.Workbooks.Open("D:\MemberTemp\Master.xls")
xl_app.Run ("ProcessData")
xl_spreadsheet.Save
xl_spreadsheet.Close
xl_app.Quit
set xl_app = Nothing
Main = DTSTaskExecResult_Success
End Function

The goal is to call this DTS package from the original stored procedure that feeds Crystal so that at the same time the user submits the report with whatever selection critera he/she picked the excel spreadsheet is created.

I have inserted the following line at the end of the stored procedure:

exec master..xp_cmdshell 'DTSRUN /S TORNADO /N Test /E' (apparently this is the only way to run a DTS package from a stored procedure)

I can run the DTS package from the Package window with no errors, I can run the DTS package from a command line using the DTSRUN.... as above, I can run the DTS package from a .bat file. When I try to execute the original stored procedure the DTS package fails with the following message copied from a query window

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000
DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 1250 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1250
DTSRun OnFinish: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied: 'CREATEOBJECT'
Error on Line 3Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied: 'CREATEOBJECT'
Error on Line 3
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.

I can't figure out why the CREATEOBJECT fails. I have checked all permissions. All processes are being run from the SERVER using the Admin Account. Why does it work from the DTS window and other sources and not from another procedure

Thanks a million. Who ever can find the answer is a genious. I have searched all of Google for this one with no answer!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-24 : 15:49:32

When you run xp_cmdshell, SQL Server uses the account that the MSSQLSERVER service account uses. Check that service. Is it using Local System Account? If so, that could be your problem. Both SQL Server services should be using a domain account that has local admin privileges.

Tara

isanlu
Starting Member

7 Posts

Posted - 2004-11-24 : 16:03:33

I fixed it, thanks anyway

see the following article that I found from another topic

http://support.microsoft.com/default.aspx?scid=kb;en-us;298725

In conclusion sqlteam.com is by far the best site out there!!!

Thanks again!!

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-11-24 : 18:14:32

Can I ask why you export to a text file then import to Excel? Couldn't you just put it straight into Excel, perhaps into a template? Could the formatting not be done as part of the export?

steve

To alcohol ! The cause of - and solution to - all of life's problems

rajamalado
Starting Member

1 Post

Posted - 2007-03-01 : 00:16:49

I am facing the same Problem Permission denied error when run the DTS which calls the Excel Macro.Please reply me immediately.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources