Solved

capturing SCOPE_IDENTITY() from linked ASA 9 server with openquery

Posted on 2012-03-28
13
1,177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 40

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 40

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 40

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 40

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
 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

623 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