Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...sql

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...

Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...

Problem querying linked server

I have a problem querying a linked server (Oracle) from my SQL server 2000. I am able to query it normally but when I try to do it through a stored procedure i get the following message

Msg 7399, Sev 16: OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000]
Msg 7312, Sev 16: [SQLSTATE 01000]
Msg 7300, Sev 16: OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. [SQLSTATE 01000]Can anybody help me ??

Originally posted by Enigma
I have a problem querying a linked server (Oracle) from my SQL server 2000. I am able to query it normally but when I try to do it through a stored procedure i get the following message

Msg 7399, Sev 16: OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000]
Msg 7312, Sev 16: [SQLSTATE 01000]
Msg 7300, Sev 16: OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. [SQLSTATE 01000]|||Looks like you may have a problem with your access rights on either one of the servers......|||Well,

I am able to query the server when I am in the sql query analyzer.
I.E.
SELECT * INTO ABCD FROM TESTSVR..USER.ABCD

This works perfectly and i get the result

When I try to run this as a stored procedure

CREATE procedure sp_TransferData @.server varchar(100),@.userid varchar(30)
as
declare
@.tablename varchar (30),
@.fieldnametemp varchar(100),
@.query varchar (2500)
declare tablenames cursor for
select distinct table_name from TABLES

open tablenames
FETCH NEXT FROM tablenames into @.tablename
WHILE @.@.FETCH_STATUS = 0
begin
declare @.fieldname varchar(2000)
select @.fieldname = ''
declare fieldname cursor for
select field_name from tables where table_name = @.tablename
open fieldname
FETCH NEXT FROM fieldname into @.fieldnametemp
WHILE @.@.FETCH_STATUS = 0
begin
select @.fieldname = @.fieldnametemp + ',' + @.fieldname
fetch next from fieldname into @.fieldnametemp
end
CLOSE fieldname
DEALLOCATE fieldname
select @.fieldname = left(@.fieldname,len(@.fieldname)-1)
select @.query = 'select '+ @.fieldname + ' into ' + @.tablename + ' from ' + @.server + '..' + @.userid + '.' + @.tablename + ''')'
select @.query

execute (@.query)
print 'Table Processed'

fetch next from tablenames into @.tablename
end

CLOSE tablenames
DEALLOCATE tablenames

the error crops up ... can somebody suggest a way around

Problem populating temp table from linked server.

Hello,

I have a 2000 sql server linked to a 2005 sql server an I am trying to return data across the link. If I just run the sp I get the data back fine but if I try to Insert the data into a temp table the process just hangs and has to be killed.

This works fine:

EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (returns 156k records in about 2 sec.)

However inserting the results into a local temp table never returns. In fact the process never really runs.

CREATE TABLE #tmpOrgResult
(
intObjectID INT NOT NULL,
intObjectTypeCodeID INT NOT NULL

)
GO

-- Insert org records that match the search.
INSERT INTO #tmpOrgResult
(
intObjectID,
intObjectTypeCodeID
)
EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (This statement just hangs)

Try with

SET REMOTE_PROC_TRANSACTIONS OFF

before the INSERT statement. What I suspect happens is the local transaction is promoted to a distributed one and something gets messed up.

Zlatko

|||

You are correct it is getting promoted to a distributed transaction. Turns out MSDTC was off by default on the server. The following article showed me how to enable it. Works like a champ now.

http://support.microsoft.com/?kbid=873160

|||You are welcome.

Problem passing parameter into remote stored proc

I'm having a problem passing a parameter value into a stored procedure
that I am running on a remote (linked) server, and am receiving a DTC
error because of it.
I have a stored procedure that brings in a variable (@.CustID int). I
later pass that parameter to another stored procedure. The code looks
like this...
EXEC LinkedServer.dbname.dbo.spname @.CustID
When I run that, I get this error...
Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
However, if I hard-code the parameter, it works:
EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
I can even do this:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
But if I accept the variable as an input parameter to my stored
procedure, I get the error listed above.
Any ideas?
Thanks in advance for your help...
Zev Steinhardtzev_steinhardt
what happen if you assign the parameter to a variable?
DECLARE @.var int
SET @.var = @.CustID
EXEC LinkedServer.dbname.dbo.spname @.var
...
AMB
"zev_steinhardt" wrote:

> I'm having a problem passing a parameter value into a stored procedure
> that I am running on a remote (linked) server, and am receiving a DTC
> error because of it.
> I have a stored procedure that brings in a variable (@.CustID int). I
> later pass that parameter to another stored procedure. The code looks
> like this...
> EXEC LinkedServer.dbname.dbo.spname @.CustID
> When I run that, I get this error...
> Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> However, if I hard-code the parameter, it works:
> EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
> I can even do this:
> DECLARE @.var int
> SET @.var = 1234
> EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
> But if I accept the variable as an input parameter to my stored
> procedure, I get the error listed above.
> Any ideas?
> Thanks in advance for your help...
> Zev Steinhardt
>|||Thanks for the reply, Alejandro.
I tried that. It didn't work.
I even tried to trick it into thinking that it's another variable
altogether. I put the variable into a temp table, declared a new
variable, populated it with the value from the temp table and passed it
in. That didn't work either.
Zev Steinhardt|||zev_steinhardt,
Are you executing the remote sp inside a transaction?
AMB
"zev_steinhardt" wrote:

> Thanks for the reply, Alejandro.
> I tried that. It didn't work.
> I even tried to trick it into thinking that it's another variable
> altogether. I put the variable into a temp table, declared a new
> variable, populated it with the value from the temp table and passed it
> in. That didn't work either.
> Zev Steinhardt
>|||Yes. The remote sp is within a transaction.
Zev|||zev_steinhardt,
you are using a distributed one, correct?
begin distributed transaction
exec ...
AMB
"zev_steinhardt" wrote:

> Yes. The remote sp is within a transaction.
> Zev
>|||Alejandro...
Yes, it is a distributed transaction... and I have XACT_ABORT on
Zev|||zev_steinhardt,
When you execute the remote sp using:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
then you are not executing it using a distributed transaction, that is why
you do not get the error.
See if this helps.
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
AMB
"zev_steinhardt" wrote:

> Alejandro...
> Yes, it is a distributed transaction... and I have XACT_ABORT on
> Zev
>

problem on registering linked server in SQL2005

I have an InterBase 5 database, which has been registered as an ODBC System Data Source in my Windows 2000. When I try to add it as a linked server in SQL server 2005, the Add Linked Server page make me so frustrated. I can select the ODBC driver, but I don't know how to specify values for the fields such as Product Name, Database Source, and Provider string. I tried many combinations on these field values, all unsucceed . Anybody can tell me how to determine the values of these fields? Any reference articles about this? Please help me!There are examples in Books On Line have you looked at these?

Problem of Permissions for Linked Servers in .net page

I searching using Keywords in MS Word and pdf documents for which i
have used the Index Server and linked with SQL Server but when i am
running the stored procedure thro my webpage i'm getting these errors.
And when i run this code in Query Analyzer its giving expeceted
results. I dont know where the problem is can anyone help please . I'm
really badly stuck. Thanks in Advacne
User does not have permission to perform this action. Line 3: Incorrect
syntax near '@.searchstring'. Invalid object name 'FileSearchResults'.
And my stored procedure is given below
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
Exec sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web',
'c:\inetpub\wwwroot\sap-resources\Uploads'
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE() WHERE FREETEXT(''@.searchstring'')'')')
SELECT * FROM FileSearchResults F, CVdetails C WHERE C.CV_Path = F.PATH
AND C.DefaultID=1
GONext time print out the strings you build before executing them.
See if this helps:
exec ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE() WHERE FREETEXT(' + @.searchstring + ')'')')
ML
http://milambda.blogspot.com/|||Thanks for your reply
its not working out.
I modified the stored procedure as shown below
its giving no errors but no results but if i execute the same without
the stored i'm gettting the expected results
Thanks in Advance
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM FileSearchResults F, CVdetails C WHERE C.CV_Path = F.PATH
AND C.DefaultID=1

problem making Oracle linked server work

Hi all,

I set up our Oracle Financials as a linked server to one of my SQL
Server boxes. On running a test query, I got the following error
message:
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'TEST_NUM'
(compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reported
to have a
DBTYPE of 130 at compile time and 5 at run time].

The Oracle datatype of the column with the supposedly inconsistent
metadata was NUMBER, which according to the Oracle OLE DB documentation
actually maps to 139. 130 is a null-terminated unicode character
string, 5 is a float, and 139 is a variable-length, exact numeric value
with a signed scale value. Oracle NUMBER is an all-purpose numeric
type, apparently they use that instead of int, float, etc. The Oracle
guy used it for this column in the test table because in OF it is used
in pretty much every table. (For starters it is the datatype of their
identity columns.)

There is something in the OLE DB spec about all datatypes having to be
able to be expressed as DBTYPE_WSTR (130), but what I don't get is that
I can connect to the same Oracle instance using VB6 code and the
MSDAORA provider and there is no problem at all interpreting the NUMBER
columns. So why does it work from VB and not as a linked server? And
much more importantly, HOW DO I MAKE THE LINKED SERVER WORK?

TIAYou might try upgrading the latest MDAC version (2.8) if you haven't already
done so. You can also run the MDAC component checker to ensure the binaries
match for the installed version.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1110695346.557634.70280@.l41g2000cwc.googlegro ups.com...
> Hi all,
> I set up our Oracle Financials as a linked server to one of my SQL
> Server boxes. On running a test query, I got the following error
> message:
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column 'TEST_NUM'
> (compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reported
> to have a
> DBTYPE of 130 at compile time and 5 at run time].
> The Oracle datatype of the column with the supposedly inconsistent
> metadata was NUMBER, which according to the Oracle OLE DB documentation
> actually maps to 139. 130 is a null-terminated unicode character
> string, 5 is a float, and 139 is a variable-length, exact numeric value
> with a signed scale value. Oracle NUMBER is an all-purpose numeric
> type, apparently they use that instead of int, float, etc. The Oracle
> guy used it for this column in the test table because in OF it is used
> in pretty much every table. (For starters it is the datatype of their
> identity columns.)
> There is something in the OLE DB spec about all datatypes having to be
> able to be expressed as DBTYPE_WSTR (130), but what I don't get is that
> I can connect to the same Oracle instance using VB6 code and the
> MSDAORA provider and there is no problem at all interpreting the NUMBER
> columns. So why does it work from VB and not as a linked server? And
> much more importantly, HOW DO I MAKE THE LINKED SERVER WORK?
> TIA|||Thanks, Dan.

My desktop and the SQL Server box are currently both running the exact
same version of MDAC 2.7 but I guess moving to 2.8 can't hurt.|||Did 2.8 solve the problem?|||I didn't try it. Honestly I don't see how the problem can be with
MDAC.

As previously noted, using the MSDAORA provider from VB6 I have no
problem talking to Oracle, the problem is only using the MSDAORA
provider to talk to Oracle as a linked server from SQL Server. The
MDAC on the SQL Server box is exactly the same as the MDAC on the box
using VB6.

<shrug|||Hi

Did you check out
http://support.microsoft.com/defaul...kb;en-us;251238
http://support.microsoft.com/defaul...kb;en-us;280106

John

"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1112595163.198646.201490@.g14g2000cwa.googlegr oups.com...
>I didn't try it. Honestly I don't see how the problem can be with
> MDAC.
> As previously noted, using the MSDAORA provider from VB6 I have no
> problem talking to Oracle, the problem is only using the MSDAORA
> provider to talk to Oracle as a linked server from SQL Server. The
> MDAC on the SQL Server box is exactly the same as the MDAC on the box
> using VB6.
> <shrug|||Hi John,

Thank you very much!

The second one has a bunch of links to other ones, at one of which I at
least found a more exact explanation: "The column with Numeric
datatype has no Length specified (no Precision, no Default, allows
NULL). The number datatype without a precision and scale is represented
in Oracle by a variable-length numeric with precision of up to 255.
There is no SQL Server type that this can be mapped to without loss of
precision." And one of the others states "An Oracle numeric type is
now mapped to nvarchar (384) if the precision is too large for a
numeric SQL Server type."

So -- ta-dah! -- this is how the datatype is being converted at
runtime, although none of the articles explains this in so many words.
It's also interesting that even though these articles claim to be about
SQL Server 7, I am having the problem on 2000.

One of the workarounds they mention, specifying precision and scale of
any NUMBER columns, we already thought of and tried and it works... but
I don't think we can go do that to every NUMBER column in Oracle
Financials, it might not be possible to change them at all and even if
so I don't think it would be a very good idea.

The main reason I wanted the linked server was to be able to make a
distributed transaction to set up products in Oracle Inventory, my SQL
Server transactional database, and my SQL Server data warehouse to
ensure that everybody will be in sync. To do the Oracle piece we
created a private table on the Oracle box, to which I will write... the
Oracle guy has a trigger on it that sends the data to the Oracle
product setup process... when it finishes he comes back and writes to a
process flag column, which I can then read to make sure it worked. So
for the private table if he defines the precision and scale we have no
problem.

It would have been nice to be able to read directly from Oracle also
but I can live without that, just code the parts of the data warehouse
ETL that need Oracle data, because as previously noted the problem does
not occur from VB code.

One of the articles did also mention that MSDAORA is in maintenance
mode and not updated for Oracle versions greater than 8i (we are on
9i), but there is now a .Net managed provider... maybe I will try that
when we go to .Net later this year. (Oracle also puts out their own
OLE DB provider, but with that one I can't even connect.)

Meanwhile the part I REALLY don't get is how come *I* didn't find these
articles when I searched on the Microsoft site!!!

Anyway, thanks again,

Ellen :)|||Hi Ellen

Ellen K wrote:
> Hi John,
> Thank you very much!
> The second one has a bunch of links to other ones, at one of which I
at
> least found a more exact explanation: "The column with Numeric
> datatype has no Length specified (no Precision, no Default, allows
> NULL). The number datatype without a precision and scale is
represented
> in Oracle by a variable-length numeric with precision of up to 255.
> There is no SQL Server type that this can be mapped to without loss
of
> precision." And one of the others states "An Oracle numeric type is
> now mapped to nvarchar (384) if the precision is too large for a
> numeric SQL Server type."
> So -- ta-dah! -- this is how the datatype is being converted at
> runtime, although none of the articles explains this in so many
words.
> It's also interesting that even though these articles claim to be
about
> SQL Server 7, I am having the problem on 2000.
I think you will have the same behaviour as this is (probably) more to
do with MDAC versions than SQL Server.
> One of the workarounds they mention, specifying precision and scale
of
> any NUMBER columns, we already thought of and tried and it works...
but
> I don't think we can go do that to every NUMBER column in Oracle
> Financials, it might not be possible to change them at all and even
if
> so I don't think it would be a very good idea.
> The main reason I wanted the linked server was to be able to make a
> distributed transaction to set up products in Oracle Inventory, my
SQL
> Server transactional database, and my SQL Server data warehouse to
> ensure that everybody will be in sync. To do the Oracle piece we
> created a private table on the Oracle box, to which I will write...
the
> Oracle guy has a trigger on it that sends the data to the Oracle
> product setup process... when it finishes he comes back and writes to
a
> process flag column, which I can then read to make sure it worked.
So
> for the private table if he defines the precision and scale we have
no
> problem.

If your Oracle server is male then it will never work!!

> It would have been nice to be able to read directly from Oracle also
> but I can live without that, just code the parts of the data
warehouse
> ETL that need Oracle data, because as previously noted the problem
does
> not occur from VB code.
> One of the articles did also mention that MSDAORA is in maintenance
> mode and not updated for Oracle versions greater than 8i (we are on
> 9i), but there is now a .Net managed provider... maybe I will try
that
> when we go to .Net later this year. (Oracle also puts out their own
> OLE DB provider, but with that one I can't even connect.)

At a guess something to do with SQL*Net or the configuration files, I
have never used the OLEDB driver but it may be worth investigating.

> Meanwhile the part I REALLY don't get is how come *I* didn't find
these
> articles when I searched on the Microsoft site!!!
C'est la vie.

> Anyway, thanks again,
> Ellen :)

John|||Hi John,

If MDAC was the issue, I would not be able to pull NUMBER data with VB
code. SQL Server is the issue, it's because as the one article noted
it doesn't have any datatype with a precision of 255. (I guess maybe
some scientific applications might need precision of 255, but if I were
designing an RDBMS I would make that some special datatype, I wouldn't
default all numerics to such a thing. It's very arrogant.)

I'm going to try using OPENQUERY instead of the four-part identifier,
with TO_CHAR on any NUMBER data elements that don't have a reasonable
precision specified... I can convert them back to the appropriate
numeric types. If this works I will forget about the Oracle brand
provider, since our Oracle guy already has plenty of work to keep him
busy.

And I'm SURE the Oracle server is male! <ggg
Thanks again for your help,

Ellen :)|||Ellen K wrote:

> Hi John,
> If MDAC was the issue, I would not be able to pull NUMBER data with VB
> code. SQL Server is the issue, it's because as the one article noted
> it doesn't have any datatype with a precision of 255. (I guess maybe
> some scientific applications might need precision of 255, but if I were
> designing an RDBMS I would make that some special datatype, I wouldn't
> default all numerics to such a thing. It's very arrogant.)
> I'm going to try using OPENQUERY instead of the four-part identifier,
> with TO_CHAR on any NUMBER data elements that don't have a reasonable
> precision specified... I can convert them back to the appropriate
> numeric types. If this works I will forget about the Oracle brand
> provider, since our Oracle guy already has plenty of work to keep him
> busy.
> And I'm SURE the Oracle server is male! <ggg>
> Thanks again for your help,
> Ellen :)

Why don't you either (A) read the Oracle documentation which is all
readily available at http://tahiti.oracle.com or (B) post your inquiry
at comp.databases.oracle.server?
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||The Oracle documentation CD was the FIRST place I looked. I did find
the definition of the NUMBER datatype there, but nothing else useful.
If you found something specific that would be helpful, please post it.|||All,

The solution turned out to be to use OPENQUERY instead of the four-part
qualifier.

I am a very happy camper right now. :)

Thanks to all who helped,

Ellen

Problem linking to tables in SQL Server 2003 using MS-Access 2000

Hi,

I have an MS-Access database on a shared drive.The Access database has tables linked to a SQL server database.When I try to query the tables in MS-Access database by accessing the share drive,I get the ODBC--call failed error.So I tried creating an ODBC driver for the SQL Server and when I try to link the tables,no tables are displayed.Can anyone help me out with this problem?

-Ruth

If your Access Database has both Code and Data, you should separate them, Put the Data on the shared Drive,

The Code mdb is your client application and can be Linked to SQL-Server thru ODBC

create an ODBC connection to SQL-Server, So Your Client mdb app has both linked Tables to SQL Server and To the Shared Access Database.

You can not link to SQL Server thru an mdb indirectly.

|||

The mdb just has tables linked to SQL Server.I tried creating a ODBC and linking the tables.But it did not work.But when I tried to link to tables in the Northwind database,I am able to do so.I think it is a SQL Server access issue.But I am not sure where exactly the issue lies.

-Ruth

|||

Those are links to the SQL Server table, not the Actual table, so if you want, you can create link to SQL Server by Craeting a ODBC connection from your Control Panel.

Then in your Access app, add linked table using the DSN that you created.

You do not need an mdb on shared drive. if It does not have any local tables.

The once you compiled and distributed you app. On every PC, that it is installed the DSN should be created.

Problem inserting into DB2 from SQL Server via Linked Server

Here goes: (good luck understanding all this)
I have data in SQL Server that needs to be inserted into DB2. I have
installed the IBM DB2 Client Configuration Assistant on the SQL Server and
created a DSN. I can use the IBM Command Center to execute commands (both
reads and writes) successfully. I then created a Linked Server on SQL
Server that uses the Microsoft OLE DB provider for ODBC Connections in order
to connect to DB2. Using this method, I can run SELECT statements
successfully. When I try to do any writes (a DELETE for example), I get
this error message:
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I verified that the user on DB2 has appropriate permissions and have on the
Security tab of the linked server properties page specified that user with
its password. Personally, I think that the whole SQL Server-->OLE
DB-->ODBC-->DB2 route is kinda obtuse. I would love to get rid of ODBC
alltogether. Unfortunately, the Microsoft OLE DB Provider for DB2 only
comes with their Host Integration Server product (which we don't have and
wouldn't be able to install on the SQL Server anyway) and I don't have the
equivalent IBM OLE DB driver...
Any recommendations or suggestions on how I can get the writes working?
Thanks..
Peace,
Gary Hampson
SQL Server - Siebel DBA
Application Development Group - IS
Horizon Blue Cross Blue Shield of New Jersey"Driver not capable" sounds like whatever driver you are using doesn't meet
the minimum capabilities required for the type of operation you are
performing. Drivers will often be queried for standards compliance and this
is the type of error that's reported when the driver does not report that
it supports one or more requested standards.
Do you have the latest version of all drivers involved on both the SQL
Server machine and the DB2 side? Do you have the latest SQL Server service
pack and security rollup (SP3 + MS03-031)?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem Insert into from Linked Server SP.

Hi All , I am using SQL Server 2005 and trying to Insert the Data from Linked server SP but i am getting Error Like "Transaction context in use by another session." but the same is Working in SQL Server 2000.

Example Code :

create table #sSS

(

mmmm varchar(200)

)

Insert into #sSS (mmmm) Exec IC2.IC2.dbo.SSA

Msg 3910, Level 16, State 2, Line 1

Transaction context in use by another session.

Pls any one help me to solve this Issue

Regards

Sivaraman Latchapathi

Is this a loopback linked server? If so, it's a known, unsupported issue.
http://msdn2.microsoft.com/en-us/library/ms188716.aspx

The workaround is to split the insert/exec into 2 steps:
1. execute the remote stored procedure and insert the data into a #remote table
2. insert into #local select * from #remote