Red Gate forums :: View topic - Unable to refresh or cache database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt Previous Versions
SQL Prompt Previous Versions forum

Unable to refresh or cache database

Search in SQL Prompt Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
DR_CHAOS



Joined: 28 Apr 2009
Posts: 2

PostPosted: Tue Apr 28, 2009 7:35 pm    Post subject: Unable to refresh or cache database Reply with quote

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
Back to top
View user's profile Send private message
tanya



Joined: 30 Apr 2007
Posts: 253

PostPosted: Fri May 01, 2009 5:06 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
DR_CHAOS



Joined: 28 Apr 2009
Posts: 2

PostPosted: Sun May 03, 2009 6:42 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
bauerga



Joined: 07 May 2009
Posts: 3

PostPosted: Thu May 07, 2009 5:33 pm    Post subject: Unable to refresh or cache database Reply with quote

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?
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 685
Location: Cambridge

PostPosted: Fri Jun 19, 2009 2:36 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
GaryHampson



Joined: 07 Jan 2005
Posts: 14
Location: New York, NY

PostPosted: Tue Jul 21, 2009 5:20 pm    Post subject: Reply with quote

Hi Red Gate folks,

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

Quote:
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
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 685
Location: Cambridge

PostPosted: Wed Jul 22, 2009 9:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
GaryHampson



Joined: 07 Jan 2005
Posts: 14
Location: New York, NY

PostPosted: Mon Aug 03, 2009 7:01 pm    Post subject: Re: Reply with quote

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)

Quote:
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 )
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 685
Location: Cambridge

PostPosted: Tue Aug 04, 2009 2:50 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
GaryHampson



Joined: 07 Jan 2005
Posts: 14
Location: New York, NY

PostPosted: Tue Aug 04, 2009 7:15 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
TimHS



Joined: 17 Sep 2009
Posts: 1

PostPosted: Thu Sep 17, 2009 8:10 pm    Post subject: Reply with quote

Was there a solution ever found for this problem?
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 685
Location: Cambridge

PostPosted: Fri Sep 18, 2009 1:23 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Trevorwin



Joined: 16 Sep 2011
Posts: 5

PostPosted: Thu Oct 27, 2011 3:17 pm    Post subject: Reply with quote

Couple of things to check...

Try using IE to open the following URL...http://testserver01/Store/SiteCacheRefresh.axd?CacheToRefresh=AllMarketingCaches'.

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group