Solved

Linked Servers for Ingres and SQLServer

Posted on 2003-12-09
34
2,295 Views
Last Modified: 2012-05-04
Hi,

I'm currently trying to link a remote server using Ingres to a local server using SQLServer.  For the local SQLServer, I'm using the free desktop version available on the Microsoft website.  Therefore I'm using osql.exe in command prompt... The following is the command I'm using with osql in command prompt:

osql -E -S localhost -d master -i server.sql

where master is the name of the database on the local server (SQLServer), and the following is the code in server.sql:

USE master
GO
EXEC sp_addlinkedserver
@server = 'FISH',
@srvproduct = 'ingres',
@provider = 'MSDASQL',
@datasrc = 'fish'
GO

The problem I have is when I run the above commands, I get: "1> 2> 1> 2> 3> 4> 5> 6> (1 row affected) (1 row affected)" BUT when I run the following trigger:

CREATE TRIGGER op_trig
     ON op_temp
     FOR INSERT
     AS
     INSERT INTO FISH.operation (code, name, etc etc)
     SELECT code, name, etc etc
     FROM inserted
     DELETE FROM op_temp

where op_temp is a table on the local server and operation is a table on the remote server,I get an error: "Invalid object name 'cefish.operation'"?!?!?!?!? What am I doing wrong?

0
Comment
Question by:pikapi
  • 19
  • 14
34 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9909244
Hi pikapi,
3 or 4 part name perhaps...

Miscrosoft likes to use the

4 part name convention

ServerName.DatabaseName.Schema/OwnerName.TableName

so i expect fish to be

FISH.Database..operation

?
hth

Cheers!
0
 

Author Comment

by:pikapi
ID: 9909317
Hiya :)

I gave that a try: FISH.fish.dbo.operation

but I get the following errors basically one after the other:

ODBC Driver Load Error: neither oiapi.dll nor iiapi.dll could be loaded. LoadLibrary error code = 126 (get this one twice)

and then:

OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Specified driver could not be loaded due to system error 1114 (Ingres)].
OLE DB error trace [OLE/DB provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:  ].

???
0
 
LVL 34

Expert Comment

by:arbert
ID: 9909829
This is so cool, I thought I was the only one doing a linked server to ingres....

Do you have the Ingres Net Client loaded on your machines?  Do you have a vnode configured???
0
 

Author Comment

by:pikapi
ID: 9909859
I'm new to the Ingres scene, and have IngresII loaded... I don't know if that translates to Net Client though, unfortunately. And if Ilook in the Visual DBA, I see that under "Nodes" my "fish" database is there...
0
 

Author Comment

by:pikapi
ID: 9909872
So it is possible t make a linked server to Ingres? (I wasn't sure - not with all these delightful errors popping up)
0
 
LVL 34

Expert Comment

by:arbert
ID: 9909875
Ok, that looks good.  You have setup a system DSN that points to the VNODE?

Go into control panel, administrative tools, data source, add new system dsn, ingres....

Also, make sure that you have the Ingres service set to automatically start up.  If the VNODE isn't running, you won't be able to connect to the server....
0
 

Author Comment

by:pikapi
ID: 9909902
Ummm I can't find Aministrative tools in control panel??? I think that there is a system dsn that points to the node... is there a way to check that there is for sure?
0
 
LVL 34

Expert Comment

by:arbert
ID: 9909948
What operating system are you running.  If you don't have administrative tools, you should have a data sources icon right under control panel...You will use the data sources icon to check to see if the system dsn is setup....

The system dsn should be the same name as your data source on the linked server.
0
 

Author Comment

by:pikapi
ID: 9910000
Windows 2000 Professional.  I found it :) under Start -> Programs -> Administrative Tools for some reason? :| Under the tab "system DSN" I have Name: fish, Driver: ingres. Basically everything has the same name... the dsn is fish, the database is fish using sp_addlinkedserver, the server is also called fish... but i'm still getting those errors :(
0
 
LVL 34

Expert Comment

by:arbert
ID: 9910019
If you edit the system dsn fish and you click on the test button, does it succesfully connect to ingres?
0
 
LVL 34

Expert Comment

by:arbert
ID: 9910024
another thing, after we figure out the linked server DLL problem, dbo probably isn't the right thing--this will be your database on the ingres server.
0
 

Author Comment

by:pikapi
ID: 9910046
Sorry if I'm asking silly questions - you can tell I'm a noob at this - but how to go about editting the system dsn fish? also there is no test button, unless you mean testing the node??  If I don't use dbo, what should I actually be using? I wasn't sure what I should be putting into that spot, execpt that some examples I found used dbo...
0
 
LVL 34

Expert Comment

by:arbert
ID: 9910093
Go into your data sources.  Click on the system dsn tab.  Click on your FISH system dsn, click the CONFIGURE button.  This is where you will also configure the default VNODE, user and password, and test the config.

Usually, you store the user/password in the VNODE and just leave the DSN blank (for testing any way).

As far as DBO, it depends on your server.  It would be linked server name and the database name, usually the owner is blank, and then the table name.

Open query also works very well (from osql or isql):

select * from openquery(fish,'select * from youringrestablenamehere')


Anyway, I'm off to bed for the night--hope you get  a good start and if you have questions, I'll see them in email :)
0
 

Author Comment

by:pikapi
ID: 9916357
Testing the config seems to work fine - I get the test successful popup. So I tried changing the DBO to:

CREATE TRIGGER op_trig
     ON op_temp
     FOR INSERT
     AS
     INSERT INTO FISH.fish.operation (code, name, etc etc) ....

and

CREATE TRIGGER op_trig
     ON op_temp
     FOR INSERT
     AS
     INSERT INTO FISH.fish..operation (code, name, etc etc) ....

but both of them return the same errors as before... So I tried the openquery command:

CREATE TRIGGER op_trig
     ON op_temp
     FOR INSERT
     AS
     INSERT INTO (SELECT * FROM openquery (cefish, 'SELECT * FROM operation'))
     SELECT code, name, etc etc
     FROM inserted
     DELETE FROM op_temp

