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;"
      .Open
      WScript.Echo "Connection was established."
End With
rs.Open sql,conn
If conn.State = adStateOpen Then
      WScript.Echo "Connection was established."
Else
  WScript.Echo "No Connection ."
End If
rs.Close
Set rs = Nothing
conn.Close
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

0
 
yo_beeDirector of Information TechnologyAuthor Commented:
Now I am getting.

errooe2
0
 
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.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
0
 
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.
0
 
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
0
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.