Post reply

Transfering the Excel Data to 64 bit MS-sqlserver 2005 using openrowset

  • August 9, 2007 at 12:19 pm

    #180040

    Hi

    How to transfer the data from excel to the 64 Bit Ms-sqlserver 2005 using openrowset method. i am able to insert successfully in 32 Bit Ms-sqlserver 2000. please find the code below which i used for ms-sqlserver 2000.

    INSERTINTO STG_MANPOWER_DETAIL (MONTH_DATE,COST_CENTER_ID,SCENARIO,UNIQUE_NAME,CLEAN_ID,PEOPLESOFT_ID, [POSITION],[NAME],TYPE,STATUS,COMMENT1,CORPORATE_TITLE,START_DATE,SOURCE_DESCRIPTION, REPORTING_MANAGER,DBPRISM,EMAIL,HEADCOUNT,FTE,DATA_SOURCE_ID,EXTR_DATE) SELECT MONTH,COSTCENTRE,PERIOD,UNIQUENAME,CLEANID,PEOPLESOFT, [POSITION],[NAME],TYPE,STATUS,COMMENT1,[CORPORATE TITLE],CONVERT(char(10),STARTDATE,111),SOURCEDESCRIPTION, REPORTINGMANAGER,DBPRISM,EMAIL,HC,FTE,13,CONVERT(CHAR(8),GETDATE(),112)+' '+CONVERT(CHAR(5),GETDATE(),114)

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=D:\MRLOAD.xls;IMEX=1',

    'SELECT MONTH,COSTCENTRE,PERIOD,UNIQUENAME,CLEANID,PEOPLESOFT,[POSITION],

    [NAME],TYPE,STATUS,COMMENT1,[CORPORATE TITLE],STARTDATE,SOURCEDESCRIPTION,

    REPORTINGMANAGER,DBPRISM,EMAIL,HC,FTE,13 FROM [Data$]').

    when i try to do the same in Ms-sqlserver 2005, its thrwoing error , SQL OLE DB is not registered. Please let me know,is there any other that i can proceed...

    Thx

    Naren

  • August 9, 2007 at 12:30 pm

    #725829

    The problem is that Microsoft has not released a 64-bit MDAC which includes the Jet database engine. It makes 64-bit reading of Excel or MS Access data sources in SSIS or via linked servers/openquery, etc... very difficult. Can you move the data out of excel and into CSV?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply