OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied

Author

Topic

jjamjatra
Starting Member

13 Posts

Posted - 2008-08-28 : 15:47:39

I cannot see why I am getting this error msg in QueryAnalyzer results window:
Server: Msg 7354, Level 16, State 1, Line 10
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied invalid metadata for column 'Property State'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'Property State' (ordinal 1) of object 'SELECT
[Property State] ,
[Property County] ,
[Property Zip] ,
[Originating Mortgagee] ,
[Originating Mortgagee Number] ,
[Sponsor Name] ,
[Sponosr Number] ,
[Down Payment Source] ,
[Non Profit Number] ,
[Product Type] ,
[Loan Purpose] ,
[Property/Product Type] ,
[Interest Rate] ,...

Here is my query producing the above error (using SQL 2000):

SELECT
'PropertyState' = [Property State] ,
'PropertyCounty' = [Property County] ,
'PropertyZip' = [Property Zip] ,
'OriginatingMortgagee' = [Originating Mortgagee] ,
'LenderID' = Convert(varchar(6),[Originating Mortgagee Number]) ,
'SponsorName' = [Sponsor Name] ,
'SponsorID' = Convert(varchar(6),[Sponosr Number]) ,
'DownPaymentSource' = [Down Payment Source] ,
'NonProfitTaxID' = str([Non Profit Number]) ,
'ProductType' = [Product Type] ,
'LoanPurpose' = [Loan Purpose] ,
'PropertyProductType' = [Property/Product Type] ,
'InterestRate' = Convert(float,[Interest Rate]) ,
'MortgageAmount' = Convert(int,[Mortgage Amount]) ,
'SaleYear' = Convert(int,[Year]) ,
'SaleMonth' = Convert(int,[Month] )

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\RawData_MDS\FHA_Rawdata_P_or_R.xls;HDR=Yes;IMEX=1',
'SELECT
[Property State] ,
[Property County] ,
[Property Zip] ,
[Originating Mortgagee] ,
[Originating Mortgagee Number] ,
[Sponsor Name] ,
[Sponosr Number] ,
[Down Payment Source] ,
[Non Profit Number] ,
[Product Type] ,
[Loan Purpose] ,
[Property/Product Type] ,
[Interest Rate] ,
[Mortgage Amount] ,
[Year] ,
[Month]
FROM [Sheet1$]')

Strangely, if I simplify the query just a bit like this below (i.e. accepting all column names as they are from Excel), then everything returns fine:

SELECT
*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\RawData_MDS\Book2.xls;HDR=Yes;IMEX=1',
'SELECT
[Property State] ,
[Property County] ,
[Property Zip] ,
[Originating Mortgagee] ,
[Originating Mortgagee Number] ,
[Sponsor Name] ,
[Sponosr Number] ,
[Down Payment Source] ,
[Non Profit Number] ,
[Product Type] ,
[Loan Purpose] ,
[Property/Product Type] ,
[Interest Rate] ,
[Mortgage Amount] ,
[Year] ,
[Month]

FROM [Sheet1$]')

There is nothing special about the first column of the excel file; it is defined as format 'General' and populated with state codes like TN,TX,VA,CA,etc.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 06:07:28

Try this. Some providers have difficulties with single quotes.

SELECT	[Property State] AS PropertyState,
	[Property County] AS PropertyCounty,
	[Property Zip] AS PropertyZip,
	[Originating Mortgagee] AS OriginatingMortgagee,
	CONVERT(VARCHAR(6), [Originating Mortgagee Number]) AS LenderID,
	[Sponsor Name] AS SponsorName,
	CONVERT(VARCHAR(6), [Sponosr Number]) AS SponsorID,
	[Down Payment Source] AS DownPaymentSource,
	STR([Non Profit Number]) AS NonProfitTaxID,
	[Product Type] AS ProductType,
	[Loan Purpose] AS LoanPurpose,
	[Property/Product Type] AS PropertyProductType,
	CONVERT(FLOAT, [Interest Rate]) AS InterestRate,
	CONVERT(INT, [Mortgage Amount]) AS MortgageAmount,
	CONVERT(INT, [Year]) AS SaleYear,
	CONVERT(INT, [Month]) AS SaleMonth
FROM	OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\RawData_MDS\FHA_Rawdata_P_or_R.xls;HDR=Yes;IMEX=1', 
	'SELECT	[Property State] ,
		[Property County] ,
		[Property Zip] ,
		[Originating Mortgagee] ,
		[Originating Mortgagee Number] ,
		[Sponsor Name] ,
		[Sponosr Number] ,
		[Down Payment Source] ,
		[Non Profit Number] ,
		[Product Type] ,
		[Loan Purpose] ,
		[Property/Product Type] ,
		[Interest Rate] ,
		[Mortgage Amount] ,
		[Year] ,
		[Month] 
	FROM	[Sheet1$]')

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources