0

The following code is stored in a procedure on SQL Server 2008 and is executed using ADODB in MS Access; I am receiving the following error when trying to execute it in Access...

Run-time error -2147217913(80040e07)
[Microsoft][ODBC SQL Server Driver][SQL Server]
Conversion failed when converting the nvarchar value 'fldNumericData' to data type int.

fldNumeric holds only the values 0,1,2,3,4, and 5 but the following statement fails...

SELECT  Order#, MachineID, Addr1, 
        Addr2, City, [State], Country, ZIP, Contact, Phone, Fax, email,
        Product, CAST([fldNumeric] AS INT) AS [fldNumeric], 
        CAST([AutoSub] AS INT) AS [AutoSub], Mode, [FileName]
INTO #sRst1
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'C:\Documents and Settings\database.mdb';
                'admin';'',tempTable) LEFT JOIN FN_qryOne() ON fldMatch =                         
                 FN_qryOne.fldMatch AND fldCutOff = FN_qryOne.fldCutOff
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
codingManiac
  • 1,654
  • 4
  • 29
  • 42

1 Answers1

0

ugly that you have to join to a function but try this:

SELECT  Order#, MachineID, Addr1, 
    Addr2, City, [State], Country, ZIP, Contact, Phone, Fax, email,
    Product, CAST([fldNumeric] AS INT) AS [fldNumeric], 
    CAST([AutoSub] AS INT) AS [AutoSub], Mode, [FileName]

INTO  #sRst1
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'C:\Documents and Settings\database.mdb';
            'admin';'',tempTable) AS FM LEFT JOIN FN_qryOne() AS F ON FM.fldMatch =                         
             F.fldMatch AND FM.fldCutOff = F.fldCutOff
T McKeown
  • 12,971
  • 1
  • 25
  • 32
  • Still throwing the error :/ ... Interestingly enough it started after I added a UDF for bankers rounding (though I haven't even implemented the function yet). This is really odd, removing the function and restarting my computer will resolve the issue. – codingManiac Jan 14 '14 at 21:36
  • The root of my problem must be in the way my procedure is coded though, I assume. – codingManiac Jan 14 '14 at 21:37