Incorrect syntax near the keyword 'current_timestamp' - But only on one Database

Question

I have a SQL Server 2008 R2 Instance with several databases on it.

I'm trying to run a Table-Valued Function on one of the databases (let's call it DB1) that will take a date as an input and return a table of relevant information.

I run my query as such:

SELECT * FROM dbo.getAllStatusesForGridProjectsByMaximumDate(CURRENT_TIMESTAMP)

to get the most up-to date information. Instead of the result table, however, SQL just kicks the error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CURRENT_TIMESTAMP'.

What's strange though is that if I hop onto another database (DB2) and run the function while specifically referencing the server, it runs:

USE DB2
GO

SELECT * FROM DB1.dbo.getAllStatusesForGridProjectsByMaximumDate(CURRENT_TIMESTAMP)

That returns results. If I try and run the query off of DB1, though, it kicks back that same Incorrect syntax error:

USE DB1
GO

SELECT * FROM DB1.dbo.getAllStatusesForGridProjectsByMaximumDate(CURRENT_TIMESTAMP) 

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CURRENT_TIMESTAMP'.

I've looked through the databases to see if there's some sort of setting or property that I missed that would allow me to pass CURRENT_TIMESTAMP to my Table-Valued function and haven't found anything. I've tried an explicit CAST/CONVERT of CURRENT_TIMESTAMP, and it doesn't like any kind of function in there.

What witchcraft has been performed upon my DB2 that would allow it to run dbo.Function(Current_Timestamp) that hasn't been performed on DB1? I'm going to keep checking on my own, but any help you wonderful people could send would be greatly appreciated.

Aaron Bertrand · Accepted Answer · 2012-02-16 20:52:27Z

4

Check the compatibility level of the database (it wasn't 100% clear if the problem was happening in DB1 or when calling the function in DB1).

I bet the compatibility level is 80 in whatever database is exhibiting the problem; SQL Server 2000 didn't allow functions to be passed directly to UDFs that way (and we have the same problem calling some of the dynamic management functions if the compat level is 80 - see this blog post and the comments). This database must have been restored or attached after being backed up or detached from 2000. Or after being upgraded from 2000. You can check the current compatibility this way:

SELECT name, compatibility_level FROM sys.databases WHERE name = 'DB1';

If you find it is < 100, you can say:

ALTER DATABASE DB1 SET COMPATIBILITY_LEVEL = 100;

But you should only do so if you know that the lower comptibility is not needed for some other reason - and you should validate on a test system that the database works as expected under the new compat level. If you have problems, you can always change it back, but it's better to be prepared.

If you don't want to mess with the compatibility level, you can change your logic slightly.

DECLARE @now DATETIME = CURRENT_TIMESTAMP;
SELECT * FROM dbo.getAllStatusesForGridProjectsByMaximumDate(@now);

edited Feb 16, 2012 at 20:52

answered Feb 16, 2012 at 20:41

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RockiesMagicNumber Over a year ago

So... uh... Can we be best friends now?

Aaron Bertrand Over a year ago

Of course. Or just pay it forward when you see someone else struggling with the same issue. :-)

RockiesMagicNumber Over a year ago

Seriously though, I got kind of thrown into the DBA role (small company, you've all heard the story), so I'm having to learn all of these things when other things break. The compatibility level was exactly what I needed, as DB1 was at 80 and DB2 at 90. I'm going to research compatibility levels more so I understand what it was that I just did, so thank you very, very, very much for your input.

Collectives™ on Stack Overflow

Incorrect syntax near the keyword 'current_timestamp' - But only on one Database

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related