#ident_current

[varindersandhu-blog]

New Post has been published on Varinder Sandhu 's Blog

New Post has been published on http://www.varindersandhu.in/2012/06/01/sql-server-2008-get-the-next-identity-value/

SQL Server 2008 - Get the next Identity Value

Question: How to get the next Identity Value in SQL Server 2008?

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Answer: It is very simple to the get the identity value in SQL Server. Let’s see with the example below

Lets‘s create some dummy data as mentioned below …

SQL Server 2008 – Get the next Identity Value

In SQ Server, there are two functions IDENT_CURRENT and IDENT_INCR. With the help of these funtions, we can get next value Identity.

IDENT_CURRENT: It returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

IDENT_INCR: It Returns the increment value (returned as numeric (@@MAXPRECISION, 0)) specified during the creation of an identity column in a table or view that has an identity column.

Get the Last Identity Value as below…

SQL Server 2008 – Get the next Identity Value

Get the Identity Increment

SQL Server 2008 – Get the next Identity Value

Get the next Identity Value

SQL Server 2008 – Get the next Identity Value

Hope this help you.

[varindersandhu-blog]

Post has been published on Varinder Sandhu 's Blog

Post has been published on http://www.varindersandhu.in/2011/01/20/scope_identity-ident_current-and-identity-not-working-with-linked-server-or-remote-server/

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY not working with Linked Server or Remote server ???

Hello Friends,

I have faced a problem using SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY with Linked server

Let me explain with example as below

Question:

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

We have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008. Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on SQL 2000 with Linked server

Now the problem is the some triggers and stored procedures which need to insert the values generated from SQL 2008 to SQL 2000 databases using functions SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are not working in this scenario.

We are fixing this problem by writing another select statement for selecting identity based on unique parameters in that scope.

Answer: The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

Same for SCOPE_IDENTITY, IDENT_CURRENT also……

If anybody has any idea, please share as comment…