Hi When i attempt to use SQL prompt on a SQL server 2008 installation i get the following error:
Unable to refresh or cache database [172.16.102.63].[SydEnergiTestNew] due to the following error: Could not continue scan with NOLOCK due to data movement..

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException: Unable to refresh or cache database [172.16.102.63].[SydEnergiTestNew] due to the following error: Could not continue scan with NOLOCK due to data movement.. ---> System.Data.SqlClient.SqlException: Could not continue scan with NOLOCK due to data movement.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at t.Read()
at o.a(n , Boolean )
at o.a(n )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects, Boolean includeDependencies)
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.Refresh(IAuthenticationTokenProvider provider)
at ao.DoTask()

I am unable to use SQL prompt on this sql server and so is all my colleagues.
I hope you can help

SQL Prompt has a NOLOCK enabled on our queries to the database which causes the caching to fail when there is any data movement in the database. However, it is unlikely to happen all the time unless the database you are trying to access is a heavy load database that is consistently being updated/changed.

Can you run the Profiler and try to execute the query manually to see if it works?
If the above attempt fails try to execute the query after removing the NOLOCK hint and that will help understand the issue.

I would also recommend to check if you have VIEW DEFINITION permissions enabled on the database/server you are trying to use SQL Prompt on.

Hi Tanya
I ran the profiler and picked up the query in question and i even when i removed all no lock hints it still gave the same error.
I then ran a Dbcc checkDb on the master database and there seems to be error in the master database on both servers and my guess is that is causing the errors.
Regards
Nicolai

SQL Prompt needs to query the system tables to pull back schema information for the specific database.
To do this the user needs the following permissions when using SQL 2008:
1. Use the GRANT VIEW DEFINITION TO [{username}] which provides SQL Prompt with permission to retrieve the META-DATA.
2. Grant VIEW SERVER STATE to access information about encryption keys.

I am getting the following error message when I try to refresh the cache of a previously accessed database:

Unable to refresh or cache database [IBLONPSP33X281\\AME].[Cobra] due to the following error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

Can you please try to delete the existing cache by navigating to SQL Prompt--> Cache Management and delete all the cache and open and new query window that will recreate the cache and this should resolve your issue.

Can you please try to delete the existing cache by navigating to SQL Prompt--> Cache Management and delete all the cache and open and new query window that will recreate the cache and this should resolve your issue.

Kindly let us know if that helped.

Have deleted all cached databases and am still getting the same error regardless of database (have tried master this time instead of the Cobra database)

Unable to refresh or cache database [IBLONPSP33X281\\AME].[master] due to the following error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

And also let us know the detailed version of SQL Prompt?
You can know the detailed version of SQL Prompt by navigating to SQL Prompt --> About SQL Prompt and version number is displayed on bottom right (just above Close button.)

Kindly let us know above details that will help us to investigate further.

And also let us know the detailed version of SQL Prompt?You can know the detailed version of SQL Prompt by navigating to SQL Prompt --> About SQL Prompt and version number is displayed on bottom right (just above Close button.)

Kindly let us know above details that will help us to investigate further.