How to run TSQL from vb net 2003

Hi All
I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
In vb6 I called a function...
retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
which ran the TSQL script against OSQL. The script basically created the
Database in MSDE then imports a heap of tables from Access 2000.
Private Function ExecCmd(cmdline$)
'used for MSDE to launch osql
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret&
' Initialize the STARTUPINFO structure:
start.cb = Len(start)
' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
'Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
What is the preferred method of running a TSQL script from VB Net 2003 using
sqlclient class?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
> In vb6 I called a function...
> retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
> which ran the TSQL script against OSQL. The script basically created
> the Database in MSDE then imports a heap of tables from Access 2000.
> Private Function ExecCmd(cmdline$)
> 'used for MSDE to launch osql
> Dim proc As PROCESS_INFORMATION
> Dim start As STARTUPINFO
> Dim ret&
> ' Initialize the STARTUPINFO structure:
> start.cb = Len(start)
> ' Start the shelled application:
> ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
> NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
> 'Wait for the shelled application to finish:
> ret& = WaitForSingleObject(proc.hProcess, INFINITE)
> Call GetExitCodeProcess(proc.hProcess, ret&)
> Call CloseHandle(proc.hThread)
> Call CloseHandle(proc.hProcess)
> ExecCmd = ret&
> End Function
> What is the preferred method of running a TSQL script from VB Net
> 2003 using sqlclient class?
http://www.absistemi.it/permalink=tn169.ashx
this will use the very same feature with .Net..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to run multiple dependent SQL Jobs with OSQL

I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT

:ERROR
ECHO *** ERROR *** Check Log File

:EXIT
*** JOB COMPLETED ***

DaveCreate a new job that uses separate steps consisting of an sp_start_job to launch one of the other jobs. Each step will execute in turn, launching the appropriate job.

-PatP|||I already tried that with no success. The logic looks as follows:

sp_start_job 'DBA Test' -- Inserts one record into a table
go
sp_start_job 'DBA Test2' -- Waits 20 seconds and inserts a record
go
sp_start_job 'DBA Test3' -- Insert a record
go

All jobs execute immediately.

Dave|||That looks suspiciously like a SQL script instead of a job with a set of job steps to me.

-PatP|||Lightbulb.

We had a DR test this week and apparently I'm still experiencing the affects. Thanks for the help. That should do the trick.

Dave|||I spoke too soon. I added sp_start_job 'job name' to three different job steps and the same problem occurs. All three jobs are executed before any job completes. I believe the reason is due to sp_start_job. SQL Server successfully executes the command, which simply starts the job. It doesn't care whether or not the job it started has completed.

Any suggestions.

Dave