MAS200 ODBC connection string to Sharepoint

[PriceDon]

I am trying to use Microsoft’s SharePoint Designer to create a database connection to our MAS200 database. I seem to be having problems creating the correct connection string.

The SharePoint web site is on a different server other than MAS200. On the SharePoint server I have an ODBC connection defended and it seems to be working, when I do a test of that connection it tells me that it sees all the tables. I have tried a wide verity of connection strings in SharePoint Designer, but have had no luck.

DSN=SOTAMAS90; UID=XXX; PWD=password; Directory=\\srvpra02\MAS200\Version4\MAS90; Company=P96; SERVER=NotTheServer

The error I receive in SharePoint Designer when I try and create the database connection is: Server Error: An error occurred while retrieving the list of databases. The server for the data source returned a non-specific error when trying to execute you query. Check the format and content of your query and try again/

 

[BigLouie]

Have you tried:

DSN=SOTAMAS90; UID=XXX|P96; PWD=password; Directory=\\srvpra02\MAS200\Version4\MAS90;SERVER=NotTheServer

 

[PriceDon]

Thank you BigLouie, I just tried it and I get the same results.

MAS200 is version 4.1, does this mater?

What is the purpose of SERVER=NotTheServer? Should this be the name of the MAS200 server?

 

[BigLouie]

You have to remember that MAS has several virtual tables. Tables that do not exist until a report is run. Some software such as UPS World Ship attempts to open a list of tables and since the table does not exist an error is returned. This MIGHT be your case. Are you trying to open just certain tables are ALL MAS tables. Can you post your query.

 

[PriceDon]

I really only need read access to maybe 3 tables.

The steps that I am taking are:

On our SharePoint web server I have installed v.4.10.1001 ProvideX ODBC drivers. I then created a System DSN pointing to the MAS200 server. This tested OK

I have SharePoint Designer installed on my desktop PC, I open the SharePoint web site and then navigate to the Data Source Library where I have the ability to add a connection. The wizard walks me through creating a custom connection string. The first window in the wizard prompts me for a Provider Name, and it gives me a drop down list of provider, I choose Microsoft .NET Framwork Data Provider for ODBC. There is a box below this for the Connection String. This is where I have been entering the connection string of DSN=SOTAMAS90; UID=XXX; PWD=password; Directory=\\srvpra02\MAS200\Version4\MAS90; Company=P96; SERVER=NotTheServer
My assumption is once I have this connection we could use SharePoint to query the data

 

[BigLouie]

Is the SOTAMAS90 one of your provider options? If so I would select that. I would have also installed the work station and run it once and then used some other application such as Excel, Crytal or Access to connect to the database to make sure things work before moving forward with SharePoint.

 

[Kruken]

Ive had quite alot of difficulty getting the Providex ODBC driver to consistently give the expected results when using it with different applications ... the solution i have been using for the past few months is the following (came up with this to be able to interface MAS with a 64bit instance of SQL 2005)

1. Install SQL Express 2005 On server where MAS is located

2. Setup MAS as a "Linked Server"

3(A). Direct all your MAS targetted queries to the newly setup SQL Express server using OPENQUERY ie. SELECT * FROM OPENQUERY(MAS371,'SELECT * FROM IM1_InventoryMasterFile')

3(B). To make things even simpler you can setup a DB in SQL Express and make a view of each MAS table or only the MAS tables you intend on using with outside applications. You will need to use the OPENQUERY when creating your views but after that you can simply query the views of your new database.

Hope this helps.



Steven Yvinec-Kruyk
Ubisoft Canada

http://www.ubi.com

 

[PriceDon]

Thank you Steve. I now have SQL Express installed and I am trying to make the Linked Server, it asks me for a Provider, Product Name, Data Source, Provider String, Location and Catalog. For the provider do I choose Microsoft OLE DB Provider for ODBC Driver? I am also unclear what is required for Product Name, Data Source, Provider String, Location and Catalog.

Thank You
Don Price

 

[Kruken]

Hi Pricedon,

Sorry about the missing steps ... it has been quite some time since i set this up ...

Let me try this again

Step 1 - Windows ODBC Setup:
On the server where you have SQL Express and normally also MAS , Setup a silent ODBC connection using the MAS/Providex ODBC driver to be used for the link server (I called mine LINKEDSERVER)

In the logon tab of the MAS/Providex ODBC driver properties
when creating a "SILENT" ODBC connection include the desired company, the login to be used as well as the password

In the Options Tab put a check in the following boxes
-Dirty Read
-Burst Mode
-Silent Mode

Step 2 - SQL Express Setup:
-Create a new Linked Server (The name you give the linked server will be the name you must use in your queries)
-Use "Microsoft OLE DB Provider for ODBC Drivers" as your provider
-Product name is not important ... (I Put MAS371)
-for Data Source use the name of the ODBC connection you just created (in my case "LINKEDSERVER")

(I left Provider String,Location, and Catalog blank)

In the server options i set the following
-Collation compatible : False
-Data Access: True
-RPC: True
-RPC Out: True
-Use Remote Collation: True
-Collation Name: Blank
-Connection Timeout: 0
-Query Timeout: 0


Step 3 - Querying MAS:

Now you can simply use an OPENQUERY to your new SQL Express server for all queries directed at MAS.

SELECT * FROM OPENQUERY(LINKEDSERVERNAME,'SELECT * FROM ARO_InvHistoryDetail)

(basically i start all my queries with the;

SELECT * FROM OPENQUERY(LINKEDSERVERNAME,'**************************')

and place my MAS query where i put the asterix.

What has been very useful for me was to setup views on all the tables i frequently use.The code for the view contains the OPENQUERY statement as shown above , however once the view is setup I can query the view as if it were a table stored in my SQL Express DB , ie. SELECT * FROM NAMEOFVIEW

Bottom line is my SQL Express server has one DB that doesn't really contain any tables , just views that use the linked server ... it's great when working with other applications because u do not need to install the MAS/Providex ODBC driver everywhere , also saved my life when we invested in a 64bit SQL 2005 Cluster ,before i realised that the 32bit ODBC Providex driver was simply not going to work.

....

Hope this helps , Let me know if you need any more info.

Cheers!





Steven Yvinec-Kruyk
Ubisoft Canada

http://www.ubi.com