but I return the same errors?? Or maybe my SQL is wrong? (I wasn't very sure how to do the openquery part) Or maybe there's still something wrong in my database?? Please help! :(




0
 

Author Comment

by:pikapi
ID: 9916394
Ah that should be:

CREATE TRIGGER op_trig
     ON op_temp
     FOR INSERT
     AS
     INSERT INTO (SELECT * FROM openquery (fish, 'SELECT * FROM operation'))
     SELECT code, name, etc etc
     FROM inserted
     DELETE FROM op_temp

sorry minor typo...
0
 
LVL 34

Expert Comment

by:arbert
ID: 9916578
What do you get if you run this from OSQL?

SELECT * FROM openquery (fish, 'SELECT * FROM operation')
0
 

Author Comment

by:pikapi
ID: 9916786
Okay... now this is really getting bizarre... previously I've been testing the SQL stuff in a C# program (I'm creating a program that's meant to handle all this) and I decided to see if this worked in osql instead of in the program:

CREATE TRIGGER op_trig
     ON op_temp
     FOR INSERT
     AS
     INSERT INTO FISH.fish.operation (code, name, etc etc) ....

and the osql command returned: "1> 2> 3> 4> 5> 6> 7> 8> 9>" does that mean it worked or didn't work? Because doing the same command in the program returns the previous error...

After the osql response, I tried to enter data into the op_temp table which should then be transferred to the ingres table (operation) using the trigger if it's working... but I get the error: "Invalid object name 'FISH.fish.operation'"

After these delightful responses,I tried something really really simple:

select * from fish.fish.operation
and
select * from openquery(FISH, 'select * from operation')

but both return an error! surprise surprise... it was the same error as before...
ODBC Driver Load Error: neither oiapi.dll nor iiapi.dll could be loaded. LoadLibrary error code = 126
and
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Specified driver could not be loaded due to system error 1114 (Ingres)].
OLE DB error trace [OLE/DB provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:  ].

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pikapi
ID: 9916906
Ah correction: select * from fish.fish.operation gets the "invalid object name 'FISH.fish.operation'" error and select * from openquery(FISH, 'select * from operation') gets the ODBC and OLE DB etc error
0
 
LVL 34

Expert Comment

by:arbert
ID: 9916941
And you're positive that you have successfully tested the ingres driver and you can connect to the tables with Visual DBA?  It really sounds like the driver isn't installed properly.

If you search your system for oiapi.dll and iiapi.dll do you find them?  I find both of these files located in my Ingres directory and under the bin sub directory.  I ALSO HAVE THIS DIRECTORY IN THE WINDOWS PATH--you might want to check that on your system as well.

Brett
0
 

Author Comment

by:pikapi
ID: 9916983
i think you may have hit the nail on the head so to speak :| I found oiapi.dll but not iiapi.dll in my Ingres directory!!! What should I do??? Also, where prcisely do you mean when you say "windows path"?? Thanks for all your help so far Brett!!!!!
0
 

Author Comment

by:pikapi
ID: 9917066
strangely though, using the Server Explorer in Visual Studio... I can view all the tables in the ingres database...
0
 

Author Comment

by:pikapi
ID: 9917771
We've tried everything we could think of now... tested as much as possible :( Seriously can't think of what else to do :( We tried copying oiapi and naming it iiapi (apparently that's worked before here) but now we're our wits end - absolutely any suggestions would be much appreciated :(
0
 
LVL 34

Expert Comment

by:arbert
ID: 9918312
What version of the ingres driver do you have?

Windows  path--right click on my computer, choose properties, select the advance tab, click "environmental variables".  The path variable should have an entry for your Ingres-bin directory.

Brett
0
 

Author Comment

by:pikapi
ID: 9918313
Basically I can view all the remote tables and everything, it's only with the osql commands that I don't seem able to view anything?
0
 
LVL 34

Expert Comment

by:arbert
ID: 9918399
View from where?  What version of ingres client do you have?  Did you check your windows path????
0
 

Author Comment

by:pikapi
ID: 9919652
IngresII - I had my admin guy check it out for me and he says it all checks out... and sorry, but I'm still not sure I understand what you mean by windows path :(
0
 
LVL 34

Expert Comment

by:arbert
ID: 9920066
IngresII isn't the version number--the client software actually has a version (we run 2.6 and 3.5 without problems).

Go to a DOS command (start button,  run, type in CMD hit enter).  Type PATH at the prompt.  Do you see your ingres-bin directory listed????
0
 

Author Comment

by:pikapi
ID: 9923982
oops... Ingres2.5 I believe

there's a c:\ingresII\ingres\bin.... or do you mean I need a c:\ingres\bin directory there?
0
 
LVL 34

Expert Comment

by:arbert
ID: 9924146


"there's a c:\ingresII\ingres\bin.... or do you mean I need a c:\ingres\bin directory there? "  Depends on where you installed it at--the directory should be the directory you installed it in....
0
 

Author Comment

by:pikapi
ID: 9924484
yes. c:\ingresII\ingres\bin is where it was installed into
0
 
LVL 34

Expert Comment

by:arbert
ID: 9925410
Well, I'm afraid I'm at a loss here then.  Sounds like there's a possible installation issue with your Ingres client--even though you can get to tables and VDBA works.

What is the end result you're looking for?  Looking at your trigger above, I wouldn't be positive that would be a good idea on  a linked server anyway--could be painfully slow for the client waiting on the trigger.
0
 

Author Comment

by:pikapi
ID: 9925569
Well triggers was the only thing I could come up with... basically I'm waiting for data to come in on the local server, which would happen at anytime over the course of a day, and whenever something comes in, the data is copied to the relevant table on the Ingres server and another copy on a table on the local server.  It's all being done via a C# program, but i still need to get the SQL before I can write the program in C#.
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 9926049
So you can view the tables in Visual Studio, why don't you code a watch program in C# that will grab the data when it comes in and move it where it needs to go.  I still think, just from the performance I have with ingres/sql connectivity, that a trigger would be sloooow...
0
 

Author Comment

by:pikapi
ID: 9938986
Thanks for all your help and effort! And yes, I think I'll just try and write the enter code into the program... thanks :)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now