Export-CSV -Append

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 6 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
markcusano
Posts: 21
Last visit: Mon Oct 23, 2017 1:22 pm

Re: Export-CSV -Append

Post by markcusano »

I see.

Note: When I run my code from the ISE, everything works great. I'm running PSVersion 3 from my desktop.

Here is my desktop $env:psmodulepath, which is where my Powershell Studio is installed.
C:\Users\MyAccount\Documents\WindowsPowerShell\Modules
C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\
C:\Program Files\Microsoft Application Virtualization\Client\
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft Azure Information Protection\Powershell


My SQL Server psmodule path gets automatically loaded when I log into a server, based on what version is on the server. We only have one version on the server. Here is the psmodulepath of that server.

C:\Users\MyAccount\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\windows\system32\WindowsPowerShell\v1.0\Modules\
X:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\
Z:\Program Files\Microsoft Monitoring Agent\Agent\PowerShell\



I compile with v3 host command line 64 and get this error when I run the exe on the server from the server:

Invoke-Sqlcmd : Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.




All I basically need to do is something like this:
Create a button that loops through a list of sql servers to get database names and Export the databases to a file and append each to the same file.

Invoke-Sqlcmd -ServerInstance $MySqlServerInstanceName -query 'Select * from sys.databases' | Export-CSV -Path "c:\temp\MyFile.csv" -Append -NoTypeInformation


Are you saying that my desktop $env:psmodule path needs to change, even if I'm running the exe from the server on the server where it only has one version of sql server and only one reference to the sql module path?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

First check to see which SQLServer module is installed.

Get-Module *SQL*
and what path is it using.
Get-Module *SQL*|select path
User avatar
markcusano
Posts: 21
Last visit: Mon Oct 23, 2017 1:22 pm

Re: Export-CSV -Append

Post by markcusano »

ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Manifest 1.0 SQLASCMDLETS {Add-RoleMember, Backup-ASDatabase, Invoke-ASCmd, Invoke-ProcessCube...}
Script 0.0 Sqlps
Manifest 1.0 SQLPS {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Backup-SqlDatabase, Convert-UrnToPath...}




PS SQLSERVER:\> Get-Module *SQL*|select path

Path
----
X:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLASCMDLETS\SQLASCMDLETS.psd1
X:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1
X:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

You will need to upgrade the version of SSMS installed. It is the old V2 which will not run under PS 3.
The fix you posted was to allow a Net version 2 app to run Net 4 modules. That is not your issue.
Try installing the newer package from the Gallery

find-module SQLServer | Install-Module
Must be run from PS 5 or download from gallery and install manually.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

You can also save the module to a path and copy it to the target server.

find-module sqlserver|Save-Module -Path <path to folder>
User avatar
markcusano
Posts: 21
Last visit: Mon Oct 23, 2017 1:22 pm

Re: Export-CSV -Append

Post by markcusano »

jiverra!

I really appreciate you helping me on this.

I moved to a PS Version 5 Server, Installed the SQLServer Module, and it works.

Thank you again!!
This topic is 6 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked