24

A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however:

'CONCAT' is not a recognized built-in function name.

I understand that CONCAT() is a new built-in function introduced in SQL Server 2012, which is all fine and well, however I have been asked to roll back my change to make this 2008R2 compatible under the guise of "the user executing the query may not have Transact-SQL permissions to execute." So I am just proving my point that the client most likely has a different version of SQL Server installed in DEV than they do in PROD.

I can't find any information about specifically denying SELECT/EXECUTE permissions for built-in scalar valued functions, but is it possible, and if so does the user still receive the same error text?

  • If concat is not working then try this one SELECT (c.FirstName+','+c.LastName) AS FULLNAME FROM graduatejob c; – Nilachal Sethi May 2 '18 at 7:02
  • CONCAT supports decimal type concat(ID + '_' + OtherID), ID can be int type. – Zhang Aug 28 '18 at 7:15
  • In older versions, use this instead: DECLARE @msg VARCHAR(200) = 'Shrink file completed. Target Size: ' + @TargetSize + ' MB. Timestamp: ' + CURRENT_TIMESTAMP; – Jack Whittaker Mar 27 at 16:40
21

CONCAT was introduced in SQL Server 2012; there is no way to make it work in SQL Server 2008 R2. From the documentation:

enter image description here

There's also no way to make it fail in 2012+, even with compatibility level. So have your people check SELECT @@VERSION; on both servers; you'll find that where CONCAT fails it is < 11. In order to make your code compatible with earlier versions, you'll need to use the standard string concatenation operator (+). I don't know how you would do this with a scalar function, unless you always used the exact same number of input strings and you change your code to use dbo.CONCAT() instead of CONCAT() (there will be scenarios where it matters, plus if your function does anything the native doesn't do, you want consistent behavior if/when you upgrade). So I wouldn't recommend that approach. You may also need to add NULL handling and other minor changes (impossible to tell you how to change your existing script exactly, if we can't see it).

  • Thanks, Aaron. The code is trivial to change, as CONCAT() is just used to join some strings together before a SP_ExecuteSQL dynamic SQL call. I really just wanted to confirm that there was NO way to deny access to using CONCAT() in SQL Server 2012 – beeks Feb 1 '16 at 15:16
  • 1
    @beeks I don't know of any way to deny users the ability to use CONCAT(), no. I don't quite follow what bearing that has on what you need to do to make the code work on 2008 R2, though. You need to remove CONCAT(), not add it. – Aaron Bertrand Feb 1 '16 at 15:17
  • 1
    yes, I understand how to make the code compatible with 2008 R2, that is trivial. Thanks for confirming that one cannot DENY access to this function. – beeks Feb 1 '16 at 15:25
  • Just to note when changing the concat to the + you'll need to ensure values being concatenated are strings to begin with, if you have any numerical fields it will try to add them (and fail because a string is not an int) so if there is a numeric field (type) make sure you convert / cast it to a string first – Ste Bov May 2 '18 at 7:33
3

You can use the ODBC CONCAT function like this:

SELECT {fn CONCAT('foo ', 'test') }

The problem with this is that this function only allows you two parameters at a time. So unless you want to use more than two like this:

SELECT {fn CONCAT('foo ', {fn CONCAT('test ', 'buddy')}) }

You might as well just use the '+' operator.

protected by Community Mar 27 at 18:30

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.