Any way to capture errors from Shell command

[standish22]

I want to programmatically capture an error that is done from a Shell
command in VB.Net code.

What I have is a Test.sql file that looks like

---------------------------------------------------
select * from Table1
GO
select * from Table2
GO
----------------------------------------------------

Then in my vb.net code I have:

Private Sub btnRun_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRun.Click

Shell("osql -U UserID1 -P Password1 -S MyServer" & _
"-d MyDatabase -i C:\Test.sql -o C:\Output.sql", _
AppWinStyle.NormalFocus)

End Sub

Is there anyway inside the VB.Net code to capture an error if it
occurs. If I'm required to change my Test.sql file, that is fine. I
have access to this file created.

I'm wanting to run a series of these commands (some may be
updates/creates/etc) But if there is an error, I want to stop.

Thanks...

 

[Chris]

I want to programmatically capture an error that is done from a Shell
command in VB.Net code.

What I have is a Test.sql file that looks like

---------------------------------------------------
select * from Table1
GO
select * from Table2
GO
----------------------------------------------------

Then in my vb.net code I have:

Private Sub btnRun_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRun.Click

Shell("osql -U UserID1 -P Password1 -S MyServer" & _
"-d MyDatabase -i C:\Test.sql -o C:\Output.sql", _
AppWinStyle.NormalFocus)

End Sub

Is there anyway inside the VB.Net code to capture an error if it
occurs. If I'm required to change my Test.sql file, that is fine. I
have access to this file created.

I'm wanting to run a series of these commands (some may be
updates/creates/etc) But if there is an error, I want to stop.

Thanks...

Why not connect through ADO? Then you will have access to the error, if
any.

Chris

 

[standish22]

We're currently using ADO. But because our scripts are so large, we
are having to parse these files because of the GO's.

Right now, to run these scripts thru VB and ADO, it takes 3 hours. In
query analyzer it takes 30 minutes.

We're trying to find an alternative to parsing the files. But we need
to handle errors so our customer doesn't continue if there's a problem.

Thanks

 

[Cor Ligthert [MVP]]

Standish,

If you want to use instead shell the application start than you can get the
commandline message.

Just a sample that I copied from my HKW database.
\\\
Dim p As New Process
Dim pi As New ProcessStartInfo
pi.UseShellExecute = False
pi.RedirectStandardOutput = True
pi.Arguments = "www.google.com"
pi.WorkingDirectory = "C:\\windows\\system32"
'this for nt* computers
pi.FileName = "ping"
p.StartInfo = pi
p.StartInfo = pi
p.Start()
Dim sr As IO.StreamReader = p.StandardOutput
Dim sb As New System.Text.StringBuilder("")
Dim input As Integer = sr.Read
Do Until input = -1
sb.Append(ChrW(input))
input = sr.Read
Loop
MessageBox.Show(sb.ToString)
///

I hope this helps,

Cor

 

[standish22]

Once you get the messages, how would you know there was an error?

 

[m.posseth]

i have once writte a program in VB.Net 2003 that would import a mysql
dump file from 7,5 GB
first i looped through the file from start to end to and converted the mysql
dialect to MS SQL dialect for the DDL
then i looped again to convert all inserts on the fly and executed them (
with 1000 inserts at a time )

this program ran for +- 20 minutes to import this file

computers used :

client computer AMD XP 2000 + with Windows 2000 with 512 MB ram

server DELL 4600 Poweredge Dual XEON 2,8 GHZ with 6 GB MEM 800 GB RAID 10
running SQL server enterprise and Windows 2000 advanced server
with PAE and AWE extensions enabled

The program was written in VB.Net 2003 and used the ADO.NET command object
to execute the sql batches a string builder was used to create the SQL from
the file data

so how big are your sql batch files ?? if you run in such performance
problems ,,,,, as you do not even have to convert the SQL dialect

maybe if you could show some of the SQL and how you process it we might be
able to help you in this task

regards

Michel Posseth [MCP]

 

[Marina]

You should try using SQLDMO. This is a programmatic interface you can use to
run the script, I think it should be the same as using query analyzer in
terms of what it does.

 

[Herfried K. Wagner [MVP]]

Cor,

Just a sample that I copied from my HKW database.


\\\
Dim p As New Process
Dim pi As New ProcessStartInfo
pi.UseShellExecute = False
pi.RedirectStandardOutput = True
pi.Arguments = "www.google.com"
pi.WorkingDirectory = "C:\\windows\\system32"
'this for nt* computers

I'm not sure what the comment in the snippet above is referring to. I'd
replace the double backslashes by single backslashes. This should work with
Windows NT too.

In addition to your reply, it's worth a look if the application writes error
messages to the error stream ('RedirectStandardError'). This would make
analyzing the application's output easier.

 

[Cor Ligthert [MVP]]

Herfried,

I changed it in my HKW.

I could have better used another one, this was first a C# sample made from
another VBnet one that is in HKW too.

Thanks

Cor