asked
Syntax error with Table Valued UDF when called from another DB
Greetings! We're having a curious problem with a table-valued UDF. When the function is called from within the database where the function resides, it works fine. If I call the function from another database, I get the following error: Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '.'. Msg 102, Level 15, State 1, Line 12 Incorrect syntax near ')'. Here's the query the developer is using (sorry, it's a bit of a messy one): DECLARE @siteCategoryNo int, @notificationValue int SET @siteCategoryNo = 632 SET @notificationValue = 12303 SELECT DISTINCT tblCommunities.communityNo FROM ( SELECT communityNo FROM core.dbo.communities communities LEFT JOIN core.dbo.categories categories ON categories.categoryNo = communities.communityValue WHERE categoryNo IN ( SELECT DISTINCT s.item FROM core.dbo.categories c OUTER APPLY core.dbo.fnSplit(c.lineage, '/') AS s WHERE c.categoryNo in ( SELECT DISTINCT categoryNo FROM products.dbo.equipmentCategories equipmentCategories LEFT JOIN core.dbo.forumVariableTypes forumVariableTypes ON forumVariableTypes.forumVariableTypeValue=equipmentCategories.equipmentNo AND forumVariableTypes.forumVariableType='equipment' LEFT JOIN core.dbo.forumPosts forumPosts ON forumPosts.forumNo=forumVariableTypes.forumNo WHERE forumPosts.threadNo=@notificationValue ) ) AND communities.siteCategoryNo=@siteCategoryNo AND communities.communityType='category' ) as tblCommunities WHERE tblCommunities.communityNo IS NOT NULL The "fnSplit" function is stored in the "core" database. If this query is run from within the "core" database (use core) then it works just fine. However, we're running this query from a web server, and the datasource the web server is using has the default database set to "main". If I run this query from "main" (use main) then it generates the syntax error. Any idea why I would get a syntax error based on which database I'm currently using? The other curious thing is, I have a Dev and Staging environment. On the DEV environment it works fine in both cases. I only get this error in the staging instance. DEV and STAGING both run on the same SQL Server (named instances). They're the same version, same server properties, same database properties, etc. I cannot find any permissions differences between them. I'm stumped! Which is why I'm here... :) Note: I don't know for sure that the UDF is the reason for the issue. I just suspect it. The UDF simply splits the value passed in based on the delimiter passed in, and returns a table of the "split" values. I can post the code if needed. Thanks! Bill
It really looks like the problem is caused by the compat level of your "main" database. The odds are that the level is 80 (native for SQL Server 2000). Usually, it happens when the original SQL Server 2000 database is upgraded to 2005. By design, the compat level does not get changed after the upgrade, it has to be done manually. With compat 80 the engine has no way to understand the meaning of the outer apply. -- check compat level select [compatibility_level] from sys.databases where name = db_name(); -- if the above returned 80 then set it to native 90 sp_dbcmptlevel main, 90; go Hope this helps, Oleg
@Bill Nothing is embarassing about it. Who would think that the option is left out during upgrade. There is one interesting thing about the **sp_dbcmptlevel** proc which had bitten me pretty badly once. I submitted a rather big script which included a compat level change, something like this:
use master; go sp_dbcmptlevel SomeDB, 90; go -- more scriptIt failed miserably and of course rendered a bunch of procedures invalid (as those included cross and outer apply). The deployment account running the script was a member of the db\_owner role, and it just so happens that in order to change the compat level of the user database from the context of master, the db\_owner membership is not enough! One must be sa or the database owner. In other words, if I had the script written like this:
use SomeDB; go sp_dbcmptlevel SomeDB, 90; gothen it would work, because db\_owner membership would be enough, but to achieve the same result from the context of master is not enough. The good news is that this proc should not be used in better versions of SQL Server, the **alter database set compatibility_level = xx** should be used instead. Anyway, it taught me a lesson :)
ARGH! Thanks Oleg. That was exactly the problem. I compared all the database options between them (in the list of options)...several times, convinced something was off, but they're identical. I missed the obvious one (compatability level) at the top! (kick myself). Thanks everyone. I figured it would be something simple. I was hoping the answer would be less embarrassing. :)
+1 for the answer and +1 for this last comment. Wow..these ones are indeed good lessons to learn. Thanks for sharing it.
Are there any differences in the database owners, on each server?
