question

RakeshDhar-0664 avatar image
0 Votes"
RakeshDhar-0664 asked ·

Error while executing the powershell from Management Studio using xp_cmdshell

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-generalsql-server-transact-sqlwindows-server-powershell
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

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.



· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yeah ,,..just check . it does not look installed on the Sql Server. Ok . i will let team to install this on the server where SQL is running...
meanwhile i was trying to test this on my local system ...by executing this with xp_cmdshell command and i got this ...the error

0 Votes 0 ·

New-Object : Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed
due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).
At G:\testshell\Best\testsamplefile.ps1:4 char:14
+ $Excel = New-Object -ComObject Excel.Application
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [New-Object], UnauthorizedAccessException
+ FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.NewObjectCommand


You cannot call a method on a null-valued expression.
At G:\testshell\Best\testsamplefile.ps1:5 char:5
+ $wb = $Excel.Workbooks.Open($excelFile)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull



0 Votes 0 ·

80070005 Access is denied.

A clear error message: Access denied = missing permissions.



0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.