none
SharePoint Designer 2010, Error While trying to connect to an external database.

    Question

  • HI,

    I have an issue when connecting SharePoint designer 2010 to an External data source. I am having hosted SharePoint 2010 plan from a hosting company.

    I ‘m using a machine with windows server 2008 installed and all development tools installed in it like Visio 2010, office 2010, visual studio2010, and SharePoint designer 2010. In this machine I have SQL server 2008 enterprise installed with default instance.

    I have three databases, one that I have created directly using SQL management studio, the other one is a sample that I did download it from internet (northwind sample),The last one, I got it from my ERP system that I have, which is the main one i want to use in my SharePoint project.

    In SharePoint Designer when I want to connect to my external databases: Site Objects-> data sources in the ribbon by clicking on database connection, Data source properties will get opened, after clicking configure database connection button, configure database connection dialog will open to fill the required information to get connected to the required external database, I have entered the IP address in the server name,  I choose In provider dropdown list Microsoft .Net framework data provider for SQL server, and then I enter the user name and password.

    After that, I am able to connect to two databases out of three I am having; I am able to connect to the one I created locally and with the sample northwind database, and my issue raise when trying to connect the last one that I fetched from anther system.

    I got this database from another SQL server (SQL server 2000) that is installed in another machine (database that hold all the data of the ERP System), and then I attached it to the SQL server 2008 that I am using now.

    When i try to connect and wait for some time, an dialog appear that says, the server could not complete your request. For more specific information, click the details button, when i click on details there will be a message that says object moved object moved to here, and if click ok, an error will fire, that says Server Error: An Error occurred while retrieving the list of tables from the Database_name Database: object moved object moved to here.

    I totally don’t know what the cause of this problem is.

    I really need your assistance.

    Thank you.

    Sunday, July 01, 2012 11:36 AM

Answers

All replies

  • Hi,

    Thank you for your question. I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience. Thank you for your understanding and support.

    Thanks,

    Entan Ming


    Entan Ming

    TechNet Community Support

    Monday, July 02, 2012 9:05 AM
    Moderator
  • Hi Osman,

    Thank you for the forum posting. I have the following questions that would help us troubleshoot this further if you could please provide us with the answers:

    1. How was the database move done? (example backup and restore)

    2. Size of the database

    3. Have we tried to create an external content type based on this instead of just a datasource?

    4. What happens if you try to run select statements against it in SQL?

    Gregg


    MSFT

    Monday, July 02, 2012 3:15 PM
    Moderator
  • Hi,

    The following are the answers,

    1- I moved the database by attaching it using SQL Server Management Studio.

    2- The database size is 5,604,608 KB.

    3-I have created ECT using this database.

    4- I did run select statement using Sql Server management studio, and i could get all the records.

    In addition,

    let me tell more about this database,

    I have tired it from another server Machine that have sql server 2000 SP 4.

    When I try to connect to this server, I can access it and I can view all the tables.

    Thank you for the reply.

    Regards,

    Osman Teyeb.

     

    Monday, July 02, 2012 7:29 PM
  • Hi Osman,

    If we’ve created an ECT to it, why not use that instead of trying to create a separate datasource? You can build a list to that ECT then put a list view on the page you want. All this gets rid of creating a completely separate datasource.

    Gregg


    MSFT

    Monday, July 02, 2012 9:24 PM
    Moderator
  • Hi,

    Yes, but i was trying by following a video and this step was the essential step to get the work done.

    One more thing , i have notice another issue when creating ECT from SQL server 2000.

    When trying to expand the tables,  i got error. The error details says"Skipping the following table because it is not configured for use: table_name

    Line 1: Incorrect syntax near '(' ". (Note: The creation of ECT from SQL server 2008 works fine and it  has no Issue)

    I concluded from these issues that i can use ECT to connect to a database that exist in sql server 2008, and with sql server 2000 i can use direct connection to database.

    Please Correct me if i am wrong.

    Thank you.


    Wednesday, July 04, 2012 2:03 PM
  • Hi Osman,

    You should be able to connect to SQL just fine directly when using any version of SQL 2000 or later per : http://office.microsoft.com/en-us/sharepoint-designer-help/add-a-database-as-a-data-source-HA010355745.aspx : Also as long as SharePoint is running on Sql 2005 or later and you have a completely separate server running the Sql 2000 instance you should be able to create a BDC and External content type to it per : http://msdn.microsoft.com/en-us/library/ee557243%28office.14%29.aspx : If these articles don’t help I would recommend that you open a paid support case to have this further investigated.

     Gregg


    MSFT

    Tuesday, July 24, 2012 6:52 PM
    Moderator