OPENROWSET & ERROR

Similar Threads:

1.Linked Server/OpenRowset errors accessing Access 97 database

I have an Access 97 database which resides on a local drive of a Windows 
Server 2003 that is running SQL Server 2005 Standard Edition.

I have SQL Server 2005 Developer Edition running on my laptop with XP and 
have found the following issues when using either Linked Server to the 
Access 97 database
or executing SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', '<uncpath 
access 97 mdb>';'admin';'',<tablename>)

On Windows 2003 Server
When using MSSMS expanding Tables node under default catalogs in the Linked 
Server using the connection from the same server the following error occurs:
Cannot initialize the data source object of OLE DB provider 
"Microsoft.Jet.OLEDB.4.0" for linked server "<Linked Name>". (Microsoft SQL 
Server, Error: 7303)

When executing openrowset the following error occurs:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" 
returned message "Cannot open database ''. It may not be a database that 
your application recognizes, or the file may be corrupt.".

Cannot initialize the data source object of OLE DB provider 
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


However if I create the same Linked Server on my local database on my laptop 
I can successfully browse the tables and run the openrowset command.

So why does the Linked Server fail on the Windows Server 2003 and succeed on 
XP when both point to the same Access database on the Windows Server 2003?
I even installed Access 97 SR-2 on Windows Server 2003 to make sure the 
database was ok and it opened fine. I have scurried through on the net and 
most of the suggestions point to permissions which I can't see how it would 
apply in this instance.

Regards
Peter 

2.OpenRowSet Error

Has anyone ever seen this error?  If so what did you do to fix

Cannot bulk load. Invalid column number in the format file

3.How to catch OPENROWSET error in stored procedure

Hi Steve,

You can't handle this error gracefully in T-SQL. Because the error is in an
external source and SQL Server doesn't know what it means, it goes safety
first and aborts the SQL batch. You have to handle it in your client
application.

-- 
Jacco Schalkwijk
SQL Server MVP


"Steve" < XXXX@XXXXX.COM > wrote in message
news:125b01c3a872$0e0e9280$ XXXX@XXXXX.COM ...
> Hello,
>
> I have a stored procedure that performs an OPENROWSET to
> retrieve data from a dBase III DBF table. The location of
> the table can be different, so I pass the path as a
> parameter.
>
> The problem I am having is that occasionally the DBF file
> has corrupt records or feilds. This causes the OPENROWSET
> to fail. An example of the error messages I get when this
> happens is given below:
>
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'MSDASQL'
> IColumnsInfo::GetColumnsInfo returned 0x80004005:  The
> provider did not give any information about the error.].
>
> I have tried using IF @@ERROR <> 0 but the stored
> procedure bombs out anyway.
>
> How do I detect this error within my stored procedure and
> handle it gracefully?
>
> Many thanks,
>
> Steve

4.OpenRowSet Error 7353

When running the following query:
SELECT ID_i 
FROM 
OPENROWSET('SQLOLEDB','PARS100585';'abcp_admin';'Anna',abcp.dbo.Transactions)

I get the error:
Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column 
was supplied during execution that was not found at compile time.

I am testing on 2 servers with 2 databases on both and it will succsefully 
run against the three other database. The query will also succed on the 
problem one when I query another table. 

5.OPENROWSET error

Hello!

I am having a strange problem. I have a stored procedure accessing remote 
server using OPENROWSET. When I edit this procedure in VSS.NET, I am getting 
an error 'OLE/DB provider returned message: New transaction cannot enlist in 
the specified transaction coordinator...' Stored procedure is below:

ALTER PROCEDURE dbo.vsp_test1
AS

create table #tmp_pub_adv_agg(year int null,month int null,pubid int 
null,advid int null,impressions money null,

raw_clicks int null,unique_clicks int null,actions int null,

tot_cost money null,tot_revenue money null

)

INSERT INTO #tmp_pub_adv_agg(year,month,pubid,advid,impressions,

raw_clicks,unique_clicks,actions,

tot_cost,tot_revenue)

SELECT a.*

FROM OPENROWSET('SQLOLEDB','10.1.1.1;'user_name';'password',

'select top 1 datepart(yy, Date) as year, datepart(mm, Date) as 
month,pubid,advid,

sum([Impressions]) as impressions,sum([Raw Clicks]) as 
raw_clicks,sum([Unique Clicks]) as unique_clicks,

sum([Actions]) as actions,sum([Total Cost]) as tot_cost,sum([Total Revenue]) 
as tot_revenue --,COUNT(*) c

from tmpadserv_warehouse.dbo.Accounting_RevenueReport

where datediff(mm,''01/01/2002'', [Date])>=0 AND 
DATEDIFF(DD,Date,GETDATE())>0

AND DATEDIFF(MM,Date,GETDATE())=0

GROUP BY datepart(yy, Date), datepart(mm, Date),pubid,advid

') a

select * from #tmp_pub_adv_agg

RETURN

   I am able to edit/execute this procedure using Query Analyzer. I suspect 
that it has something to do with SET settings setup at connection level. I 
noticed that VSS.NET doesn't have set arithabort on set on connection level. 
But even if I reset this setting in QA, I am still able to edit this 
procedure. Am I on the right track? Even If I remove INSERT INTO part, I am 
still getting this error. I am not sure why SQL Server is trying to initiate 
distributed transaction when there is only SELECT statement.

    I am running SQL Server 2000 SP3.



Any help is greatly appreciated,

Igor

6. openrowset error in query analyzer

7. OPENROWSET error and SQL service restart

8. OpenRowSet error, can not kill hung spid