How to check if a sql script is run successfully using isqlw in a VB6 program

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to check if a sql script is run successfully using isqlw in a VB6 program

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-05 : 07:52:43

Ming writes "Hi expert

I need to use shell command to run isqlw for sql scripts in a VB application program. Is it anyway to check if the sql is run successfully?"

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-05 : 10:26:44

My first reaction is...why not create stored procedures?

Then I realize, "it's gotta be the dynamic sql thingee"

Then my response is No.

Even if the SQL fails with in the shell command (or master..xp_cmdshell) call fails, the "program" which is the shell still completes successfully.

You need to add business logic to your process so you have something to check.

For example, I call a bcp using master..xp_cmdshell...I'll then check header and trailer information in a work table to make sure all of the rows are loaded correctly.

If you could be more descriptive, I'm sure the guys and gals here can come up with a unique solution for you

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-05 : 22:35:08

And you probably can't run isqlw from a VB app, since this is the Windows version of isql (Query Analyzer). You'd want to use osql or isql instead. Both utilities have options to set an error threshhold to display error messages, change the DOS ERRORLEVEL setting, and also can send custom output back to the command line. Books Online details all of these settings.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-06 : 20:07:04

why not use dmo or sqlns?

but if you insist there is also a nice utility out there.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25861

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-06 : 20:19:49

You'll want to run osql if it is SQL 2000 we're talking about.

An example of how to run osql (which can be run from xp_cmdshell through your VB application):

osql -Sserver1 -Uuser1 -Ppassword -iC:\Deploy\SQLScript.sql -oC:\Temp\OutputFile.txt

OR

osql -Sserver1 -Uuser1 -Ppassword -iC:\Deploy\SQLScript.sql >C:\Temp\OutputFile.txt

I have always used the second method for some reason. Just go to a cmd window and type "osql /?". This will show you how to run it. Also see BOL as robvolk mentioned.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources