Unable to refresh or cache database

DR_CHAOS

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

tanya

Hello,

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.

I hope that helps.

Thanks,
Tanya
Project Manager
Red Gate Software Ltd

DR_CHAOS

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

bauerga

When I open a query on a SQL 2008 database, I receive the following error:


Unable to refresh or cache database [SQD-102\QSRV1].[OWCD_CESYSTEM] due to the following error: The user does not have permission to perform this action..

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException: Unable to refresh or cache database [SQD-102\QSRV1].[OWCD_CESYSTEM] due to the following error: The user does not have permission to perform this action.. ---> System.Data.SqlClient.SqlException: The user does not have permission to perform this action.
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.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at v.a(String )
at v.ExecuteSqlSetting()
at o.aa()
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.a(IAuthenticationTokenProvider )
at cy.GetMetaData(IAuthenticationTokenProvider provider)
at M.b(G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )

I am dbo on this database, and SQL Prompt seems to function after clicking through on the error. What are the permissions this message refers to?

Anu D

Sorry for the delay in reply.

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.

Kindly let me know if that helped.

GaryHampson

Hi Red Gate folks,

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).

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException: 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). ---> System.Data.SqlClient.SqlException: 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)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at RedGate.SqlPrompt.Engine.ConnectionProperties.RetrieveServerDetails()
at RedGate.SqlPrompt.Engine.ConnectionProperties.j(ConnectionProperties , IAuthenticationTokenProvider , Boolean )
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.Refresh(IAuthenticationTokenProvider provider)
at ao.DoTask()

I am connected to the database in question and can query with no issue. Any ideas?


Gary

Anu D

Thanks for your post Gary.

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.

GaryHampson

Anu Deshpande wrote:

Thanks for your post Gary.

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).

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException: 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). ---> System.Data.SqlClient.SqlException: 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)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at RedGate.SqlPrompt.Engine.ConnectionProperties.RetrieveServerDetails()
at RedGate.SqlPrompt.Engine.ConnectionProperties.j(ConnectionProperties , IAuthenticationTokenProvider , Boolean )
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.a(IAuthenticationTokenProvider )
at cy.GetMetaData(IAuthenticationTokenProvider provider)
at M.b(G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )

Anu D

Thanks for the update.

Are you using VPN to connect to the remote database?

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.

GaryHampson

Anu Deshpande wrote:

Thanks for the update.

Are you using VPN to connect to the remote database?

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.

I am not using VPN to connect.

The version of SQL Prompt is 3.9.0.43.

TimHS

Was there a solution ever found for this problem?

Anu D

Thanks for your post.

Can you kindly email us your exception and detailed version of SQL Prompt on support@red-gate.com?

Trevorwin

Couple of things to check...

Try using IE to open the following URL...http://testserver01/Store/SiteCacheRefr ... ingCaches'.

Is the IIS web site where the Commerce site is configured set to use host headers? If so, try adding the machine name as a host header for the site and see if IE will then work on the URL.

Also, check that the app pool that the web site is running under is set to use the correct identity.