[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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" For Input As #intFHandle
strBatch = Input (intFHandle,Lof(intFHandle))
Close #intFHandle
0
DickSwart6
Asked:
DickSwart6
  • 6
  • 4
  • 2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Hi DickSwart6,

Open a ADO connection and use the Execute method of the Connection object to run the content of your strBatch variable.

Cheers!
0
 
DickSwart6Author Commented:
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"
0
 
Éric MoreauSenior .Net ConsultantCommented:
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)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DickSwart6Author Commented:
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!
0
 
Éric MoreauSenior .Net ConsultantCommented:
DickSwart6,

How long is your file? Try with another (small file). It works for me.
0
 
deightonCommented:
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
0
 
DickSwart6Author Commented:
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!
0
 
Éric MoreauSenior .Net ConsultantCommented:
I would say to use the method proposed by deighton.

>>If I use this I get errors!

Which error?
0
 
deightonCommented:
do you mean errors in reading the file, or errors in executing the sql.

How are you going to execute the sql exactly?
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
DickSwart6Author Commented:
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" For Input As i
Do Until EOF(i)

    Line Input #i, x
    sql = sql + x
   
Loop

Close i
     
        sNewDBName = "C4_" & UcaseConverter(txtDBName.Text)
       
        Cn.ConnectionString = "Provider=SQLOLEDB.1;Password=" & Static_Password & ";Persist Security Info=True;User ID=sa;Data Source=" & Static_Server
        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

0
 
Éric MoreauSenior .Net ConsultantCommented:
>>Error in executing the sql

That was not very clear. I thought it was still reading the file!

>>sqlServer.Databases("" & sNewDBName & "").ExecuteImmediate Replace(sql, "GO", vbCrLf & "GO" & vbCrLf), SQLDMOExec_ContinueOnError

Not everything can be run into a single pass into a SQL query. If you have a GO, you might be better splitting you string at this point and run it in small batches.

Open your query into Query Analyzer, remove all the GO and try to run it. You should get an error too.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now