Run SQL script file from powershell -

i trying run queries stored in text file powershell. use following that;

invoke-expression 'sqlcmd -d testdb -u $user -p $pw -i "e:\sqlquery1.sql"' 

if error or exception occurs when executing queries .sql file, how can capture in powershell script? how can script output?

note: cannot use invoke-sqlcmd

to answer question

if error or exception occurred when executing .sql file how can powershell script? how can script output?"

invoke-expression returns output of expression executed. however, may capture stdout, not stderr (i haven't tested, don't use method), might not actual error message.

from help:

the invoke-expression cmdlet evaluates or runs specified string command , returns results of expression or command

the better route use powershell method have available - invoke-sqlcmd installed if have installed of sql server 2008 (or newer) components/tools (like ssms). if you've got sql server 2012, it's easy: import-module sqlps. 2008, need add snap-in, add-pssnapin sqlservercmdletsnapin. , since have sqlcmd.exe, powershell components should there already.

if else fails, go system.data.sqlclient route:

$conn=new-object system.data.sqlclient.sqlconnection "server=yourserver;database=testdb;user id=$user;password=$pw"; $conn.open(); $datacmd = new-object system.data.sqlclient.sqlcommand; $myquery = get-content "e:\sqlquery1.sql"; $datacmd.commandtext = $myquery; $datacmd.connection = $conn; $daadapter = new-object system.data.sqlclient.sqldataadapter; $daadapter.selectcommand = $datacmd; $dtable = new-object system.data.datatable; $daadapter.fill($dtable)|out-null; $conn.close(); $conn.dispose(); $dtable; 

with both , invoke-sqlcmd, you'll able use try/catch pick , handle error occurs.