| Author |
Topic |
|
a_shipra
Starting Member
20 Posts |
 Posted - 2005-09-07 : 03:17:24
|
| Can anyone halp me with this?I want to link/access informix database to Sql Server stored procedure. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-07 : 04:47:11
|
| I've never tried it before, but it should work as long as you have an ODBC or OLE DB driver for Informix. Just create a linked servr like you normally would, but use the informix driver instead of SQL Server. If you are unfamiliar with linked servers, check out the topic in BOL. There are many examples of how to things up.-ec |
 |
|
|
a_shipra
Starting Member
20 Posts |
Posted - 2005-09-07 : 20:46:35
|
| Thank you for response.i have created the informix linked server.i can select,update,delete the records using sql statements but can not insert a record.it is giving following error.OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.]. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-07 : 21:05:48
|
| I'm guessing here, but it's likely that Informix did not like the UPDATE statement you wrote. Posting the statement you attempted would help.If you are trying to perform updates on a linked server by joining to a local SQL Server table, you should know that a) it is not likely to work, b) especially with lesser-known and -supported database products, c) and even if it does, the performance is likely to be extremely bad. OLE DB and ODBC drivers will further constrain what you can do with a linked server due to their generalized nature. |
|
|
|
a_shipra
Starting Member
20 Posts |
Posted - 2005-09-07 : 21:19:55
|
| my statement was this:-insert into servername.databasename.ownername.tablename (id_no) values('12345678')i suppose this is the simplest insert statement. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-07 : 21:41:03
|
 Yep, I need to learn to READ THE QUESTION a little more carefully.I know that I've had very little luck, if any, performing INSERTs like the one you're attempting (even with other SQL Servers). Also, are you certain that Informix supports the INSERT...VALUES syntax? The error indicates that the driver is expecting a FROM clause.It's possible that the ODBC driver is translating your statement into another format that is incompatible with Informix. You may want to consider getting another Informix driver if you plan on using linked servers.By the way, do you have another means of inserting data into Informix? What features do linked servers provide that other methods don't? Is DTS (Data Transformation Services) an option for you? |
|
|
|
a_shipra
Starting Member
20 Posts |
Posted - 2005-09-07 : 22:09:23
|
| Informix does support insert into values syntax.I need to insert into a table when a row is inserted into sql server table. I.e. I am writing a insert trigger.So I think that is the only way out.Please suggest if you know something else i can do in this situation.Thanks |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-07 : 22:26:55
|
| I think you need to open a ticket up with microsoft or IBM on this. I googled your error and found many similar errors with no resolution.-ec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-07 : 22:27:20
|
Well, why are you even using SQL Server if you need to put the data into Informix?One problem with your current approach is that it will not support INSERT operations that insert more than one row. Another problem is that doing this kind of thing with a trigger will incur performance problems even if it does work properly. And if you get certain types of errors, it could roll back the entire operation and cause the INSERT to fail in the SQL Server table.The approach I take in situations like this is to have the trigger insert the data into a holding table, NOT into the linked server's table. I then create a job that takes the data from the holding table and inserts it into the final table. Some of the reasons are:- The trigger is doing a simpler operation and won't interfere or impede the main function
- Network traffic is reduced or eliminated
- It allows the transfer operation to work on a larger batch at a time, which is often far more efficient than many smaller operations that achieve the same result
- You can offload or schedule the transfer to a time when the server is not as busy
- You can use another program to pull data out of the holding table into a file, and use a file import program to put it into the final destination (DTS, for example)
I would recommend setting up the holding table and create a DTS package that transfers the data from there to Informix. You can schedule the DTS package to run periodically, every hour, every 10 minutes, etc., and then clear out the holding table when it's done. DTS is a lot more forgiving of ODBC quirks than linked servers are, so it will probably be the more reliable approach. |
|
|
|
a_shipra
Starting Member
20 Posts |
Posted - 2005-09-07 : 22:47:04
|
| HiThanks For Suggestion!Actually what i am trying to do is whenever i insert a row in a sqlserver table.i need to insert a row in informix table with some related data.I am not sure if your solution still works in this case? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-07 : 23:17:21
|
| It may not, but I think it's more likely to work than using the trigger method you have now. Triggers should do as little as possible, and nothing that requires signficant CPU or network resources. If you're looking for immediate replication of data to Informix, I'm afraid there's no good way to do that. |
|
|
|
gtan
Starting Member
6 Posts |
Posted - 2009-01-21 : 10:06:22
|
| Hi, Shipra, i am trying to create a linked server to Informix, but with no luck. Could you please tell me how you do it with detail, a script will be great. thanks a lot |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-21 : 10:45:12
|
| Do you have ODBC driver for informix installed in your remote box? |
|
|
|
|
Linking informix database to Sql server