How to execute a t-sql file from VB6

zareer
zareer used Ask the Experts™
on
Dear All,
I wanna know how to execute a normal t-sql file which we run on the sql query analyzer (*.sql or *.txt) form the VB6 front end...

Please give me solutions
thank you
Zareer
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Read the contents of the file into a SQL variable and execute it.
Head of Software Services
Commented:
The following solution works using the SQL-DMO library, add a reference to Microsoft SQL-DMO to your project and away you go. You need to read the contents of the .Sql file into strBatch first. You can also use a specific database by changing the executeimmediate line to : sqlServer.Databases("My Database").ExecuteImmediate.....

Dim sqlServer As SQLDMO.sqlServer
Set sqlServer = New SQLDMO.sqlServer
sqlServer.Connect "MySQLServer"
sqlServer.ExecuteImmediate Replace(strBatch,"GO",vbCRLF & "GO" & vbCRLF), SQLDMOExec_ContinueOnError
sqlServer.DisConnect
Set sqlServer = Nothing

This assumes that strBatch contains the complete text from the .SQL file.
TimCotteeHead of Software Services

Commented:
This method is the most effective as using ADO and DAO both have their problems when trying to do this sort of thing

Author

Commented:
Thanx TimCottee it works fine as u said and thanx for all who replied for this question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial