Error while executing the powershell from Management Studio using xp_cmdshell

Rakesh Dhar 96 Reputation points
2021-03-05T08:16:18.927+00:00

hello all ,

i am trying to execute the powershell script which is converting the excel file to .csv file from
SQL management studio. i am using xp_cmdshell .please help.

i cannot use execution Policy as Unrestricted .bcz of security issues.

please let know any idea where i am missing anything below is the error.

New-Object : Retrieving the COM class factory for component with CLSID
{00000000-0000-0000-0000-000000000000} failed due to the following error:
80040154 Class not registered (Exception from HRESULT: 0x80040154
(REGDB_E_CLASSNOTREG)).
At \11.1\Public$\DBA\Content\DEV\PtoS.ps1:101 char:12

  • $ExcelWb = New-Object -ComObject excel.application
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : ResourceUnavailable: (:) [New-Object], COMExcept
    ion
  • FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Comman
    ds.NewObjectCommand

The property 'DisplayAlerts' cannot be found on this object. Verify that the
property exists and can be set.
At \11.2.1\Public$\DBA\Content\DEV\PtoS.ps1:103 char:1

  • $ExcelWb.DisplayAlerts=$False
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At \11.1\Public$\DBA\Content\DEV\PtoS.ps1:105 char:1

  • $Workbook = $ExcelWb.Workbooks.Open(($InputFilePath + $InputFile))
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \11.1\Public$\DBA\Content\DEV\PtoS.ps1:107 char:1

  • $Workbook.SaveAs($OutputFileFullName,62) # UTF8 format csv
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \11.1\Public$\DBA\Content\DEV\PtoS.ps1:113 char:1

  • $WorkSheet = $WorkBook.Sheets.Item(1)
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \11.1\Public$\DBA\Content\DEV\PtoS.ps1:117 char:1

  • $Workbook.Close($False)
  • ~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \11.1\Public$\DBA\Content\DEV\PtoS.ps1:119 char:1

  • $ExcelWb.Quit()
  • ~~~~~~~~~~~~~~~
  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

NULL

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,675 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,359 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,736 Reputation points
    2021-03-05T08:39:20.357+00:00

    Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered

    Is MS Excel installed on the server where SQL Server is running? By the error message I guess it's not installed.

  2. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-05T22:41:28.66+00:00

    Stop!

    Don't try to run Excel from within SQL Server! Excel is a Windows application which likes to have a desktop, but SQL Server runs as a service and does not have a desktop.

    This will create a mess sooner later. Better backtrack right now, and give it a second thought what you really want to achieve.

    0 comments