August 28, 2008 at 1:54 pm
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.
January 25, 2009 at 4:36 pm
Hi
Have you tried to replace all the single quotes '' with square brackets [] ?
Single quotes in SQL Server are text delimiters
whereas square brackets are words/names delimiters
so instead of:
SELECT
'PropertyState' = [Property State] ,
'PropertyCounty' = [Property County] ,
....
write:
SELECT
[PropertyState] = [Property State] ,
[PropertyCounty] = [Property County] ,
....
Alain
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply