OLE DB2 Provider

Hi

Running SQL 2005 standard edition, using OLE DB2 provider from Host integration server. When connected we can acces data with select statements, but cannot browse tables from import wizard in SSIS.

Is there a hint or solution?

rgds

It's probably the provider. The Enterprise Edition of Sql Server comes with a DB2 provider. You could also use an ODBC connection instead and see if that works. Finally, IBM might have a provider as well?|||Do you get an error message? I'm using the MS OLE DB2 provider quite regularly and have no issues... Setup is key, though, to getting it to work correctly.

OLE DB Source Error Output

In the Input and Output Properties tab under Advance Editor for OLE DB Source, I cannot remove columns. I copied this Source from a standard template and have made the normal changes to make it work. However I keep getting this error...

Error: 0xC020837B at Load Server Security, OLE DB Source [1]: The output column "DBName" (1632) on the error output has no corresponding output column on the non-error output.

Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

DBName of course is one of the columns that no longer exist, but I can't remove. Whenever I try to remove one of the columns, I get this error...

Error at Load Server Security [OLE DB Source[1]]: The column cannot be deleted. The component does not allow columns to be deleted from this input or output. Is there anything that I can do to remove the columns? Is there just a simple setting that I can change to make this work?
If you just double click on the OLE DB source, you can choose "Columns" on the left hand side and unselect the columns you do not want in the data flow.|||There is a problem with that though because I can't make any changes within the source. I always get the error...

Invalid object name 'tempdb.dbo.Server_Roles. This is because the table isn't created yet. It gets created when the connection manager connects to each server. It is a temporary table. Thus, it won't let me make any changes that I can't make in advance editor.

-Kyle
|||

Why don't you create the table somewhere else, get it configured properly, then change the table name afterward?

|||

That doesn't work. Currently I created a table in the temp directory and pointed the connection manager towards that DB and server. I still can't click on column and I can't change anything unless I hit cancel. My guess is that it may not be connecting to the server and DB correctly. I don't get any explanation for the error, its just one of the H errors.

-Kyle

|||I guess I don't understand what you are trying to do. Please explain further with more detail.

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "

Hi All,

I have a strange problem with one of my Linked server connections. I am connecting from

SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".

This probem happens if the table has rows or has no rows (empty table).

I have tried SELECT * and count(*) along with selecting a specific column from the table.

I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.

Any suggestions on what I can try next?

Thanks.

Did you try using Oracle's own oledb provider? MSDAORA is a legacy provider and not officially supported for Oracle 10g.|||

Yes I had tried that. Found the solution that worked for me:

Provider: used the Oracle Provider

Product name: Oracle

Data source: The database name in TNSNames.ora

And here is the important part. Under the Provider Options for the Oracle provider enable (check) the "Allow inprocess" provider option.

Regards,

Scott

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "

Hi All,

I have a strange problem with one of my Linked server connections. I am connecting from

SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".

This probem happens if the table has rows or has no rows (empty table).

I have tried SELECT * and count(*) along with selecting a specific column from the table.

I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.

Any suggestions on what I can try next?

Thanks.

Did you try using Oracle's own oledb provider? MSDAORA is a legacy provider and not officially supported for Oracle 10g.|||

Yes I had tried that. Found the solution that worked for me:

Provider: used the Oracle Provider

Product name: Oracle

Data source: The database name in TNSNames.ora

And here is the important part. Under the Provider Options for the Oracle provider enable (check) the "Allow inprocess" provider option.

Regards,

Scott

OLE DB for DB2 Built on Dev run on Standard

I have built a simple package using the Microsoft OLE DB provider for DB2 with SSIS Developer. If I schedule this on a Standard SQL2005 machine the package will not run. The error is blank, just says it had an error. Can I not run this package because the provider is not installed on this machine? If so is there a work around for this?you probably have to run your package on the box that has DB2OLEDB physically installed.|||

I believe that the ole db for DB2 has to be used on enterprise edition (if i read the licesne right from the ssis extension site). I am not sure if the ms one works very well. The IBM one sucked very badly when dealing with decimals. Even they recommended not using it (IBM) as part of the Client access express in their redbooks. If you are not going to fork out the extra few thousand bucks for enterprise, try the ODBC connect from DataDirect. It is a bazillion times faster than the CLAE ODBC, and i could actually get it to work (on db2 400).

Dev edition has all rights/ features as enterprise.

OLE DB Connectivity

Do users need to be setup with the Client Connectivity
only for an application that uses OLE DB to access MS SQL
Standard Edition?
Thanks,
AnjelinaNo, Users you need to have the MDAC installed.
If you send your queries DSN-less in your Apps, they even don´t have
maintain a connection info and their computers (like DSNs)
HTH, Jens Süßmeyer.
"anjelina" <ajturner@.canada.com> schrieb im Newsbeitrag
news:023d01c37b5c$0de07f50$a001280a@.phx.gbl...
> Do users need to be setup with the Client Connectivity
> only for an application that uses OLE DB to access MS SQL
> Standard Edition?
> Thanks,
> Anjelina|||Sorry, my english sounded like this from a drunk, so
the corrected version (just for my ego :)
--
No, you only have the MDAC to be installed on the clients.
If you send your queries in your Apps DSN-less, you
even don´t have to maintain a connection info on the
clients (like DSNs)
--
"anjelina" <ajtuener@.canada.com> schrieb im Newsbeitrag
news:068701c37b67$61096740$a101280a@.phx.gbl...
Thanks a million!!!
Anjelina
>--Original Message--
>No, Users you need to have the MDAC installed.
>If you send your queries DSN-less in your Apps, they
even don´t have
>maintain a connection info and their computers (like
DSNs)
>HTH, Jens Süßmeyer.
>"anjelina" <ajturner@.canada.com> schrieb im Newsbeitrag
>news:023d01c37b5c$0de07f50$a001280a@.phx.gbl...
>> Do users need to be setup with the Client Connectivity
>> only for an application that uses OLE DB to access MS
SQL
>> Standard Edition?
>> Thanks,
>> Anjelina
>
>.
>