asked on

capturing SCOPE_IDENTITY() from linked ASA 9 server with openquery

I have a query that inserts transactions into a Sybase(ASA 9) database via a linked server in SQL Server 2005 Express. The table I'm inserting into has an autoincrement identity field. I need to be able to capture the SCOPE_IDENTITY() from the insert but am having difficulty doing this from the SQL side without having to return to the database after the insert to get the value of the id field.

Any thoughts out there on how this can be done without having to run a select after the insert?

Here is my syntax for the insert:
--**********************************************--
--**********************************************--
insert openquery(SybaseLinkedSvr, 'select STORENUM
                                                             ,TICK_DATETIME
                                                             ,REGISTER
                                                             ,TICKET
                                                             ,SHIFT
                                                             ,FISCALDATE
                                                             ,CASHIER
                                                             ,SOURCE_DEV
                                                       ,SOURCE_UNT
                                                       ,CUSTOMER
                                                       ,CUST_DATE
                                                       ,TRANS_TYPE
                                                       ,TOTAL_AMT
                                                       ,TOTAL_TAX
                                                  from dba.ticket')
Select StoreNum
                   ,Tick_DateTime
                   ,Register
                   ,Ticket
                   ,Shift
                   ,FiscalDate
                   ,Cashier
                   ,Source_Dev
                   ,Source_Unt
                   ,Customer
                   ,Cust_Date
                   ,Trans_Type
                   ,Total_Amt
                   ,Total_Tax
            From dbo.udf_GetTicketData(@xml)
--**********************************************--
--**********************************************--
I've also tried using sp_executesql and adding @id = SCOPE_IDENTITY() at the end of the Sybase select statement, in hopes that it would return it as an output variable, but that didn't work. I got incorrect syntax near '=' returned from Linked Server.

I also have to do this from the sql side, so no DDL is allowed on the Sybase db. (Such as triggers, procedures, etc.)

it seems like there would have to be something out there, I just haven't been able to find it yet with my trusty Google tool. :-/ Any suggestions out there?

Thanks in advance!

lcohan

You should use Sybase NEXTVAL function like below but I suggest put that in a Sybase function and run that function from linked server in SQL:

SELECT sequence-name.NEXTVAL;

http://dcx.sybase.com/1200/en/dbusage/sa6a77dc1f-9039-4ea6-9f46-0aedfd34d24e.html
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01031.0400/doc/html/asc1252677221029.html

edfreels

ASKER

Thanks for the response. Based on what I'm reading, that is an object on the table and would require modifying the structure of the ASA db. Correct me if I'm wrong. I don't have the option to make any structure changes on the ASA db. how do I capture that on the SQL end without running 2 separate openquery statements?

lcohan

"Based on what I'm reading, that is an object on the table and would require modifying the structure of the ASA db. "

Not realy - that "sequence-name" is the implicit function created on the Identity column in Sybase right? You said above: "The table I'm inserting into has an autoincrement identity field. " so all you need is to find that sequence function name for that table.column and use the NEXTVAL to get the next value to be generated for an insert.

edfreels

ASKER

There is no sequence function on the table or column or in the db at all.

lcohan

Then how the "...autoincrement identity..." can work? Are you sure that "...The table I'm inserting into has an autoincrement identity field." ??

lcohan

mySQL and postgres have similar Sequence function auto generated for any table that has a "autoincrement identity field" (or serial column).

edfreels

ASKER

the column is set as identity autoincrement by 1. I run insert and it automatically populates that column with the next id increment. You don't have to use a sequence function. If you read the link you sent me, it gives you an explanation of the sequence function and the autoincrement as 2 seperate things.

edfreels

ASKER

Here is what I'm doing right now, but it is obviously not the most optimal way to capture the id of the inserted record.

After the insert statement above, I have this statement running right after to get the id. Is there a way to capture the id with openquery in the same statement or is this the only way to do it from SQL Server?

set @ParmDef = '@Ticket_Id int OUTPUT'
set @sql = 'select @Ticket_Id = Ticket_id
from openquery(Journal, ''Select max(Ticket_id) as Ticket_id from dba.h_ticket where register = 9 and
ticket = '+Convert(varchar(12),@Ticket)+' and tick_datetime = ''''' + Convert(varchar(30),@tick_datetime,120) + ''''''')'
execute sp_executesql @sql, @ParmDef, @Ticket_Id = @Ticket_Id OUTPUT

It just seems sloppy to me and inefficient to have the hit the db twice for one transaction. I appreciate your help though. I am not that up on SQL Anywhere and working with it as a linked server.

ASKER CERTIFIED SOLUTION

Rajeev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edfreels

ASKER

Thanks rajeevnadanmishra. That would work if my process was the only one inserting transactions into the database. Unfortunately, there is another process that is inserting transactions as well. so it may have incremented by many numbers between 2 of my inserts. Great idea though.

Rajeev

You do need to set the correct SEED value @ once in the starting. (by verifying on the sybase)

Rajeev

Ah! In my case it was just one procedure which takes the parameter for inserting the data in Access. And the same has been used through-out the application for inserting data.

Good Luck to you for some better move.

edfreels

ASKER

Rajeevnandanmishra,

It looks like your response about having to run a separate 'select' is my only option. I haven't gotten any other solutions, so I will award you the points. Thanks for your help.