DickSwart6
asked on
Write SQL Script (*.SQL) to string
I want to write my script file to string and then execute it in VB - Create my "SQL Server DB"
Here is what I have :
Dim intFHandle As Integer
Dim strBatch As String
Open App.Path & "\SQL Scripts\Create_New_DB.sql"
strBatch = Input (intFHandle,Lof(intFHandle
Close #intFHandle
Here is what I have :
Dim intFHandle As Integer
Dim strBatch As String
Open App.Path & "\SQL Scripts\Create_New_DB.sql"
strBatch = Input (intFHandle,Lof(intFHandle
Close #intFHandle
ASKER
Hi emoreau,
My problem is to get the info into strBatch file - see my code above. I get a error - "bad file name or number"
My problem is to get the info into strBatch file - see my code above. I get a error - "bad file name or number"
DickSwart6,
I misread your question. Sorry.
You have 2 problems in your code.
1. intFHandle is not initialized with this line (before the Open line):
intFHandle = FreeFile
2. Parameters to the Input line are in the reverse order:
strBatch = Input(LOF(intFHandle), #intFHandle)
I misread your question. Sorry.
You have 2 problems in your code.
1. intFHandle is not initialized with this line (before the Open line):
intFHandle = FreeFile
2. Parameters to the Input line are in the reverse order:
strBatch = Input(LOF(intFHandle), #intFHandle)
ASKER
emoreau ,
Thanks it open the file now, but I get a error : "Run-time error '62' - Input past end of file".
At : strBatch = Input(LOF(intFHandle), #intFHandle)
Please help!
Thanks it open the file now, but I get a error : "Run-time error '62' - Input past end of file".
At : strBatch = Input(LOF(intFHandle), #intFHandle)
Please help!
DickSwart6,
How long is your file? Try with another (small file). It works for me.
How long is your file? Try with another (small file). It works for me.
Dim sql As String
Dim x As String
Dim i As Integer
i = FreeFile
Open "c:\download\rob.sql" For Input As i
Do Until EOF(i)
Line Input #i, x
sql = sql + x
Loop
Close i
Dim x As String
Dim i As Integer
i = FreeFile
Open "c:\download\rob.sql" For Input As i
Do Until EOF(i)
Line Input #i, x
sql = sql + x
Loop
Close i
ASKER
emoreau,
The file is 176KB, If I use a small file that just create stored prosedures it works. Now how will I get It to work with my large file?
deighton,
If I use this I get errors!
Please help!
The file is 176KB, If I use a small file that just create stored prosedures it works. Now how will I get It to work with my large file?
deighton,
If I use this I get errors!
Please help!
I would say to use the method proposed by deighton.
>>If I use this I get errors!
Which error?
>>If I use this I get errors!
Which error?
do you mean errors in reading the file, or errors in executing the sql.
How are you going to execute the sql exactly?
How are you going to execute the sql exactly?
You should alos try FSO: http://www.4guysfromrolla.com/webtech/faq/FileSystemObject/faq2.shtml
ASKER
Error in executing the sql - Run-time error -2147199229.
Here is what I Do.
Dim sql As String
Dim x As String
Dim i As Integer
i = FreeFile
Open App.path & "\SQL Scripts\Create_New_DB.sql"
Do Until EOF(i)
Line Input #i, x
sql = sql + x
Loop
Close i
sNewDBName = "C4_" & UcaseConverter(txtDBName.T
Cn.ConnectionString = "Provider=SQLOLEDB.1;Passw
Cn.Open
Cn.Execute "Create database " & sNewDBName
Cn.Close
'---------------Create Run The Script File----------------------
Set sqlServer = New SQLDMO.sqlServer
sqlServer.Connect Static_Server, "sa", Static_Password
sqlServer.Databases("" & sNewDBName & "").ExecuteImmediate Replace(sql, "GO", vbCrLf & "GO" & vbCrLf), SQLDMOExec_ContinueOnError
sqlServer.Disconnect
Set sqlServer = Nothing
Here is what I Do.
Dim sql As String
Dim x As String
Dim i As Integer
i = FreeFile
Open App.path & "\SQL Scripts\Create_New_DB.sql"
Do Until EOF(i)
Line Input #i, x
sql = sql + x
Loop
Close i
sNewDBName = "C4_" & UcaseConverter(txtDBName.T
Cn.ConnectionString = "Provider=SQLOLEDB.1;Passw
Cn.Open
Cn.Execute "Create database " & sNewDBName
Cn.Close
'---------------Create Run The Script File----------------------
Set sqlServer = New SQLDMO.sqlServer
sqlServer.Connect Static_Server, "sa", Static_Password
sqlServer.Databases("" & sNewDBName & "").ExecuteImmediate Replace(sql, "GO", vbCrLf & "GO" & vbCrLf), SQLDMOExec_ContinueOnError
sqlServer.Disconnect
Set sqlServer = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open a ADO connection and use the Execute method of the Connection object to run the content of your strBatch variable.
Cheers!