SQL agent on SQL Server 2005 (64bit) failing to run activex script

• virendra_india

  Newbie

  Points: 1

  More actions

  January 8, 2011 at 3:14 pm

  #148186

  hi,

  I'm using following activex script on SQL Server 2005 (64 bit) and it failing every time, the error is

  Message

  Executed as user: test\test. Error Code: 0 Error Source= Microsoft Excel Error Description: Microsoft Excel cannot access the file 'c:\test.xlsx'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. Error on Line 1. The step failed.

  Activex code

  Set objExcel = CreateObject("Excel.Application")

  Set objWorkbook = objExcel.Workbooks.Open("c:\test.xlsx")

  objExcel.Application.Visible = False

  objExcel.Application.DisplayAlerts = False

  objExcel.ActiveWorkbook.SaveAs "c:\test.xls", 56

  objExcel.ActiveWorkbook.Close

  objExcel.Application.DisplayAlerts = True

  objExcel.Application.Quit

  If I use this same script in SSIS as a Activex script task it runs perfectly fine in BIDS. Please help and let me know why sql agent behaves differently whereas SSIS in BIDS runs same script with no errors.

  What I think is SQL Agent fails to instantiate Excel.Application object and hence it can't go further with opening a workbook and rest of the statements.

  Please respond quickly as this is very urgent.

  thanks

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 8, 2011 at 4:21 pm

  #1271092

  I doubt that the problem is instantiating the Excel Application object - you are getting an error from Microsoft Excel!

  You are running the script in BIDS on the server? Logged in as "test\test"?

  Have you tried putting a delay in the script - straight after the CreateObject - so that you can see in task manager whether Excel is starting - it should be...?

  Are you sure that "test\test" has read/write permissions on the file and on "c:\" ?

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]