|Subject:||Can you run a function in SQL Server?|
|Posted by:||Patrick Finucane (patrickfinucane…@gmail.com)|
|Date:||Wed, 11 Dec 2019|
Hi. I am working on an Access app that is an ADP, not an Accdb. The backend is SQL Server.
I have the following SQL command
SELECT qs.*,ConcatRelated("ContactCategory", "tblContactRCategory",
"ContactI_ID = " & 22058) AS ContactCategory
FROM dbo.qryLocalSelContactSeries qs INNER JOIN
dbo.tblLocalSelContact tsel ON qs.Contact_ID = tsel.Contact_ID
WHERE (tsel.LocalStaff = @strStaffLocal) AND (tsel.blnPrint = 1)
I have no idea what qs.* is. There is no query called qs. There is no table called qs. Qs is unknown to me.
The function ConcatReleated is a function written by Allen Browne. The function works. It is declared as Public.
The Query Builder in the app is different than normal Access. The fields are listed vertically. The heading columns are Column (the field names), Alias (ConcatCategory), Table (qs), Output, and Criteria.
When I attempt to verify the SQL or run the SQL I get the following error
ADO Error. ConcatRelated is not a recognized built in function name. Statement(s) could not be prepared. Deferred prepare could not be completed.
As near as I can determine, I am being told that since ConcatRelated is a function I created, it can't be run. I am blown away. I would think SQL Server could run a query that contains a function. It can do so in regular Access.
Am I messing something about running queries with functions in SQL Server? Can SQL Server handle queries with functions?
Do you have any suggestions on how to get a function to be called that can run with SQL Server as the backend?