CROSS APPLY - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

CROSS APPLY

Author

Topic

jrunamdin
Starting Member

3 Posts

Posted - 2010-02-18 : 16:36:22

HELLO GUYS,
I am currently working a udf function in SQL Server 2008.
Here is the example I tried
http://technet.microsoft.com/en-us/library/ms175156.aspx

Unfortunately, When I run the select statment, I got query compiled with error

SELECT D.deptid, D.deptname, D.deptmgrid
,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

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

But, If i change the statement to the following

SELECT D.deptid, D.deptname, D.deptmgrid
,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
CROSS APPLY fn_getsubtree(1) AS ST;

it works. Also, I tried the same example in SQL Server 2005, it works fine too.

Anyone has any idea about that?

The server installed SQL Server 2008 SP1.

Regards,

Eddy

jrunamdin
Starting Member

3 Posts

Posted - 2010-02-18 : 17:01:09

you need set competibility to 90

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 23:57:52

Run the below and see if you're atleast on compatibility level 90


EXEC sp_dbcmptlevel 'yourdbname'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jrunamdin
Starting Member

3 Posts

Posted - 2010-02-19 : 09:30:48

quote:
Originally posted by visakh16

Run the below and see if you're atleast on compatibility level 90

EXEC sp_dbcmptlevel 'yourdbname'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, it works now. I set the competitbily to 90

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 09:32:55

quote:
Originally posted by jrunamdin

quote:
Originally posted by visakh16

Run the below and see if you're atleast on compatibility level 90

EXEC sp_dbcmptlevel 'yourdbname'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, it works now. I set the competitbily to 90

Great

If it was initially set to 80 by somebody purposefully make sure you dont have any breaking code changes in your db before you change it to 90 else you never know what all you'll be breaking

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources