Solved

ADODB Connection string from (Word) VBA - "Multi-step OLE DB operation generated errors..."

Posted on 2013-11-21
2
1,060 Views
Last Modified: 2013-11-23
Developing a Word (2010) Add-in that communicates with a SQL Server (2008) database.  This has worked successfully for weeks using an internal server name, and this connection string:

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=QualitativeDB;Integrated Security=SSPI"
objConnection.Open

Open in new window

Trying now to transition that to be a public facing address.  With help from www.connectionstrings.com, I'm trying this string.  

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=00.000.000.00,1433;Network Library=DBMSSOCN=TCP/IP;Initial Catalog=QualitativeDB;Integrated Security=SSPI"
objConnection.Open

Open in new window

I know I'm close because I get the "Multi-step..." SQL error message above, rather than just timing out.  I've tried it with and without the =TCP/IP, but with seems to get me closer.

Others who have asked about this error appear to be attempting some illegal SQL operation, but I'm hitting the error on objConnection.open.

Any idea how to tweak my connection string to work?  Thanks.
0
Comment
Question by:versatilebb
2 Comments
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
Comment Utility
The first thing I would do is to take out the ,1433 and the Network Library sections of the connection string.

Change the connection string to this:

Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=SQLOLEDB.1;Data Source=00.000.000.00;Initial Catalog=QualitativeDB;Integrated Security=SSPI"
objConnection.Open 

Open in new window

0
 

Author Closing Comment

by:versatilebb
Comment Utility
We ended up having to go with a named DNS entry instead of the IP address, for various reasons.  but thanks for the suggestion.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

763 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