Hey everyone,
I'm trying to hit an informix box... I've found two software packages (both priced in the THOUSANDS of dollars)... Looking for a cheap...er, a FREE way to hit this informix box via DTS.
Thank you!
hi all,
Happy new year!
is anybody here familiar with the different informix ODBC protocol
such as Sesoctcp and onsoctcp and the rest of the protocols
I think my SSIS design is affected by this protocols i'm having problems with
the unicode and non unicode conversion.
many many thanks
Joey
Help!
I'm trying to create a simple .dtsx package that imports data to SQL server 2005 from an informix 7.3 db using an ADO.net ODBC connection. I am first creating the groundwork for the dtsx package in SQL server using the wizard, and then editing the file later in visual studio.
My data source SQL in the dataflow task is simple and it works great until I hit a locked record on the Informix database.
select <coulmns>
from <table>
Where <condition>
The work around syntax for the locked row on the informix DB should be:
set isolation to dirty read
go
select <coulmns>
from <table>
where <condition>
This syntax will return the data correctly using a non-microsoft SQL editor, however it will will not parse corectly within visual studio. Interestingly enough, in visual studio I can parse the islolation and the select indenpendantly, just not in the same statement.
Has anyone come across this before? Any ideas on what I can do to resolve my problem?
Thanks in advance!
I'm having trouble using Informix 2000 database as a source. I have an OpenLink v 5.2 driver installed and a System DSN set up. I can successfully test the connection.
I added the ADO.Net DSN to the connection manager and added it to a DataReader Source. I then added the SQLCommand property. I was able to map columns (took a long time for the columns to show up) and then I get an error: "Function sequence error:". Can not save the DataReader.
I am trying to read an Informix 9.3 blob field into a TSQL (SQL Server Stored Procedure) and the data type conversion is not supported thru the MS ODBC Driver installed with Win2000/SQL2000. Does anyone know of another method to retrieve this data, or of a vendor that provides an ODBC driver that supports the Informix blob data type. Thanks!
View 1 Replies View RelatedWhat can be the reason(s) why I can't get data from a linked server using an ODBC datasource that works fast & fine from MS Access?
I have an ODBC connection (System DSN) configured for an Informix ODBC driver.
The Test button (belonging to this driver-setup) reports a successfull connection test.
Getting data from this database by linking tables in MS Access works fast and easy.
But I have tried for many days now to setup a linked server from SQL Server (2005)
Creation goes fine, but as soon as I issue a query, (e.g. 'select * from infrem723...remotetable' or using 'openquery')
I get the following error:
---
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "infrem723" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "infrem723".
---
Why is Access able to read what "MSDASQL" cannot?
I am desperate - can anybody help?Thanks a lot!
Basically, I am asking if it is possible to use SSMS (Query Analyzer window) with Informix. I am thinking linked server. If I have the ODBC driver installed on my workstation (win 7 pro VM workstation -- SSMS on same VM machine) is this doable? Do I just do the standard linked server routine or is there a special way (if it is even doable).
View 4 Replies View RelatedAny SQL Server people out there with Informix experience?
View 7 Replies View RelatedPlease help. I am trying to translate this to SQL 2005. This language is in Informix SQl. I do not know anything about informix. I am especaially confused with the "JOINS". Any help would be appreciated. THANKS.
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd
FROM property,
address,
ppi,
outer code_table state,
outer code_table country,
code_table prop_role
WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
{AND (property.eff_to_date IS NULL OR property.eff_to_date >= TODAY)
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= TODAY)
AND ppi.eff_from_date <= TODAY}
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = "OWNER"
AND property.pact_code <> 'PERS'
ORDER BY ppi.id
I have downloaded the datadirect ole db provider for informix 7.x and am need the correct sp_addlinkedserver syntax to get my sql server 7 and informix hooked up. Can anyone help?
Thanks!
Dean
Does anyone have any pointers on how i may import an Informix database into my SQL 2000 server?
What ODBC driver would i need?
Can i connect via DTS to import the data?
The application is not going to be moved, i am only interested in getting the data into a SQL2000 environment.
TIA
Hi,
I want to transfer data from an Informix database using DTS. What driver do I need to use for that? What are the settings for the Data Source Name? Has anyone of you accomplished DTS from Informix to SQL Server?
Let me know if you have any ideas that you can share.
Thank you.
Vivek
My task is to convert this Informix SQl Script to SQL 2005, unfortunately I am not really familiar with Informix SQL. Below is an example script. I can figure out the "Select" statement and the "Where" statement, but the "From" statement is giving me a hard time. Any assistance would be appreciated.Thanks
set explain on;
UNLOAD TO '/export/home/permit/propinact.txt'
SELECT distinct property.parcel_number,
property.id,
property.eff_from_date,
property.eff_to_date,
property.situs_address_id,
legal_lines.legal_desc_line,
tax_code_area.tca_number,
usecd.value,
ct.code_description,
property_char.value,
property.property_status_cd,
legal_description.section,
legal_description.township,
legal_description.range,
property.pact_code
FROM property,
OUTER (legal_description, legal_lines),
outer property_char,
outer (property_char usecd, code_table ct),
tax_code_area,
prop_valuation
WHERE property.id = prop_valuation.property_id
AND property.id = legal_description.property_id
AND property.eff_to_date <= TODAY
AND legal_description.id = legal_lines.legal_id
AND legal_lines.line_nr = 1
AND tax_code_area.id = prop_valuation.tca_id
AND property.id = property_char.property_id
AND property_char.prop_char_typ_code = "SIZE"
AND property.id = usecd.property_id
AND usecd.prop_char_typ_code = "USECD"
AND usecd.value = ct.code_table_cd
AND property.pact_code <> 'PERS'
ORDER BY id
Hello,
I'm getting the below errors when using sql server's data import wizard. Please Note: My test connection was successful and the error occurs when I click the "Next" button after closing the properties window. For Security reasons I've filled the User Id and DataSource with a bogus account, database, and server i.e. xxxx. Notice the password is blank?... Is that normal? I'm in a jam and appreciate your help.
Thanks,
Matt
===================================
Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=xxx;Data Source=xxx@xxxx". (SQL Server Import and Export Wizard)
===================================
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)
------------------------------
Program Location:
at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(OleDbConnection myDestConnection)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)
We are using Informx for our transactional DB and want to be using reporting services to provide reports. We have some using ODBC directly to Informix, but need to start extracting data from Infomix
and aggregating it in SS 2005.
Is anybody else doing this?
Where can I find information of how to do this? We are totally new to SSIS.
Thanks
JPS
I need to create packages that connects to a client's Informix DB server. When I tried to create a new OLEDB datasource from configuration manager, I could not find any Informix driver option. Can someone shed some light as to what I'm missing and the steps I need to take to be able to source data from Informix? Any drivers that needs to be installed and configured on the server and the developer's machine?
Thanks in advance.
PS. I discovered from the link below that IBM's Informix OLEDB driver does not support SSIS. Can someone validate this claim and what alternatives I have?
http://ssis.wik.is/Data_Sources
I am having trouble using an OLE-DB source in my data flow task. I am using Informix. In the OLE DB source editor I get the following message:
Warning at {4f24a418-f95e-4185-8989-4f960aaabd68} [OLE DB Source [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Using the preview button in the OLE DB source editor, it maps the integers to DT_I4 so most of the numbers in the preview convert as null and that is how they end up in the target. I added a data conversion component and they still end up in the target as null.
I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.
When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.51650
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.
I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.50938
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?
Hi,
I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".
When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.
How can I use DDL statements via ODBC?
I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.
Thanks for help!
Regards,
Stefan D.
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?
Hi ,
I am an Informix user who has shifted to SQL Server,,,,I just wanted to know the equivalent of 'ISOLATION TO DIRTY READ' as in Informix in SQL SERVER....
Thanks,
Visitor
Hi,
Could anyone tell me how the data can be ported from informix to sql server ?
Is there a way to run the informix query from sql server ?
Thanks,
Vijay.
Hi all,
Anyone here ever used the Informix database and can give me some differences between Informix and SQL.
One of our users is thinking about purchasing a COTS product that only supports an Informix database. I need to convince the user to evaluate other rival applications that can support SQL and need some arguments in favor of not going with Informix.
Any ideas appreciated,
Faustina
Hi,
I am considdering to link a Informix database (running on SCO Unix) to a SQL Server 2005. Only problem is ... I have no idea how to do this! The reason I'm attempring this is to try and see if it would fix a problem I'm experiencing when using Reporting Services to generate reports from a Informix data source. I've been told that setting up a linked server might fix the problem. If anyone could perhabs guide me through setting up the linked server I would be very grateful! The ODBC credentials for setting up a connection to the Informix DB as follows:
Servername : pal_tli
Hostname : server_name
Service : pal_ser
Protocal : sesoctcp
Database : db_name
Username : user_name
Password : pword
:confused:
Migrate an Informix table (and eventually an entire database) to MS SQL Server 2012. This is my first experience using SSIS. I started off using the SSIS Import/Export Wizard to create a package. I was actually able to successfully copy a table, but it just didn't copy all the data. It skipped all the data that was in string columns. It created the columns with the proper datatype in the destination table, it just didn't copy the data. The other columns all copied properly. I need to do to get the string column data to copy?
View 7 Replies View RelatedOur Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting.
SQL is an obvious choice because we have it already for our retail system.
However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem.
We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores.
What we don't want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work.
The problem is that if a store doesn't post their transactions e.g. because of a system failure then these will get missed.
What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night.
We thought of adding a flag onto the Informix server but we are not able to make any modifications to it.
I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server.
With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link......
Hi, folks!
I want to pump data from Informix to SQL Server using DTS to fill / fresh up my SQL-Server data warehouse every day.
In the SQL documentation I found "Don't use the ODBC driver from Informix"
Which is a good choice of ODBC/OLE-DB drivers to deal with Informix via SQL servers DTS?
Regards from Berlin!
SeBaFlu
Dear All,
I'm using the DTS to transfer data from Informix 7.30 UC9 to MS SQL 2000 Enterprise. The tables I'm transferring contains data for BaaN ERP.
While transferring certain tables, I am successful. On some, I get an error that says "Data overflow". It was caused by "DBTYPE_DBTIMESTAMP".
Any idea why this happens?
Thanks!
Hi all. Informix and DB2 support something called synonyms that allow you to basically create sort of an alias for a table at the database level. Think of it sort of as a shortcut or link to a table. Does SQL Server 2000 have a similar ability and if so how?
I know someone will ask why you want to do this, so heres a quick example:
If you have one legacy application that expects to write to one particualr table, but you wish to partition that table across several tables, you can break table1 up into tablea,tableb,tablec and then create a synonym called table1 that would point to only the appropriate table at the appropriate time. This way you can break a huge HUGE table up into logically discreet smaller tables and manage the creation of the appropriate synonym in some wrapper that sits in front of the legacy application...thus allowing you to retool a table that has outgrown its original design without having to crack open dreaded legacy code.
So, anyone?
Hi everyone. I'm trying to switch from using Access, to using Sql Server to create my databases. The first thing I'd like to do, is connect to our CMS (Call Management System) to start creating reports on some of our data. I can't seem to figure out how to connect to it though. In Access, I use an ODBC connection. I installed a "Generic 32 Bit Driver" and I created a .dsn file called Lucent.dsn that Access uses to connect to the Informix database. I guess my question is, what is the connection string and where does it go? I've searched on this forum and on a few others and I can't seem to find help for someone as new as me!
Thanks for any help!
Dave
Windows XP, Office XP
Hi everybody!I'm working on a migration from Informix to SQL Server... I'm doing itthrough DTS and ODBC.What I'm doing is using the DTS, my data origin is tha Informixdatabase and mi target is the SQL Server database that I just created,this is via IBM Informix ODBC and I copy the tables... but the resultsare something like this:--INFORMIX--create table r_cap_dbt_uni(num_db smallint ,cve_uni char (8) ,fecha_mens datetime YEAR to MONTH ,valor float ,primary key ( num_db, cve_uni, fecha_mens ) ,foreign key ( num_db )references cat_db_tec ( num_db ));-- SQL SERVER --create table r_cap_dbt_uni(num_db smallint ,cve_uni char (8) ,***********************************************fecha_mens smalldatetime ,valor float ,***********************************************primary key ( num_db, cve_uni, fecha_mens ) ,foreign key ( num_db )references cat_db_tec ( num_db ));as you can see the float and datetime year to month data types changelike the example before.... I changed the "float" datatype manually to"decimal" datatype... but I don't know what to do with the "datetimeyear to month" Informix datatype, I mean, I dn't know why the DTSchanges this datatype to "smalldatetime"... how can I map this? what'sthe process?Thank you very much!Rodolfo
View 3 Replies View RelatedI downloaded and installed the Informix Client SDK 3.0 and was able to extract data from an Informix 10 database. All of a sudden, it stopped working. The error message I received is "... error in initializing provider. (-25580) System error occurred in network function.". Has anyone encountered this error before and know of a solution?
Thanks.
Hello,
I am currently in the process of converting an ASP.net app from an Informix backend to SQL Server 2005.
Informix has an OUTER keyword which wraps a table in a query and allows it to return NULL.
i.e. this:
SELECT * FROM Table1 OUTER(Table2) WHERE Table1id = Table2id
is the T-SQL equivalent of :
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1ID = Table2ID
However Informix has something called a 'Nested Simple Join' which looks like the following :
SELECT * FROM Table1 OUTER(Table2, Table3) WHERE Table1id = Table2id AND Table2id=Table3id
Would anyone know the T-SQL equivalent of the above - any help would be greatly appreciated.
THANKS