• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

executing a .sql file from a VB exe

Can I execute a .sql file from a VB app, the documentation with VB only describes how to do this with J++ and C++, if this is possible, how can it be achieved?
0
louise_8
Asked:
louise_8
1 Solution
 
AndrewDevCommented:
Try using the sp_executesql Extended Stored Procedures which are part of SQLServer.

Create an ADO connection to your database and call the stored procedure with your .sql file as a parameter

eg.
myADOConnection.exectue sp_executesql mysqlfile.sql

Regards
Andrew
0
 
Jeremy_DCommented:
sp_executesql will only accept a unicode string as input, not a filename. Besides that, your batch might well be more then 4000 characters long, so sp_executesql won't work.

I would go for a Shell call to osql.exe, something like:

Shell "c:\mssql7\binn\osql.exe -S YourServer -d YourDatabase -U YourUserID -P YourPassword -i c:\mssql7\MyScripts\MyScript.sql"

Look up osql in BOL for the exact command-line switches for osql, there are a lot of them. You can also use isql.exe or isqlw.exe, depending on what's best for your environment.
0
 
AndrewDevCommented:
Well done Jeremy
Shell was my first choice but I couldn't get the syntax right - thanks for the help
0
 
TimCotteeCommented:
louise_8: 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.
0
 
louise_8Author Commented:
Hi TimCottee

Thanks for your comment, however Im not familar with SQLDMO, I pasted your code in and am getting a connection error
Microsoft[ODBC SQL DRIVER][DBNETLIB] server does not exist or access denied
Microsoft[ODBC SQL DRIVER][DBNETLIB] ConnectionOpen Connect()

I had an odbc connection opened in the previous form but I close it before this one opens, so I cant figure out the error, my db
incidentally is located on a server but opens fine in the other form

is sqlServer.Connect "LMSDB" sufficient to open the LMSDB database on the server, or do I need to add more parameters to the string?

any help would be greatly appreciated

Louise

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now