Skip to Content (Press Enter)
2019-11-11 17-10-24
Manish Jawla
 
MVP
181Views
4Comments
find max id of ident column
Question
select IDENT_CURRENT('tablename') is not working in outsystem. may i know the another solution?
0
0
03 Jun 2015
Copy Link
2014-02-13 10-06-38
Ricardo Silva
That looks like a SQL Server specific command.

Are you using SQL Server as your backend database? Because the command would be different in MySQL or Oracle. This would be a very specific command.

Also, if you just want to find out the maximum current identifier you can simply do:

select max(id) from {Entity}  , which should work on any DBMS.
0
0
03 Jun 2015
Copy Link
2019-11-11 17-10-24
Manish Jawla
 
MVP
Ricardo Silva wrote:
That looks like a SQL Server specific command.

Are you using SQL Server as your backend database? Because the command would be different in MySQL or Oracle. This would be a very specific command.

Also, if you just want to find out the maximum current identifier you can simply do:

select max(id) from {Entity}  , which should work on any DBMS.
 Hi Ricardo,

Yes m using SQLserver as DB but MAX(id) will give me only current available record maxid, but in my case if i have deleted few records from the end then it will not work and i cannot reset the identity column bcz it is used as reference in other tables. for eg- i have 20 records in db and maxid is 20 but if i have deleted last 5 records i will get maxid 15 but counter has already reached to 20. before inserting the new record, if i want to know the current counter i.e. 20 i dont have any function. if there is any then plz let me know.
Thanks.
 
0
0
07 Jun 2015
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Also, IDENT_CURRENT needs special permissions, that probably aren't granted for the default database user (see e.g. here).
0
0
03 Jun 2015
Copy Link
2014-02-13 10-06-38
Ricardo Silva
Then the problem that you're having is probably what Kilian mentioned: the runtime user does not have permissions to access that information.

Solution: simply give the required permissions to the user.

A quick search on google tells me this is probably not so easy, as there seems to be no documentation on the required permissions for that. Some say "SELECT" on the table is enough, others say you need db_owner for that (and you shouldn't give db_owner to the runtime user).

In any case, in what context is this being used? Why do you need this information? Perhaps the solution is to not use IDENT_CURRENT at all.
0
0
07 Jun 2015
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.