Solved

capturing SCOPE_IDENTITY() from linked ASA 9 server with openquery

Posted on 2012-03-28
13
1,079 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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
There is no sequence function on the table or column or in the db at all.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
mySQL and postgres have similar Sequence function auto generated for any table that has a "autoincrement identity field" (or serial column).
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:edfreels
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL - Leading zeros 7 39
Complex SQL 10 32
SQL Script to find duplicates 16 19
Group by and order by clause 28 35
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

743 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

10 Experts available now in Live!

Get 1:1 Help Now