Incorrect syntax near '.' when accessing table inside specified DB 2

[kxramse]

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.

Any ideas as to how to resolve this?

 

[SQLBill]

What Edition of SQL Server are you using (2000, 2005)?

What is 'Cross Apply'? I can't find that as valid SQL Server syntax in the BOL.

-SQLBill

Posting advice: FAQ481-4875

 

[kxramse]

I'm on 2005.

Cross apply is apparently new to 2005. It seems like it can be powerful, lets just hope it doesn't give one a performance hit. The APPLY clause let's you join a table to a table-valued-function.

Basically I get to use that function such that I can have a driving table, in this case monthsyear which a user can select a date range to work against. Since my DB does things by a column with Month and Year seperate, I ask the user to tell me the start and end date in full and then look at the months year table to pass the associated month and year to the function. Sure I could find the month and year but I need a driver so that Crystal can use something to build off of when calling the function.

http://www.sqlteam.com/item.asp?ItemID=21502

has a good article.

I know it works, but I can't get it to work locally, only from the master database on objects in the [Mydbname].

I forgot to mention this before, I also checked the database schema for tables and routines and they are owned by dbo. I also tried all combinations of putting dbo before the function and tables without the db name while in that db with the same problem.

 

[bborissov]

What is the compatible mode for that database? Is it 80 or 90?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[kxramse]

Its set to 80 for [Mydbname], and master is at 90.

Oh, and speaking of compatability, since I saw a lot of talk on MSDN about order by and compatability (

http://msdn2.microsoft.com/en-us/library/ms178653.aspx),

I commented out the order by clause and it still gives the same error, in case that could be causing it.

 

[SQLBill]

Ah that's it......I see where Borislav is going. 80 is SQL Server 2000. So you Mydbname can only run commands that exist in SQL Server 2000. CROSS APPLY only works in 2005, which is why you can run it from MASTER.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Forgot to add....here's a star for you Borislav for figuring that out.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Bill,

Are you sure about the compatibility level? I think that 'newer' functionality still works in 'older' compatibility levels. For example, we know that table variables were created with the release of SQL 2000 (compatibility level 80).

So, you would ordinarily think that setting the compatibility level to something prior to 80 would cause table variable statements to fail. It doesn't.

Take a look at this code.

use tektips
go

sp_dbcmptlevel [tektips], 60
go

use tektips
go

Declare @Temp Table(Blah Varchar(20))
Insert Into @Temp Values('haha')
Select * From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kxramse]

When bborissov asked about compatibility I discovered this post.

http://groups.google.com/group/micr...+1"+cross+apply&rnum=1&hl=en#5477380604d038f4

I've confirmed this by placing this function in a seperate database on the same instance in a 90 compatability and it works. Which seems to indicate it doesn't work backwards.

For some reason

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

this was not listed on this page by MS, which I think was their mistake.

Triple stars to each of you and Borislav!! It was really hard to figure this one out.

 

[SQLBill]

George,

Good point that I overlooked.

-SQLBill

Posting advice: FAQ481-4875