I found a solution. Maybe someone could expand on this, because I don't fully understand it.
When I opened Management Studio as MyMachineName\SQLEXPRESS which I normally do, attached the database there, I couldn't access it through MyMachineName\SQLEXPRESS in the connection string.
However, when I reopened Managment Studio as MyMachineName, attached the database there, and removed the \SQLEXPRESS from my connection string, I can now access it.
Problem solved.
We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL Server instance for warehousing purposes, and was told by our resident SME that this was impossible; only a single Informix instance could be referenced per server.
I would consistently receive the same errors as you, and nothing I was able to dredge up online proved helpful.
Ultimately, the solution had several parts, of which you've done the first few:
- The correct
IBM Informix ODBC Driver must be installed on the database server.
- Use the SetNet32 application provided with the drivers and set up the Server Information tab appropriately. Additionally, set up a corresponding System DSN entry.
The trick with the Linked Server setup is to use the following (items in codeblocks are literal):
- Provider:
Microsoft OLE DB Provider for ODBC Drivers
- Product Name:
Ifxoledbc
- Data Source: $YourSystemDSNName
- Provider String
- Driver =
{IBM INFORMIX ODBC DRIVER}
- Database = $YourDatabaseName
- etc...
If you're more of a manual SQL kind of person, see below. The provider string is pulled into a variable for inline commenting purposes only, while still having executable code. Modify as needed:
DECLARE @provider NVARCHAR(4000);
SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};'
+ N'DATABASE= ;' --Informix DB name
+ N'HOST= ;' --Informix Hostname or IP adddress
+ N'SERVICE= ;' --Informix service name, ex. ifx1_tcp1
+ N'PROTOCOL= ;' --Informix protocol, ex. onsoctcp
+ N'SERVER= ;' --Informix server, ex. ifx1
+ N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; --Change to your locale, as needed
EXEC master.dbo.sp_addlinkedserver
@server = N'UCCX', --Linked Server system name
@srvproduct=N'Ifxoledbc',
@provider=N'MSDASQL',
@datasrc=N' ', --Your System DSN Name
@provstr= @provider;
Since you're dealing with a single instance, it should be enough to simply match the values used to create your System DSN. If you need to add additional linked servers for other instances, that can be done following exactly the same format described above without additional DSN entries.
I hope that helps.
Best Answer
I'm not immediately sure how to interpret the 'nativecode=08004' part of the message shown. In decimal, error -8004 is about 'Illegal FLOAT constant' and is for one of the Informix SQL compilers. In hex, it corresponds to 32772 decimal, but that isn't a known error number. If it is an SQLSTATE value, then … hmmm, I'm not sure how to find out the interpretation of it.
I did a rather specialized search and came across:
I suspect, but without much actual evidence at the moment, that the second bullet point might be applicable.
There is some information missing from the question which I regard as crucial:
If you have support for your Informix product, you should probably contact IBM/Informix Technical Support. If you don't, you might do better via the IIUG mailing lists. It is free to join and you can choose which lists you receive. You probably want the 'ids@iiug.org' mailing list.
Failing that, over time, and possibly with increasingly fiddly diagnostics, we might eventually get to the bottom of the trouble. But to do so, I'll need answers to at least some of the questions above.