Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2452
  • Last Modified:

Linked Servers for Ingres and SQLServer

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
pikapi
Asked:
pikapi
  • 19
  • 14
1 Solution
 
LowfatspreadCommented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
pikapiAuthor Commented:
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
 
pikapiAuthor Commented:
So it is possible t make a linked server to Ingres? (I wasn't sure - not with all these delightful errors popping up)
0
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
If you edit the system dsn fish and you click on the test button, does it succesfully connect to ingres?
0
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
What do you get if you run this from OSQL?

SELECT * FROM openquery (fish, 'SELECT * FROM operation')
0
 
pikapiAuthor Commented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
pikapiAuthor Commented:
strangely though, using the Server Explorer in Visual Studio... I can view all the tables in the ingres database...
0
 
pikapiAuthor Commented:
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
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
View from where?  What version of ingres client do you have?  Did you check your windows path????
0
 
pikapiAuthor Commented:
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
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:


"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
 
pikapiAuthor Commented:
yes. c:\ingresII\ingres\bin is where it was installed into
0
 
arbertCommented:
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
 
pikapiAuthor Commented:
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
 
arbertCommented:
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
 
pikapiAuthor Commented:
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 19
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now