1

I am writing a VBScript that connects to a Sybase database, reads some data from a table and stores it in variables, then connects to a MS SQL server and inserts data into tables with the variable data that was stored earlier.

I have found a couple of existing scripts to get me going here and here.

I understand these just fine. The only problem is the Database connection string itself. Here is the connection string that is currently being used. (I have used correct values in the actual script but changed some here for security purposes)

Provider=Sybase.ASEOLEDBProvider.2;Driver={SYBSE ASE ODBC Driver};Data Source=MyDataSource;Port=1234;Database=MyDatabase;uid=MyUser;pwd=MyPassword

But it is causing the error: Provider cannot be found. It may not be properly installed.

Here's some system info that you may need to determine the cause of the issue:

  • Script will be run on 64-bit Windows 7 machine
  • I have successfully connected to this Sybase database using MS Access
  • I have installed the Sybase ASE ODBC Driver (32-bit) version 03.50.0010
  • I have confirmed that it is installed and working by going to C:\Windows\SysWOW64\odbcad32.exe and located the drivers there
  • The database is a System DSN (not a User DSN)
  • Sybase Open Client version 10.0.4

The issue seems to be with the Provider= part of the connection string. It's worth noting here that as soon as I take away the Provider= part, I get this error: The specified DSN contains an architecture mismatch between the Driver and Application. So this leads me to believe that there is a 32-bit and 64-bit mismatch here. I've researched this and it turns out the way to fix it is to add a Provider= to your connection string to somehow specify which one to use. Here is a list of the Providers I've tried in the connection string:

  • Sybase ASE OLE DB Provider
  • Sybase.ASEOLEDBProvider
  • ASEOLEDB
  • ASEOLEDB.1
  • Sybase.ASEOLEDBProvider.2
  • Sybase
  • Advantage OLE DB Provider

All of these throw the same error. What am I missing here?

EDIT: As per your suggestions I have made some revisions to the script and have been able to push past the initial error, but now I get a completely different error. Here's the script as it is now

Dim connStr, objConn

DataSource = "ICCM_PREVIEW"
ServerIP = "1.2.3.4"
Port = "1234" 
DBuser = "myUser" 
DBpwd = "myPassword" 
DBName = "myDatabase" 
Driver = "SYBASE ASE ODBC Driver"

connStr = ""
connStr = connStr &"Driver="& Driver &";"
connStr = connStr &"Data Source="& DataSource &";"
connStr = connStr &"Srvr="& ServerIP &","& Port &";"
connStr = connStr &"Database="& DBName &";"
connStr = connStr &"uid="& DBuser &";"
connStr = connStr &"pwd="& DBpwd &";"

Wscript.Echo connStr 

'Define object type
Set objConn = CreateObject("ADODB.Connection")

'Open Connection
objConn.open connStr

Here is the error message I get now, but I have no idea how to get past this one:

Microsoft OLE DB Provider for ODBC Drivers: [SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER:Trying to connect to server
Armin
  • 1,736
  • 4
  • 19
  • 35
  • 1
    When you connected to the Sybase database from Access, what driver were you using? Also, it looks like your using an ODBC driver and specifying an OLE DB provider. Have you tried getting an OLE DB driver? – TDavis Apr 15 '15 at 19:44

1 Answers1

2

You've installed a 32 bit ODBC driver for sybase, but you intend to run it on a 64 bit machine. You either need to install the 64 bit driver as well, or you need to run your vbscript in 32 bit mode.

You can read how to run a vbscript in 32 bit mode here: How do I run a VBScript in 32-bit mode on a 64-bit machine?

Editing to add: If you try running your vbscript in 32 bit mode, make sure to leave that provider= bit off of your connection string.

Also adding: Related to that provider= bit. You are using an ODBC driver, but the provider you are trying to add into the connection string is an OLEDB provider. I'm pretty certain you can't mix and match between OLEDB and ODBC in the same connection string as they are different technologies. One other suggestion would be to install the 64 bit OLEDB driver for sybase and use an OLEDB connection string using that driver.

In the end, there are a few options, but it's important to match architectures between your driver and your client. It's also important to match driver software to the client (ODBC/OLEDB).

Community
  • 1
  • 1
JNevill
  • 46,980
  • 4
  • 38
  • 63