Add Informix linked server

I used the following syntax and was able to add a linked server to an
Informix database.
EXEC sp_addlinkedserver
@.server = 'Server1', -- defined in
-- SetNet32 on tab 'Server information',
-- field 'Informix Server'
@.provider = 'MSDASQL', -- DO NOT CHANGE !
@.datasrc = 'jaco', -- name of the
-- ODBC connection defined in step 3
@.srvproduct = 'Informix-CLI 3.12 (32 bit)';
Server1 is the Informix host defined in Setnet32. When I run the script, the
linked server is named after the host, that is "server1" However, this link
points to a single database defined in the datasource "jaco"
now I need to rename the Linked server "Server1" to something else so that I
can add another linked server using another datasource (wfi, for example).
1. How can I rename a linked server?
2. I ran "SELECT * FROM Server1.jaco.owner.station and got this error
message:
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].
Any help is greatly appreciated.
Thanks
Bill
Bill,
1. I think you can change the linked server name with sp_setnetname. Try it.
2. Try using OPENQUERY() instead.
Jon Jahren
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:#aRY0YFyEHA.1984@.TK2MSFTNGP14.phx.gbl...
> I used the following syntax and was able to add a linked server to an
> Informix database.
> EXEC sp_addlinkedserver
> @.server = 'Server1', -- defined in
> -- SetNet32 on tab 'Server information',
> -- field 'Informix Server'
> @.provider = 'MSDASQL', -- DO NOT CHANGE !
> @.datasrc = 'jaco', -- name of the
> -- ODBC connection defined in step 3
> @.srvproduct = 'Informix-CLI 3.12 (32 bit)';
> Server1 is the Informix host defined in Setnet32. When I run the script,
the
> linked server is named after the host, that is "server1" However, this
link
> points to a single database defined in the datasource "jaco"
> now I need to rename the Linked server "Server1" to something else so that
I
> can add another linked server using another datasource (wfi, for example).
> 1. How can I rename a linked server?
> 2. I ran "SELECT * FROM Server1.jaco.owner.station and got this error
> message:
> Server: Msg 7312, Level 16, State 1, Line 1
> Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> four-part name was supplied, but the provider does not expose the
necessary
> interfaces to use a catalog and/or schema.
> OLE DB error trace [Non-interface error].
>
> Any help is greatly appreciated.
> Thanks
> Bill
>
>
>
|||Jon;
I think I dis something wrong now that I can't even establish the linked
server.
I followed the instructions fromt his page:
http://www.planet-source-code.com/vb...d=243&lngWId=5
This worked the first time in establishing the linked server. I was able to
click on Tables & Views tabs of the linked server and see all the tables in
the linked (Informix) server.
However, I wasn't able to extract data from it.
I went on the to next step following the example below:
5. Now, you have to map logins from SQL server to logins on Informix server.
Execute statement:
EXEC sp_addlinkedsrvlogin 'PNDON7', 'false', 'sa', 'login_name', 'password'
You have to put your machine login name and password of course.
I was confused as to which login_name and password to use, so I used the
login name & password I always used to access Informix database (on linked
server).
After executing this sp_addlinkedsrvlogin, I wasn't able to do anything.
Clicking on the linked server Tables or Views tab generated an error message
regarding 'sa" passowrd error.
How do I go to cleanup all this mess and start a new?
Thanks
Bill
"Jon Jahren" <jon.jahren.fightspam@.sqlkompetanse.no> wrote in message
news:%23dU888JyEHA.1396@.tk2msftngp13.phx.gbl...
> Bill,
> 1. I think you can change the linked server name with sp_setnetname. Try
it.[vbcol=seagreen]
> 2. Try using OPENQUERY() instead.
> Jon Jahren
> "Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
> news:#aRY0YFyEHA.1984@.TK2MSFTNGP14.phx.gbl...
> the
> link
that[vbcol=seagreen]
> I
example).
> necessary
>