Post reply

Insert error

  • January 17, 2011 at 9:40 am

    #230553

    Hi all,

    We created a linked server from SQL server 2005 to an Oracle database 10.2. We are running a quick test to see if it works in our environment. All the sqls seem to work fine from the Management studio like the selects,updates etc. But from the application a plain insert into Oracle table using the linked server doesn't work.. We tried a few selects and it is able to read the Oracle table using the link. Just the inserts don't seem to work using the linked server. Is this a known issue? Please give me your suggestions.

    INSERT INTO aptr@linkserv (ie_id, serial_no ) VALUES ( 134, 786665 )

    Thanks

  • January 17, 2011 at 10:16 am

    #1274314

    Update permission ?

  • January 17, 2011 at 10:57 am

    #1274329

    We are having issues with the insert statement from the application.. Selects work fine though..

  • January 17, 2011 at 11:09 am

    #1274331

    newbieuser (1/17/2011)

    We are having issues with the insert statement from the application.. Selects work fine though..

    That's why I'm suggesting it's a permission issue, because you need higher permissions for an insert than for a select. Are you getting specific errors ?

  • January 17, 2011 at 11:27 am

    #1274339

    Oh okay. I created the linked server as below for all the local SQL server logins:

    EXEC sp_addlinkedserver

    'linkserv', 'Oracle',

    'ORAOLEDB.ORACLE', 'oradb'

    EXECUTE sp_addlinkedsrvlogin @rmtsrvname='linkserv',

    @useself='false', @rmtuser='orauser', @rmtpassword='orapwd'

    Created synonym for all the tables in orauser schema:

    CREATE SYNONYM aptr@linkserv FOR linkserv..ORAUSER.APTR

    go

    SELECT * FROM aptr@linkserv

    go

    Selects,inserts they all work from Management studio. But from the application, insert is not working:

    INSERT INTO aptr@linkserv (ie_id, serial_no ) VALUES ( 134, 786665 )

    Application is not returning any errors but it is not compiling either.. We are able to run selects from the application though.

    I thought if its permissions issue, inserts shouldn't work from the Management studio,right? Any suggestions please?

    Thanks.

  • January 28, 2011 at 10:06 am

    #1279807

    It's been a week, did you get this solved?

    If not, it could still be permissions since they (the application and SSMS) are being run as different users.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply