November 2, 2018 at 8:36 am
My hair pulling moment on this...
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
SELECT cp.*, cns.*
FROM contacts_master cp
Cross apply consent_by_contact ( cp.contactID ) as cns
where corporateID = 2249
and finished < GETDATE()
and GeneralText = 'general_category'
The "consent_by_contact" is a table-valued Function. I tried everything like not aliasing the tables and UDF and putting dbo. in the front
Any thoughts?
Vinay
November 2, 2018 at 8:45 am
Perhaps the issue is in the code of consent_by_contact?
Have you tried manually running consent_by_contact with some values from contacts_master.contactID?
November 2, 2018 at 8:49 am
Yes, the function on it's own works fine, when the ID is passed as a parameter.
November 2, 2018 at 9:27 am
Functions need the schema name.
Also check that you aren't somehow in compat mode 80 or lower.
The code as posted parses fine for me, can't run as I don't have your objects.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2018 at 9:44 am
GilaMonster - Friday, November 2, 2018 9:27 AMFunctions need the schema name.
Not sure that's actually the case any more Gail (at least for table value function). I just tried the following on SQL Server 2008, 2012 and 2017, and it ran without issue:
CREATE FUNCTION testfunction (@id int)
RETURNS TABLE
AS RETURN
SELECT @id AS [int];
GO
SELECT [int]
FROM testfunction(1);
GO
DROP FUNCTION testfunction
However, that isn't to say that you shouldn't include the schema. It is always good practice to do so.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 2, 2018 at 9:45 am
Yes, actually it is SQL server 2000 (80) compatibility level.
Is that the reason for this failure?
Btw, I tried with dbo.consent_by_contact too, but wouldn't work.
Vinay
November 2, 2018 at 9:50 am
datsun - Friday, November 2, 2018 9:45 AMYes, actually it is SQL server 2000 (80) compatibility level.
Is that the reason for this failure?
Btw, I tried with dbo.consent_by_contact too, but wouldn't work.Vinay
APPLY was introduced in SQL Server 2005, so yes, that's the reason for the error. Why are you using such as old compatibility level? 2008 is out of support as it is, however, 2000 is long gone.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 2, 2018 at 10:29 am
Thom,
Thank you.
We are upgrading to SQL-2016 by Jan. So, this is the last of 2008R2.
So far it's never given any trouble with "millions" of records.
First time I decided to make table-based UDF and cross joined it with a table, only to see an irritating syntax error.
November 2, 2018 at 11:44 am
datsun - Friday, November 2, 2018 9:45 AMYes, actually it is SQL server 2000 (80) compatibility level.
Is that the reason for this failure?
Yes. SQL 2000 did not allow for a column to be passed as a parameter, and that restriction was included in compat mode 80. Apply works under lower compat modes, but you need 90 or higher to pass a column as a parameter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply