OLE DB provider 'MSIDXS' reported an error. Access denied.

Not sure if I should post here of inetserver.indexserver, so I apologize
ahead of time.
In Sql2000, I've added a linked server to a development box.
EXEC sp_addlinkedserver Web,
'Index Server',
'MSIDXS',
'stjoedev01.CarenetWeb'
I ran the same command on my local Sql box.
When I execute this query
Select * from OpenQuery(web, 'Select DocTitle, vpath, Filename, Size,
Contents, characterization, PATH, URL, A_Href, rank, write
from stjoedev01.CarenetWeb..Scope(''"d:\Inetpub\"'')
where FREETEXT(Contents, ''adoption'') AND Rank > 50 order by rank desc,
write desc')
on my local box I get a result set. When I excute the same query on the
development sql box I get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSIDXS' reported an error. Access denied.
[OLE/DB provider returned message: Invalid catalog name 'CARENETWEB'.
SQLSTATE=42000 ]
OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare
returned 0x80070005: Access denied.].
In both cases the query analyzer session is logged in with dbo access. I've
checked the file system catalog and "Everyone" has read/write access to the
index files.
The development sql instance is running under a local system account. My
local instance of sql server is running under a domain user account. Would
this account for the 'access denied'?
My ultimate goal is to add a linked server to a production SQL box, but it's
looking rather grim right now.
Thanks for the help,
Barry
The correct linked server command should be this
EXEC sp_addlinkedserver Web,
'Index Server',
'MSIDXS',
'CarenetWeb'
where CarenetWeb is a catalog on the local server.
You can't build a linked server to a remote IS catalog, but you can query it
like this
Select * from openquery(LinkedServerName,'Select DocTitle, Vpath,
Characterization from RemoteServerName.RemoteCatalogName..Scope() where
FileName like ''%.txt''')
Try the above and see if this solves your problem.
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:ACAF9D1C-A2A1-42CE-8A34-57E6DE116A29@.microsoft.com...
> Not sure if I should post here of inetserver.indexserver, so I apologize
> ahead of time.
> In Sql2000, I've added a linked server to a development box.
> EXEC sp_addlinkedserver Web,
> 'Index Server',
> 'MSIDXS',
> 'stjoedev01.CarenetWeb'
> I ran the same command on my local Sql box.
> When I execute this query
> Select * from OpenQuery(web, 'Select DocTitle, vpath, Filename, Size,
> Contents, characterization, PATH, URL, A_Href, rank, write
> from stjoedev01.CarenetWeb..Scope(''"d:\Inetpub\"'')
> where FREETEXT(Contents, ''adoption'') AND Rank > 50 order by rank desc,
> write desc')
> on my local box I get a result set. When I excute the same query on the
> development sql box I get the following error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSIDXS' reported an error. Access denied.
> [OLE/DB provider returned message: Invalid catalog name 'CARENETWEB'.
> SQLSTATE=42000 ]
> OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare
> returned 0x80070005: Access denied.].
> In both cases the query analyzer session is logged in with dbo access.
> I've
> checked the file system catalog and "Everyone" has read/write access to
> the
> index files.
> The development sql instance is running under a local system account. My
> local instance of sql server is running under a domain user account. Would
> this account for the 'access denied'?
> My ultimate goal is to add a linked server to a production SQL box, but
> it's
> looking rather grim right now.
> Thanks for the help,
> Barry
|||How do I need to configure the remote server? Is there anything special I
need to configure on the IS catalog that I'm trying to access remotely?
I've execute the sp_addlinkedserver as you suggest, created the IS catalog
on the SQL box and I'm now getting the dreaded
Invalid catalog name 'CARENETWEB'. SQLSTATE=42000
The biggest problem is I haven't a clue what I'm doing, so thanks again for
the help.
Barry
"Hilary Cotter" wrote:

> The correct linked server command should be this
> EXEC sp_addlinkedserver Web,
> 'Index Server',
> 'MSIDXS',
> 'CarenetWeb'
> where CarenetWeb is a catalog on the local server.
> You can't build a linked server to a remote IS catalog, but you can query it
> like this
> Select * from openquery(LinkedServerName,'Select DocTitle, Vpath,
> Characterization from RemoteServerName.RemoteCatalogName..Scope() where
> FileName like ''%.txt''')
> Try the above and see if this solves your problem.
>
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:ACAF9D1C-A2A1-42CE-8A34-57E6DE116A29@.microsoft.com...
>
>
|||can you query the local IS server?
Also what rights does the SQL Server agent have on the remote server? Is the
account the SQL Server agent runs under in the local admin group on the
remote server?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:B5846E6D-1855-4BE1-9C09-EDC214E7D409@.microsoft.com...
> How do I need to configure the remote server? Is there anything special I
> need to configure on the IS catalog that I'm trying to access remotely?
> I've execute the sp_addlinkedserver as you suggest, created the IS catalog
> on the SQL box and I'm now getting the dreaded
> Invalid catalog name 'CARENETWEB'. SQLSTATE=42000
> The biggest problem is I haven't a clue what I'm doing, so thanks again
for[vbcol=seagreen]
> the help.
> Barry
> "Hilary Cotter" wrote:
query it[vbcol=seagreen]
apologize[vbcol=seagreen]
Size,[vbcol=seagreen]
desc,[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
My[vbcol=seagreen]
Would[vbcol=seagreen]
but[vbcol=seagreen]
|||On the Sql Box I can use the query form of IS manager and get results. I can
execute the openquery against the local catalog without receiving the invalid
catalog error, but I don't get any results returned.
The Sql Server Agent runs under a domain account, which is a member of the
remote server's Administrators group.
"Hilary Cotter" wrote:

> can you query the local IS server?
> Also what rights does the SQL Server agent have on the remote server? Is the
> account the SQL Server agent runs under in the local admin group on the
> remote server?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:B5846E6D-1855-4BE1-9C09-EDC214E7D409@.microsoft.com...
> for
> query it
> apologize
> Size,
> desc,
> the
> to
> My
> Would
> but
>
>
|||Its puzzling that you don't get any hits from the local catalog. Can you
check 1) the IS server is running locally. 2) the catalog is indexing
diriectories, expand the directories folder and verify there are directories
there 3) the directories indexed are both real and virtual (the virtual
directories have blue globes on them), 4) there are documents in the
physical directories.
Also can you try this code agains your remote catalog - make sure you change
this entry to reflect your actual remote server name and catalog
@.objIxsso, 'catalog', 'query://remoteservername.remotecatalogname'
create procedure sp_indexServer @.input char(255)
as
declare @.HResult int
declare @.objIxsso int
declare @.RecordSet int
declare @.recordCount int
declare @.Count int
declare @.fields int
declare @.path varchar(100)
declare @.vpath varchar(100)
declare @.doctitle varchar(100)
declare @.size varchar(100)
declare @.filename varchar(100)
declare @.write varchar(100)
declare @.rank varchar(100)
declare @.hitcount varchar(100)
EXEC @.HResult = sp_OACreate 'Ixsso.query', @.objIxsso Out
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Query', @.input
EXEC @.HResult = sp_OASetProperty
@.objIxsso, 'catalog', 'query://remoteservername.remotecatalogname'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Columns', _
'path, vpath, doctitle,size,filename,write,rank,hitcount'
EXEC @.HResult = sp_OASetProperty
@.objIxsso, 'MaxRecords', '200'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'SortBy', 'rank
[d]'
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
exec @.HResult =sp_OAMethod @.objIxsso,_
'CreateRecordSet("nonsequential")',@.recordSet OUTPUT
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
EXEC @.HResult = sp_OAGetProperty
@.recordSet, 'RecordCount',_
@.RecordCount output
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.recordset
RETURN
END
if @.recordcount =0
print 'No matches found'
else
begin
print convert (varchar(5),@.recordcount) +' matches found'
SET @.Count = 1
WHILE ( @.Count <= @.RecordCount )
BEGIN
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,0
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.path output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,1
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.vpath output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,2
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.doctitle output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,3
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.size output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,4
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.filename output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields',
@.fields out,5
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.write
output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields',
@.fields out,6
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.rank
output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields',
@.fields out,7
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value',
@.hitcount output
print @.path +', ' +@.vpath+', '+@.doctitle+', '+@.size +', '+_
@.filename +', '+@.write+', '+@.rank+', '+@.hitcount
EXEC @.HResult =sp_OAMethod @.RecordSet, 'MoveNext'
select @.count=@.Count+1
END
print @.count
EXEC @.HResult = sp_OADestroy @.fields
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.fields
RETURN
END
END
EXEC @.HResult = sp_OADestroy @.recordset
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.recordset
RETURN
END
EXEC @.HResult = sp_OADestroy @.objIxsso
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:8A815D4C-DED1-486F-9E20-ADDFF4A892A4@.microsoft.com...
> On the Sql Box I can use the query form of IS manager and get results. I
can
> execute the openquery against the local catalog without receiving the
invalid[vbcol=seagreen]
> catalog error, but I don't get any results returned.
> The Sql Server Agent runs under a domain account, which is a member of the
> remote server's Administrators group.
> "Hilary Cotter" wrote:
the[vbcol=seagreen]
special I[vbcol=seagreen]
remotely?[vbcol=seagreen]
catalog[vbcol=seagreen]
again[vbcol=seagreen]
where[vbcol=seagreen]
Filename,[vbcol=seagreen]
on[vbcol=seagreen]
'CARENETWEB'.[vbcol=seagreen]
ICommandPrepare::Prepare[vbcol=seagreen]
access.[vbcol=seagreen]
access[vbcol=seagreen]
account.[vbcol=seagreen]
account.[vbcol=seagreen]
box,[vbcol=seagreen]
|||The lack of hits from the local catalog was due to a bad keyboard driver. I
didn't change the scope of the query.
The permissions path you pointed me to seems to be the solution. I connected
to the Sql box with a Sql user account and was able to execute the query.
When I connect to the Sql box with windows credentials I get the invalid
catalog, access denied combination error.
Thank you so much for your assistance.
Barry
"Hilary Cotter" wrote:

> Its puzzling that you don't get any hits from the local catalog. Can you
> check 1) the IS server is running locally. 2) the catalog is indexing
> diriectories, expand the directories folder and verify there are directories
> there 3) the directories indexed are both real and virtual (the virtual
> directories have blue globes on them), 4) there are documents in the
> physical directories.
> Also can you try this code agains your remote catalog - make sure you change
> this entry to reflect your actual remote server name and catalog
> @.objIxsso, 'catalog', 'query://remoteservername.remotecatalogname'
>
> create procedure sp_indexServer @.input char(255)
> as
> declare @.HResult int
> declare @.objIxsso int
> declare @.RecordSet int
> declare @.recordCount int
> declare @.Count int
> declare @.fields int
> declare @.path varchar(100)
> declare @.vpath varchar(100)
> declare @.doctitle varchar(100)
> declare @.size varchar(100)
> declare @.filename varchar(100)
> declare @.write varchar(100)
> declare @.rank varchar(100)
> declare @.hitcount varchar(100)
> --
> EXEC @.HResult = sp_OACreate 'Ixsso.query', @.objIxsso Out
> EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Query', @.input
> EXEC @.HResult = sp_OASetProperty
> @.objIxsso, 'catalog', 'query://remoteservername.remotecatalogname'
> EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Columns', _
> 'path, vpath, doctitle,size,filename,write,rank,hitcount'
> EXEC @.HResult = sp_OASetProperty
> @.objIxsso, 'MaxRecords', '200'
> EXEC @.HResult = sp_OASetProperty @.objIxsso, 'SortBy', 'rank
> [d]'
> IF @.HResult <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.objIxsso
> RETURN
> END
> exec @.HResult =sp_OAMethod @.objIxsso,_
> 'CreateRecordSet("nonsequential")',@.recordSet OUTPUT
> IF @.HResult <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.objIxsso
> RETURN
> END
> EXEC @.HResult = sp_OAGetProperty
> @.recordSet, 'RecordCount',_
> @.RecordCount output
> IF @.HResult <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.recordset
> RETURN
> END
> if @.recordcount =0
> print 'No matches found'
> else
> begin
> print convert (varchar(5),@.recordcount) +' matches found'
> SET @.Count = 1
> WHILE ( @.Count <= @.RecordCount )
> BEGIN
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
> @.fields out,0
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
> @.path output
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
> @.fields out,1
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
> @.vpath output
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
> @.fields out,2
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
> @.doctitle output
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
> @.fields out,3
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
> @.size output
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
> @.fields out,4
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
> @.filename output
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields',
> @.fields out,5
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.write
> output
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields',
> @.fields out,6
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.rank
> output
> EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields',
> @.fields out,7
> EXEC @.HResult = sp_OAGetProperty @.fields, 'Value',
> @.hitcount output
> print @.path +', ' +@.vpath+', '+@.doctitle+', '+@.size +', '+_
> @.filename +', '+@.write+', '+@.rank+', '+@.hitcount
> EXEC @.HResult =sp_OAMethod @.RecordSet, 'MoveNext'
> select @.count=@.Count+1
> END
> print @.count
> EXEC @.HResult = sp_OADestroy @.fields
> IF @.HResult <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.fields
> RETURN
> END
> END
> EXEC @.HResult = sp_OADestroy @.recordset
> IF @.HResult <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.recordset
> RETURN
> END
> EXEC @.HResult = sp_OADestroy @.objIxsso
> IF @.HResult <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.objIxsso
> RETURN
> END
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:8A815D4C-DED1-486F-9E20-ADDFF4A892A4@.microsoft.com...
> can
> invalid
> the
> special I
> remotely?
> catalog
> again
> where
> Filename,
> on
> 'CARENETWEB'.
> ICommandPrepare::Prepare
> access.
> access
> account.
> account.
> box,
>
>
|||Hi Barry,
Could you please elaborate on how you got the openquery to work, and the
SQL user account credentials used.
I am currently struggling with the same problem you specified. However I
am already using SQL server authentication instead of windows
authentication.
I am stumped.
Many thanks in advance.
Cheers,
Luca
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Using the Management console on the Sql box I created a catalog with the same
name as the remote catalog that I wished to query.
Using Query analyzer connected to the Sql server I executed a sp_addlinked
server:
EXEC sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'CatalogName'
In the Sql Management console, under Security --> Linked Servers
-->Properties, I set the Security tab option to "Be made using this security
context:" and assigned a domain account that is a member of the local
Administrators group on the remote server where the queried catalog resides.
Then constructed a query like this:
SELECT DocTitle, vpath, Filename, Size, Contents, characterization, PATH,
URL, A_Href, rank, write
FROM RemoteServer.CatalogName..SCOPE(''" + this.Scope + "'')
Where FREETEXT(Contents,''" SearchPhrase"'')
Which I was able to then execute while connected with a sql account that was
a db_owner.
Hope that Helps,
Barry
"Luca Gnezda" wrote:

> Hi Barry,
> Could you please elaborate on how you got the openquery to work, and the
> SQL user account credentials used.
> I am currently struggling with the same problem you specified. However I
> am already using SQL server authentication instead of windows
> authentication.
> I am stumped.
> Many thanks in advance.
> Cheers,
> Luca
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Thanks very much Barry.
My environment reflects your setup, unfortunately it doesn't work.
However I have found that the excact same setup, using a pair of 2003
Servers instead of the 2000 servers works perfectly.
I suspect that I have incorrect versions of service packs, or some other
install/patch conflicting with the 2000 server setup.
I will continue playing with versions to find the root cause. Once again
thank you very much for your feedback.
Regards,
Luca
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

OLE DB provider 'MSDAORA' reported an error.(SQL Server 7399)

Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
Marcus
We recently came across a similar situation. You could try setting your
environment before the execution:
SET IMPLICT_TRANSACTIONS OFF
SET XACT_ABORT ON
In our case, the vendor had coded a BAD trigger against the destination
table. It was bad in the sense that it was coded row based instead of set
based; so, it returned an error whenever the number of records attempted to
insert were more than one, but the Distributed Transaction Coordinator
returned the error message you received.
Sincerely,
Anthony Thomas

"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:D9CF72F5-BE5B-41A5-923B-36BBE0F93A52@.microsoft.com...
Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry
says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
Marcus

OLE DB provider 'MSDAORA' reported an error.(SQL Server 7399)

Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
MarcusWe recently came across a similar situation. You could try setting your
environment before the execution:
SET IMPLICT_TRANSACTIONS OFF
SET XACT_ABORT ON
In our case, the vendor had coded a BAD trigger against the destination
table. It was bad in the sense that it was coded row based instead of set
based; so, it returned an error whenever the number of records attempted to
insert were more than one, but the Distributed Transaction Coordinator
returned the error message you received.
Sincerely,
Anthony Thomas
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:D9CF72F5-BE5B-41A5-923B-36BBE0F93A52@.microsoft.com...
Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry
says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
Marcus

OLE DB provider 'MSDAORA' reported an error.(SQL Server 7399)

Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry says
)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
MarcusWe recently came across a similar situation. You could try setting your
environment before the execution:
SET IMPLICT_TRANSACTIONS OFF
SET XACT_ABORT ON
In our case, the vendor had coded a BAD trigger against the destination
table. It was bad in the sense that it was coded row based instead of set
based; so, it returned an error whenever the number of records attempted to
insert were more than one, but the Distributed Transaction Coordinator
returned the error message you received.
Sincerely,
Anthony Thomas
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:D9CF72F5-BE5B-41A5-923B-36BBE0F93A52@.microsoft.com...
Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry
says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
Marcus

OLE DB Destination not writing data

I am using OLE DB Destination to write data to a SQL server database. However, nothing is written to the database though there is no error reported. See the following output:

SSIS package "Tbl_Dim_Dates.dtsx" starting.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Tbl_Dim_Dates, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Tbl_Dim_Dates, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Tbl_Dim_Dates, DTS.Pipeline: Execute phase is beginning.

Information: 0x402090DF at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has started.

Information: 0x402090E0 at Tbl_Dim_Dates, OLE DB Destination [2396]: The final commit for the data insertion has ended.

Information: 0x40043008 at Tbl_Dim_Dates, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Tbl_Dim_Dates, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Date extract to file" (924)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "Raw File Destination" (2518)" wrote 3652 rows.

Information: 0x4004300B at Tbl_Dim_Dates, DTS.Pipeline: "component "OLE DB Destination" (2396)" wrote 3652 rows.

SSIS package "Tbl_Dim_Dates.dtsx" finished: Success.

The program '[2708] Tbl_Dim_Dates.dtsx: DTS' has exited with code 0 (0x0).

this might be a bug. try recreating the data flow.|||

You should use SQL Server Profiler to see what is being sent to the database and what happens within SQL Server. We are pretty much just a client of SQL in such cases, and that's a good to place to start looking for the problem.

Donald

|||

Thanks for all your advices.

ghe

Old transaction log is reported as missing during recovery

I've done this at least 50 times without issue on various databases, but this
one just doesn't want to play nice.
So I have a SQLServer2K database setup. I've got a home-grown disaster
recovery process in place, so that transactions are taken from one server and
migrated to a backup server every hour. Once it's up and running, the
process is pretty flawless. However, getting it setup can be tricky.
1) Set the recovery model to "Full".
2) Backup your transaction logs
3) Do a full backup
4) Copy the backup to the backup server
5) Restore in to a read only database
Transaction logs can now be backed up on the source database, copied over
and applied to the backup server.
This isn't working this time though. No matter what I've tried, I still get
a message during the restore of the transaction logs such as the following:
"Executed as user: dbo. The log in this backup set begins at LSN
83111000001460600001, which is too late to apply to the database. An earlier
log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed."
Given the large gap between the two LSN #'s, I'm assuming that the database
thinks there is a very old transaction I'm missing. However, that is sheer
guesswork on my part.
The source box has been cycled, I've tried turning the recovery back and
forth between Full and Simple. This error is from my latest attempt, when I
swear I've done everything by the book.
Any suggestions of what might be causing this or where I should go from here?
Fred...
SQL lost a log somehow, I often had problems similar to this w/ home grown
log shipping.
Solution mostly relied upon: re-execute full backup, then restore & fire up
log shipping again. Full backup restores should be done nightly if
affordable...
Also - transactional replication ultimately proved the best solution for us,
transactions replicated within seconds, if we ever had to failover to backup,
I am much more confident minimal data would be lost as opposed to hourly log
restores...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"fkutz" wrote:

> I've done this at least 50 times without issue on various databases, but this
> one just doesn't want to play nice.
> So I have a SQLServer2K database setup. I've got a home-grown disaster
> recovery process in place, so that transactions are taken from one server and
> migrated to a backup server every hour. Once it's up and running, the
> process is pretty flawless. However, getting it setup can be tricky.
> 1) Set the recovery model to "Full".
> 2) Backup your transaction logs
> 3) Do a full backup
> 4) Copy the backup to the backup server
> 5) Restore in to a read only database
> Transaction logs can now be backed up on the source database, copied over
> and applied to the backup server.
> This isn't working this time though. No matter what I've tried, I still get
> a message during the restore of the transaction logs such as the following:
> "Executed as user: dbo. The log in this backup set begins at LSN
> 83111000001460600001, which is too late to apply to the database. An earlier
> log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
> 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
> (Error 3013). The step failed."
> Given the large gap between the two LSN #'s, I'm assuming that the database
> thinks there is a very old transaction I'm missing. However, that is sheer
> guesswork on my part.
> The source box has been cycled, I've tried turning the recovery back and
> forth between Full and Simple. This error is from my latest attempt, when I
> swear I've done everything by the book.
> Any suggestions of what might be causing this or where I should go from here?
> Fred...