Microsoft OLEDB Provider Oracle

Hi,

Previously i was using oracle8i with ssis.ssis was working fine.later i have upgraded 8i to oracle 9i.now when ever i try to establish a new connection using Microsoft OLEDB Provider for Oracle.i am getting the following error "Oracle error occurred, but error message could not be retrieved from Oracle" .but i am able to access the oracle 9i database thru pl/sql and toad. what could be the problem?

Thanks

Jegan.T

We're running fine 9.2 on a development server (2005) along with SSIS packages.

What about tnsnames.ora?

|||

Hi Enric,

my tnsnames.ora is correct .bcoz i am able to login via toad/sqlplus.while upgradeing i have unistalled 8i and installed 9i . do we need to install any drivers?

Thanks

Jegan.T

Microsoft OLEDB provider for DB2 performance issue (Missing where clause on DB2 site)

Hi!

I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A

WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the “where clause”:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on … !

If anyone out there has an idea of what could be wrong, please let me know!

NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

Moving to engine to resolve linked server specific issues. With regard to the driver, this blog is probably the place to go for experts on the driver itself:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/514702.aspx

Hope this helps,

John

|||

Thanks !

The specific blog reference you gave me didn’t have any answers. But do you suggest that a raise my problem here ?

Brgds Henrik

|||If you are using WHERE clauses, you mihgt have to set the "Dervie Parameters", initialization property of the OLE DB data source to TRUE. It will instruct the provider to derive parameter info from the data source. I think you det it in the Advanced Options page of the Data Source Wizard, or maybe in the Data Links dialog. You might have to set it in the initialization string.|||

Fyi ! I have solved the problem myself. By setting option “collation compatible” to true, the where clause was executed on the DB2 site.

The option can be changed via Microsoft SQL Server Management Studio - linked servers “properties” and “server options” or by executing :

EXEC master.dbo.sp_serveroption @.server=N'DB2TSRV', @.optname=N'collation compatible', @.optvalue=N'true'

Henrik Garde

Microsoft OLEDB provider for DB2 performance issue (Missing where clause on DB2 site)

Hi!

I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A

WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the “where clause”:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on … !

If anyone out there has an idea of what could be wrong, please let me know!

NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

Moving to engine to resolve linked server specific issues. With regard to the driver, this blog is probably the place to go for experts on the driver itself:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/514702.aspx

Hope this helps,

John

|||

Thanks !

The specific blog reference you gave me didn’t have any answers. But do you suggest that a raise my problem here ?

Brgds Henrik

|||If you are using WHERE clauses, you mihgt have to set the "Dervie Parameters", initialization property of the OLE DB data source to TRUE. It will instruct the provider to derive parameter info from the data source. I think you det it in the Advanced Options page of the Data Source Wizard, or maybe in the Data Links dialog. You might have to set it in the initialization string.|||

Fyi ! I have solved the problem myself. By setting option “collation compatible” to true, the where clause was executed on the DB2 site.

The option can be changed via Microsoft SQL Server Management Studio - linked servers “properties” and “server options” or by executing :

EXEC master.dbo.sp_serveroption @.server=N'DB2TSRV', @.optname=N'collation compatible', @.optvalue=N'true'

Henrik Garde

Microsoft OLEDB provider for DB2 performance issue (Missing where clause on DB2 site)

Hi!

I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:

SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A

WHERE FIELD_A='ABC'

I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the “where clause”:

SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A

So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on … !

If anyone out there has an idea of what could be wrong, please let me know!

NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.

Moving to engine to resolve linked server specific issues. With regard to the driver, this blog is probably the place to go for experts on the driver itself:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/514702.aspx

Hope this helps,

John

|||

Thanks !

The specific blog reference you gave me didn’t have any answers. But do you suggest that a raise my problem here ?

Brgds Henrik

|||If you are using WHERE clauses, you mihgt have to set the "Dervie Parameters", initialization property of the OLE DB data source to TRUE. It will instruct the provider to derive parameter info from the data source. I think you det it in the Advanced Options page of the Data Source Wizard, or maybe in the Data Links dialog. You might have to set it in the initialization string.|||

Fyi ! I have solved the problem myself. By setting option “collation compatible” to true, the where clause was executed on the DB2 site.

The option can be changed via Microsoft SQL Server Management Studio - linked servers “properties” and “server options” or by executing :

EXEC master.dbo.sp_serveroption @.server=N'DB2TSRV', @.optname=N'collation compatible', @.optvalue=N'true'

Henrik Garde

Microsoft OLEDB Provider Error (with SQL server and ASP)

Dear all,

Still i am having problem with connecting sql server with ASP (Intranet).

Following is the code which i am using for the connection now,

Set conn=Server.CreateObject("ADODB.Connection")
conn.open "DSN=aspfirst;Uid=;PWD=;"

The error mesage , generated by this code is attached with this mail. Please check the attachment and hel me in the same

Gracesonhttp://support.microsoft.com/default.aspx?scid=kb;en-us;q306518 To resolve.

HTH|||Thank you so much.

I could resolve the problem.

It was just add user IUSR_mechinename fromuser list.

Good finishing for a marathon...

bye

Thank you

gracesonsql

MICROSOFT JET OLEDB 4.0?

hi i have another question.. is this connection provider supported on servers running under 64 bits? if not? which provider should i use instead? is there any service pack or upgrades for this ?

i think this is the problem running my job.. there's no other option left.. it is not permissions.. the path of the connection manager exists, the file destination does exist as well.. the curious thing is that my dts runs fine from SSIS but fails using the job..

do i need to have installed excel in my server to do this or any other component?

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"|||

thaaaaaaaanks thanks thaaaank youuuuuuuu this was the solution for all my problems... i own you one!!!!!

regards

|||

DTEXEC does not work well with Excel on my system

I am using DTEXEC utility to run the packages on 64-bit system. One of the packages has connection to Excel. The package runs fine from SSMS but return error “Class not registered” OLE DB error has occurred. Error code: 0x80040154.

The package is stored in MSDB. I use following syntax to run the package:

dtexec /sq DTSpkg

Any help?

|||Have you read the discussion above?

There is no 64-bit OLEDB provider, so you need to use 32-bit DTEXEC:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" <rest of the arguments>|||

Hi Michael, Thanks for help. Connection to Excel works. Can you take a look on another problem bellow?

I use Flat File Connection manager in different package. The package creates a file on other server on completion. The package runs successfully from SSMS but returns error when run with dtexec /sq DTSpkg

Description: The file name "\\SERVER2\FTProot\PackageOK.RCV" specified in the connection was not valid.

MICROSOFT JET OLEDB 4.0?

hi i have another question.. is this connection provider supported on servers running under 64 bits? if not? which provider should i use instead? is there any service pack or upgrades for this ?

i think this is the problem running my job.. there's no other option left.. it is not permissions.. the path of the connection manager exists, the file destination does exist as well.. the curious thing is that my dts runs fine from SSIS but fails using the job..

do i need to have installed excel in my server to do this or any other component?

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"|||

thaaaaaaaanks thanks thaaaank youuuuuuuu this was the solution for all my problems... i own you one!!!!!

regards

|||

DTEXEC does not work well with Excel on my system

I am using DTEXEC utility to run the packages on 64-bit system. One of the packages has connection to Excel. The package runs fine from SSMS but return error “Class not registered” OLE DB error has occurred. Error code: 0x80040154.

The package is stored in MSDB. I use following syntax to run the package:

dtexec /sq DTSpkg

Any help?

|||Have you read the discussion above?

There is no 64-bit OLEDB provider, so you need to use 32-bit DTEXEC:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" <rest of the arguments>|||

Hi Michael, Thanks for help. Connection to Excel works. Can you take a look on another problem bellow?

I use Flat File Connection manager in different package. The package creates a file on other server on completion. The package runs successfully from SSMS but returns error when run with dtexec /sq DTSpkg

Description: The file name "\\SERVER2\FTProot\PackageOK.RCV" specified in the connection was not valid.