Solved

SharePoint ODBC Connection w/ Linked Tables Possible?

Posted on 2010-11-29
12
1,871 Views
Last Modified: 2012-06-21
I can add an ODBC connection to my access db (C:\SW.mdb) on the share point server.  The problem is, no data is returned.  I keep getting an unspecified error.

I am running the query against linked tables to a Firebird database. There are no usernames or passwords set on the MDB.

Does ODBC/SharePoint support queries against these linked tables or do I need to search elsewhere?

By the way, I am doing this in SharePoint Designer 2007 and running WSS 3.0.

Also, this is my first question on here so, what would be the appropriate amount of points for a question like this?
0
Comment
Question by:cefranklin
  • 7
  • 4
12 Comments
 
LVL 42

Expert Comment

by:zephyr_hex
ID: 34235235
one possible issue is the location which you've set in your ODBC connection...
unless sharepoint is running on your computer.  most likely, it's looking for c:\ on the sharepoint server.

try using a network share.  and make sure you have an odbc connection configured on the sharepoint server.
http://office.microsoft.com/en-us/sharepoint-designer-help/add-a-database-as-a-data-source-HA010100908.aspx

also, check Event Viewer on your sharepoint server to see if it's logging an error.


p.s.  50 pts is a little low, but that's ok.  i'm not in it for the points.  just keep in mind that the higher the points, the more EE responses your question will attract.  so if you have a question that isn't getting answered, consider higher points.
0
 
LVL 2

Author Comment

by:cefranklin
ID: 34235376
Event viewer is nil. Network location didn't work, that's why I moved it to C:\.  I used SharePoint designer to add the ODBC connection to the site in question using a custom string. (I will get that posted up tomorrow, it's very simple).

My idea is this:

I have a form in InfoPath that, when submitted will go to the form library.  The form itself can only be submitted by a manager.  I want the manager to put in their employee number, last 4 and password, hence the db for checking against it by employee number.

The Employee table is in a Firebird database and the mdb just has a linked table to the one in Firebird.  Sucks, I know but, I can't get around it unless there is some hidden tool that will sync between FB and MS SQL.

I can add the data source in the infopath form itself but, relies on a connection to a network .mdb file.  
I am wanting the form to use a connection from the site instead of trying to access it with the network location for a few reasons.  

The main one being is, after I created it and opened it on another machine for testing, it's asking for all kinds of usernames and passwords for the network location etc.  That would be quite a lot for me to log into each machine, open the form and input that information and then have to do it again for every form I create.

So, yes, I want the SharePoint server to run the query on the local database, and I would just have to add the sharepoint db connection to the form. (In theory). The server is Windows 2003 R2, SP 2, with all updates installed.  Sharepoint has all it's updates and service packs as well.  At first, it couldn't find the db but, I had found another post by you detailing how to add the jet driver to sharepoint with stsadm.  That worked to be able to find the db, now, to get the information out...

My other thought is try to write a data control or web service that will connect to the FB database but, this is for future rainy days.

I also bumped this up to 500 points.  Thanks for the tip!
0
 
LVL 2

Author Comment

by:cefranklin
ID: 34240739
Here is my connection string:

Data Source=C:\SW.mdb;User Id=admin;Password=;Initial Catalog=root;Provider=Microsoft.Jet.OLEDB.4.0

Open in new window


Here is the query:

select * from V_EMPLOYEE where CODE=9

Open in new window

( I just put code = 9 so I would at least get one record back.  I have also tried to limit what is returned by changing * to CODE )

0
 
LVL 2

Author Comment

by:cefranklin
ID: 34240786
Oh, and here is the error after all that. I ran the same query from visual studio and it works, I ran the same query in the mdb and it works.

Could this just be a designer error?
odbc-error.JPG
0
 
LVL 42

Expert Comment

by:zephyr_hex
ID: 34252374
i think your problem may be the linked table in access.  
to test, why not create a test table in your access db and see if you're able to connect?
0
 
LVL 2

Author Comment

by:cefranklin
ID: 34258798
Heh, I tried this and the same result :(  Now I am really confused.  I created a table called Test which is not a linked table.  Created a columnt called TestId and TestData.  TestId is 1 and TestData is "Table data from database."  Same result.  When I connect, there are no tables shown so, I did the custom SQL option and typed in SELECT * FROM Test. Same error.

I tried moving the DB to Intepub wwwroot to see if the IUSR account had any bearing on accessing the DB and still a no go.

Upon further review, this scenario seems best suited to create a custom in browser form on the SharePoint site, not infopath.  I think this is great for lists, dropdowns etc and even creating a web service to provide the data back to the Infopath form.

Ultimately, I just need the Infopath form to make sure that the employee id is actually valid.  Seems like such a hard thing to do when you're not using MS products.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 42

Expert Comment

by:zephyr_hex
ID: 34259887
maybe use SQL instead of Access?  sharepoint runs on SQL... so just use the same SQL instance, but create a new database.

you'll need to create a sql user & login and associate them with this new db.
then use the connection wizard in sharepoint designer to connect to the SQL instance using those credentials.
0
 
LVL 2

Author Comment

by:cefranklin
ID: 34260042
That works fine but, the point of using Access was so that I can have the linked tables from Firebird, where all the data is stored.  If I make a new SQL db, I would need a way to sync over that information every 5 minutes or so throughout the work day.  I had seen there was a peice of software called DBSync for $80 but, will try the web service thing first.

Funny part is, I can download the Firebird SQL Client DLL for .NET 4.0 and query the DB using that and I have the ODBC drivers installed but, when I create a new connection, not all of the DB drivers are in the drop down.

If I get this written and working, I will provice instructions on here for anyone else looking to do the same thing in the future.  I have given up on the Access route.
0
 
LVL 28

Expert Comment

by:clayfox
ID: 34266845
Another way I have done this in the past.

If you can get an automated export from Firebird. Typically a csv dump of a scheduled custom report.
Then you can create a sql job that runs an import job which would process the csv on a schedule and insert it into a table.  Often then you need a second job to analyze the data dump and either insert or update your copy of the information.
Not ideal but I have had it work successfully and reliably in production.
0
 
LVL 42

Accepted Solution

by:
zephyr_hex earned 500 total points
ID: 34268309
have you tried a Linked Server from SQL to Firebird?

i used to do this with an ERP app that wasn't based in SQL.  i used SQL Linked Server to get the data from that ERP database, and created a SQL View based on that linked server.  then i pointed sharepoint to the SQL View.

i had two different mechanisms set up, depending on the size of the query.
the first one is as i described above, and the data showing in sharepoint was "real time" from the ERP database.  any time a user viewed the sharepoint page, that query would run and give them the real time ERP data

but in some cases, the query was big and i wanted to control how often that query ran on the ERP database.  if 15 users pulled up the page within 10 seconds of each other, i didn't want the query to run 15 times in 10 seconds.  so i used SQL Agent to schedule an import using that SQL Linked Server.  how this worked:  i created a SQL Table, and wrote a SQL query which pulled data from the SQL Linked Server to the table.  i then used SQL Agent to schedule that query to run based on some schedule.  like every 30 min, or every 8 hours... whatever i wanted.  i then pointed sharepoint data view to the SQL table.

configuring a linked server in SQL is not difficult if you use SSMS.  go to Server Objects and right click on Linked Servers.  select New Linked Server and follow the wizard.
this blog shows how to do it for a ODBC connection to another db:
http://sql-articles.com/blogs/creating-linked-server-to-mysql-from-sql-server/

your steps should be similar.  you'll need to know the syntax for querying a sql linked server... and examples are given in that blog.  remember that "mysql" is the name of the sql linked server in that example.  if he had named his sql linked server "bob", then he'd be using "bob" in the query.

the benefits of using this method over Access:
authentication is simpler.  you don't have to worry about the location of the db.  SQL is more efficient than Access.  you can schedule updates.


0
 
LVL 2

Assisted Solution

by:cefranklin
cefranklin earned 0 total points
ID: 34268592
Hey, that actually works.  Going to go ahead and figure out the query to copy everything over from all the tables that start with V_ to sql and set it on a schedule.  If that's the case, I don't think I would need to write the views?  Maybe just some T-SQL to act like the web service I wrote.

And by the way, the web service method works great too!

SWService.asmx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace TestSWWebService
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "https://intranetservices/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class SWService : System.Web.Services.WebService
    {

        [WebMethod]
        public bool IsEmployeeInSWInt(int EmployeeNumber)
        {
            if (EmployeeNumber <= 0)
                return false;
            SWDataServiceLib.SWLookupLib _swLookupLib = new SWDataServiceLib.SWLookupLib();
            return _swLookupLib.IsEmployeeInSW(EmployeeNumber);
        }

        [WebMethod]
        public bool IsEmployeeInSWString(string EmployeeNumber)
        {
            if (EmployeeNumber == String.Empty)
                return false;
            SWDataServiceLib.SWLookupLib _swLookupLib = new SWDataServiceLib.SWLookupLib();
            return _swLookupLib.IsEmployeeInSW(EmployeeNumber);
        }
    }
}

Open in new window


Here is my lookup dll
SWLookupLib.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FirebirdSql.Data.FirebirdClient; // Must be referenced

namespace SWDataServiceLib
{
    public class SWLookupLib
    {
        private FbConnectionStringBuilder _fbConnectionStringBuilder = new FbConnectionStringBuilder();
        private FbConnection _fbConnection = new FbConnection();

        private FbConnection Connect()
        {
            try
            {
                _fbConnectionStringBuilder.ConnectionLifeTime = 30;
                _fbConnectionStringBuilder.ConnectionTimeout = 30;
                _fbConnectionStringBuilder.UserID = "USERNAME";
                _fbConnectionStringBuilder.ServerType = FbServerType.Default;
                _fbConnectionStringBuilder.Password = "password";
                _fbConnectionStringBuilder.Database = @"C:\Path\To\Database.FDB";
                _fbConnectionStringBuilder.DataSource = "servername";
                _fbConnection = new FbConnection(_fbConnectionStringBuilder.ToString());
                _fbConnection.Open();
            }
            catch (Exception e)
            {
                throw e;
            }
            return _fbConnection;
        }

        private FbDataReader ExecuteSQL(string sql)
        {
            FbCommand fbCommand = new FbCommand(sql, Connect());
            return fbCommand.ExecuteReader();
        }

        public bool IsEmployeeInSW(string employeeNumber)
        {
            FbDataReader reader = ExecuteSQL("SELECT * FROM V_EMPLOYEE WHERE CODE = " + employeeNumber);
            reader.Read();
            try
            {
                if (reader.GetString(3) == employeeNumber)
                {
                    reader.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                // Need this empty block. reader.HasRows is always true as of 12/1/2010.  Getting a value
                // Will throw an error.  Just continue on and assume false
            }
            
            reader.Close();
            return false;
        }

        public bool IsEmployeeInSW(int employeeNumber)
        {
            FbDataReader reader = ExecuteSQL("SELECT * FROM V_EMPLOYEE WHERE CODE = " + employeeNumber);
            reader.Read();
            try
            {
                if (reader.GetInt32(3) == employeeNumber)
                {
                    reader.Close();
                    return true;
                }
            }
            catch (Exception e)
            {
                // Need this empty block. reader.HasRows is always true as of 12/1/2010.  Getting a value
                // Will throw an error.  Just continue on and assume false
            }

            reader.Close();
            return false;
        }
    }
}

Open in new window


You can get the .NET drivers from Firebird's website on sourceforge.  Add a reference by browsing to the dll.

Publish the service to a website and it's ready for use.  Also notice I haven't implimented any error checking or scrubbing. This is a rough draft and NOT production ready!
0
 
LVL 2

Author Closing Comment

by:cefranklin
ID: 34299408
The solution provided by zephyr was definitely what I was looking for but, the web service route worked too.  If you don't have coding ability, use zephyr's only, if you do, use either one or a combination fo the two.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I have just recently built a new SharePoint 2007 farm on a complete Windows 2008 R2 server platform and part of my standard build procedure is to implement a warm up routine, usually in the form of a script that is scheduled every morning to launch …
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

17 Experts available now in Live!

Get 1:1 Help Now