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!
LVL 1
edfreelsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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
edfreelsAuthor Commented:
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
lcohanDatabase AnalystCommented:
"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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

edfreelsAuthor Commented:
There is no sequence function on the table or column or in the db at all.
0
lcohanDatabase AnalystCommented:
Then how the "...autoincrement identity..." can work? Are you sure that "...The table I'm inserting into has an autoincrement identity field." ??
0
lcohanDatabase AnalystCommented:
mySQL and postgres have similar Sequence function auto generated for any table that has a "autoincrement identity field" (or serial column).
0
edfreelsAuthor Commented:
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
edfreelsAuthor Commented:
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
rajeevnandanmishraCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
edfreelsAuthor Commented:
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
rajeevnandanmishraCommented:
You do need to set the correct SEED value @ once in the starting. (by verifying on the sybase)
0
rajeevnandanmishraCommented:
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
edfreelsAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.