Sybase (ASA 9.0.2) db problem with multiple connections

edfreels
edfreels used Ask the Experts™
on
I have a SQL 2005 Express db that loads XML and inserts the records into a SQL Anywhere 9.0 (Sybase) linked server.  There is another application updating this Sybase db as well.  I am successfully inserting records into the Sybase db via the linked server configuration, but continue to have sporadic permission issues.  It seems to be banging heads with the other application.

Is there something special you have to configure for the ASA 9.0 to have more than one connection to the db or is it even possible?  Could there be environmental settings or specific odbc settings to allow more than one connection to have access to the db at the same time?  Any direction or help is appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you give more detail on the 'sporadic permission issues' e.g. the exact error messages you are getting would be useful.

Author

Commented:
Good point.  I guess I should break this out into 2 questions because during my testing I was having the "sporadic" issues with the inserts giving me the permission denied error. I then tried to set up on another machine to recreate, I began receiving the error that "the specified database file already in use".

The initial problem would be where I really need a resolution.  For this, Let's say I have 100 transactions to load, with that meaning 100 XML files containing 1 transaction a piece.  I kick off the stored procedure in SQL Server that loads(inserts) the data to the Sybase linked server db.  In the meantime, the other application referenced is connected to the Sybase db but making no updates at the time.  My process that I kicked off in SQL inserts approximately 40 transactions and then I receive the error:

------------------------------
"OLE DB provider "MSDASQL" for linked server "Journal" returned message "[Sybase][ODBC Driver][Adaptive Server Anywhere]Permission denied: you do not have permission to insert into "TICKET""."  
------------------------------

The number of transactions vary each time I run it before it fails, so there is no pattern there other than the failure itself and the error associated.

This may or may not be a problem with the other application, but that was my first assumption.  I have tried it using the same ODBC connection as the other application and also setting up a different ODBC connection to the "Journal" ASA db, with no success.

Could the inserts coming from SQL Server be stepping on top of itself?  Any thoughts as to why half way through the load, it gives me permission denied?

Commented:
This behaviour does seem a little odd - if all your stored proc is doing is simply repeated 'INSERT INTO ticket' statements then I would expect either all or none of of the statements to fail with that error, not for it to fail half way through.

some questions:
1) is you stored procedure as simple as described above or is it conditionally inserting into/updating different tables based on some program logic?
2) are there any triggerson the Sybase tables that your stored procedure is updating?

Author

Commented:
The stored procedure in inserting only and inserts into 3 tables for 1 transaction.  It isn't consistent on any individual table where it fails either.  I am using SQL Server's openquery function for the inserts.  Here is the statement from the proc that inserts to the ticket table statements that I pulled from the log just before the last failure:

-------------------------------------------------------------
--insert into h_ticket
                              insert openquery(Journal, 'select STORENUM,TICK_DATETIME,REGNUM,TICKET,SHIFT_SEQ,ELSDATE,CASHIER,SOURCE_DEV
                                                                                     ,SOURCE_UNT,CUSTOMER,CUST_DEMO,CUST_DATE,TRANS_TYPE,CUST_TYPE,TOTAL_AMT
                                                                                     ,TOTAL_TAX,FUEL_AMT,FUEL_VOL,SCAN_COUNT,PLU_COUNT,DEPT_COUNT,NFND_COUNT,DIRC_COUNT
                                                                                     ,TOTAL_FS,TOTAL_WIC,TOTAL_DISC,ELAPSED,DELAY,MERCH_CUST,GAS_CUST,PRINTED,DRAWERTIME
                                                                                     ,KV_SENT,COPIED,DOB_TYPE,VOID_COUNT,POS_TYPE,ML_UPDATE
                                                                              from DBA.TICKET')
                                    Select StoreNum,Tick_DateTime,RegNum,Ticket,Shift_Seq,NULL as ElsDate,Cashier,Source_Dev,Source_Unt,NULL as Customer
                                           ,'' as Cust_Demo,CONVERT(SMALLDATETIME,CONVERT(varchar(10),GetDate(),101)) as Cust_Date,Trans_Type,'' as Cust_Type
                                           ,Total_Amt,Total_Tax,Fuel_Amt,Fuel_Vol,0,0,0,0,0,0,0,0,Elapsed,0,Merch_Cust,Gas_Cust,0,0,0,'',DOB_Type,0,2,NULL as ML_Update
                                    From dbo.udf_GetTicketData(@xml)
                                    Where Ticket = @ticket and Tick_DateTime = @tick_datetime
-------------------------------------



resulting with the below query executed on ASA server:

BEGIN TRANSACTION
go
--INSERT-1001-01450206910
INSERT INTO DBA.TICKET(STORENUM,TICK_DATETIME,ticket_id,REGNUM,TICKET,
      SHIFT_SEQ,ELSDATE,CASHIER,SOURCE_DEV,SOURCE_UNT,CUSTOMER,CUST_DEMO,
      CUST_DATE,TRANS_TYPE,CUST_TYPE,TOTAL_AMT,TOTAL_TAX,FUEL_AMT,FUEL_VOL,
      SCAN_COUNT,PLU_COUNT,DEPT_COUNT,NFND_COUNT,DIRC_COUNT,TOTAL_FS,TOTAL_WIC,
      TOTAL_DISC,ELAPSED,DELAY,MERCH_CUST,GAS_CUST,PRINTED,DRAWERTIME,KV_SENT,
      COPIED,DOB_TYPE,VOID_COUNT,POS_TYPE,ML_UPDATE)
VALUES (4020,'2012-04-14 02:07:12.0',399931,9,93563,999999999999.000192,NULL,0,44,9,NULL,'','2012-04-17 00:00','S','',275.02,0,275.02,69.644,0,0,0,0,0,0,0,0,249,0,0,1,0,0,'0','','N',0,2,NULL)
go
--COMMIT-1001-01450207039
COMMIT WORK
go
--------------------------------
I am also using XACT_ABORT ON before executing the stored procedure for DTC.

There are no triggers on the Sybase tables.

Commented:
Cant really think what could be happening on the ASA side to cause this so I suspect it's a problem with the way that SQL Server is connecting via OpenQuery.

Is it possible that the nature of OpenQuery might cause repeated connections to the ASA database and under certain circumstances the connection is made as a user who does not have INSERT permission?  Can you see in the transaction log on the ASA side which user was connected at the point that the INSERT failed and then confirm that the user in question has the correct permissions?

Author

Commented:
I'm not aware of openquery causing repeated connections, but I can't say for sure.  Maybe I'll post this under SQL Server too, if the possibility is the linked server functionality on SQL Server.  

I'm using the dba account for the connection, so permissions shouldn't be an issue.  I don't have Sybase Central installed on the test machine where this is happening.  Do you know of a way to monitor the logins other than SC?
Commented:
You can record connects/disconnects by adding the '-o' and '-z' switches to the Sybase engine (dbeng9.exe/dbsrv9.exe) e.g.

dbeng9.exe -o c:\temp\sybase-log.txt -z [...existing parameters...]

Then look in the specified log for the details

Author

Commented:
ok, great!  I'll try that and see if that is the culprit!  I'll keep you posted.  Thanks!

Author

Commented:
it looks like there is only one connection being created.  I am taking a different approach now by writing an update/insert script to a sql file and executing with dbisqlc.  That way I don't have to deal with the inconsistency of the linked server.  It seems to be really flaky with the ASA driver and not much info out there on this inconsistency.  Thanks for your help.

Author

Commented:
Thanks again for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial