Unable to refresh or cache database

Provides intelligent code completion for SQL Server editors.

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson

Unable to refresh or cache database

Postby DR_CHAOS » Tue Apr 28, 2009 7:35 pm

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
DR_CHAOS
 
Posts: 2
Joined: Tue Apr 28, 2009 7:31 pm

Postby tanya » Fri May 01, 2009 5:06 pm

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
tanya
 
Posts: 252
Joined: Mon Apr 30, 2007 8:29 pm

Postby DR_CHAOS » Sun May 03, 2009 6:42 pm

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
DR_CHAOS
 
Posts: 2
Joined: Tue Apr 28, 2009 7:31 pm

Unable to refresh or cache database

Postby bauerga » Thu May 07, 2009 5:33 pm

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?
bauerga
 
Posts: 3
Joined: Thu May 07, 2009 5:26 pm

Postby Anu Deshpande » Fri Jun 19, 2009 2:36 pm

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.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby GaryHampson » Tue Jul 21, 2009 5:20 pm

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
GaryHampson
 
Posts: 14
Joined: Fri Jan 07, 2005 7:15 pm
Location: New York, NY

Postby Anu Deshpande » Wed Jul 22, 2009 9:42 am

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.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Re:

Postby GaryHampson » Mon Aug 03, 2009 7:01 pm

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 )
GaryHampson
 
Posts: 14
Joined: Fri Jan 07, 2005 7:15 pm
Location: New York, NY

Postby Anu Deshpande » Tue Aug 04, 2009 2:50 pm

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.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Re:

Postby GaryHampson » Tue Aug 04, 2009 7:15 pm

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.
GaryHampson
 
Posts: 14
Joined: Fri Jan 07, 2005 7:15 pm
Location: New York, NY

Postby TimHS » Thu Sep 17, 2009 8:10 pm

Was there a solution ever found for this problem?
TimHS
 
Posts: 1
Joined: Thu Sep 17, 2009 8:03 pm

Postby Anu Deshpande » Fri Sep 18, 2009 1:23 pm

Thanks for your post.

Can you kindly email us your exception and detailed version of SQL Prompt on support@red-gate.com?
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby Trevorwin » Thu Oct 27, 2011 3:17 pm

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.
Trevorwin
 
Posts: 5
Joined: Fri Sep 16, 2011 9:29 am


Return to SQL Prompt Previous Versions

Who is online

Users browsing this forum: Yahoo [Bot] and 0 guests