how to log execution result of script

Question

Hello All ,

I'm using following code to execute a .sql script-

SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
server.ConnectionContext.ExecuteNonQuery(script, ExecutionTypes.ContinueOnError);


This script file has bunch of "ALTER" statement which apply primary key and foreign key constraint. I'd like to log the execution output of that script to a text file. Please advise how that can be achieved.

Regards,
Gurmit

Answer 1

Hi Gurmit,
As far as I know, there's no such method in SMO. Is it possible to exec the script in SSMS?

Regards.
Chao

---

panchao

Answer 2

Hello Chao Pan,

Do you think it would be possible to use the Capture Mode ?
http://msdn.microsoft.com/en-us/library/ms162182.aspx

I am not sure that this excellent page is related to the OP's question , but i am using this method when i am creating a full database ( i get the generated script and i write it in a "log file" with a StreamWriter ).
I would be grateful if you could give me explanations in case of getting wrong ( the best way to learn )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Hi,

The outpout from the statements is as I understand it equal to PRINT statements, which are event and difficunt, if not impossible, to handle using PowerShell.
Using SMO in C# or something else that can handle events makes the output more available. Unfortunately I am not a programmer, so I can't give a example.

Outside a "real" application try execute the statements with sqlcmd.exe and send the output to a file using the option "-o" or pipe the output it using ">".
This can be done in PowerShell by the cmdlet Start-Proces.

Quick and dirty...

Enjoy
Niels

---

Sincerely Niels Grove-Rasmussen