Connecting to Simply from SQL 2005

Look up the BOL for "Linked server".
You probably need to create the Registry entry described to specify the path to the mdw (sdw) file.

/gustav

Thanks Gustav,
I'm not sure which registry setting you mean. I've already set DisallowAdHocAccess to 0 for MSDASQL and for Microsoft.Jet.Oledb.4.0
Can you paste the link in here.

My DSN that works with Excel looks like this;
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=sysadmin
UserCommitSync=Yes
Threads=3
SystemDB=C:\Program Files\Winsim\Data\SOS.SDW
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DBQ=C:\Program Files\Winsim\Data\SOS.MDB

For now I'm doing an OPENDATASOURCE as it's quicker to change and test than setting an actual Linked Server up.
I'm doing the following query;
SELECT * FROM OPENDATASOURCE('MSDASQL', 'FILEDSN=c:\SimplySOS.dsn;Uid=sysadmin;)...DATADICT

But I am getting;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "(null)".

DATADICT shows as a valid table in Excel.

I don't understand why this is such a hard thing to get going! Mainly because there is so little clear, concise info on the subject, I'd guess!

Hi Cactus_Data,
Thanks for that, it didn't work for me though.

However, I have got it to work but only using OpenQuery. This in itself should be good enough. But it still raises a question.

I have a File DSN which works with Excel OK.

Then the SQL to create the Linked Server is;
    EXEC master.dbo.sp_addlinkedserver
              @server = N'SOS', @srvproduct=N'SOS',
              @provider=N'MSDASQL', @provstr=N'FILEDSN=C:\Simply SOS.dsn'

    EXEC sp_addlinkedsrvlogin 'SOS', FALSE, NULL, 'sysadmin', Null

Then the following query works;
    Select * From OpenQuery(SOS, 'Select * From tAccount')

What I can't understand is that, in theory, this query should also work;
    Select * From SOS...tAccount

But I get;
    Msg 7313, Level 16, State 1, Line 1
    An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "SOS".

Access doesn't have a catalog or schema, hence the ... in the 4 part table identifier.
Does anyone know why this doesn't work and whether it's possible in this form?

This will work:

   Select * From SOS...tAccount

If it doesn't, something else is wrong. Could be the provider:

   @provider=N'MSDASQL'

That should be the Microsoft Jet 4.0 OLE DB Provider

/gustav

I used MSDASQL because I had got Excel to work with a DSN, which is ODBC.
MSDASQL is the OLEDB driver for ODBC - and the only way SQL 2005 can use ODBC connectivity.

Obviously, I'd like to use the Jet OLEDB driver to avoid the extra layer, but I can't get the provider strings right.
It's complicated by the fact that Simply Accounting uses a Workgroup file too (whatever that is!).

My expertise lies in SQL 2005, not Access.

But, I have a UDL file that links to the database using the Jet OLEDB driver and that works OK with Excel too.
But in SQL, defined as follows;
      EXEC master.dbo.sp_addlinkedserver
            @server = N'SADataJET',
            @srvproduct=N'SimplyAccounting Jet',
            @provider=N'Microsoft.Jet.OLEDB.4.0',
            @provstr=N'FILE NAME=C:\SAData.udl'

      EXEC sp_addlinkedsrvlogin 'SADataJET', FALSE, NULL, 'SQLDB', '******'

Then if I use the OPENQUERY or 4-part qualifier method to do a simple SELECT I get;
      OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
      Msg 7399, Level 16, State 1, Line 1
      The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET" reported an error. Authentication failed.
      Msg 7303, Level 16, State 1, Line 1
      Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET".

So, I then applied your registry change (setting it to C:\Program Files\WinSim\Data\SOS.sdw) which then changes the error message to;
      OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET" returned message "Could not find installable ISAM.".
      Msg 7303, Level 16, State 1, Line 1
      Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET".

My suspicion is that something is wrong in my sp_addlinkedserver command rather than any ISAM file issue because it works fine with Excel.

But this:
  C:\SAData.udl

doesn't look right. Should be the database file (..\sos.sdb?) according to BOL:

sp_addlinkedserver 'Nwind', 'Access', 'Microsoft.Jet.OLEDB.4.0',
   'c:\mydata\Nwind.mdb'

/gustav

Hi Gustav,
If I do that I get;
    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET" returned message "Could not find installable ISAM.".
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET".

That's with the registry change, and without the registry change, I get;
    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET" reported an error. Authentication failed.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "SADataJET".

The full connection string as copied from ConnectionStrings.com should be something like;
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:System Database=system.mdw;User ID=myUsername;Password=myPassword;

The UDL file is just the OLEDB equivalent of ODBC's File DSN. It's just a wrapper for a connection string, very much like a DSN is a wrapper, and if double clicked you get a handy wizard to set the string up for you.

And as I say, Excel worked fine through the udl.

I've tried a udl setup with a simpler access database and it doesn't work. And I used the database file name directly too and that didn't work either.

If you're out of ideas to, I'll stick with OLEDB for ODBC and OpenQuery!

Strange. Still I'm sure the UDL has nothing to do here.

Try to link the database via the GUI of SQL Manager (Server Objects, Linked Servers, Right click: New Linked Server).
When I do that, only "Product name" and "Data source" (the fuld path to the mdb) are filled out. A Username and a Password are only passed by the Registry entry and the SP sp_addlinkedsrvlogin.

/gustav

I just tried that and it works on my basic db (but the registry key has to be set back to system.mdw)
but it doesn't work with the SOS.sdw db.

And I can't actually open it in Access anyway, it harps on about permissions. Even though I have all the Active Directory permissions possible on the 2 files.

I think we'll have to put it down to the way SQL sets up the linked server for Jet and how SimplyAccounting have set the databases up!

Annoying when it works so well in Excel.

Thanks for your time and effort.

Cheers
Chris

Why not address this to SimplyAccounting support? If it is a question about security adjustments they will know what to do.

By the way, can you copy and rename the sdw file to a local mdb file and open it with Access? That may give you some information about accounts etc.

/gustav

Simply no longer support 2006! Talk about a short shelf life.
Not only that, after being on hold for over an hour and a half across 3 phone calls to about 5 different people, I can safely say they "don't know their ODBC from their elbow!" ;-)

I did try, initially renaming the sdb/sdw to mdb/mdw and still Access said I didn't have permission.
I'll leave it for now, OPENQUERY will do the job!!

This is a stop gap solution anyway. The customer will likely be looking at a new accounting system next year.

OK, that pretty much marks the end of the road.
Thanks for the feedback.

/gustav

Thanks for your efforts Gustav, much appreciated.

Chris, you may be able to give this guy a hint:

https://www.experts-exchange.com/Database/Miscellaneous/Q__23331783.html

/gustav

Thanks Gustav, will do.