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
1
vote
1answer
32 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 ...
0
votes
0answers
17 views

What are drawbacks of running CmdExec job step?

Is there any risk of running CmdExe job step in SQL Server? Which one is more secure, running powershell script via task manager (windows) or that of via SQL Server agent (CmdExec)?
1
vote
1answer
62 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
18 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
44 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
29 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
44 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
55 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
50 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
17 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
23 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
111 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
42 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
43 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
48 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 , ...
1
vote
1answer
49 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 ...
5
votes
2answers
576 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
61 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 ...
3
votes
0answers
120 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
328 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 ...
0
votes
1answer
33 views

Compress-Archive showing exception in Windows Server 2008 R2

Can any one help to solve this issue. Compress-Archive in PowerShell showing below exception in Windows Server 2008 R2. c:\datafolder\powershell Compress-Archive . A.zip
1
vote
1answer
75 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
175 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
123 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
54 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
26 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
102 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
200 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
98 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
26 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
585 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
83 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
765 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
698 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
197 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
50 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 $...
1
vote
0answers
20 views

Extracting AD subgroups information [duplicate]

I need to extract AD groups and all of it's subgroup into an excel sheet.Basically the script should loop though each of the AD group and get their subgroup. AD_Group Subgroup ---------- -----...
3
votes
1answer
104 views

SQL Visual Studio Find and Replace in Publish Profile

We have source control in Visual Studio Database Projects (SSDT). To make cross database references, we utilize something like this below in Project References. We need Project references, since ...
3
votes
3answers
107 views

How to speed up rebuilding a database from scratch/scripts

Our Database exists in many different scripts within our source control. i.E. every table has it's own scripts for creating the table, the indexes and so on. When needed, we can rebuild the entire ...
2
votes
2answers
92 views

DBATools - pass through flat file configs to Agent Job Commands

I'm experimenting with source controlling my SQL Agent Jobs & Schedules as .json files and deploying them with the dbatools SQL Agent command suite. Given $sa = Get-Credential and foo.config of ...
1
vote
1answer
711 views

DBATools offline install - export-dbainstance was not found

I followed the instruction at https://dbatools.io/offline/: download zip & unzip run $env:PSModulePath to find all the paths. copy to C:\Program Files\WindowsPowerShell\Modules. run Get-ChildItem ...
1
vote
1answer
165 views

how to script out all the permissions in a folder using powershell?

Using the script below I find out where are the data and log folders: declare @rc int, @dir nvarchar(4000) declare @data nvarchar(4000) declare @log nvarchar(4000) ...
1
vote
0answers
80 views

Switch-SQLAvailability error during DB Restore in AlwaysON Group

Can anyone share your inputs on below and how to fix the issue and possible causes of failure? I am trying to restore a DB using PS script and at one step it is failing during the role change test ...
0
votes
1answer
271 views

CmdExec Powershell Job Step Escape Double Quotes

I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with ...
0
votes
1answer
493 views

How do I compare Schemas for Multiple Databases to a True Copy?

We have a database for each customer, over 700 databases. All the schemas are supposed to be exact same. We have Source Control database, and want to schema compare to the 700. Sometimes schema in ...
0
votes
1answer
24 views

How to Remove Scripts Date Comments from Powershell Generate Database Script?

I am generating Database Generate scripts with Powershell. What is the option in Powershell to remove Script dates comment lines? Automated Script-generation with Powershell and SMO Object: Table ...
2
votes
2answers
569 views

SSIS package deployed to SQL Server hangs when running a Powershell script

We have a SSIS package that tidies up a few files and folders, and want to automate it to run every night via the SQL Server Agent. When running the package via Visual Studio (2015 and 2017) the ...
0
votes
1answer
51 views

Instead of C:\SQLDBA\ServerName\Instance rather C:\SQLDBA\ServerName_Instance

I am trying to create a folder based on the servername\instance to backup up to, i want the folder to create as servername_instance. Could you please point out where i am going wrong as it creates as ...
1
vote
2answers
1k views

How to find the list of SQL Server jobs using PowerShell?

I need to create Windows PowerShell Scripts that will check and list down all SQL Jobs scheduled on a given database server. Is it possible to find out the list using PowerShell Scripts? If yes, how ...