Not getting desired SQL Remote View Results 1

[eric0524]

I am trying to set a filter on a remote view in the data remote view designer to get a dataset back.

When I call:
cSearchStr = "BANK"

SELECT DISTINCT Sdn.ent_num, Sdn.SDN_Name, Sdn.SDN_Type, Sdn.Program,;
Sdn.Title, Sdn.Call_Sign, Sdn.Vess_type, Sdn.Tonnage, Sdn.GRT, Sdn.Vess_flag,;
Sdn.Vess_owner, Sdn.remarks;
FROM ;
dbo.SDN Sdn;
WHERE Sdn.SDN_Name LIKE ( ?'%'+cSearchStr+'%' );
ORDER BY Sdn.SDN_Name

I have tried "IN" forward and backwards.

I want it to return records that have in the name "CUBA BANK", "BANK OF IRAN" and so on but I am only getting records that are an exact match. When I set the search string to BANK it returns nothing. I can do a stored procedure in SQL 2008 that has:

SELECT * FROM sdn WHERE sdn_name like '%' + @sdn_name + '%' order by ent_num

and it returns the data set I want. Is there a way to make a remote view do the same?

 

[Olaf Doschke]

Well,

the equivalent for like '%' + @sdn_name + '%' is like '%' + ?cSearchStr + '%', not like ( ?'%'+cSearchStr+'%' ).

Bye. Olaf.

 

[eric0524]

Thanks Olaf,

That was what I just came up with. But I also set near on and set exact off (not sure if they had anything to do with it but it works) LOL

-Eric

 

[Olaf Doschke]

NEAR and EXACT have influence on many things, but with SQL that depends on the ANSI setting. None of these influence how LIKE works, however. These would just influence if an exepression as FIELD = ?var works like exact match or as FIELD LIKE ?var+'%' (so you could do 'begions with queries). But never ever would that make this expression work as FIELD LIKE '%'?var+'%'.

Besides all that, all these settings have no effect on SQL Server and would only affect native SQL or local views done on DBFs. Remote Views are just prepared with the parameters, but executed by the remote database. That's why they are called remote views.

Bye, Olaf.