SQL Search does not work in SSMS

• 
  Hi @vjacob1,

  
  

  I'm sorry to hear about the issues you are experiencing with SQL Search! Thanks for reaching you to us regarding this.

  
  

  Just to check, what version of SSMS are you currently running?

  
  

  Regarding the log files, if you navigate to the Help menu (within SQL Search) ->Logging ->Locate log files  do you see any log files located there? If not, it could be that the logging level is set to 'No logging' (I think it is by default). If you change this to Verbose you should then see full log files created:

  
  

  
  

  
  
  

  Dan_J March 09, 2021 16:10

  - 0 +
• 
  Hi Dan
  The SSMS Version that I am using is shown below
  

  SQL Server Management Studio 15.0.18369.0

  SQL Server Management Objects (SMO) 16.100.46041.41

  Microsoft Analysis Services Client Tools 15.0.19342.0

  Microsoft Data Access Components (MDAC) 10.0.18362.1

  Microsoft MSXML 3.0 6.0 

  Microsoft .NET Framework 4.0.30319.42000

  Operating System 10.0.18363
  
  Logging is set to Verbose as that was the first thing that I did check.
  
  

  vjacob1 March 11, 2021 19:58

  - 0 +
• 
  Thank you @vjacob1, do you recall if you've set an rgtemp environment variable to change the location of Redgate temporary files? Does browsing to %rgtemp% bring up a valid path?
  
  Can we also confirm:
  - Is SQL Search grayed out?
  - What SQL Server version are you attempting to search?
  - If you open SQL Search before connecting to the SQL Server, does that have any effect?

  Jessica R March 12, 2021 19:09

  - 0 +
• 
  
  That variable is not found at all on my system and I do not recall adding that variable.
  No SQL Search is not greyed out.
  SQL Server 2017
  No it does not have any affect.

  vjacob1 March 13, 2021 16:32

  - 0 +
