Questions tagged [powershell]

Windows PowerShell™ is a task-based command-line shell and scripting language designed especially for automation of Windows tasks. It has become the defacto CLI for Microsoft products since the its release, and can fully leverage .NET frameworks.

Filter by
Sorted by
Tagged with
0
votes
2answers
31 views

How to automate creating credentials in a secure way

We have created an automated process to stand up a new SQL Server using TFS and powershell. As part of a new server build, we deploy a database and a set of jobs for dba use. As part of the install ...
2
votes
2answers
41 views

PowerShell command 'New-Item' fails when invoked through SQL Server Agent job

I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing. The problem is the ...
0
votes
1answer
28 views

How to find port using Get-DbaTcpPort

How to find whether the port used by SQL Server is static or dynamic using Get-DbaTcpPort dbatools command? Below command doesn't show whether it's static/dynamic: Get-DbaTcpPort -sqlinstance TEST ...
0
votes
1answer
16 views

How to create multiple azure sql database in an elastic pool using powershell script?

I want to create a sql server and deploy the elastic pool on that server. Then create multiple azure sql database in that elastic pool using powershell script.
0
votes
0answers
34 views

Get-Counter not accepting array created from SQL

When I try and generate an array from a SQL output, although it's the same data type and content, perfmon doesn't accept these values, whereas it does for a manually created a list. So the first call ...
0
votes
0answers
17 views

PowerShell Subsystem max_worker_threads reverts back to default after applying cumulative update for SQL Server 2016

We change the PowerShell subsystem max_worker threads to 10, but after we install the latest cumulative update, it reverts back to 2. Is there a way to keep that from happening? SELECT s....
1
vote
1answer
41 views

How to list Extended Properties for all databases

I am currently working on task where we use Central Management Server and Server Group for easy access to our SQL server boxes. I am trying to query the extended properties for all databases on ...
0
votes
1answer
57 views

How to pass parameter value for ExecuteReader() - powershell

I am trying to run following powershell script. How Can I pass parameter value for @P0= '2019-11-22' and @P1 = '123456789' during runtime? $sqlconnection = New-Object System.Data.SqlClient....
1
vote
1answer
44 views

Union from Multiple Tables in many databases and group results in batches of n

First post in dba, so please be gentle. I have a project I am working through and have 4+ SQL Server databases to work with. These DBs contain 2 (or more tables) I wish to UNION the results from. ...
0
votes
0answers
32 views

SQL/Powershell job failing

Need more help from you brilliant folks. I am trying to import a CSV file to a SQL table with this code: $SQLServer = "MYSQLINSTANCE" $SQLDatabase = "DBATools" $SQLString = "Server=$SQLServer;...
1
vote
2answers
191 views

Invoke-SQLCMD with special characters

I'm currently working on a project integrating AD with SQL Server data, which I am handling mainly via powershell (Invoke-SQLCMD in particular). The issue I'm running into is if someone has a special ...
0
votes
1answer
22 views

Why does a fresh SQL Server instance with the same details as the original think that my DB backup is in the original location?

So I'm working on a server application for a large government body and need to setup a development environment to test on. For this I'm using a Vagrant-controlled VM with Windows Server 2019 installed ...
0
votes
1answer
18 views

Where does [reflection.assembly] reside?

In a disconnected DEV box, I attempt to load the reflection assembly, "Microsoft.SqlServer.SMO". Then I try to call for a SMO.Server object, like this [Reflection.assembly]::("Microsoft.SqlServer....
0
votes
1answer
44 views

SQL Job PowerShell task fails same command succeeds as user

Simple requirement: delete files on remote path older than 20 days. PS C:\Users\Administrator> Get-ChildItem ` -Path \\FileServer\SQLBackups\SQLServer\ ` -File -Recurse -Force | Where-...
1
vote
1answer
38 views

How to create a job that uses Powershell script? [duplicate]

I have a powershell script which deletes old backups from NAS storage. I have already tested it using Windows Powershell and it works but I couldn't perform the same thing in SQL Server. I have ...
1
vote
1answer
78 views

Index Fragmentation Monitoring: Can I make this query faster?

I have a query that is run against each database on two of my servers using a PowerShell script to initiate the script and throw the results into a CSV thus the need to have temp tables. Is there a ...
0
votes
0answers
126 views

Postgres using powershell scripts - where do trigger messages go?

I have a windows (10 pro) powershell script that reads messages from an external server and INSERTS them into a postges (v10.5) database table. The table has an AFTER INSERT trigger defined on on it ...
2
votes
2answers
58 views

SMO, Powershell, & Failed Logins

Background At my current organization, we've got a SQL Agent Job running a Powershell script, originally based on Script All Server Level Objects to Recreate SQL Server. This job writes a bunch of ...
0
votes
1answer
42 views

How to Configure Always Encrypted using PowerShell

I just want to ask if there is a way to configure always encrypted columns using power shell? like for example i want to encrypt a column in a table with deterministic using powershell codes?
0
votes
3answers
192 views

Check if a SQL Login exists without SA

How can I check if a SQL Login exists for an arbitrary domain user if the account I'm running doesn't have SA rights to the instance (but will be dbo on at least one DB) Ideally I'd like the solution ...
1
vote
2answers
87 views

Import table data into a text file

I'm new to powershell. I need to write a script which will import data for each table into separate text file.For sample table, I tried with below script: bcp -u sa por.dbo.audit out D:\tbackup\...
1
vote
1answer
292 views

Use Powershell to query SSAS connection string

I want to retrieve the existing connection string from SSAS for each OLAP Cube or Database. The data will be used in a control/audit report. I've found that Powershell allows me to drill into SSAS ...
1
vote
0answers
46 views

SQL Vulnerability Assessment Error in PowerShell

I am running Vulnerability Assessments against my SQL Servers and databases using the PowerShell cmdlets Invoke-SqlVulnerabilityAssessmentScan and Export-SqlVulnerabilityAssessmentScan, however, I am ...
0
votes
1answer
51 views

Does MySQLDump Verify the backup as restorable

I have written a PowerShell script to use MySQL dump to perform full backups of a specific database at a specific time. I am wondering if I need to confirm the backup is restorable via my script or ...
5
votes
1answer
505 views

How to Natively Return Output when using Invoke-Sqlcmd?

How do I natively log output from the PowerShell Invoke-Sqlcmd? I am using Invoke-Sqlcmd to loop through a large (50,000-90,000 SQL files w/ 1-10 inserts in each) set of small SQL insert statements. ...
0
votes
1answer
289 views

SQL Server unattended / Silent installation override Configuration.ini file in command line parameter

I am creating a PowerShell script to install SQL Server 2012 silent installation. It is not hard to find a tutorial or instructions online on how to do so. I want to my script be a bit more flexible ...
0
votes
0answers
104 views

SQL Agent PowerShell job runs in ISE but not in SQL Servere

My PowerShell script uses WinSCP to download files. It writes a message to a sql server table containing the number of files downloaded. When run within ISE it's fine. When run as a sql agent job ...
2
votes
1answer
52 views

Call Powershell command string from Sql Query Publish profile script

Is there a way to call powershell in SSMS Sql query window? I know Command prompt can be invoked with xp_cmdshell. Does similar method for powershell exist ? (Note, I know about powershell window , ...
2
votes
1answer
34 views

SQL Server Audit File Metrics Information

I'm currently working in an environment that requires we enable SQL Server Audit, enterprise-wide. We generate .sqlaudit files as we have retention and access guidelines, and I'm finding that we need ...
1
vote
1answer
92 views

Powershell runs locally but fails when put in a job - login failed, login is from an untrusted domain

I have a powershell script that queries from the local server for a list of servers and then iterates through that list of servers, connects to them, queries some data, and then connects and inserts ...
7
votes
2answers
1k views

Run Powershell Script From Agent As Administrator

I need to recycle an AppPool in IIS on my server when the SQL Server Service starts. The route I am taking is to have a startup stored procedure which runs an Agent job that has a Powershell job ...
1
vote
1answer
122 views

Powershell Invoke-Sqlcmd Replacing £ with?

When I create a SQL Script that inserts the value '£' or '€' into a table the value on the table is a question mark (?) Though, when I insert '$' it inserts a dollar sign. Anyone know what is going ...
4
votes
1answer
212 views

When using DAC, receiving the “[…] the maximum number of '1' dedicated administrator connections already exists” message in error log

I am about to escalate this to MS, but I thought I would try here first and see if anyone has any ideas as to how to prevent this faux error message... When connecting to SQL Server (2016 and 2017 ...
0
votes
1answer
886 views

powershell Write-Output format [closed]

I am finding what seems to be an inconsistency to the way a Write-Output line (which prints out a variable) should be formatted. I can't find any information on this, which makes me guess that I am ...
1
vote
1answer
120 views

Convert SQL Server results to a text file that PowerShell can access and use

The query below is used to create a text file with a list of server names. The problem I have is that the text file gets created like Server, Server, Server etc. but I need it as follows Server ...
0
votes
2answers
48 views

Looking for recommendation [closed]

I am a non IT background, my recent projects uses PowerShell for a lot of SQL Server administration. I would like to start from absolute zero, and learn to use and code the Windows PowerShell as it ...
0
votes
0answers
328 views

Test connection between application and DB using Oracle instant-client

Good morning I have on one side an 12.1.0.2 Oracle database server and on the other side an application server which is running Windows Server 2012 R2 Standard. Client connection to the database are ...
1
vote
1answer
261 views

Fail SSIS “Execute Process Task” if false is returned

I have an "Execute Process Task" that calls Powershell and checks if a folder is empty. I'm wanting the SSIS job to fail if $false is returned. Here is the code param ([string] $FolderLocation) $...
0
votes
1answer
98 views

Set service startup using DSC

Windows PowerShell Desired State Configuration (DSC) newbie here. The MOF file generation fails with: PSDesiredStateConfiguration\Node : The argument is null or empty. Provide an argument that is not ...
1
vote
0answers
37 views

Add signature to stored procedure using PowerShell SMO

I want to add a signature to a stored procedure using PowerShell SMO. The equivalent command in TSQL is: ADD SIGNATURE TO [myprocedure] BY CERTIFICATE [mycertificate] with password = 'CertificatePW' ...
0
votes
0answers
180 views

Silent install of Oracle XE 11g fails with error -3

I'm trying to install silently Oracle 11g XE in the default location (C:\oraclexe). PS D:\Profiles\xxx\Downloads\x.x\oraclexe\oraclexe-11.2.0> .\setup.exe /S /v/qn However, the command generates ...
0
votes
2answers
336 views

Create SQL login to multiple instances at once

I have a AD group that needs to be added to multiple SQL instances, I am wondering is there a quicker way of adding this group at once rather than having to login to each server and creating a login ...
1
vote
1answer
333 views

How to copy all firewall rules and apply them on a new server?

I often install new SQL Server Servers. Be them production, test or development. There is one thing I still have not automated and it is time consuming: the firewall settings. I know how to ...
0
votes
1answer
29 views

Missing data when trying to import large columns using powershell [closed]

Powershell command (whitespace added for readability): Invoke-Sqlcmd -ServerInstance "$db" -Database "$dbname" -Username "xxxx" -Password "xxx" -Query "select logmessages from log ...
2
votes
1answer
1k views

how to Enable these Windows Firewall rules using powershell?

I have a few servers where I see a blue question mark on the sql server service, as per the picture below. This is the solution: Enabling these Windows Firewall rules did the trick for me ...
0
votes
2answers
141 views

How to have SQL Server's SQL Agent pick up a new Powershell module

I have a Powershell module that I've written and uploaded to my SQL Server at C:\Program Files\WindowsPowerShell\Modules.I have a SQL Agent job with a powershell step that calls the module. This all ...
1
vote
2answers
1k views

Running Powershell Code embedded in SSIS or TSQL, not in external file

Is there a method to embed powershell code in SSIS or T-SQL, without saving the code in an external file *.ps1? We know SSIS/Tsql can run powershell, however the code always seems to be saved ...
0
votes
1answer
945 views

Powershell Run Stored Procedures in Parallel in Database

We want to run all stored procedures in parallel. The powershell script finds a list of stored procedures in the database (only have 5 right now), and runs them simultaneously. This code seems to be ...
1
vote
0answers
244 views

SQL Agent Powershell step not using specified proxy

I'm trying to execute a powershell script, and use a proxy to do. The job runs correctly, but using whoami within the script to log the executing user indicates that the script is not being run by the ...
1
vote
2answers
62 views

Get-SqlAgentJobHistory Invoked By

I want to report on failed Sql Agent jobs using Get-SqlAgentJobHistory but excluded jobs that were not invoked by the service account. I'm running this cmd: Get-SqlAgentJobHistory -ServerInstance $...