none
Unable to create workbook_open event dynamically from vb script in sql server (OS -windows 2008 server) RRS feed

  • Question

  • Hi All,

     

    We are facing one issue since last 3 weeks, requesting you to kindly help me on this.

    Issue details are mentioned below:

     

    Problem Statement:

                    Unable to create workbook_open event  dynamically from vb script in sql server (OS -windows 2008 server)

     

    Issue details:
                    We have a VB script code (test.vbs), it opens an excel sheet and calls one function, this function will dynamically create an workbook_open event.

                    We are executing this VB script from sql server 2008  using ‘xp_cmdshell’ command and it has  ##xp_cmdshell_proxy_account## on the server on the server.

     

    Execution Command:
                    xp_cmdshell 'C:\windows\System32\CScript.exe //B D:\test.vbs "D:\test.xls"


    Success Scenario:

    When we log onto the server remotely with the proxy account and execute the query from local system (Query Analyzer), then above  command executes successfully.

     

    Failure Scenario:

    whenever we log off from the remote server (Or no active login to remote server) and and execute the query from local system (Query Analyzer), we are getting below error.
                   
    Error: Object variable or With block variable not set

     

    Code Snippet:

    Code to create an event dynamically.


     Dim VBComp As VBIDE.VBComponent
            Dim CodeMod As VBIDE.CodeModule
            Dim LineNum As Long
            Const DQUOTE = """" ' one " character

    With CodeMod
               LineNum = .CreateEventProc("Open", "Workbook")
               LineNum = LineNum + 1
                .InsertLines LineNum, _
                "   RunTestRpt"
    End With

    I am getting the issue exactly at with block.


    I think this is the issue with windows 2008 permissions only, the same code is working fine on windows 2003 server.

    Please help me on this issue.

     

     

    Thanks,

    Raja.

    Friday, January 13, 2012 6:26 AM