• 
  Hi @vjacob1
  

  
  

  My sincere apology for the delay in responding to you on this!

  
  

  After some research on this I have been made aware of a current bug within SQL Search that may relate to the issue you are seeing. Until a fix is released the following workaround may help:

  1. Disconnect from the SQL Server in SSMS you want to search
  2. Close and reopen SQL Search
  3. Reconnect the SQL Server

  If this does not work for you, please can you attempt a re-installation of SQL Search? We suggest using Revo uninstaller (https://www.revouninstaller.com/revo-uninstaller-free-download/) to remove any residual files before then reinstalling the latest version of SQL Search.
  

  
  

  Again, if this does not resolve the issue, it may be worth trying to install a slightly older version of SQL Search. Version 3.5.0 can be downloaded here: https://download.red-gate.com/checkforupdates/SQLSearch/SQLSearch_3.5.0.2590.exe

  
  

  Please let me know the outcome of these suggestions.
  

  
  
  

  Dan_J March 18, 2021 10:51

  - 0 +
• 
  @Dan_J
  Using an older version of SQL Search - allowed me to open the log file but getting the following errors when trying to search for objects.
  
  I increased the timeout for the SQL Connection to the max but it is still timing out when trying to index.
  See Errors below
  
  
  

  Error Number:-2,State:0,Class:11

  18 Mar 2021 11:00:37.822 [9] ERROR ServerObjectIndexer - Exception while indexing server: 

  System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=14283; handshake=9878;  ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

     at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

     at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

     at System.Data.SqlClient.SqlConnection.Open()

     at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnection..ctor(String , CancellationToken )

     at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnectionSource.Connect(CancellationToken )

     at RedGate.SQLSearch.Indexing.ServerObjectIndexer.Index(CancellationToken )

  ClientConnectionId:4eee3a17-980e-4914-89c8-c07babd546ab

  Error Number:-2,State:0,Class:11

  18 Mar 2021 11:00:37.843 [24] ERROR DatabaseObjectIndexer - Exception while indexing database SHN: 

  System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=14305; handshake=9905;  ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

     at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

     at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

     at System.Data.SqlClient.SqlConnection.Open()

     at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnection..ctor(String , CancellationToken )

     at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnectionSource.Connect(CancellationToken )

     at RedGate.SQLSearch.Indexing.DatabaseObjectIndexer.Index(CancellationToken )

  ClientConnectionId:f6559440-11f0-415a-b9f4-8d2d89ef1456

  Error Number:-2,State:0,Class:11

  
  

  vjacob1 March 18, 2021 15:03

  - 0 +
• 
  Hi @vjacob1
  

  
  

  Thank you for your update on this, my apology for the delay in responding to you!

  
  

  It is interesting that rolling back to a slightly older version of SQL Search allows for some, but not all, functions to work.
  

  
  

  I don't appear to have asked you this before now, but when did you start seeing this issue? Was it following any particular action/update?
  

  
  

  Also, what connection method do you use to connect to your database(s)?
  

  Dan_J April 07, 2021 14:50

  - 0 +
• 
  I have been having this issue for a long time now, I previously thought it was the way we were connected to the SQL Server through SSMS - previously we were using the Run AS command to run as a different user but now things have changed and they gave us a direct connection so we can run as a normal user and now the SQL Search runs fine but now it does not return any results or come back with any errors. I am using a TCP/IP Connection to the SQL Server using the IP Address - I have tried to changed the timeout for the connection to the max value but that does not seem to change anything.

  vjacob1 April 07, 2021 15:36

  - 0 +
• 
  It appears to timeout while indexing the databases this the error that I am seeing
  

  08 Apr 2021 05:46:09.631 [97] ERROR DatabaseObjectIndexer - Exception while indexing database SHN: 

  System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=9735; handshake=4826;  ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

     at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

     at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

     at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

     at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

     at System.Data.SqlClient.SqlConnection.Open()

     at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnection..ctor(String , CancellationToken )

     at RedGate.SQLSearch.Indexing.SQLServer.SqlServerConnectionSource.Connect(CancellationToken )

     at RedGate.SQLSearch.Indexing.DatabaseObjectIndexer.Index(CancellationToken )

  ClientConnectionId:ccb84e40-92ae-47d3-a2f5-a364112f596c

  Error Number:-2,State:0,Class:11

  
  

  vjacob1 April 08, 2021 09:49

  - 0 +
• 
  Good Morning - is there any solution for me to use SQL Search in my current setup
  I have a feeling that the reason for my issues is because the SQL Server that I am trying to search is connected using a VPN.
  Perhaps this could be the reason this is not working?
  I have another PC that is on the network where it is a two way trust and SQL Search works without any issue.
  Any ideas of what to try?
  
  Thanks,
  Vinod Jacob

  vjacob1 April 29, 2021 15:32

  - 0 +
• 
  Hi @vjacob1
  

  
  

  My apology again for the delay in responding to you on this!

  
  

  Thank you for highlighting that this could be related to you using a VPN.
  

  
  Firstly, can you please try launching SQL Search before connecting to the server to see if that helps at all?
  
  If not, it would be greatly appreciated if you can you send me all of the log files from the following directory:
  C:\Users\your username\AppData\Local\Red Gate\Logs

  
  
  

  Dan_J April 30, 2021 13:40

  - 0 +
• 
  Hi Dan,
  https://1drv.ms/u/s!AjMRocjlC4Py4RJEoUPlH0ZSbWf5?e=2jLZxQ
  Here is the link to the Log Files.

  vjacob1 April 30, 2021 14:02

  - 0 +
• 
  Any updates?

  vjacob1 May 08, 2021 17:43

  - 0 +
• 
  Yes... very frustrating.. having the grayed out issue too

  LlewellynAtBluestone May 13, 2021 00:19

  - 0 +
• 
  Hi @vjacob1
  

  
  

  My sincere apology for the delay in getting back to you on this.

  
  

  I am currently struggling to reproduce the issue you are seeing here, and I wondered if it might be possible for you to try a remote connecting (RDP) to a machine that is on the other side of your VPN that has SSMS and SQL Search installed? If so, it would be good to see if you experience the same issue then?

  
  

  Also, I have been made aware that one of our other products (SQL Source Control) can cause performance issues with SSMS when a VPN connection is involved. If you do have SQL Source Control installed, would it be possible to try temporarily removing it to see if you continue to see the same issue in SQL Search?
  

  
  

  Also, one final thing to check, do the search results ever show? It would be interesting to know if they do after leaving SQL Search to run for a few minutes.
  

  Dan_J May 27, 2021 11:47

  - 0 +
• 
  Hi Dan,
  It was already mentioned in this post but I do have another computer that I previously RDPing to use SSMS as part of our work setup 
  SQL Search works without no issues as this computer has a direct network connection to the SQL Server
  I uninstalled SQL Source Control and SQL Search just crashes when it was trying to pull up a list of the databases and SSMS terminates and restarts but this was the behavior all time when using SQL Search with this VPN Connection

  vjacob1 May 29, 2021 10:51

  - 0 +
• 
  Hi @vjacob1
  

  
  

  Thank you for update on this, our apology for the delay in responding to you!

  
  

  I wondered if you could please try the following: in SSMS, disconnect from your server, reopen the SQL Search tab, then reconnect to your server (via VPN), does SQL Search work then?
  

  
  
  
  

  Dan_J June 07, 2021 11:32

  - 0 +
• 
  @Dan_J Sorry that did not work...I tried a couple of times but SQL Search does not respond at all and just sits and sometimes causes SSMS to crash

  vjacob1 June 08, 2021 09:47

  - 0 +
• 
  Hi @vjacob1
  

  
  

  Thank you for your update on this, I'm sorry that my suggestion did not help on this occasion.
  

  
  

  Just to let you know that I will be attempting to reproduce this issue again tomorrow by creating a VPN scenario with SQL Search. I will get back to you as to the outcome of that as soon as possible.
  

  
  

  Dan_J June 15, 2021 15:29

  - 0 +
• 
  I use to get crashing problems, I uninstalled redgate tool belt, installed the latest SSMS and reinstalled the toolbelt and was okay after that.  With regards to the grayed out SQL Search, I removed the server name from the Object Explorer window in SSMS and reconnected to it.  SQL Search worked again after that.

  LlewellynAtBluestone June 15, 2021 23:15

  - 0 +
• 
  Hi @vjacob1
  

  
  

  I'm afraid again we are not seeing the same issue you are reporting with SQL Search.
  

  
  

  It could be that we are not quite replicating your scenario, we did the following:

  
  

  - Connected to VPN (to the Redgate office network)
  - RDP'd onto a PC sat in the office
  - Launched SSMS (15.0.18)
  - Test 1 - SQL Search v3.5.5 - we performed a search, after indexing for around 30 seconds search results are produced.
  - Test 2 - SQL Search v.3.5.3 - Same search as above, same result (search results are produced)

  
  

  Is there anything we should have done differently to the above steps?
  

  
  

  One question that came out of our discussion this morning was around the size of the database you are searching on. Is it a particularly large database?
  

  Dan_J June 16, 2021 11:04

  - 0 +
• 
  @Dan_J - the size of the database is about 1 GB or so last I checked
  SQL Search is now crashing my SSMS when I try to go into the tool
  Attached is a link to the log file - https://1drv.ms/u/s!AjMRocjlC4Py43bG4ZhD8FSItpha?e=OWnT63 
  
  

  vjacob1 June 16, 2021 14:10

  - 0 +
• 
  Hi @vjacob1
  

  
  

  Thank you for providing the latest log file, I am reviewing this now.

  
  

  I just wanted to check, are the replication steps outlined below a correct representation of the scenario in which you are seeing this issue with SQL Search?:
  

  
  

  - Connected to VPN (to the Redgate office network)
  - RDP'd onto a PC sat in the office
  - Launched SSMS (15.0.18)
  - Test 1 - SQL Search v3.5.5 - we performed a search, after indexing for around 30 seconds search results are produced.
  - Test 2 - SQL Search v.3.5.3 - Same search as above, same result (search results are produced)

  
  
  

  Dan_J June 21, 2021 08:59

  - 0 +

Add comment

Please sign in to leave a comment.