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 Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgrade
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w
/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt
',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgra
de
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com