remote view and identity column of sql server

orders
-------------
orderid int, identity, primary key

I used a view to open orders on sql server as t_orders. I then append
blank a record in t_orders and tableupdate(.t.). How could I know the
new value of orderid from sql server? If I issue requery("t_orders"),
the record pointer would no longer on the newly appended record.

I need the new value of orderid to build a new view with order_details.


--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 5:38pm up 12 days, 18:10, 0 users, load average: 0.99, 0.96, 0.91

Stefan
Wed Feb 04 05:12:26 CST 2004

Quoted from a posting by Remus Rusanu:
>>
SELECT @@IDENTITY
Returns the last-inserted identity value in the current session.
SELECT IDENT_CURRENT('table_name')
Returns the last identity value generated for a specified table in any
session and any scope.
SELECT SCOPE_IDENTITY( )
Returns the last IDENTITY value inserted into an IDENTITY column in the same
scope.

You probably want @@IDENTITY
>>

-Stefan

"toylet" <toylet@mail.hongkong.com> schrieb im Newsbeitrag
news:eSaTfMw6DHA.2764@TK2MSFTNGP09.phx.gbl...
> orders
> -------------
> orderid int, identity, primary key
>
> I used a view to open orders on sql server as t_orders. I then append
> blank a record in t_orders and tableupdate(.t.). How could I know the
> new value of orderid from sql server? If I issue requery("t_orders"),
> the record pointer would no longer on the newly appended record.
>
> I need the new value of orderid to build a new view with order_details.
>
>
> --
> .~. Might, Courage, Vision. In Linux We Trust.
> / v \ http://www.linux-sxs.org
> /( _ )\ Linux 2.4.22-xfs
> ^ ^ 5:38pm up 12 days, 18:10, 0 users, load average: 0.99, 0.96,
0.91

toylet
Wed Feb 04 06:10:29 CST 2004

how should I make use of @@identity? I am not very familiar with SQL server.

create sql view t_orders remote connect myconn ;
as select * from orders
create sql view t_ident remote connect myconn ;
as select @@identity
select 0
use t_orders
append blank
=tableupdate(.t.)
select 0
use t_ident
browse

I got the value NULL. I am using SQL Server 7.0

> SELECT @@IDENTITY
> Returns the last-inserted identity value in the current session.
> You probably want @@IDENTITY
>> orders
>> -------------
>> orderid int, identity, primary key
>> I used a view to open orders on sql server as t_orders. I then append
>> blank a record in t_orders and tableupdate(.t.). How could I know the

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 8:06pm up 12 days, 20:38, 0 users, load average: 1.00, 1.02, 0.95

Stefan
Wed Feb 04 07:36:42 CST 2004

I'm not an SQL-Server expert as well.
With a Vfp remote view SELECT IDENT_CURRENT('table')
works for me.
@@IDENTITY seems to work in a SPT example as below,
although I'm not sure if SCOPE_IDENTITY( ) might be
prferable in case of any (update) triggers on the server.


-Stefan

*
LOCAL cConnString, h, aTemp

lcConnString = ;
[DRIVER=SQL Server;] + ;
[DATABASE=test;] + ;
[APP=Microsoft(R) Windows NT(TM) Operating System;] + ;
[SERVER=(local)]

h = SQLSTRINGCONNECT(cConnstring)
IF h > 0
CLEAR
*!* cSQL = [CREATE TABLE _rvtest (pk int identity, cTest char(10))]
*!* ? SQLEXEC(h, cSQL) < 0
cSQL = [INSERT INTO _rvtest (cTest) VALUES ('] + SYS(2015) +[')]
? SQLEXEC(h, cSQL)
? SQLEXEC(h, [SELECT @@IDENTITY as LastID], 'crsResult')
? crsResult.LastID
? SQLEXEC(h, [SELECT IDENT_CURRENT('_rvtest') as LastID], 'crsResult')
? crsResult.LastID
? SQLEXEC(h, [SELECT SCOPE_IDENTITY( ) as LastID], 'crsResult')
? crsResult.LastID
ENDIF
*


"toylet" <toylet@mail.hongkong.com> schrieb im Newsbeitrag
news:uwgqefx6DHA.2392@TK2MSFTNGP11.phx.gbl...
> how should I make use of @@identity? I am not very familiar with SQL
server.
>
> create sql view t_orders remote connect myconn ;
> as select * from orders
> create sql view t_ident remote connect myconn ;
> as select @@identity
> select 0
> use t_orders
> append blank
> =tableupdate(.t.)
> select 0
> use t_ident
> browse
>
> I got the value NULL. I am using SQL Server 7.0
>
> > SELECT @@IDENTITY
> > Returns the last-inserted identity value in the current session.
> > You probably want @@IDENTITY
> >> orders
> >> -------------
> >> orderid int, identity, primary key
> >> I used a view to open orders on sql server as t_orders. I then append
> >> blank a record in t_orders and tableupdate(.t.). How could I know the
>
> --
> .~. Might, Courage, Vision. In Linux We Trust.
> / v \ http://www.linux-sxs.org
> /( _ )\ Linux 2.4.22-xfs
> ^ ^ 8:06pm up 12 days, 20:38, 0 users, load average: 1.00, 1.02, 0.95

toylet
Wed Feb 04 09:08:27 CST 2004

so I have to use sqlexec() to get the result.
can I use create sql view instead?

> cSQL = [INSERT INTO _rvtest (cTest) VALUES ('] + SYS(2015) +[')]
> ? SQLEXEC(h, cSQL)
> ? SQLEXEC(h, [SELECT @@IDENTITY as LastID], 'crsResult')
> ? crsResult.LastID

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 11:06pm up 12 days, 23:38, 0 users, load average: 1.00, 1.07, 1.04