Post reply

The query did not run, or the database table could not be opened.

  • December 15, 2014 at 2:52 am

    #296031

    Hi there.

    I have a view saved on server - mhsvi-datawarehouse\datawarehouse.

    This view, in it's TSQL connects to a databasethat is set up as a linked server. That server is mhsvi-sql2008a\instance1

    When I try to add the view to Excel in order to automatically refresh for users as a report - I get the following error - (I get it as well)

    The query did not run, or the database table could not be opened.

    Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.

    I have access to the database where the view is saved and the database that the TSQL calls - so not sure where the problem could lie?

    Thanks

  • December 15, 2014 at 3:04 am

    #1764670

    Hi,

    Add a "Set NoCount on" in the beginning of the proc or script you are running and check again.

    Hope it helps...!!

    Regards

    Shafat Husain

    Regards,
    Shafat Husain
    🙂
    And Your Lord Never Forgets...!! (64:19 -- Quran)

  • December 15, 2014 at 4:24 am

    #1764684

    Check the security settings to see how you're connecting to the database and how the view is connecting to the linked server. It sounds like a disconnect is occurring between how you get to the view and how the view gets to the linked server.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 15, 2014 at 8:33 am

    #1764730

    Hi - Set NoCount on didn't make any difference.

    Also where do I need to look for the security? My username of domain\uerid has access to both databases on each server?

  • December 15, 2014 at 8:45 am

    #1764734

    The linked server settings will have separate security. It could use your credentials, or something else could be set. Get the properties of the Linked Server and check there.

    If you connect to the database with the view, can you query the view?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 16, 2014 at 3:57 am

    #1764882

    Guys worked it out -

    I've created a SQL Server login on both databases - called linkedserver. I will then use this account to read the data from the linked server.

    Under the Linked Server properties - security - I ticked the "Be made using this security context" and then using the username and password that I have created on both DBs.

    Thanks for pointing me in the right direction.

  • December 16, 2014 at 4:41 am

    #1764888

    Not a problem. Glad to help. And thanks for posting the solution back here. Now anyone with a similar issue that finds this thread on a search will know how you solved it.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • November 10, 2015 at 4:39 pm

    #1839006

    Worked for me Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply