locked
No tables or views could be loaded. RRS feed

  • Question

  • Hi,

    I am using the newest Visual Studio and setting up everything for SSIS. I tried to add an OLE DB connection in Connection Managers. Since I installed my SQL Server in a virtual machine, I used the IP address of my virtual server and my SQL Server Authentication account to the connection and it works fine when I tried Test Connection. But although my account have been added in the Connection Managers, it didn't show up when I tried to add a source by dragging Source Assistant to the Data Flow window. I had to make a new connection with the same account again. Anyway, I created the same connection again and it was connected, so I moved on to choose the table/view for the source. This time, there is no way I can get any information from that connection. It shows 'No tables or views could be loaded' if I use choose a table/view and it shows an error if I use SQL command.

    Somebody knows where could be the problem?


    Wednesday, July 17, 2019 2:56 PM

Answers

  • Hi Yaofang,

    Please share versions of the following:

    • Visual Studio
    • SSDT
    • SQL Server on a virtual machine

    Additionally, please try to connect to the SQL Server on a virtual machine and issue some queries from the SSMS on your machine.

    And one more step.

    Please install Microsoft® OLE DB Driver 18 for SQL Server® on your machine.

    Here is the download link:

    Microsoft® OLE DB Driver 18 for SQL Server®

    • Edited by Yitzhak Khabinsky Wednesday, July 17, 2019 3:06 PM
    • Marked as answer by Yaofang Wednesday, July 17, 2019 11:07 PM
    Wednesday, July 17, 2019 3:03 PM
  • Hi Yaofang,

    • SSIS for Visual Studio 2019
      SSDT was replaced by the SSIS Projects for VS 2019
      https://chanmingman.wordpress.com/2019/06/04/ssis-for-visual-studio-2019/
      It is a pre-release, so it could be a bug with what you are experiencing.
    • Also, right mouse click on your connection, edit it and try to change the OLEDB provider to the latest.
    • Edited by Yitzhak Khabinsky Wednesday, July 17, 2019 5:03 PM
    • Marked as answer by Yaofang Wednesday, July 17, 2019 11:07 PM
    Wednesday, July 17, 2019 4:57 PM

All replies

  • Hi Yaofang,

    Please share versions of the following:

    • Visual Studio
    • SSDT
    • SQL Server on a virtual machine

    Additionally, please try to connect to the SQL Server on a virtual machine and issue some queries from the SSMS on your machine.

    And one more step.

    Please install Microsoft® OLE DB Driver 18 for SQL Server® on your machine.

    Here is the download link:

    Microsoft® OLE DB Driver 18 for SQL Server®

    • Edited by Yitzhak Khabinsky Wednesday, July 17, 2019 3:06 PM
    • Marked as answer by Yaofang Wednesday, July 17, 2019 11:07 PM
    Wednesday, July 17, 2019 3:03 PM
  • Thanks for your help!

    Here are the softwares I use:

    VS 2019 with integrated SSDT

    SQL Server 2016 installed in Microsoft Server 2016(Virtual)

    I've been using SSMS and it works well sending queries and getting results from the SQL Server.

    And I downloaded the Driver from the link you provided. It detected a lower version in my computer and replaced the old one. But the connection is still not working.

    Btw, I experimented dragging more components to the Data Flow, but every time I tried to add a Destination Assistant or Source Assistant, it doesn't show up the connection I added in Connection Managers and requires me to create a new one. 


    • Edited by Yaofang Wednesday, July 17, 2019 4:05 PM typo
    Wednesday, July 17, 2019 4:04 PM
  • I solved the 'No tables or views could be loaded' problem. It is THE demo database I used that causing troubles. I changed it to a different database and it works now. 

    However, I still have problem using the added connection whenever I want to add a destination or source. I notice that when I create a new connection for the destination/source, the provider automatically choose 'Microsoft OLE DB Driver fro SQL Server', but my connection only works from 'SQL Server Native Client 11.0'. I think that's the reason I have to add a new connection every time. Is there a way to change that default?

    • Edited by Yaofang Wednesday, July 17, 2019 4:53 PM new problem found
    Wednesday, July 17, 2019 4:48 PM
  • Hi Yaofang,

    • SSIS for Visual Studio 2019
      SSDT was replaced by the SSIS Projects for VS 2019
      https://chanmingman.wordpress.com/2019/06/04/ssis-for-visual-studio-2019/
      It is a pre-release, so it could be a bug with what you are experiencing.
    • Also, right mouse click on your connection, edit it and try to change the OLEDB provider to the latest.
    • Edited by Yitzhak Khabinsky Wednesday, July 17, 2019 5:03 PM
    • Marked as answer by Yaofang Wednesday, July 17, 2019 11:07 PM
    Wednesday, July 17, 2019 4:57 PM
  • I just solved the problem by checking the link you send me and the new SSIS page. Obviously only the old servers use the Native Client provider to connect and I've been using Native Client to connect to the server. Since I use a Windows Server 2016, it requires a OLEDB Driver instead, which is the one you asked me to install earlier today. I didn't realize until now that I should install it in my virtual machine where runs the server instead of my real machine. It connects well now. Thank you very much!

    Wednesday, July 17, 2019 9:49 PM
  • Hi Yaofang,

    Glad to hear that everything is working for you now.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Wednesday, July 17, 2019 9:53 PM