How to create a linked server to MYOB in SQL Server Management Studio?

0
votes

I need to extract data from MYOB and wish to do it via a linked server in Microsoft SQL Server Management Studio.

nb: The MYOB data file resides on a network share.

Provider String:

Driver={MYOAU1001};Database=M:\Premier19\xxx.MYO;TYPE=MYOB;UID=Administrator;PWD=xxx;KEY=;ACCESS_TYPE=READ_WRITE;DRIVER_COMPLETION=DRIVER_NOPROMPT;SQL_LOGIN_TIMEOUT=30;

No success so far. I get the following error:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYOB".
OLE DB provider "MSDASQL" for linked server "MYOB" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MYOB" returned message "[MYOB ODBC] - Company file does not exist.". (Microsoft SQL Server, Error: 7303)

2
sql-servermyob

2 Answers

0
votes

Unfortunately I'm not too sure on what a linked server connection is. However the MYOB ODBC driver is not a real SQL connection. It's just an interface that uses the MYOB .exe and offers something that looks a little like SQL but it actually isn't an SQL interface.

If you could provide a bit more info, like your connection settings in the MYOB ODBC interface?

0
votes

try constructing it something like this sConnString = "Driver={" & MYOBDriver & "};" & _ "Database=" & datafilename & ";" & _ "TYPE=MYOB;UID=" & MYOBUser & ";PWD=" & MYOBPassword & ";" & _ "KEY=" & keyfilename & ";ACCESS_TYPE=READ_WRITE;" & _ "HOST_EXE_PATH=" & MYOBExepath & ";" & _ "DRIVER_COMPLETION=DRIVER_PROMPT;SQL_LOGIN_TIMEOUT=90;" & _ "SUPPRESS_WARNINGS=TRUE;"

Also use OPENROWSET in your query

note that your error message points out that you havent included the company file name