SQL Server

SQL Server connection error

Posted on Updated on

 

Lately I had an error connecting to SQL Server from a remote connection and I had the firewall disabled in both machines and the error I had was:

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: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)

All the suggestions pointed me to enabling remote connections in SQL Server, Enable TCP/IP, checking if I was using named instances… but I was able to connect to SQL Server from the remote machine using the command line tool "OSQL /s<machinename,1433> /E" (I used the port to verify that SQL Server was listening from the right port).

Finally I discovered what the problem was. In the client machine I run “cliconfg” to open the following configuration dialog:

cliconfg

Once there, I discovered that I had set up an alias for the remote machine. I deleted that alias so the result was the one in the image and everything started working again.

 

I hope it helps.

Default full-text index language is not a language supported by full-text search.

Posted on Updated on

Recently I have experienced a number of problems with my Full-Text indexes in SQL Server (2005). Creating or updating the columns being indexed, I had this error:

Default full-text index language is not a language supported by full-text search.

I run SELECT * FROM sys.fulltext_languages; and I got no results, when I should have had in my install at least 17.

The fist time it happened, I couldn’t find any simple solution. They all pointed me to reinstalling SQL Server, but I hate reinstalling SQL Server, it takes too long (And luckily it isn’t Oracle). I refused to reinstall all the services so what I did was:

  1. Uninstall only the full text service.
  2. Reinstall the service again
  3. Apply the service pack again.
  4. Re-start all SQL Server services.

That solved the problem but it happened again.

After searching for solutions, I couldn’t find any reasonable solution and what’s more important, why it did happen. I thought that it would be something related to our DB update scripts, but it wasn’t.

Investigating, I noticed that SQL Server creates some entries in the registry with the word separators and other information related to the ways the data gets indexed depending on the languages it has to index.

Then, BINGO. I remembered that I had installed an app to help cleaning the registry, IObit – Advanced SystemCare 3 (A free version of the tool that supposedly optimised the system but what it did was deleting all the registry keys that defined the Full-Text languages, and who knows what else). I went to the Restore Centre from that tool and restored all the changes made to bring back all the deleted registry entries.

I run exec sp_fulltext_service ‘update_languages’; to update the languages and my languages were bac.

Now, SELECT * FROM sys.fulltext_languages; returned all the languages.

fulltext_languages

If you have this error and you have any tool to clean the registry, have a look at it to see what it has messed up. I better have a dirty registry rather that some applications that don’t work and I spend a lot of time trying to figure out what happened.

R.

ADOMD.Net Exception

Posted on

Just then I had an exception in a piece of code that was querying a Cube and It had been working well up until now.

The error came when I was doing development in ASP.net running my page under the Web Server that comes with VS.Net (WebDev.WebServer.EXE). When I moved the code to IIS it didn’t work anymore.

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: XML for Analysis parser: The CurrentCatalog XML/A property was not specified.

Well, the error message is quite confusing, specially when it had been working previously with no errors!!!!

The reason why this happened is because I was using Windows Authentication and the code was running under my credentials(in WebDev.WebServer.EXE and my user had access to Analysis Services). However, the IIS user didn’t.

So, to fix it, I used impersonation to run the query under the right user credentials.

Creating a connection using SQL Server authentication using a user with access to the cube would have worked as well, I guess.

I hope it helps.