September 6, 2005 at 4:50 am
I have a DTS Package which uses VBScript to update specific rows and columns of a specific Excel worksheet of an Excel Spreadsheet, my DTS Package appears to work, visibly creating Spreadsheets etc when I execute from within DTS Designer. However when I call it from a Stored Proc, it does not activate or make visible the Excel window and gets stuck. Is there a work around to force the Excel objects to work as within the DTS Designer, even trying to call DTSRun, using xp_cmdshell does not fix it. Running from proper command line though does work.
Cheers
Steve Giergiel
easyget.biz
September 6, 2005 at 6:35 am
Sounds very much like a permissions problem to me, is the account you're using to run the SP the same one as the DTS execution?
September 6, 2005 at 10:10 am
Hi Mike, thanks for the reply.
However I am running both the SP and the DTS as myself, and I have set the SQL Services to run under my account also, as I have had issues with SQL being unable to write to network folders in the past.
I suspect it is related to SQL preventing Excel from going visible for whatever reason, as looking at the processes Excel does fire up but just sits there, presumably waiting for visibility or something to be confirmed before continuing with the actual assigning of the various column, row cell values. I have even tried switching the visibility true statements off within the DTS VBScript, but it appears that for the assigns to work using the Range object values Excel needs to be visible.
I have tried Linked Server access to Excel from SQL, but this seems to work on full Tables not allowing specific row column settings, and appears very fiddly to set up using DDL creates etc....
I have also tried CopyFromRecordSet executes using sp_OA.. which almost works but appears to fall over when trying to send more than a very small chunk of data around. And more recently fails to work at all ie very unreliable.
The latest theory I am trying is to effectively build up each work sheet inside a dummy table which mirrors the spreadsheet layout, and then I will try to use the linked server method or similar to stuff these dummy table layouts into the appropriate worksheets. This does seem however like a very long winded way and will require much more development than originally anticipated.
Cheers
Steve Giergiel
easyget.biz
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply