STILL UNSOLVED: Incorrect syntax near '.'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

STILL UNSOLVED: Incorrect syntax near '.'

Author

Topic

greenmtnsun
Starting Member

13 Posts

Posted - 2006-09-12 : 18:51:09

I have a problem getting a piece of SQL to run.

SELECT Myfunction.*
FROM [Mydbname].[dbo].monthsyear as m
Cross Apply
[Mydbname].[dbo].[fnEarningsByStateSitePlanMonthYear] (m.[Month],m.[year],205,'W') as [MyFunction]
where m.monthStartDate>='2004-02-01 00:00:00'
and m.[MonthEndDate]<='2005-01-31 23:59:59'
order by [MyFunction].[Name], [MyFunction].[period] asc

When I run that in the "Mydbname" database it won't run, however when I run it in the master database it will. When I take of the "[Mydbname].[dbo]." part while in the right database it also gets the error below. No combinations seem to resolve it so far.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.

The function fnEarningsByStateSitePlanMonthYear is in the Mydbname database with dbo. I can't figure out why SQL Server is seemingly wanting me to run the statement remotely rather than when I'm connected to the actual database the tables and function reside in.

Here is a description of what the function call does.

-- A user will be prompted for a parameter for the following:
-- Start Date 12/31/2005 (example data)
-- End Date 06/30/2006 (example data)
-- SiteID 205 (example data)

-- That data will triger a call to the ReportMonthsYear Table which will return the following:
-- Month, Year, Start date, End date
-- 01, 2006, 01/01/2006, 01/31/2006
-- 02, 2006, 02/01/2006, 02/28/2006
-- 03, 2006, 03/01/2006, 03/31/2006
-- 04, 2006, 04/01/2006, 04/30/2006
-- 05, 2006, 05/01/2006, 05/31/2006
-- 06, 2006, 06/01/2006, 06/30/2006

-- That data will then return the report such that all rows from the monthsyear table that are returned
-- by filtering out before and after the start date will drive a call to the UDF.
--
-- Results
-- 01, 2006, Experience data from function for the period
-- 02, 2006, Experience data from function for the period
-- 03, 2006, Experience data from function for the period
-- 04, 2006, Experience data from function for the period
-- 05, 2006, Experience data from function for the period
-- 06, 2006, Experience data from function for the period
-- =============================================

I want to turn this into a function (that calls this SQL which calls the function) but I can't yet since this keeps popping up. The same error occurs when used in a create function statement as in a native SQL statement.

I also checked the database schema for tables and routines and they are owned by dbo.

Any ideas as to how to resolve this?

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-09-12 : 19:34:30

Try this in the correct db -

SELECT Myfunction.*
FROM [Mydbname].[dbo].monthsyear as m
Cross Apply
[dbo].[fnEarningsByStateSitePlanMonthYear] (m.[Month],m.[year],205,'W') as [MyFunction]
where m.monthStartDate>='2004-02-01 00:00:00'
and m.[MonthEndDate]<='2005-01-31 23:59:59'
order by [MyFunction].[Name], [MyFunction].[period] asc

You've to prefix the functions with owner name

greenmtnsun
Starting Member

13 Posts

Posted - 2006-09-12 : 19:39:19

Nope. I had actually tried it before but forgot to mention that.

Same error: Incorrect syntax near '.'.

Thanks for the suggestion. Any other ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-13 : 11:49:17

Can't think of anything that could cause this.
Maybe something in the function.

Try simplifying the query.
Start by ommitting the where and order by clauses then knock out most of the code in the function and see if you still get the error.

I can only think you are running a different version when you connect from master but if everything is owned by dbo and only one schema don't see how that can happen.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 12:22:51

Did you create the function in the MASTER database?
Are you running the code in a SQL 2005 database?

quote:
When I run that in the "Mydbname" database it won't run, however when I run it in the master database it will. When I take of the "[Mydbname].[dbo]." part while in the right database it also gets the error below. No combinations seem to resolve it so far.

Peter Larsson
Helsingborg, Sweden

greenmtnsun
Starting Member

13 Posts

Posted - 2006-09-13 : 12:30:52

No Peso, its in [Mydbname], I checked everything in regards to schema and database. They are owned by dbo and in the [Mydbname] database.

Kristen
Test

22859 Posts

Posted - 2006-09-14 : 07:45:13

I pondered this but didn't come up with any useful suggestions I'm afraid.

Posting the source code for your function might reveal something - particularly if its doing some dynamic SQL!!

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 10:20:33

quote:
SELECT Myfunction.*
FROM [Mydbname].[dbo].monthsyear as m
Cross Apply
[Mydbname].[dbo].[fnEarningsByStateSitePlanMonthYear] (m.[Month],m.[year],205,'W') as [MyFunction]
where m.monthStartDate>='2004-02-01 00:00:00'
and m.[MonthEndDate]<='2005-01-31 23:59:59'
order by [MyFunction].[Name], [MyFunction].[period] asc

1. What is Cross Apply ? Cross Join ?
2. You can't pass a column name into table function. Only constant value.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-14 : 11:41:20

Have a look for cross apply in bol.
It is a new feature for table valued functions in v2005.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 11:43:30

quote:
Originally posted by nr

Have a look for cross apply in bol.
It is a new feature for table valued functions in v2005.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks. Will take a look at it

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 11:51:09

Should have just google for cross apply.

The first link bring me back here http://www.sqlteam.com/item.asp?ItemID=21502

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 11:59:37

I believe he is working with SQL Server 2005.
CROSS APPLY is one of the new features in T-SQL language.

Peter Larsson
Helsingborg, Sweden

greenmtnsun
Starting Member

13 Posts

Posted - 2006-09-14 : 12:21:34

Hi All, this is the original poster making this post from home on vacation.

I'm really dissappointed in some research I found. It appears all but certain that this is a compatibility level issue. I found a peice of confirmation as well, and I made this work in another database at compatability of 90. I'm on vacation now, but I recall doing a search on google for the error number with the state information and then switching to groups, and then found someone else who confirmed it as well.

SQL 2005 only supports this in level 90, NOT 80.

If anyone has an idea how this can be re-written to be in level 80/for sql 2000 that would be a blessing passing values from the select into the function without the cross apply. I won't be able to reply much at all for 10 days. Thanks for any help.

Keith

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-14 : 12:30:27

cross apply wasn't available in v2000 so it won't be available in v2000 compatability mode (well shouldn't be - it doesn't apply tyo everything).
Bit surprised about theerrror you got but....

Cross apply is a weird statement as it produces a variable length resultset for each call and duplicates the calling row for each row in that resultset.
I think the only way to do this in v2000 would be to loop through the table calling the function with each row and poopulate a temp table.
That's if you can't turn the function call into a join to a query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-14 : 14:05:01

I guess the question is - why are you running the database in 8.0 compatibility? I assume you have some old code in there that needs it to run correctly but you have to weigh the work to update that old code to work with 2005 correctly versus the work to figure out a workaround to get functionality that 2005 will give you (like CROSS APPLY) when you're not in 8.0 compatibilty.

jmvspam
Starting Member

2 Posts

Posted - 2010-09-13 : 16:07:31

Hello people,

I tried to use the examples and got in trouble because “cross apply” only works on SQL 90 compatibility mode – not in SQL 2005 with mode 80 enabled (my case). Since I can’t change the database, I create the following work around:
-- Code
USE tempdb – tempdb is a native SQL 2005 DB
SELECT
tb.field1,
tb.field2,
fc.*
FROM #tempTB as tb
CROSS APPLY OficialDB_Compatibility80.dbo.myUDF (tb.field1, tb.field2, tb.field3, @dinamicVAR) AS fc
USE OficialDB_Compatibility80
-- rest of the Code

This worked for me. Good lock!

Bye
Josué Monteiro Viana

Josué Monteiro Viana

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources