Oracle linked server

Hello,
I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
database.
I've set up the linked server and necessary permissions and it has been
working successfully for months. However occasionally after rebooting the
machine I receive this message when attempting a query from the linked
server:
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
I can still connect successfully through either Oracle Net Configuration
Assistant or an ODBC DSN and the only way I know how to solve this is by
stopping and restarting the SQL Server instance. The only thing I can think
of is that during server startup, the SQL Service gets started before the
oracle service starts, and somehow SQL caches this connection.
Has anyone experienced this? If so, what's the best way to deal with this
without stopping the server?
Thank you,
Aaron Lowe
In some similar situations, reinstalling the Oracle client
on the server when logged in using the service account has
fixed this issue.
-Sue
On Tue, 12 Apr 2005 10:28:46 -0500, "Aaron M. Lowe"
<alowe@.uic.edu> wrote:

>Hello,
>I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
>database.
>I've set up the linked server and necessary permissions and it has been
>working successfully for months. However occasionally after rebooting the
>machine I receive this message when attempting a query from the linked
>server:
>Server: Msg 7399, Level 16, State 1, Line 3
>OLE DB provider 'MSDAORA' reported an error.
>[OLE/DB provider returned message: Oracle client and networking components
>were not found. These components are supplied by Oracle Corporation and are
>part of the Oracle Version 7.3.3 or later client software installation.
>Provider is unable to function until these components are installed.]
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>I can still connect successfully through either Oracle Net Configuration
>Assistant or an ODBC DSN and the only way I know how to solve this is by
>stopping and restarting the SQL Server instance. The only thing I can think
>of is that during server startup, the SQL Service gets started before the
>oracle service starts, and somehow SQL caches this connection.
>Has anyone experienced this? If so, what's the best way to deal with this
>without stopping the server?
>Thank you,
>Aaron Lowe
>

Oracle Linked Server

Does anyone have any experience with Oracle linked servers? We are
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Shoul
d
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:

>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Shou
ld
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?

Oracle Linked Server

Does anyone have any experience with Oracle linked servers? We are
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Should
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Should
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?

Oracle Linked Server

Hi,
I have an Oracle 9i client installed on SQL Server 2000, and I want to add a
linked Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB
error trace: initialize returned 0x80004005', and I can not view the tables
or views in the Oracle lin
ked server, can anybody help me on this?
HongHi
0x80004005 is usually a general error, have you checked the connection
string?
http://www.connectionstrings.com/
John
"Hong Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:8AB9BCEB-F6C5-4AD6-ACA2-0A3C0A8890CC@.microsoft.com...
> Hi,
> I have an Oracle 9i client installed on SQL Server 2000, and I want to add
a linked Oracle server to SQL Server, I got an error says 'OLEDB error,
OLEDB error trace: initialize returned 0x80004005', and I can not view the
tables or views in the Oracle linked server, can anybody help me on this?
> Hong|||Try turning on trace flag 7300 on or use SQL Profiler to capture the
OLEDB Errors event. This should allow you to get more information
about the error.
For additional info, you may want to refer to the following:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 2 Jun 2004 13:01:08 -0700, Hong Wang
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have an Oracle 9i client installed on SQL Server 2000, and I want to add a linked
Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB error trace: i
nitialize returned 0x80004005', and I can not view the tables or views in the Oracle
li
nked server, can anybody help me on this?
>Hong

Oracle linked server

Hello,
I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
database.
I've set up the linked server and necessary permissions and it has been
working successfully for months. However occasionally after rebooting the
machine I receive this message when attempting a query from the linked
server:
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking componen
ts
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
I can still connect successfully through either Oracle Net Configuration
Assistant or an ODBC DSN and the only way I know how to solve this is by
stopping and restarting the SQL Server instance. The only thing I can think
of is that during server startup, the SQL Service gets started before the
oracle service starts, and somehow SQL caches this connection.
Has anyone experienced this? If so, what's the best way to deal with this
without stopping the server?
Thank you,
Aaron LoweIn some similar situations, reinstalling the Oracle client
on the server when logged in using the service account has
fixed this issue.
-Sue
On Tue, 12 Apr 2005 10:28:46 -0500, "Aaron M. Lowe"
<alowe@.uic.edu> wrote:

>Hello,
>I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
>database.
>I've set up the linked server and necessary permissions and it has been
>working successfully for months. However occasionally after rebooting the
>machine I receive this message when attempting a query from the linked
>server:
>Server: Msg 7399, Level 16, State 1, Line 3
>OLE DB provider 'MSDAORA' reported an error.
>[OLE/DB provider returned message: Oracle client and networking compone
nts
>were not found. These components are supplied by Oracle Corporation and are
>part of the Oracle Version 7.3.3 or later client software installation.
>Provider is unable to function until these components are installed.]
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>I can still connect successfully through either Oracle Net Configuration
>Assistant or an ODBC DSN and the only way I know how to solve this is by
>stopping and restarting the SQL Server instance. The only thing I can think
>of is that during server startup, the SQL Service gets started before the
>oracle service starts, and somehow SQL caches this connection.
>Has anyone experienced this? If so, what's the best way to deal with this
>without stopping the server?
>Thank you,
>Aaron Lowe
>

ORACLE Linked DB

I'm playing around with Linked Servers in SQL Server 2K and when linking to an Oracle Database I'm having very slow response time. If I just query the whole table with out a WHERE statement, it seems to go at a decent rate. If I include any parameters whatsoever, it goes horribly slow. I have used the four part name scenraio and the openquery scenerio. Also I get Date errors occasionally which could probably be corrected if I change the format of the date. Any ideas why the linked server acts so slow? If I use a DTS extract I can query the database any way I want and it runs fine. But I cannot do this because so much data changes in these particular tables in the Oracle DB that I need a live connection.1) ------------------
For your date problem, I sympathize !

I've got about the same date problem (format is not the same in both of my Databases)
I've decided to use varchar(26) types in my SQL Server db.
You should consult my threads fore more info on the dates
maybe it will give ideas !

2) ------------------
For the perfs problem maybe it's due to :
- no indexes on the "where columns"
- too much transformations to do on all the date columns
- not using pre-compiled queries

You should post your query so we can see it

Oracle link performance is horible

I have a simple select statement that I execute against an Oracle database i
n
PL/SQL and the results are, for the purposes of this discuaaion,
instantaneous.
I have that Database linked to my SQL server. If I perform that same select
statement to the oracle server:
SELECT X,Y,Z FROM SERVER..SCHEMA.TABLE
it takes 1 minute 14 seconds.
What's up with that? Any ideas, folks?select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE')
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:0CA663E4-0A8F-435D-9087-861D6B5C4580@.microsoft.com...
> I have a simple select statement that I execute against an Oracle database
in
> PL/SQL and the results are, for the purposes of this discuaaion,
> instantaneous.
> I have that Database linked to my SQL server. If I perform that same
select
> statement to the oracle server:
> SELECT X,Y,Z FROM SERVER..SCHEMA.TABLE
> it takes 1 minute 14 seconds.
> What's up with that? Any ideas, folks?|||That helped a lot. Thanks.
However. I can't use a parameterized query...can I?
DECLARE @.someValue varchar(30)
select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE WHERE
x='+@.someValue)
"CK" wrote:

> select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE')
> "David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
> news:0CA663E4-0A8F-435D-9087-861D6B5C4580@.microsoft.com...
> in
> select
>
>|||You can do it, but you'd need to make the statement dynamic sql. I don't
have an oracle connection to test it. I've done this before and you really
have to play with the quoting to get it right...but it will work.
Something like this:
DECLARE @.someValue varchar(30)
declare @.cmd varchar(1000)
set @.cmd = 'select X,Y,Z FROM openquery(oraserver,'''select X,Y,Z from
oraTABLE WHERE x='''+@.someValue)''
exec (@.cmd)
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:13E1AA87-0199-4CFA-BCA5-DB09D0945321@.microsoft.com...
> That helped a lot. Thanks.
> However. I can't use a parameterized query...can I?
> DECLARE @.someValue varchar(30)
> select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE WHERE
> x='+@.someValue)
>
>
> "CK" wrote:
>
database

Oracle LIKE

I have column C and i need to enforce the constraint
C is in the format (char)(number)(number)
eg, A12 B09 etc.
Cheers.Please ignore the previous post.

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji
When YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas

"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me o
n
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hjiWhen YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas
"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle Instant Client and DTS data transformation from Oracle

I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hjiWhen YOU ran it, it loaded the drivers off of YOUR machine. When you had
SQLAgent run it, it could find the drivers on the server. Make sure that
the SQL Server host machine also has the drivers loaded and under the
Windows User account you are running the SQL Server service accounts under.
I'm not sure how you are going to do this since you are running the service
account under Local System.
Sincerely,
Anthony Thomas
"hji" <hji@.discussions.microsoft.com> wrote in message
news:9F55BBC9-E1DF-4351-B9D9-4B92FDE74421@.microsoft.com...
I've tried to use Oracle 10g Instant Client driver so I can transport data
from Oracle to SQL Server without success so far. Anybody can enlighten me
on
this subject? Thanks in advance.
1. I downloaded and setup Instant Client according to Oracle documentations.
I modified PATH, added a few environment variables, and setup TNSNAME.ORA
files;
2. I was able to see the Oracle Instant Client driver, both from ODBC
applets within Control Panel and DTS designer's data source;
3. I created a DTS data transformation task and was able to run it. The data
was transformed successfully;
4. I scheduled this package as a SQL Server Agent job. The job fails. This
is the message I got:
Executed as user: ServerName\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string:
Specified driver could not be loaded due to system error 1114 (Oracle in
InstantClient). Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 1114
(Oracle in InstantClient). Error source: Microsoft OLE DB Provider for
ODBC Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
5. I scheduled this job as sysadmin, so I don't think access is the issue
By the way, I did all this via Terminal Services on this server. Not sure if
that has anything to do with the problem I had.
Thanks.
hji

Oracle financials on SQL Server

Does anyone know if Oracle financials can run on SQL Server and what to look
for? Thanks.
Hi
I seriously doubt it. Check with Oracle.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:33DBDB24-CA3D-4E89-9B62-D4F42377F294@.microsoft.com...
> Does anyone know if Oracle financials can run on SQL Server and what to
> look
> for? Thanks.
|||Does anyone with experience know the answer...obviously I can call Oracle
and dont' want to.
"fnguy" wrote:

> Does anyone know if Oracle financials can run on SQL Server and what to look
> for? Thanks.
|||Not that I personally have experience with this. But I did discuss it with
an Oracle Financials guy about six months ago. He said it would work (not
that he had seen it either) because it's ODBC compliant.
Danny
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:33DBDB24-CA3D-4E89-9B62-D4F42377F294@.microsoft.com...
> Does anyone know if Oracle financials can run on SQL Server and what to
> look
> for? Thanks.

Oracle financials on SQL Server

Does anyone know if Oracle financials can run on SQL Server and what to look
for? Thanks.Hi
I seriously doubt it. Check with Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:33DBDB24-CA3D-4E89-9B62-D4F42377F294@.microsoft.com...
> Does anyone know if Oracle financials can run on SQL Server and what to
> look
> for? Thanks.|||Does anyone with experience know the answer...obviously I can call Oracle
and dont' want to.
"fnguy" wrote:
> Does anyone know if Oracle financials can run on SQL Server and what to look
> for? Thanks.|||Not that I personally have experience with this. But I did discuss it with
an Oracle Financials guy about six months ago. He said it would work (not
that he had seen it either) because it's ODBC compliant.
Danny
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:33DBDB24-CA3D-4E89-9B62-D4F42377F294@.microsoft.com...
> Does anyone know if Oracle financials can run on SQL Server and what to
> look
> for? Thanks.

Oracle financials on SQL Server

Does anyone know if Oracle financials can run on SQL Server and what to look
for? Thanks.Hi
I seriously doubt it. Check with Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:33DBDB24-CA3D-4E89-9B62-D4F42377F294@.microsoft.com...
> Does anyone know if Oracle financials can run on SQL Server and what to
> look
> for? Thanks.|||Does anyone with experience know the answer...obviously I can call Oracle
and dont' want to.
"fnguy" wrote:

> Does anyone know if Oracle financials can run on SQL Server and what to lo
ok
> for? Thanks.|||Not that I personally have experience with this. But I did discuss it with
an Oracle Financials guy about six months ago. He said it would work (not
that he had seen it either) because it's ODBC compliant.
Danny
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:33DBDB24-CA3D-4E89-9B62-D4F42377F294@.microsoft.com...
> Does anyone know if Oracle financials can run on SQL Server and what to
> look
> for? Thanks.

Oracle error witrh Report Builder

Hi
I am doing reports and UDMs from a DW in Oracle and it is great (some
doubt expressed in another post).
I tried Report Builder but the new model wizard comes back with the
error:
ORA 02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ
COMMITTED }
The data source file (.ds) has been configured with most of the
connections to Oracle available (e.g. .NET Providers Oracleclient Data
provider; Microsoft OLE DB Provider for ORacle etc.) with same error.
The strange thing is that I can create the .dsv with no problem, browse
the tables, refresh with no problems.
What I am doing wrong?
cheers
EnzoYou are not doing anything wrong. Oracle is not supported for the Report
Builder (note that it is supported for the Report Designer).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Enzo M" <emartoglio@.gmail.com> wrote in message
news:1141724038.842766.151540@.j33g2000cwa.googlegroups.com...
> Hi
> I am doing reports and UDMs from a DW in Oracle and it is great (some
> doubt expressed in another post).
> I tried Report Builder but the new model wizard comes back with the
> error:
> ORA 02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ
> COMMITTED }
> The data source file (.ds) has been configured with most of the
> connections to Oracle available (e.g. .NET Providers Oracleclient Data
> provider; Microsoft OLE DB Provider for ORacle etc.) with same error.
> The strange thing is that I can create the .dsv with no problem, browse
> the tables, refresh with no problems.
> What I am doing wrong?
> cheers
> Enzo
>|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

Oracle Error When running Niku Clarity with MS JDBC

Hi All

I get a very strange error when running the JDBC in WAS6.0

This is running the Clarity 7.5.3 Application
Failed to verify database: Io exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

Any ideas

Not sure that I understand the scenario here. The Microsoft 2005 JDBC driver is a type 4 Sql Server driver. The only databases supported are Sql Server 2000 and Sql Server 2005.

|||

After working with the CA Clarity system now for some time, it is apparent that they just did not change the code to reflect another vendor, so the Oracle errors are realy just database errors

Just another case of Oracle coders thinking that their code will never move to MS SQL...

Oracle Error When running Niku Clarity with MS JDBC

Hi All

I get a very strange error when running the JDBC in WAS6.0

This is running the Clarity 7.5.3 Application
Failed to verify database: Io exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

Any ideas

Not sure that I understand the scenario here. The Microsoft 2005 JDBC driver is a type 4 Sql Server driver. The only databases supported are Sql Server 2000 and Sql Server 2005.

|||

After working with the CA Clarity system now for some time, it is apparent that they just did not change the code to reflect another vendor, so the Oracle errors are realy just database errors

Just another case of Oracle coders thinking that their code will never move to MS SQL...

Oracle error through DTS

Hi all,
I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execute
the SQL.
When I use the execute SQL task and execute the package I get the following error:
ORA-01861: literal does not match format string.
I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
Any help provided would be greatly appreciated.
Thanks
It's hard to say without see the sql you are running but
trying using OpenQuery to execute the script and see if that
works. Generally you get that Oracle error with dates and
date formats.
-Sue
On Wed, 28 Jul 2004 14:51:02 -0700, "J. Gonzalez"
<JGonzalez@.discussions.microsoft.com> wrote:

>Hi all,
>I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execut
e the SQL.
>When I use the execute SQL task and execute the package I get the following error:
>ORA-01861: literal does not match format string.
>I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
>Any help provided would be greatly appreciated.
>Thanks
|||I always forget about openquery. I'll give it a shot.
Thanks!
"J. Gonzalez" wrote:

> Hi all,
> I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execu
te the SQL.
> When I use the execute SQL task and execute the package I get the following error:
> ORA-01861: literal does not match format string.
> I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
> Any help provided would be greatly appreciated.
> Thanks
|||I'll chalk this one up as user error on my part. I didn't cast a field correctly (which explained why it didn't error out when there were no records).
J
"J. Gonzalez" wrote:

> Hi all,
> I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execu
te the SQL.
> When I use the execute SQL task and execute the package I get the following error:
> ORA-01861: literal does not match format string.
> I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
> Any help provided would be greatly appreciated.
> Thanks
|||We all do it - stare at the sql and convince ourselves it is
correct. Thanks for posting back.
-Sue
On Tue, 3 Aug 2004 08:01:02 -0700, "J. Gonzalez"
<JGonzalez@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I'll chalk this one up as user error on my part. I didn't cast a field correctly (which explained why it didn't error out when there were no records).
>J
>"J. Gonzalez" wrote:
ute the SQL.[vbcol=seagreen]

oracle error

Hope this isnt to cheeky but
I am trying to extract data from an oracle database using
access and am getting an error
'#03232, cannot alocate tablespace 7'
the oracle source is read only
can any one help me with this oracle errorTo get the best answer you should re-post this on some Oracle site, but it
looks like the Oracle database is trying to grow by allocating another
tablespace, but the database is set to read-only ( which apparently means it
can not grow.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"billy" <anonymous@.discussions.microsoft.com> wrote in message
news:1463401c413e2$d7224320$a401280a@.phx
.gbl...
> Hope this isnt to cheeky but
> I am trying to extract data from an oracle database using
> access and am getting an error
> '#03232, cannot alocate tablespace 7'
> the oracle source is read only
> can any one help me with this oracle error
>

Oracle Equivalent of SQL Server Trace

All
Can anyone recommend a Trace utility to capture SQL statements on Oracle
similar to SQL Server Trace.
Thanks--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"David" <David@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9B7E236E-0B42-4CD5-87C8-ADAD4066A8E4@.microsoft.com...
> All
> Can anyone recommend a Trace utility to capture SQL statements on Oracle
> similar to SQL Server Trace.
> Thanks|||The tool I know about the logminer, perhaps you have more luck in the Oracle
NG, asking about realtime database tracking.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"David" <David@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9B7E236E-0B42-4CD5-87C8-ADAD4066A8E4@.microsoft.com...
> All
> Can anyone recommend a Trace utility to capture SQL statements on Oracle
> similar to SQL Server Trace.
> Thanks

Oracle Equivalent of SQL Server Trace

All
Can anyone recommend a Trace utility to capture SQL statements on Oracle
similar to SQL Server Trace.
ThanksHTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David" <David@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9B7E236E-0B42-4CD5-87C8-ADAD4066A8E4@.microsoft.com...
> All
> Can anyone recommend a Trace utility to capture SQL statements on Oracle
> similar to SQL Server Trace.
> Thanks|||The tool I know about the logminer, perhaps you have more luck in the Oracle
NG, asking about realtime database tracking.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David" <David@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9B7E236E-0B42-4CD5-87C8-ADAD4066A8E4@.microsoft.com...
> All
> Can anyone recommend a Trace utility to capture SQL statements on Oracle
> similar to SQL Server Trace.
> Thanks

Oracle Equivalent of SQL Server Trace

All
Can anyone recommend a Trace utility to capture SQL statements on Oracle
similar to SQL Server Trace.
Thanks
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"David" <David@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9B7E236E-0B42-4CD5-87C8-ADAD4066A8E4@.microsoft.com...
> All
> Can anyone recommend a Trace utility to capture SQL statements on Oracle
> similar to SQL Server Trace.
> Thanks
|||The tool I know about the logminer, perhaps you have more luck in the Oracle
NG, asking about realtime database tracking.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"David" <David@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9B7E236E-0B42-4CD5-87C8-ADAD4066A8E4@.microsoft.com...
> All
> Can anyone recommend a Trace utility to capture SQL statements on Oracle
> similar to SQL Server Trace.
> Thanks

Oracle empty string == NULL behavior in SQLServer 2k5?

I'd like to have Oracle's empty string behavior in SQLServer 2k5. Oracle treats an empty string as NULL's.

In PL/SQL can do:
SELECT * FROM TABLE WHERE TABLE.FIELD IS NULL
... and it'd return rows containing NULL's as well as empty strings.

Can this be done? I couldn't find a setting for it.

Thanx

PeterAs far as I know, the only way to do this isSELECT * FROM theTable WHERE field IS NULL or field = ''|||OrSELECT * FROM theTable WHERE ISNULL(field, '') = ''|||As far as I know, the only way to do this isSELECT * FROM theTable WHERE field IS NULL or field = ''

Yep, thanx but we got this far. We were hoping to avoid typing this. Maybe some system wide setting... but I guess we'll have to learn to do it the MS way. :)|||It is less doing things the MS way and more not doing things the Oracle way. I didn't know you could do that in PL\SQL but it sure as 'ecky thump isn't part of the ANSI standard.

Anyway - a lot of people just prevent nulls from their database in the first place.


CREATE TABLE MyTable
(
MyCol VARCHAR(10) DEFAULT '' NOT NULL
)

HTH|||Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.|||Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.This issue has been discussed a lot ;)
I think it's perfectly OK.|||The war in Iraq has been discussed a lot too. I guess that makes it OK as well?

A zero-length string is distinctly different that a null value. Anyone who equates the two does not understand the concept of a null value. A null value is "unknown" or "undetermined". A zero-length string has a known value. How can you possibly say that a known value is equal to some value that is not known? You can't. Hence, Oracle's implementation is flawed.|||zustimmung|||Anyone who equates the two does not understand the concept of a null valueI do understand the concept of a null value ;)

A zero-length string has a known value.And what is this "value"?
Can you have a similar distinction for a numeric column or a date column? (i.e. an "empty" value which is not null?)

The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".

When it comes to other data types then nobody seems to want this distinction between an "empty" value and a null value. So what's so special about a character value that makes it different from other data types?|||The value of an empty string is 'empty'. The value of null is 'unknown'. There is a difference. (For one thing, one unknown value cannot be determined to be equal to another unknown value but 'empty' is definitely equal to 'empty'.)

FWIW our standard is to avoid nulls whenever an appropriate default value can be determined. Our standard default character value is the empty string so Oracle's null handling usually works for us on a practical level-but it's not ideal & definitely not ANSI-compliant. It'd be nice if you could turn that behavior on & off according to the database. Mostly I like defaulting nulls to empty strings & having a database setting for it would eliminate mistakes-but sometimes I *don't* want nulls to default to empty strings. Then, I'd like to be able to turn that 'automatic default' off.|||The value of an empty string is 'empty'. The value of null is 'unknown'.Then why doesn't anybody need this concept (empty vs. unknown) for non-character data types?
In my(!) opinion there is no difference between empty and unknown.|||I do understand the concept of a null value ;)
Ok maybe you do understand the concept of a null value. I'll give you the benefit of the doubt for now.

And what is this "value"?Zzzzzzt! Doubt removed!

The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".Zzzzzzt again! A null value means you don't have data for it. A zero-length string means there is no data for it.

In my(!) opinion there is no difference between empty and unknown.

Then your opinion is wrong.

Here is an empty box: []
Here is a box which may be empty, but which may contain $1,000,000: []

Would you say the two are of equal value? Would you trade the box who's contents are unknown for the box which is known to be empty?

Well? Deal or no deal?

Do not feel bad. You are not alone. The concept of a NULL value is strangely difficult to grasp for many people. A common post of the SQL Server forums is something like "How come when I execute SELECT * FROM TABLE WHERE COLUMNVALUE = NULL I don't get any rows returned?"|||The concept really isn't empty vs. unknown, it's 'known' vs. unknown. Empty string just happens to be a convenient default (known) value for character data. The concept exists for non-character data too, it's just more difficult figuring out the appropriate default value. What's the appropriate default for Boolean?

Oracle EM for SQL Server 2000?

Hi,
Can anyone suggest a tool that manages SQL server instances - other than SQL
Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
Manager.
I need something that will give me a view of, and manage, 50+ SQL Server
instances.
Cheers,
James G.
http://www.aspfaq.com/show.asp?id=2442
David Portas
SQL Server MVP
|||Try
DBArtisan
u can get a tial version from the DBartisan website
search in google for the toolls
i do not remember the web site.
from
Doller
|||I believe you can also use OEM, just pay and install agent into sql server
PC.
Tell us how it looks if you try it.
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:8POZe.671$TL4.582@.newsfe4-win.ntli.net...
> Hi,
> Can anyone suggest a tool that manages SQL server instances - other than
> SQL
> Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
> Manager.
> I need something that will give me a view of, and manage, 50+ SQL Server
> instances.
> Cheers,
> James G.
>

Oracle EM for SQL Server 2000?

Hi,
Can anyone suggest a tool that manages SQL server instances - other than SQL
Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
Manager.
I need something that will give me a view of, and manage, 50+ SQL Server
instances.
Cheers,
James G.http://www.aspfaq.com/show.asp?id=2442
--
David Portas
SQL Server MVP
--|||Try
DBArtisan
u can get a tial version from the DBartisan website
search in google for the toolls
i do not remember the web site.
from
Doller|||I believe you can also use OEM, just pay and install agent into sql server
PC.
Tell us how it looks if you try it.
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:8POZe.671$TL4.582@.newsfe4-win.ntli.net...
> Hi,
> Can anyone suggest a tool that manages SQL server instances - other than
> SQL
> Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
> Manager.
> I need something that will give me a view of, and manage, 50+ SQL Server
> instances.
> Cheers,
> James G.
>

Oracle EM for SQL Server 2000?

Hi,
Can anyone suggest a tool that manages SQL server instances - other than SQL
Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
Manager.
I need something that will give me a view of, and manage, 50+ SQL Server
instances.
Cheers,
James G.http://www.aspfaq.com/show.asp?id=2442
David Portas
SQL Server MVP
--|||Try
DBArtisan
u can get a tial version from the DBartisan website
search in google for the toolls
i do not remember the web site.
from
Doller|||I believe you can also use OEM, just pay and install agent into sql server
PC.
Tell us how it looks if you try it.
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:8POZe.671$TL4.582@.newsfe4-win.ntli.net...
> Hi,
> Can anyone suggest a tool that manages SQL server instances - other than
> SQL
> Enterprise Manager? I'm thinking of an equivalent to Oracle Enterprise
> Manager.
> I need something that will give me a view of, and manage, 50+ SQL Server
> instances.
> Cheers,
> James G.
>

Oracle DSV: Problems creating relationships with reported inconsistent datatypes.

I am adding tables to the DSV and adding the relationships but SSAS keeps complaining that the data types of the FK and PK tables do not match, even though I can see that they are in fact the same, ie NUMBER with no scale/precision set. Presumably this means Oracle uses a default?

Any ideas as to the fix? I have created a NamedQuery as a SELECT * FROM <table> and this seems to work but does seem to be a hack and is very annoying!

Searching connect, I found at that this is a bug. however, it was reported as fixed in SP1 but I am using SP2 CTP.. so I have added a new bug report for this under SP2.

the only workaround is to go back and edit the XML file. (View Code in the solution explorer context menu).

Oracle drivers in connection managers

I am not seeing an option to use the OraOLEDB.Oracle driver when defining connections in ssis. This driver shows up in management studio when trying to create linked servers so I don't know why it wouldn't show up here. All the client tools are installed fine and the linked server works. Any ideas?This might be a novice answer, but are you sure the actual OLE driver is installed? I'm no Oracle expert by far, but the client and the actual driver that my Oracle dba gives for sql server installation are on seperate disks.|||Yes, the oledb drivers are installed. One important thing I failed to mention is that this is a 64 bit server. What appears to be happening is that even though we can create a linked server with the 64 bit oracle oledb driver, when you create a package in ssis it only lists the 32 bit native oledb drivers. I confirmed this by installing the 32 bit oracle client stuff as well and now the oracle oledb driver is listed in the drop downs. This seems a little absurd that ssis would only use the 32 bit drivers, can anyone explain this?|||

"The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. If you want to select a specific 64-bit provider to configure a connection manager, you must have the 32-bit version of the provider installed. However, you can still run the package in 64-bit mode in the development environment. Because the 32-bit and 64-bit versions of a provider have the same ID, the runtime selects the appropriate version to use based on the value of the Run64BitRuntime project property, which by default is True."

Found in this article....

http://msdn2.microsoft.com/en-us/library(d=robot)/ms141766.aspx

|||

Thanks for the info, that's a big help.

I understand why they did this but just like every other weird x64 quirk you have to spend hours digging through various documentation sources to even find a mention of it. Please just give us a comprehensive x64 guide instead of spreading stuff out in a thousand different places.

oracle driver

Where can I download the Oracle driver for SQL Server Reporting Server? The
Oracle Client is 500MB, wasn't sure if there's something smaller for just the
driver. Thanks!I don't think there is a smaller download for the Oracle client. Make sure
you are installing version 8.1.7 or later of the Oracle client.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> Where can I download the Oracle driver for SQL Server Reporting Server?
> The
> Oracle Client is 500MB, wasn't sure if there's something smaller for just
> the
> driver. Thanks!|||Hi Robert,
Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
places. I'm running into a more critical issue with executing Oracle reports.
I kept getting the "rsErrorOpeningConnection with Oracle" when running from
the server and I know I can make the db connection from the server. And I
also double check the permission in the Oracle folder per one of your
posting. Could you think of anything else I can try?
Thank you very much!
"Robert Bruckner [MSFT]" wrote:
> I don't think there is a smaller download for the Oracle client. Make sure
> you are installing version 8.1.7 or later of the Oracle client.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> > Where can I download the Oracle driver for SQL Server Reporting Server?
> > The
> > Oracle Client is 500MB, wasn't sure if there's something smaller for just
> > the
> > driver. Thanks!
>
>|||My first suggestion is to make sure that you have rebooted your server once
after the Oracle client installation happened (I have seen some machines
where applications running inside ASP.NET, such as report server, cannot
access the Oracle client without a reboot after the client installation).
Then, if this is RS 2005, you have to apply the following steps:
* make sure the WebServiceIdentity is configured through the RS
Configuration tool; often it is set to the Network Service user. You can
also check it by looking up the <WebServiceAccount> element in the
RSReportServer.config file.
* For the specified <WebServiceAccount> apply these steps on the Oracle
client installation directory (Oracle\Oracle version):
Right-click the directory, and then Properties. On the security tab, add the
<WebServiceAccount> and grant read&execute permission. Important: click on
the advanced button and select "Allow inheritable permissions from the
parent ..." before you click OK!
Note: it is also necessary to grant the account read&execute permissions
that is used to execute the RS Windows Service. For RS 2005, both the RS
Windows Service account and the configured RS Web Service account must have
permissions to access the files of the Oracle client installation in the
file system! On RS 2000, this was generally only needed for the RS Windows
Service account.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
PS: I guess I should know a few things about RS since I'm
designing/implementing some areas of the product :)
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:6BF6E4C3-B612-454D-99A7-DDFF431A04D2@.microsoft.com...
> Hi Robert,
> Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
> places. I'm running into a more critical issue with executing Oracle
> reports.
> I kept getting the "rsErrorOpeningConnection with Oracle" when running
> from
> the server and I know I can make the db connection from the server. And I
> also double check the permission in the Oracle folder per one of your
> posting. Could you think of anything else I can try?
> Thank you very much!
> "Robert Bruckner [MSFT]" wrote:
>> I don't think there is a smaller download for the Oracle client. Make
>> sure
>> you are installing version 8.1.7 or later of the Oracle client.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
>> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
>> > Where can I download the Oracle driver for SQL Server Reporting Server?
>> > The
>> > Oracle Client is 500MB, wasn't sure if there's something smaller for
>> > just
>> > the
>> > driver. Thanks!
>>

Oracle DBA to learn MS SQL

Is there a materials such as the following for an Oracle DBA who would
like to learn MS SQL(e.g. 2005)
- Training courses
- Web sites/ebooks
- MS articles
- etc
SQL Server features and compare it with Oracle.
So, it should be something of learning method for a person with DBA
knowledge(i.e. not for a beginner!)
cheers
Sandiyansandiyan@.yahoo.co.uk wrote:

> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
Try typing this into Google:
migrate oracle microsoft sql server
It yields many results, including this:
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm|||Hi
Its really funny because I'm a SQL Server DBA is going to learn Oracle
:-))))))
Well, as for Oracle , there are lots of info about SQL Server as well
I'd start with www.microsoft.com\sql
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
http://www.umachandar.com/resources.htm-- very good site , a lot of
examples
http://support.microsoft.com/direct...B;EN-US;Q224453>--
--
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
http://support.microsoft.com/defaul...q246133--
How To Transfer Logins and Passwords Between SQL Servers (Q246133)
<sandiyan@.yahoo.co.uk> wrote in message
news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
> - Training courses
> - Web sites/ebooks
> - MS articles
> - etc
> SQL Server features and compare it with Oracle.
> So, it should be something of learning method for a person with DBA
> knowledge(i.e. not for a beginner!)
>
> cheers
> Sandiyan
>|||Check out Microsoft SQL Server 2000 for the Oracle Professional:
http://www.microsoft.com/sql/techin...os.mspx

Hope this helps.
Dan Guzman
SQL Server MVP
<sandiyan@.yahoo.co.uk> wrote in message
news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
> - Training courses
> - Web sites/ebooks
> - MS articles
> - etc
> SQL Server features and compare it with Oracle.
> So, it should be something of learning method for a person with DBA
> knowledge(i.e. not for a beginner!)
>
> cheers
> Sandiyan
>|||> Check out Microsoft SQL Server 2000 for the Oracle Professional:
> http://www.microsoft.com/sql/techin...racle-pros.mspx

That link was for Microsoft SQL Server 2005 for the Oracle Professional,
which is probably most relevant for your situation. If you are interested
in SQL 2000 too, that article is at the link below. There are enough
architectural differences in the SQL Server versions to warrant different
articles.
http://www.microsoft.com/technet/pr...y/sqlorpro.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oe%23RVUHvGHA.3552@.TK2MSFTNGP03.phx.gbl...
> Check out Microsoft SQL Server 2000 for the Oracle Professional:
> http://www.microsoft.com/sql/techin...os.ms
px
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <sandiyan@.yahoo.co.uk> wrote in message
> news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
>|||SQL Server Books online will a very good startup for learning SQL Server
Thanks
Hari
SQL Server MVP
<sandiyan@.yahoo.co.uk> wrote in message
news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
> - Training courses
> - Web sites/ebooks
> - MS articles
> - etc
> SQL Server features and compare it with Oracle.
> So, it should be something of learning method for a person with DBA
> knowledge(i.e. not for a beginner!)
>
> cheers
> Sandiyan
>

Oracle DBA to learn MS SQL

Is there a materials such as the following for an Oracle DBA who would
like to learn MS SQL(e.g. 2005)
- Training courses
- Web sites/ebooks
- MS articles
- etc
SQL Server features and compare it with Oracle.
So, it should be something of learning method for a person with DBA
knowledge(i.e. not for a beginner!)
cheers
Sandiyansandiyan@.yahoo.co.uk wrote:
> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
Try typing this into Google:
migrate oracle microsoft sql server
It yields many results, including this:
http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm|||Hi
Its really funny because I'm a SQL Server DBA is going to learn Oracle
:-))))))
Well, as for Oracle , there are lots of info about SQL Server as well
I'd start with www.microsoft.com\sql
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
http://www.umachandar.com/resources.htm-- very good site , a lot of
examples
http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--
Moving SQL Server Databases to a New Location
<http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q224453>--
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133--
How To Transfer Logins and Passwords Between SQL Servers (Q246133)
<sandiyan@.yahoo.co.uk> wrote in message
news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
> - Training courses
> - Web sites/ebooks
> - MS articles
> - etc
> SQL Server features and compare it with Oracle.
> So, it should be something of learning method for a person with DBA
> knowledge(i.e. not for a beginner!)
>
> cheers
> Sandiyan
>|||Check out Microsoft SQL Server 2000 for the Oracle Professional:
http://www.microsoft.com/sql/techinfo/whitepapers/sql-server-2005-oracle-pros.mspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
<sandiyan@.yahoo.co.uk> wrote in message
news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
> - Training courses
> - Web sites/ebooks
> - MS articles
> - etc
> SQL Server features and compare it with Oracle.
> So, it should be something of learning method for a person with DBA
> knowledge(i.e. not for a beginner!)
>
> cheers
> Sandiyan
>|||> Check out Microsoft SQL Server 2000 for the Oracle Professional:
> http://www.microsoft.com/sql/techinfo/whitepapers/sql-server-2005-oracle-pros.mspx
That link was for Microsoft SQL Server 2005 for the Oracle Professional,
which is probably most relevant for your situation. If you are interested
in SQL 2000 too, that article is at the link below. There are enough
architectural differences in the SQL Server versions to warrant different
articles.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlorpro.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oe%23RVUHvGHA.3552@.TK2MSFTNGP03.phx.gbl...
> Check out Microsoft SQL Server 2000 for the Oracle Professional:
> http://www.microsoft.com/sql/techinfo/whitepapers/sql-server-2005-oracle-pros.mspx
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <sandiyan@.yahoo.co.uk> wrote in message
> news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
>> Is there a materials such as the following for an Oracle DBA who would
>> like to learn MS SQL(e.g. 2005)
>> - Training courses
>> - Web sites/ebooks
>> - MS articles
>> - etc
>> SQL Server features and compare it with Oracle.
>> So, it should be something of learning method for a person with DBA
>> knowledge(i.e. not for a beginner!)
>>
>> cheers
>> Sandiyan
>|||SQL Server Books online will a very good startup for learning SQL Server
Thanks
Hari
SQL Server MVP
<sandiyan@.yahoo.co.uk> wrote in message
news:1155208415.129550.57030@.q16g2000cwq.googlegroups.com...
> Is there a materials such as the following for an Oracle DBA who would
> like to learn MS SQL(e.g. 2005)
> - Training courses
> - Web sites/ebooks
> - MS articles
> - etc
> SQL Server features and compare it with Oracle.
> So, it should be something of learning method for a person with DBA
> knowledge(i.e. not for a beginner!)
>
> cheers
> Sandiyan
>

Oracle DB Update Error With SQL

I keep getting this error when i try to update to an oracle table through SQL:

An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].

This is the code I'm using:

**************

DECLARE @.cmd VARCHAR(500)

DECLARE @.Link VARCHAR(100)
set @.Link = Oracle_DB'

DECLARE @.Table VARCHAR(100)
set @.Table = 'Product_table'
declare @.Factory varchar(3)
set @.Factory = 'N12'

declare @.ProdPull varchar(10)
set @.ProdPull = '10500'

declare @.Prod varchar(25)
set @.Prod = ' 44JBGG'

declare @.WW varchar(6)
set @.WW = '200704'

declare @.SchedType varchar(15)
set @.SchedType = 'SCHED 1'

SET @.cmd =
'
select * from OPENQUERY
(
' + @.Link + ',
''
UPDATE '+ @.Table +'
SET TARGET_VALUE = '+@.ProdPull+'
WHERE TARGET_LEVEL2_VALUE = '+@.Prod+'
AND PERIOD_VALUE = '+@.WW+'
AND TARGET_LEVEL_VALUE = '+@.Factory+'
AND TARGET_TYPE = '+@.SchedType+'
''
)
'

EXEC (@.cmd)

*********

Have no idea why this isn't working .. help appreciated.

You need quotes around your string values in the update.

UPDATE '+ @.Table +' SET TARGET_VALUE = '''+@.ProdPull+''' WHERE TARGET_LEVEL2_VALUE = '''+@.Prod+''' AND PERIOD_VALUE = '''+@.WW+''' AND TARGET_LEVEL_VALUE = '''+@.Factory+''' AND TARGET_TYPE = '''+@.SchedType+''''
|||

spot on that worked 100%

but now I am getting this error Unable to start a nested transaction for OLE DB provider 'MSDAORA'. A nested transaction was required because the XACT_ABORT option was set to OFF.

I tried turning XACT_ABORT on but then the update statement just doens't work.

Any ideas anybody.

Oracle DB Link in Reporting Services Query String

I am trying to select for a report some data from an Oracle table through a
DB Link, using a select statement:
SELECT a.filed1, a.field2, dbl.field3, dbl.field4
FROM table_name1 a, table_name2@.db_link_name dbl
WHERE a.filed1=dbl.filed5
However, the SQL Report Wizard returns an error â'Invalid characterâ'
(selected data source is ORACLE type). Apparently, it doesnâ't like â'@.â'
character in the string. To find a way around I have tried to create an
Oracle stored procedure which returns Oracle REF Cursor created using the
same select statement. The procedure was compiled and I changed data type in
the dataset to â'Stored Procedureâ' and query string to the procedure name.
But, again, a failure was received since it canâ't refresh the list of
parameters and fields.
Excluding reference to the DB Link from both the Select statement and the
stored procedure solves the problem, but I need the data from it.
Is there another way to retrieve the data for a report through an Oracle DB
Link?Two things to try. First off, did you do this via the generic query
designer. If not try that. Next, what happens if you put double quotes
around it. I.e.
SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
"table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
Just a wild guess whether the double quote would work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> I am trying to select for a report some data from an Oracle table through
a
> DB Link, using a select statement:
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> FROM table_name1 a, table_name2@.db_link_name dbl
> WHERE a.filed1=dbl.filed5
> However, the SQL Report Wizard returns an error "Invalid character"
> (selected data source is ORACLE type). Apparently, it doesn't like "@."
> character in the string. To find a way around I have tried to create an
> Oracle stored procedure which returns Oracle REF Cursor created using the
> same select statement. The procedure was compiled and I changed data type
in
> the dataset to "Stored Procedure" and query string to the procedure name.
> But, again, a failure was received since it can't refresh the list of
> parameters and fields.
> Excluding reference to the DB Link from both the Select statement and the
> stored procedure solves the problem, but I need the data from it.
> Is there another way to retrieve the data for a report through an Oracle
DB
> Link?
>|||Thanks Bruce,
I am afraid, I canâ't use the generic query designer, since it canâ't access a
table though a db link. I can only add a table or a view in the graphical
schema representation.
After I tried to edit the Select statement and put a double quote the syntax
problem was resolved, however I canâ't even save the query string, because of
an error message:
â'Couldnâ't generate a list of fields for the query.â' A similar message was
received when I was trying to use a stored procedure with ref cursor as OUT
parameter. It looks like because the db link table column list canâ't be
generated a critical run time error is raised and it stops execution of the
procedure.
Is there any way to solve this problem, for example to try to enter the list
of parameters and fields manually?
"Bruce L-C [MVP]" wrote:
> Two things to try. First off, did you do this via the generic query
> designer. If not try that. Next, what happens if you put double quotes
> around it. I.e.
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> Just a wild guess whether the double quote would work.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > I am trying to select for a report some data from an Oracle table through
> a
> > DB Link, using a select statement:
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > FROM table_name1 a, table_name2@.db_link_name dbl
> > WHERE a.filed1=dbl.filed5
> >
> > However, the SQL Report Wizard returns an error "Invalid character"
> > (selected data source is ORACLE type). Apparently, it doesn't like "@."
> > character in the string. To find a way around I have tried to create an
> > Oracle stored procedure which returns Oracle REF Cursor created using the
> > same select statement. The procedure was compiled and I changed data type
> in
> > the dataset to "Stored Procedure" and query string to the procedure name.
> > But, again, a failure was received since it can't refresh the list of
> > parameters and fields.
> >
> > Excluding reference to the DB Link from both the Select statement and the
> > stored procedure solves the problem, but I need the data from it.
> >
> > Is there another way to retrieve the data for a report through an Oracle
> DB
> > Link?
> >
> >
>
>|||There are some unusual things going on with Oracle. When you pick the Oracle
provider it is using Oledb provider when you are in the graphical query
designer. If you are in the generic query designer it is using the dotnet
provider. Plus, when deployed it will be using the dotnet provider. The
reason for all of this is that the graphical query designer component is a
shared component that knows nothing about dotnet. Hopefully with Widbey this
will no longer be true but for now, what I said is try.
Now, the generic query designer should act like a passthrough query. Have
you tried it since you started putting in the double quotes?
You can definitely add the fields manually (right click on the fields list).
The graphical designer does a lot with your query. That is why I suggested
the generic. I would spend some more time in the generic trying to get it to
work (generic plus adding the fields manually is the most likely combination
to work). You can always view the rdl and put the query string in that way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:F03A20D6-FE5E-479A-93B0-BBCD0B9DAA3C@.microsoft.com...
> Thanks Bruce,
> I am afraid, I can't use the generic query designer, since it can't access
a
> table though a db link. I can only add a table or a view in the graphical
> schema representation.
> After I tried to edit the Select statement and put a double quote the
syntax
> problem was resolved, however I can't even save the query string, because
of
> an error message:
> "Couldn't generate a list of fields for the query." A similar message was
> received when I was trying to use a stored procedure with ref cursor as
OUT
> parameter. It looks like because the db link table column list can't be
> generated a critical run time error is raised and it stops execution of
the
> procedure.
> Is there any way to solve this problem, for example to try to enter the
list
> of parameters and fields manually?
>
> "Bruce L-C [MVP]" wrote:
> > Two things to try. First off, did you do this via the generic query
> > designer. If not try that. Next, what happens if you put double quotes
> > around it. I.e.
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> > "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> >
> > Just a wild guess whether the double quote would work.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> > news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > > I am trying to select for a report some data from an Oracle table
through
> > a
> > > DB Link, using a select statement:
> > >
> > > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > > FROM table_name1 a, table_name2@.db_link_name dbl
> > > WHERE a.filed1=dbl.filed5
> > >
> > > However, the SQL Report Wizard returns an error "Invalid character"
> > > (selected data source is ORACLE type). Apparently, it doesn't like
"@."
> > > character in the string. To find a way around I have tried to create
an
> > > Oracle stored procedure which returns Oracle REF Cursor created using
the
> > > same select statement. The procedure was compiled and I changed data
type
> > in
> > > the dataset to "Stored Procedure" and query string to the procedure
name.
> > > But, again, a failure was received since it can't refresh the list of
> > > parameters and fields.
> > >
> > > Excluding reference to the DB Link from both the Select statement and
the
> > > stored procedure solves the problem, but I need the data from it.
> > >
> > > Is there another way to retrieve the data for a report through an
Oracle
> > DB
> > > Link?
> > >
> > >
> >
> >
> >

oracle date conversion

is really bugging me...
Found out that some date fields wont' be allowed on sql server so my syntax
is
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
this works fine when selecting but when inserting this result into a table
on sql server I'm in trouble
insert into table xx
(columns...)
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
I get an error saying use ROBUST PLAN.
then i put on Robust plan and it says The query processer could not produce
a query plan
Then I found this article that says post you troubles here...? Anyone ?
My next attempt would be to avoid linked server and use transform data task
in a DTS package to see if that helps
http://www.aspfaq.com/show.asp?id=2400Perhaps a language neutral datetime format will work better. Try converting
to the format yyyymmdd.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...

> is really bugging me...
> Found out that some date fields wont' be allowed on sql server so my synta
x
> is
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> this works fine when selecting but when inserting this result into a table
> on sql server I'm in trouble
> insert into table xx
> (columns...)
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> I get an error saying use ROBUST PLAN.
> then i put on Robust plan and it says The query processer could not produc
e
> a query plan
> Then I found this article that says post you troubles here...? Anyone ?
> My next attempt would be to avoid linked server and use transform data tas
k
> in a DTS package to see if that helps
> http://www.aspfaq.com/show.asp?id=2400
>|||Thanx for the reply but found out that it wasn't the date conversion at all.
It was a column with 4000 characters as then lenght.
When not trying to insert this column it works fine...
What to do ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Perhaps a language neutral datetime format will work better. Try
converting to the format yyyymmdd.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
syntax
table
produce
task|||Ahh, that explains the error message. You could try adding some substring to
short the number of
characters returned, I guess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> Thanx for the reply but found out that it wasn't the date conversion at al
l.
> It was a column with 4000 characters as then lenght.
> When not trying to insert this column it works fine...
> What to do ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> converting to the format yyyymmdd.
> news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
> syntax
> table
> produce
> task
>[/color]|||thanx
but I found an article mentioning trouble with row size limit / varchar
fields.
i changed the field from varchar to text and it worked.
Is there something I should now about the text field type ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#Wgp2vy0FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Ahh, that explains the error message. You could try adding some substring
to short the number of
> characters returned, I guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...
all.
in
?
data
>

Oracle Datasource with sql express version

Hi,
do you know if it's possible to connect Reporting Services, installed
with the sql express version, to an oracle datasource.
The oracle server is on the same machine.
This work fine in dev environnement but the deployment failed with this
message: "provider is not registered on the local machine".
Thank'sI don't think so. I think the express version is for reporting against
express data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<amfred@.gmail.com> wrote in message
news:1156321685.686065.70410@.m73g2000cwd.googlegroups.com...
> Hi,
> do you know if it's possible to connect Reporting Services, installed
> with the sql express version, to an oracle datasource.
> The oracle server is on the same machine.
> This work fine in dev environnement but the deployment failed with this
> message: "provider is not registered on the local machine".
> Thank's
>

Oracle database in enterprise manager SQL 2000

Can I access an Oracle database in enterprise manager SQL 2000? How do I proceed?

YOu will have to create a linked server in SQL Server 2000 using the appropiate provider. THen you can scroll through the metadata of oracle server. Querying the tables is not possible from the GUI. After creating a linked server you will have to use queries in the syntax of

SELECT * FROM LinkedServername.Database.Schema.ObjectName

(four part naming) to access the oracle Database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Oracle Data Source Problem: Cannot connect during deployment. Help!

I am developing an SSAS project which looks at an Oracle database but I cannot for the life of me get it to process.

This is one of those awful Oracle connectivity problems!

Now, in the DataSource, I can edit and the test connection works perfectly. Every other application can connect to the Oracel server correctly. The problem comes when I try to deploy my project. I get errors left right and centre. They even change between attempts! Most common is TNS name resolution error:

Error 1 OLE DB error: OLE DB or ODBC error: ORA-12154: TNS:could not resolve the connect identifier specified. 0 0

Yet, as I have said, when I test the connection everything is OK. And I can use the same connection details in every other application. What is so different about the deployment operation that means it cannot find the connection configuration?

I have tried all the providers, impersonation etc. FYI: the main method for getting the Oracle connection details is through LDAP.

OK.. I found a work around.. I put the connection details directly into the tnsnames.ora file and everything went smoothly again!

So, why does the deployment processing not look at sqlnet.ora when everything else does, including the UI/designer? I am just worried things might get difficult in a production environment.

The other interesting thing is that this has worked from my other machine, so their must be some Oracle config somewhere? Any Oracle connectivity experts out there?

|||

If you running 64 bit be aware of this thread as well --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1

Oracle data source connection problem using Report Manager

I have an issue when using Report Manager for viewing reports that use a certain Oracle data source. When viewing the reports in Report Designer the reports display without problems. But when trying to display the reports with Report Manager, after deploying the reports to the Report Server, I now get the following error message:

blablabla....ORA-12705: invalid or unknown NLS parameter value specified

For testing purposes I am running Report Designer, Report Server and Report Manager locally on a PC with Windows XP. I am using Reporting Services 2005, IIS 6, Visual Studio 2005, an Oracle 9.x client and an Oracle 9.x (not 100% sure) database that is hosted on a remote server.

Earlier I got an error message that stated "ORA-12154: TNS: could not resolve the connect identifier specified". Access rights to some Oracle folders for the Network service account did the trick here but then I got the "NLS parameter" error message. What I have tried to do is removing the NLS_LANG register variable and also modified the value of the variable so that it matches the value of the NLS_LANG variable on the server that hosts the Oracle database, but none of them have worked. Any ideas?

Maybe this is an issue that is more appropriate in an Oracle forum but what the heck..the problem could reside in Reporting Services..

Thanks, Stefan

I found the solution to the problem. I was jerking around with the wrong NLS_LANG registry variable. Everywhere I have looked it says that the NLS_LANG variable is found in the Home0 directory in the Windows registry, so naturally I have tested settings with that NLS_LANG variable. Also, the first thing I did was to search the registry for the NLS_LANG variable and the only search result was the one found under the Home0 directory. Out of coincidence, I found another NLS_LANG variable located directly under the Oracle directory in the registry. This variable was set to "NA". I changed it to AMERICAN_AMERICA.WE8ISO8859P15, and boom shakalak!

/Stefan

oracle connection using configuration file

I created a package that uses configuration file for db connections. SQL Server connection works fine, but for oracle it fails.

I m putting servername=alias from tnsnames.ora file.

the connection works fine from ide

For an OLE-DB connection the value of the config file property assignment, for connection string, would look something like this-

Data Source=ABC.XYZ;User ID=UserName;Password=secret;Provider=MSDAORA.1;Persist Security Info=True;

ABC.XYZ is the alias from tnsnames.

|||

Thanks Darren...

I exactly have the same line as you suggested. The issue was with InitialCatalog propterty. I took that out from configuration file and it worked fine.

I read somewhere only the servername, userid and password property is enough to make a connection to oracle database.

Thanks

Mahesh

Oracle Connection String Stored in SQL Server Configuration Not Working

I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages

(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).

Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!

Mark

Hi Mark,

for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.

HTH,
Ovidiu Burlacu

|||Ovidiu,

in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.

This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...

Fridtjof|||

This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.

We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?

Thanks,

Mark

|||

Here are a couple examples of how I am doing this:

Oracle adonet

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.adonet].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

-Oracle OLEDB

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

--SQL Server oledb

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

|||Mark,

since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.

Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*

Fridtjof|||

Friedel wrote:

Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof

It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.

-Jamie

|||Jamie,

I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!

Fridtjof|||

Friedel wrote:

Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof

Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.

-Jamie

|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...

Fridtjof

Oracle Connection String Stored in SQL Server Configuration Not Working

I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages

(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).

Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!

Mark

Hi Mark,

for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.

HTH,
Ovidiu Burlacu

|||Ovidiu,

in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.

This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...

Fridtjof|||

This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.

We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?

Thanks,

Mark

|||

Here are a couple examples of how I am doing this:

Oracle adonet

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.adonet].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

-Oracle OLEDB

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[one.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

--SQL Server oledb

<?xml version="1.0"?>

<DTSConfiguration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

|||Mark,

since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.

Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*

Fridtjof
|||

Friedel wrote:

Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof

It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.

-Jamie

|||Jamie,

I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!

Fridtjof
|||

Friedel wrote:

Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof

Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.

-Jamie

|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...

Fridtjof