InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 16:29:33 GMT20http://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspxSurprisingly, I can also do cross apply... There might be some difference with running sql server 2005 (set to compatible to sql server 2000 ) and running sql server 2000 directly?Wed, 05 Sep 2012 07:08:44 GMTnzhang6666http://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspx[quote][b]Lowell (9/5/2012)[/b][hr][quote][b]nzhang6666 (9/5/2012)[/b][hr]Just found out. The compatible level of the database is set to sql server 2000 (8.0).I didn't know function can not be used in parameters of a table valued function in 2000...But I can run a query with recursive CTE on the database, does 2000 supports CTE?[/quote]best way to find out is to try it yourself...but no, CTE's will not work in 2000/ 80 compatibility...you have to change it to subqueries.[/quote]That's what I was thinking, but I just run a CTE, and it worked. ;with cte(a) as ( select 1 union all select a + 1 from cte where a < 10)select * from cteWed, 05 Sep 2012 06:45:58 GMTnzhang6666http://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspx[quote][b]Lowell (9/5/2012)[/b][hr]i just tested it in 2008R2, 2005, and compatibility 80.change your database compatibility.[code]EXEC dbo.sp_dbcmptlevel @dbname=N'SANDBOX', @new_cmptlevel=90[/code]try it in tempdb if you want, and it works fine, since tempdb will be the version of the server.[/quote]You are right. The database is set to be compatible with sql server 2000.Wed, 05 Sep 2012 06:29:35 GMTnzhang6666http://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspx[quote][b]nzhang6666 (9/5/2012)[/b][hr]Just found out. The compatible level of the database is set to sql server 2000 (8.0).I didn't know function can not be used in parameters of a table valued function in 2000...But I can run a query with recursive CTE on the database, does 2000 supports CTE?[/quote]best way to find out is to try it yourself...but no, CTE's will not work in 2000/ 80 compatibility...you have to change it to subqueries.Wed, 05 Sep 2012 06:28:52 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspxJust found out. The compatible level of the database is set to sql server 2000 (8.0).I didn't know function can not be used in parameters of a table valued function in 2000...But I can run a query with recursive CTE on the database, does 2000 supports CTE?Wed, 05 Sep 2012 06:26:22 GMTnzhang6666http://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspx[quote][b]nzhang6666 (9/5/2012)[/b][hr]I got following error when compiling --1 (commented --2 and --3).Incorrect syntax near '('. Are you testing on SQL server 2005?[/quote]It's probably the missing batch separator "GO" after the create function script.Wed, 05 Sep 2012 06:23:54 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspxi just tested it in 2008R2, 2005, and compatibility 80.the database you are testing in is set for compatibility 80 instead of 90.in SQL2000 (80), functions were not allowed, and that's why you get the syntax error;change your database compatibility.[code]EXEC dbo.sp_dbcmptlevel @dbname=N'SANDBOX', @new_cmptlevel=90[/code]try it in tempdb if you want, and it works fine, since tempdb will be the version of the server.Wed, 05 Sep 2012 06:23:13 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspxI got following error when compiling --1 (commented --2 and --3).Incorrect syntax near '('. Are you testing on SQL server 2005?Wed, 05 Sep 2012 06:19:22 GMTnzhang6666http://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspx[quote][b]Lowell (9/5/2012)[/b][hr]except for the missing GO statement between the end of the proc and the DECLARE, when i uncomment #2, all three work perfectly.[/quote]Same here. I wonder what the secret error message was?Wed, 05 Sep 2012 06:04:41 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspxexcept for the missing GO statement between the end of the proc and the DECLARE, when i uncomment #2, all three work perfectly.Wed, 05 Sep 2012 05:59:40 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1354447-149-1.aspxHi all,I got following table valued function (SQL Server 2005). I got an compile error when I run --1, but --3 is ok, --2 is used to generate the parameters to be used in --3, which should be the same as in --1. But why --1 got the error?create function test_udf_nz_2 ( @a datetime ,@b datetime)returns @result TABLE( c1 datetime ,c2 datetime)asbegin insert into @result select @a, @b returnenddeclare @dt_report_date DATETIME ,@v_stores VARCHAR(MAX) select @dt_report_date = '20120831' ,@v_stores = '152'--1select * from dbo.test_udf_nz_2( DATEADD(hour,0,DATEDIFF(d,0,@dt_report_date)), DATEADD(hour,24,DATEDIFF(d,0,@dt_report_date))) AS t--2--select DATEADD(hour,0,DATEDIFF(d,0,@dt_report_date)), DATEADD(hour,24,DATEDIFF(d,0,@dt_report_date))--3select * from dbo.test_udf_nz_2( '20120831', '20120901') AS tWed, 05 Sep 2012 05:43:01 GMTnzhang6666