Executing a script via Powershell possibility

Question

Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it from Powershell by calling it? What syntax would I use for myquery.txt to run? Put another way, what's a way to end-run not being able to use a stored procedure on a query that should be made into one?

Answer 1

It depends on your Powershell versions but you have a few options. I tend to use several of them based on the scenario but in your case, why not just call a SQLCMD.EXE from your powershell Script? It has the most compatibility, it's less buggy and more compatible than Invoke-SQLCMD, and outside of DSC I haven't seen MS use osql. It would look something like this:

#Check SQLCMD /? for help and syntax
$Query = "SELECT @@SERVERNAME"
SQLCMD -S "ServerName" -E #integrated security# -q $Query

Otherwise you'd have to install the SQL Server feature pack from v12 and above, then use Invoke-SQLCMD which has some compatibility issues and doesn't provide too many benefits except in certain cases I've found. You also get full SMO functionality with the Feature Pack but it's overkill and has too much config overhead for a simple query.

Answer 2

I notice you tagged the question netezza but I'm not sure that SQLCMD (suggested in the accepted answer) works with Netezza or just with SQL Server. An alternative is to use Aginity Workbench for Netezza which provides an unattended commandline option:

"C:\Program Files (x86)\Aginity\Aginity Workbench for PureData System for Analytics(x64)\Aginity.NetezzaWorkbench.exe" --unattended --stdout MyOutput.txt --stderr MyErrors.txt --description "New Execute SQL Command" --action exec --connstr "Driver={NetezzaSQL};server=mynzbox;UserName=myuser;Password=mypwd;Database=system;LoginTimeout=120" --dbtype NetezzaODBC --sqlfile MyQuery.sql

Answer 3

The equivalent of sqlcmd on Netezza is nzsql, which can be installed on both Windows and UNIX. We run automated powershell scripts that execute SQL statements through nzsql, and that works fine :)