How to connect to MSSQL using ADODB

I have a test script to attempt to establish a connection with a DB in my MSSQL server
Dim conn , rs, sql, ConnString
sql = "SELECT * FROM sessiontbl"
Set rs = CreateObject("ADODB.Recordset")
Set conn = CreateObject("ADODB.Connection")
With conn
      .Provider = "SQLOLEDB"
      .Mode = adModeReadWrite
      .ConnectionString ="Data Source=SANDBOX;Trusted_Connection=Yes;InitialCatalog=Inventory;"
      WScript.Echo "Connection was established."
End With
rs.Open sql,conn
If conn.State = adStateOpen Then
      WScript.Echo "Connection was established."
  WScript.Echo "No Connection ."
End If
Set rs = Nothing
Set conn = Nothing

Open in new window

I error at Line 9 char 7
Invalid connection string attribute
Code 80004005
Source Microsoft OLE DB Provider For SQL Server
LVL 25
yo_beeDirector of Information TechnologyAsked:
Who is Participating?
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
"InitialCatalog" needs a space in it. So it should say:
.ConnectionString ="Data Source=SANDBOX;Trusted_Connection=Yes;Initial Catalog=Inventory;"

Open in new window

yo_beeDirector of Information TechnologyAuthor Commented:
Now I am getting.

Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
That indicates that it cannot find a SQL Server called "SANDBOX". What is the SQL Server actually called and is it on the same server that your code is running from?

If you have installed your SQL Server as a named instance then you need to include the instance name in the connection string. Also double check that the SQL Server is listening on port 1433 (the default) otherwise you will also need to change the connection string to specify the correct port number.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Dim conn , rs, sql, ConnString
It is not best practice to declare variables without a data type, as it will will create them in memory as an Object, which may or may not be what you intend, which could open the door for all kinds of runtime errors.  Far better to explicitly declare them with what they need to be.

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim sql as String, ConnString as String
Anthony PerkinsCommented:
It is not best practice to declare variables without a data type
The author does not have a choice they are using VBScript.  All variables are variants.
yo_beeDirector of Information TechnologyAuthor Commented:
I made a couple of changes as well as the two suggestions

Here is my string
strConnect = "Provider = SQLOLEDB.1;Data Source=SANDBOXHOST;Initial Catalog=Inventory;User ID='sa';Password="********'"

I did have an incorrect Source and put the space Initial Catalog

Made a change to the provider
From: Provider = SQLOLEDB
To:Provider = SQLOLEDB.1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.