0

I'm working with SQL Server 2014 Express with FileTable. I'm trying to select documents from the filetable (unstructured, pdf) based on whether they contain specific keywords based on the registration mark of an aircraft:

--SET VARIABLE FOR A REGISTRATION MARK
DECLARE @Reg nvarchar(10)
SET @Reg = 'PH-BGA'

--SET VARIABLE FOR MANUFACTURER OF REGISTRATION MARK
DECLARE @Manufacturer nvarchar(10) = NULL
SELECT FleetInfoKLM.Manufacturer FROM FleetInfoKLM WHERE FleetInfoKLM.Reg = @Reg;
SET @Manufacturer = '""'

--SET VARIABLE FOR FAMILY OF REGISTRATION MARK
DECLARE @Family nvarchar(10) = NULL
SELECT FleetInfoKLM.Family FROM FleetInfoKLM WHERE FleetInfoKLM.Reg = @Reg;
SET @Family = '""'

--SET VARIABLE FOR SERIES OF REGISTRATION MARK
DECLARE @Series nvarchar(10) = NULL
SELECT FleetInfoKLM.Series FROM FleetInfoKLM WHERE FleetInfoKLM.Reg = @Reg;
SET @Series = '""'

--SELECT MAINTENANCE DOCUMENTS BASED ON VARIABLES FOR MANUFACTURER, FAMILY AND SERIES
SELECT MaintenanceDocumentation.name FROM MaintenanceDocumentation
WHERE ((@Manufacturer = '""') AND CONTAINS(file_stream, @Manufacturer)) 
    AND ((@Family = '""') AND CONTAINS(file_stream, @Family))
    AND ((@Series = '""') AND CONTAINS(file_stream, @Series))

Without SET @VARIABLE = '""' I get the error Msg 7645 Null or empty full-text predicate. Hence I added the SET @VARIABLE = '""' as it was found as a solution in this question 7645 Null or empty full-text predicate. However, now the result set is empty (no documents found), while I know that there should be results. The variables are properly set, in this example as Boeing, 737 and 800 respectively. If I replace the variables as a string (e.g. 'Boeing') as an argument in CONTAINS, I do get the expected results.

Anyone got a clue what the problem is? Have been Googling for hours without result other than avoiding the error with SET @VARIABLE = '""'.

Community
  • 1
  • 1
Hemmo
  • 1
  • 1
  • why are you not using semicolon `;` at the end of your statements? – Hogan Oct 22 '15 at 18:20
  • Honestly I didn't even think about it, as the code works. Apparently it's not required https://msdn.microsoft.com/en-us/library/ms177563.aspx, but I will add them. – Hemmo Oct 22 '15 at 18:24
  • You are posting the code here and adding in random SET statements. **The code does not work** – Hogan Oct 22 '15 at 18:25
  • Also the link you posted says *most* statements. It also says the **will** be required in the future. – Hogan Oct 22 '15 at 18:27
  • Agreed, that's why I do add them now. I added the SET statements, because someone else with a similar problem used it and it worked. For me, I go from an error to an empty result set. It's progress, but obviously not what I want. If I replace the variable in the CONTAINS statement to strings it does work. I'm fairly new to this and with what I've found online I can't get it working so far. – Hemmo Oct 22 '15 at 18:37

1 Answers1

0

Okay, I finally fixed it by altering the SET statement to SET @variable = (SELECT ..):

--SET VARIABLE FOR A REGISTRATION MARK
DECLARE @Reg nvarchar(10);
SET @Reg = 'PH-BGO';

--SET VARIABLE FOR MANUFACTURER OF REGISTRATION MARK
DECLARE @Manufacturer nvarchar(100);
SET @Manufacturer = (SELECT FleetInfoKLM.Manufacturer FROM FleetInfoKLM WHERE FleetInfoKLM.Reg = @Reg);

--SET VARIABLE FOR FAMILY OF REGISTRATION MARK
DECLARE @Family nvarchar(100);
SET @Family = (SELECT FleetInfoKLM.Family FROM FleetInfoKLM WHERE FleetInfoKLM.Reg = @Reg);

--SET VARIABLE FOR SERIES OF REGISTRATION MARK
DECLARE @Series nvarchar(100);
SET @Series = (SELECT FleetInfoKLM.Series FROM FleetInfoKLM WHERE FleetInfoKLM.Reg = @Reg);

--SHOW LOCAL VARIABLES VALUES
SELECT @Manufacturer;
SELECT @Family;
SELECT @Series;

--SELECT MAINTENANCE DOCUMENTS BASED ON VARIABLES FOR MANUFACTURER, FAMILY AND SERIES
SELECT MaintenanceDocumentation.name FROM MaintenanceDocumentation
WHERE MaintenanceDocumentation.path_locator IS NOT NULL
    AND CONTAINS(file_stream, @Manufacturer)
    AND CONTAINS(file_stream, @Family)
    AND CONTAINS(file_stream, @Series);

No need for the SET @variable = '""' statements.

Hemmo
  • 1
  • 1