VBscript to run sql file from MS SQL

Question

VBscript to run sql file from MS SQL

jaryszek

Default port

Points: 1425
More actions
December 5, 2017 at 7:02 am

#330404

Hi,
I am digging arount internet and the solutions are not working for me.
I am looking to connection to MS SQL Server using Windows Authentication and VBscript.
and now in my vbscript I have code:

dim dbconn, ConnectionString ConnectionString = "Driver={SQL Server};Server=SERVERTEST;database=QlikView;IntegratedSecurity=yes;" Set dbconn = CreateObject("ADODB.Connection") dbconn.Open ConnectionString dbconn.Execute("C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert.sql")
My Sql file you can see within this topic:
Link
Problem is within connection string:

I was trying to set up Server as "NR90F56ZX\SERVERTEST" but problem is with sign "\".
Additionally i was trying to create connection string using Windows Forms Application in Visual Studio but i do not have reference options here at all (wtf?):
Please help,
Best Wishes,
Jacek

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

The name is the server isn't SERVERTEST. The server is NR90F56ZX and the Instance name is SERVERTEST. You'll need to pass the Server name as NR90F56ZX\SERVERTEST. Also ensure you have named pipes enabled on the server.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jaryszek Default port Points: 1425 More actions · Answer 2

Hi Thom A,

thank you.

The code below throws error:

dim dbconn, ConnectionString ConnectionString = "Driver={SQL Server};Server=NR90F56ZX\SERVERTEST;database=QlikView;IntegratedSecurity=yes;" Set dbconn = CreateObject("ADODB.Connection") dbconn.Open ConnectionString dbconn.Execute("C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert.sql")

Error is connected with "/" sign:

Please advice with this,
thank you in advance,
Best Wishes
Jacek

jaryszek Default port Points: 1425 More actions · Answer 3

Hi All,

I found the code whis is working and opening database!

dim dbconn, ConnectionString ConnectionString ="Provider=SQLOLEDB;Data Source=NR90F56ZX\SERVERTEST;" & _ "Initial Catalog=QlikView;" & _ "Integrated Security=SSPI;" Set dbconn = CreateObject("ADODB.Connection")

dbconn.Open ConnectionString

The last problem is how to run *.sql file.

I am trying with :
dbconn.Execute("C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert.sql")
but error is showing up:

Best Wishes,
Jacek

DimUser Ten Centuries Points: 1043 More actions · Answer 4

i think your problem is in the file path for the parameter in the Execute command... don't you need \\ for each \?

jaryszek Default port Points: 1425 More actions · Answer 5

Thank you DimPerson,

i tried with double slashes and with sometihng like that:

dbconn.Execute("C:\\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert.sql")

Best Wishes,
Jacek

Thom A SSC Guru Points: 99130 More actions · Answer 6

jaryszek - Wednesday, December 6, 2017 1:30 AM
I am trying with :
dbconn.Execute("C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert.sql")
but error is showing up:
Best Wishes,
Jacek

Execute is expecting a SQL string to be passed to it; you're passing a filepath and expecting SQL server to execute that (hence the error at C:, as C: isn't valid SQL syntax).

You'll need to load the text from your sql file into a variable, as then use Execute on that.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

You cant execute a file like that, you've opened a connection to the database there so you need to give it some T-SQL to run.

You will need to parse out the file to a string variable and execute the variable, create a stored procedure and execute the procedure or build the T-SQL string directly into the dbconn.execute phase.

jaryszek Default port Points: 1425 More actions · Answer 8

Thank you Guys !

I will work on problem today 🙂
And i will you let you know.

Thank you
Jacek

jaryszek Default port Points: 1425 More actions · Answer 9

Thank you for helping me!

I am almost there, my code in VBA (i am testing it via Excel and will move into VBscript later):

Sub ConnectSqlServer()

Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim sConnString, SqlTextFile, SqlStatement As String

' Create the connection string. sConnString = "Provider=SQLOLEDB;Data Source=NR90F56ZX\SERVERTEST;" & _ "Initial Catalog=QlikView;" & _ "Integrated Security=SSPI;" ' Create the Connection and Recordset objects. Set conn = New ADODB.Connection Set rs = New ADODB.Recordset ' Open the connection and execute. conn.Open sConnString conn.CommandTimeout = 900

SqlTextFile = "C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert.sql" Debug.Print SqlTextFile Dim hFile As Long hFile = FreeFile Open SqlTextFile For Input As hFile ''SqlStatement = Input$(LOF(hFile), 1) ''Close #hFile Debug.Print SqlStatement

Dim row As String Do Until EOF(hFile) Line Input #hFile, row SqlStatement = SqlStatement & row & vbNewLine Loop

SqlStatement = Replace(SqlStatement, "˙ţ", "") Debug.Print SqlStatement

Set rs = conn.Execute(SqlStatement)

End Sub

1 problem

In my SqlStatement i have strange sign in first row: "˙ţ"
What is that ?

2problem - more important:

Create View must be the first statement in a query batch.

In SQL Management studio this query is working without problems, in VBA there is an error.

Why is that ?
Thank you for any help,
Jacek

Thom A SSC Guru Points: 99130 More actions · Answer 10

GO is an SSMS/SQLCMD batch separator. It has no context for Execute in VBS, and so isn't used. You'll need to pass those scripts separately.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jaryszek Default port Points: 1425 More actions · Answer 11

Hi Guys,

my full code is working - maybe it will be usefull for others:

Sub ConnectSqlServer()

Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim sConnString, SqlTextFile, SqlStatement As String

' Create the connection string. sConnString = "Provider=SQLOLEDB;Data Source=NR90F56ZX\SERVERTEST;" & _ "Initial Catalog=QlikView;" & _ "Integrated Security=SSPI;" ' Create the Connection and Recordset objects. Set conn = New ADODB.Connection Set rs = New ADODB.Recordset ' Open the connection and execute. conn.Open sConnString conn.CommandTimeout = 900

SqlTextFile = "C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert.sql" 'Debug.Print SqlTextFile Dim hfile As Long hfile = FreeFile Open SqlTextFile For Input As hfile

SqlStatement = DoQueries(hfile) SqlStatement = Replace(SqlStatement, "?t", "") Debug.Print SqlStatement

Set rs = conn.Execute(SqlStatement)

Close hfile Set rs = Nothing SqlStatement = vbNullString

SqlTextFile = "C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert2.sql"

hfile = FreeFile Open SqlTextFile For Input As hfile SqlStatement = DoQueries(hfile) SqlStatement = Replace(SqlStatement, "?t", "")

Set rs = conn.Execute(SqlStatement)

Close hfile Set rs = Nothing SqlStatement = vbNullString

SqlTextFile = "C:\Users\ljar01\Documents\SQL Server Management Studio\BulkInsert3.sql" hfile = FreeFile Open SqlTextFile For Input As hfile SqlStatement = DoQueries(hfile) SqlStatement = Replace(SqlStatement, "?t", "")

Set rs = conn.Execute(SqlStatement)

conn.Close Close hfile Set rs = Nothing SqlStatement = vbNullString

''Debug.Print SqlStatement

End Sub

Function DoQueries(hfile)

Dim row As String Dim sString As String

Do Until EOF(hfile) Line Input #hfile, row sString = sString & row & vbNewLine Loop

DoQueries = sString

End Function

Thank you for helping me,
Best wishes,
Jacek