Running Powershell Scripts Through SQL

How to execute .sql file using powershell?

Try to see if SQL snap-ins are present:

get-pssnapin -Registered

Name        : SqlServerCmdletSnapin100
PSVersion   : 2.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name        : SqlServerProviderSnapin100
PSVersion   : 2.0
Description : SQL Server Provider

If so

Add-PSSnapin SqlServerCmdletSnapin100 # here lives Invoke-SqlCmd
Add-PSSnapin SqlServerProviderSnapin100

then you can do something like this:

invoke-sqlcmd -inputfile "c:\mysqlfile.sql" -serverinstance "servername\serverinstance" -database "mydatabase" # the parameter -database can be omitted based on what your sql script does.

How can I execute SQL scripts via Powershell?

Perhaps you're looking for something like:

# ...
Get-ChildItem C:\Scripts -Filter *.psql | ForEach-Object {
  # Execute the content of each file as its own command.
  $DBCmd.CommandText = $_ | Get-Content -Raw
  $DBCmd.ExecuteReader();
}
# ...

If you want to merge the contents of all *.psql files and submit it as a single command:

# ...
# Execute the merged content of all script files as a single command.
$DBCmd.CommandText = (Get-Content -Raw C:\Scripts\*.psql) -join "`n"
$DBCmd.ExecuteReader();
# ...

How to execute a PowerShell file from stored procedure

Your problem is because the account that the agent is using does not have sufficient permissions to access the file.

How to check:

(1) You can run PS from the command line
(2) You cannot run the script from within xp_cmdshell
(3) Change the folder containing the file to full control by Everyone
(4) Try running the script via xp_cmdshell

Alternatively for a test, you can change the login SQL Agent is running under to your credentials. Then the agent is running as "you", the same as if you are running on the command line.

Calling Powershell script from SQL Server trigger

First off, this is almost certainly a very bad idea. Keep in mind that triggers can cause unexpected issues in terms of transaction escalation and holding locks longer than necessary while they're processing. Also keep in mind that people will probably not expect there to be triggers of this sort on your table, and that they'll try to do CRUD operations on it like it's a normal table and not understand why their applications are timing out.

That said, you could do this at least three ways:

1. Enable xp_cmdshell, and use that to shell out to PowerShell, as explained here: Running Powershell scripts through SQL - but don't do this, because xp_cmdshell is a security risk and this is very likely to cause problems for you in one way or another (whether because someone uses it in a damaging manner or because PowerShell just fails and you don't even know why). If you can't use database mail due to security restrictions, you should definitely not be using xp_cmdshell, which has even more security concerns!
2. Instead of using PowerShell, configure Database Mail have your trigger call sp_db_sendmail - but don't do this because this could easily fail or cause problems for your updates (e.g. SMTP server goes down and your table can't be updated anymore). (I wrote this part before I saw you can't use it because of security restrictions.)
3. One other option comes to mind that may be more secure, but still not ideal - create a SQL CLR library that actually sends the mail using the .NET SmptClient. This could be loaded into your instance and exposed as a regular SQL function that could be called from your trigger. This can be done more securely than just enabling xp_cmdshell, but if you don't have the ability to configure Database mail this probably violates the same policy.

Instead of these options, I'd recommend something like these:

1. Instead of sending an email every time there's an update, have your trigger write to a table (or perhaps to a Service Broker Queue); create a job to send emails periodically with the latest data from that table, or create some kind of report off of that. This would be preferable because writing to a table or SSB queue should be faster and less prone to error than trying to send an email from in a trigger.
2. Configure and use Change Data Capture. You could even write some agent jobs or something to regularly email users when there are updates. If your version supports this, it may be a bit more powerful and configurable for you, and solve some problems that triggers can cause more easily.

Running Powershell script (that runs other exe file) from SQL Server Agent

From what you are saying, it should be ok, but if want to be sure to launch your script with the appropriate user rights, you can also pass the according credentials directly to the Start-Process Cmdlet:

#Use parameters to obtain username and password, avoid to hard-code them here.

$credentials = New-Object System.Management.Automation.PSCredential -ArgumentList @($username,(ConvertTo-SecureString -String $password -AsPlainText -Force))

Start-Process $cmd -Credential ($credentials)

---

Related Topics