Solved

How to connect to MSSQL using ADODB

Posted on 2014-03-21
6
363 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
Comment Utility
"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 21

Author Comment

by:yo_bee
Comment Utility
Now I am getting.

errooe2
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
Comment Utility
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 21

Author Closing Comment

by:yo_bee
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now