I am using Sql Server 2017 with Transact and a file with synonyms in FTData. I have found several serious flaws and problems but in the Microsoft MSDN forum and in Microsoft connect no one answers me. The rest of the people say they do not know how it works.
Problem 1: Freetexttable Error With More of 3 Words.
I use Freetexttable with thousands of synonyms in a simple query in two Pc. By entering up to 3 words "Microsoft Sql Management Studio" instantly returns the result and the memory remains stable in 6Gb.
If I enter 4 words he is thinking for 10 minutes and the memory consumes until 12Gb. At the start of the video you can see what it takes Sql Server to load the synonyms and every few minutes if Sql Server is not used again it loads them again.
SELECT [Table].*, FT.* FROM [Table] INNER JOIN FREETEXTTABLE([Table], [Contenido], 'Word1 Word2 Word3') FT ON [Table].Id=FT.[Key] WHERE ([Table].STATE IS NULL OR [Table].STATE = ' ') ORDER BY RANK DESC, Page
Problem 2: Transact does not return the inflections of a word if you do not accent it correctly.
When looking for the inflectional of a word, if I introduce the word with the accent the inflections appear well. The server collation is SQL_Latin1_General_CP1_CI_AI
If I introduce the word without the accent, the inflections do not appear. In Spanish café and cafés are accentuated.
When doing the same with the synonyms, returns the same results with or without accent.
SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(THESAURUS, cafe)', 3082, 0, 0)
SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(THESAURUS, café)', 3082, 0, 0)
display_term returns: cafe chicoria sucedaneo mezcla chicoria. In Spanish sucedáneo are accentuated.
Note:After making the video I tried to recover the inflections of the word echaré (with and without accent) and returns the inflections well in both cases. So the problem is with some words.
I have found a very interesting example. I use the verb "echar". Among others, it has 2 inflections that are "echaré" and "echaría". If I consult "echaré" with and without an accent, it works well for me and it also returns me between its inflections.
If I consult "echaría" work well with accent and bad without accent. Video: https://youtu.be/DmfZmzRW4w8
Problem 3: Transact load the synonyms many times.
I have added synonyms and a function that loads them when starting sql server. The problem is that when you make the first consultation you think about 1.5 minutes. Also, if I do not do searches, every so often (it can be 15 minutes) again it seems that it reloads the synonyms, perhaps from tempdb. How is it done to load them once and not delete them from tempdb?
I have this store procedure to load the synonyms at the start of sql server from file:
CREATE PROCEDURE Sinonimos
SET NOCOUNT ON;
EXEC sys.sp_fulltext_load_thesaurus_file 3082, @loadOnlyIfNotLoaded = 1;
Activate the store procedure at startup by:
EXEC sp_procoption @ProcName='Sinonimos', @OptionName = 'startup', @OptionValue = 'on';
Problem 4: You can not protect the synonyms.
I have to install the sql server system on a client but I do not want to leave the .xml file from FTData there with synonyms. Is there a way for the file to load it from a remote URL or can the file be encrypted?
But then I came to the ship weight, which I need to get from PRODUCT_INFO.
I forget the nomemclature for my expression above, so I wasn't able to search possibilities.
Possible to do a quick join off sC into another list?
If possible, I would need a little nudge towards getting it right.
I have a table of products with names and descriptions. So take a word like kneepads. The correct way to spell it is knee pads and the column contains that correct spelling. But users will type in kneepads to search. I solved the plural issue with a custom function that I wrote.
But I'm wondering if I can query the database in Linq, and say something like without removing, replacing a value in the database
from pr in context.PRODUCT_ITEMS
WHERE pr.Name.Replace(" ", "").Contains("kneepad")
Basically I'm just looking for ideas to handle this.
My older program had another table that contains names and descriptions that were pre-stripped for searching and I really don't want to go back to that.
Guess the original idea I had might be the best. A separate table of straight text with no white spaces and just do a join.
Or maybe a table of words with white space that are parsed out with conjunctions removed and verbs fixed.
Hello everybody, i'm writing here because i'm really becoming crazy, i read every kind of internet pages but I'm not able to find a solution but i'm also sure that the solution is behind the corner.
I have an Access database with different tables. The main table has different foreign keys to other tables. I do an example with 2 tables to make the problem easier:
Field1 - Primary Key: Product Code - text
Field2: Description - text
Field3: Product Type - integer
Field1: Primary Key - ID - integer - FK on Field3 of "Table Product"
Field2: Description - Text
Now, I open Visual Studio, I add my database and I drag&drop the table "Product" on my Windows Form as a DatagridView. The wizard create for me, the tableAdapter, bindinsource ecc....
If I start my app the DatagridView is populate with the correct data but in the column "Product Type" I see the number from the PK of the table "ProductTypes" while I'd like to see the Description.
I created a new TableAdapter query that give me the result i want to see but when I confirm I get the message that i'm created a query that give a different result from the original schema. If I continue the query is created but i'm not able to retrieve data using this new query.
THe only way I've to retrieve datas as I want is to modify the original Fill query but in this case the relation between the 2 tables will be removed and also the TableAdapter Insert method will be removed.
So, I will see the data in datagridview as i'd like to see but I don't have anymore the insert method.
Can someone help me by telling I could I retrieve the data in the way I desider without losing the chance to insert new record in the table?
Yes, I already did it.
I created a View in Access and then I imported it in my Dataset. In this way I'm able to show the correct data in my DataGridView but the Wizard didn't create for me the Insert, Delete and Update method.
Do you think I could be able to update the Access database through the view?
I think to have solved the problem....maybe I'm using a bad solution but the result is good.
In my dataset I have the Main table that is filled with the original database records. In the same datased I inserted a second table that is filled with a my personal Fill query.
In my form I have inserted both table adapter. The first one is only in background while the second one is shown in datagridview.
I insert, delete and modify the records one the datagrid and when I click "Save", I reflect the changes in the background table adapter....
This question was originally asked over a week ago in quick answers.
Edit: (2018-01-06) There is no problem getting the sqloledb driver to connect to an availability group. The problem cited below has been resolved. (possibly by forcing protocol as in tcp: ag-listener_name for the server) The client's dba tweaked some settings and it all works now...not sure what he did. Thanks to all responders.
I am posting it again here due to bad timing (right before holiday break) which possibly resulted in this question not being noticed by an expert before getting pushed back to page 50+. @OriginalGriff did post a solution, albeit not a viable one. The original question is as follows:
I've been connecting to SQL Server databases for almost 20 years, but my latest client has me baffled. For the first time that I know of, our database has been made part of an Availability Group under SQL Server 2012. This is a new client and so far, their impression of me is probably not that great.
Quite simply, the problem is that I am unable to connect a legacy application using the OLEDB provider to a database hosted in a SQL 2012 Availability Group. Despite 2 hours of working with a junior tech. on their end, and trying many different combinations, my application is not able to find the server. What I'm wondering, is if there is any special parameter required in the connection string when connecting to an availability group?
The current form of the connection string that is failing is:
While on the remote with the junior tech., we were able to remote into the server and verify that the database was available, and that our sql login was setup correctly...also verified that sql authentication was enabled. (I've been bit by that dog before) Everything seems to be setup correctly...even made sure that the client firewall was disabled. No joy!
This fiasco took place on the last business day of this year for the client, so I have been unable to try anything since learning a little more about AGs/Listeners. It would be ideal to replicate the customer's environment for testing, but setting up a failover cluster and AG seems like overkill when there's probably a simple parameter that I am missing.
Other info: From the workstation having the problem, I can successfully ping the database servers. (primary and replica) The error message returned is 'The Server does not exist or access is denied'. It seems more like a timeout than a permissions issue. Also, if it matters, the application uses ADODB connections/objects.
It will be over two weeks until the customer returns from the holidays. In the meantime, I'm charged with explaining to their lead tech. (who was unavailable when we were having problems) what the problems are, and what we are going to do to fix it. (in addition to a lot more crap they want now)
What I have tried:
0) Searched connectionstrings.com
1) Spent the last two days reading up on Availability Groups and client connections. I have a few things to try, but it'll be trial by fire unless I invest the time to replicate the environment.
Things I would try if I were able:
0) Prepend the server name (in the connection string) with tcp: (is this important?)
1) Use the listener name instead of server/instance (even though all the documentation I read says this should work)
2) Add a connection string parameter (MultiSubnetFailover=True)
3) Check the port numbers being used
4) Try a different variation of OLEDB connection string
What I'm hoping is that someone here may have access to an AG test environment and can help confirm that it is even possible to connect with the OLEDB provider per the connection string example given. Any help or hints are greatly appreciated!
Update: The customer is requesting another remote and conference tomorrow to try to resolve this problem. I have a few things now to try, and they have senior staff on hand that probably will know exactly how to reach the server/AG. What I'm still in the dark about is if I will need to add another connection parameter for it to work. If that's the case, it will require an update to the code that constructs/saves/recalls the connection details. Thanks again for any hints!
"Go forth into the source" - Neal Morse
modified 6-Jan-18 15:15pm.
Last Visit: 25-Nov-20 2:05 Last Update: 25-Nov-20 2:05