Solved

How to connect to MSSQL using ADODB

Posted on 2014-03-21
6
376 Views
Last Modified: 2014-03-25
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
0
Comment
Question by:yo_bee
6 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39944923
"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
 
LVL 22

Author Comment

by:yo_bee
ID: 39944939
Now I am getting.

errooe2
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 39944948
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39945046
>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39946691
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
 
LVL 22

Author Closing Comment

by:yo_bee
ID: 39954871
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question