Oracle PL/SQL insert statement into SQL server

[llmclaughlin]

I'm having to insert data to a sql server from my stored procedures in my oracle database.
The sql tables I need to insert into all have auto increment columns as the PK. I can do the insert if I turn off the idenity on the column in sql server. In order to do this programatically I need to do the following statement:

SET IDENTITY_INSERT qpms_users@mysqlserverdsn ON;

The questions is, how can I phrase this in my stored procedure for it to work?

I already an ODBC and database link to the sql server.

Thanks

Louie

 

[Thargy]

I don't think you can.

To my knowledge, setting identity insert on is used from OSQL, and only means something therein. Putting semi colons at the end of OSQL statements is (if memory serves) not allowed.

I do not think that any stored procedure in one technology could be expected to issue a scripted command in another.

I have two suggestions. The first is to examine why you want to do this in the first place. If the information is in oracle, why not just get at it via ODBC, why duplicate it somewhere else, with all the problems which that will cause?

Second, it might be possible to get Oracle to invoke a stored procedure on the sql server database, which could toggle the identity insert of the table in question. However, I don't know enough about either technology to be able to do that.

Search this forum, as I believe that someone has already posted how to get oracle to invoke an os file. If it was an OS file that invoked osql in turn, then it might do the trick.

Regards

Tharg

Grinding away at things Oracular