Solved

capturing SCOPE_IDENTITY() from linked ASA 9 server with openquery

Posted on 2012-03-28
13
1,093 Views
Last Modified: 2012-04-16
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!
0
Comment
Question by:edfreels
  • 6
  • 4
  • 3
13 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 37778918
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
0
 
LVL 1

Author Comment

by:edfreels
ID: 37778984
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?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37779017
"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.
0
 
LVL 1

Author Comment

by:edfreels
ID: 37779052
There is no sequence function on the table or column or in the db at all.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37779070
Then how the "...autoincrement identity..." can work? Are you sure that "...The table I'm inserting into has an autoincrement identity field." ??
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37779089
mySQL and postgres have similar Sequence function auto generated for any table that has a "autoincrement identity field" (or serial column).
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:edfreels
ID: 37779090
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.
0
 
LVL 1

Author Comment

by:edfreels
ID: 37779231
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.
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 37779265
Hi,
I think that you need to use a separate "select" for fetching the last update value.
One roundway though, i had used in one of my project. I needed to insert data in an Access db.
I created a local table in SQL (with an identity field and a dummy column). Each time i needed to insert data in Access, i also enter a dummy record in my table. And i used @@identity in SQL to identify the record. It works for me atleast then.
0
 
LVL 1

Author Comment

by:edfreels
ID: 37779278
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.
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37779279
You do need to set the correct SEED value @ once in the starting. (by verifying on the sybase)
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37779291
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.
0
 
LVL 1

Author Comment

by:edfreels
ID: 37850955
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.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now