Powershell

Question

Powershell

dishantbmg

SSC Rookie

Points: 26
More actions
June 25, 2014 at 8:18 am

#290336

Hi,
As we know there are two output windows in sql server management studio like 'Results' and 'Messages',
I want to capture that 'Messages' window into a log file using powershell program .
For ex:- into a log file it should display like
(1 row(s) affected)
(1 row(s) affected)
Can anyone help me out with this one, Thanks in advance
Dishant jain

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hakan Forsberg SSC Enthusiast Points: 175 More actions · Answer 1

Can you give a more detailed view of what you are trying to accomplish. Maybe there is an easier way for the same result...

Gary Gwyn SSC-Addicted Points: 443 More actions · Answer 2

When using the Invoke-Sqlcmd, include the -Verbose option. That will output the "messages" tab from SSMS.

PS U:\> Add-PSSnapin sqlservercmdletsnapin100

PS U:\> Invoke-Sqlcmd -Query "select 'test'; PRINT N'123';" -Verbose

Column1

-------

test

VERBOSE: 123

PS U:\>

Blog: http://sqlexchange.wordpress.com

dishantbmg SSC Rookie Points: 26 More actions · Answer 3

Actually i wrote a powershell program which is taking an input file containing some scripts, i want to capture message window of SSMS into a log file. How can achieve that...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

dishantbmg (6/26/2014)
Actually i wrote a powershell program which is taking an input file containing some scripts, i want to capture message window of SSMS into a log file. How can achieve that...

I could be wrong but I'm pretty sure it (code controls SSMS) can't be done automatically where you have control over what the name of the file is. You CAN do it manually just before you run your code by selecting "Results to File" instead of "Results to Grid" or "Results to Text".

An alternative might be to use the DOS redirection character for SQLCmd or pipe the output to a file when using PoSH.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

Are the queries you are trying to capture rows affected from selects or insert/update/delete?

If they are modification you are trying to get affected rows for something like this may work for you.

function Invoke-Sqlcmd2

{

param(

[string]$ServerInstance,

[string]$Database,

[string]$Query,

[Int32]$QueryTimeout=120

)

$conn=new-object System.Data.SqlClient.SQLConnection

$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database

$conn.Open()

$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)

$cmd.CommandTimeout=$QueryTimeout

$cmd.executenonquery()

$conn.Close()

}

Invoke-Sqlcmd2 -ServerInstance "instancename" -database "dbname" -Query "update foo set id = 0 where id < 100"

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]