Solved

How to connect to MSSQL using ADODB

Posted on 2014-03-21
6
386 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 23

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 66

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 23

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

627 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