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!
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?
I 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?
I want to connect to an Informix Database via SSIS to read and store data. I did some search online and have downloaded Informix ODBC Drivers. I installed the drivers and am trying to configure a System DSN but have few doubts on what each field indicate. URL... I am not sure on what to enter in fields like IBM Informix Server, Service Name? Connecting to Informix DB via SSIS?
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).
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.
I ´d like to know if there is any way to access an Informix database from sqlserver 2005.
We are trying to build a data warehouse in SS2005, and I want to know If I could avoid the generation of hundreds of flats files from Informix and loading them into SS2005, directly recovering all information from SS2005 across any kind of migration or connection.
I have an application that creates a new database during installation,and I've noticed some strange behavior. Once I've created thedatabase, I am able to immediately create tables and populate lookupdata, provided I remain connected to the server. If, however, Idisconnect and attempt to reconnect immediately, I'll get an errorsaying that my login is invalid for the new database.I can get around this by having my code simply wait 5 seconds beforeattempting to reconnect, but I'm curious to see if anybody here cangive an explaination for why this is happening. Here is a bit ofpseudo code to explain what I'm seeing:open new connectioncreate databasecreate tablespopulate tablesclose connection// open new connection /* can't do this yet, as it would break */for (int a=0; a<5; a++){Thread.Sleep(2000)try{open new connectionbreak;}catch{Debug("still waiting...");}}Running my version of this code, I'll see that "still waiting..."message go past 2-3 times before SQL Server wakes up and realizes thatI'm allowed to connect to it. Anybody know why?Thanks,Jason KesterExpat Software Consulting Serviceshttp://www.expatsoftware.com/---Get your own Travel Blog, with itinerary maps and photos!http://www.blogabond.com/
Please 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.
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'
I was able to connect to the SQL Database Pension with table clients with table values: ID, State, Name.'Create(connection)Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)'open connectionconn.Open()However, I'm not sure how to insert a new row with an incremental ID number and a new State and Name.Sorry, I'm really new with VWD.
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?
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.
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
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'
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.
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.
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....
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.
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:
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?
Our 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 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.
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
