none
Reagrding error Msg 7302, Level 16, State 1, Line 1

    Question

  • When i am using rowset function to access ms access database through sql server 2005 i am getting the follwing error.
    I created a linked server.but the same problem persists even then.
    Msg 7302, Level 16, State 1, Line 1
    Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    Please provide a solution as i have been struggling with this for two days..
    Friday, May 02, 2008 10:54 AM

All replies

  • Can you post your script?

    Can you do a SELECT?

     

     

    Friday, May 02, 2008 10:59 AM

  • Declare @sql varchar(8000),
        @FilePath    Varchar(200)
    declare     @FromDate    datetime
    declare     @ToDate        datetime   
    declare  @columnname varchar(2000)
    set @FilePath='D:\DCT\DCT Export and Import after 26042008\test.mdb'
    set @FromDate='19 feb 2008'
    set @columnname=''
    set @columnname=dbo.fn_GetcolumnNamesofTables(object_id('DCT_AdvanceWorkFlow'))
    set @sql='Insert into DCT_AdvanceWorkFlow ('+@columnname+')'+char(13)+char(10)+'Select  '+@columnname+char(13)+'From OPENROWSET('+ char(39)+ 'Microsoft.Jet.OLEDB.4.0' + char(39)+','  +char(39)+@FilePath + char(39)+ ' ;;,DCT_AdvanceWorkFlow ) '+'where  '+char(39)+convert(varchar(20),@FromDate,103) +char(39)+' between DCTFromDate and DCTToDate and DMLStatus = '+char(39)+'I'+char(39)
    exec(@sql)


    No even select doesn't work...
    Friday, May 02, 2008 11:03 AM
  • declare     @FromDate    datetime
    declare     @ToDate        datetime   
    declare  @columnname varchar(2000)
    set @FilePath='D:\DCT\DCT Export and Import after 26042008\test.mdb'
    --set @FromDate='19 feb 2008'
    --set @columnname=''
    --set @columnname=dbo.fn_GetcolumnNamesofTables(object_id('DCT_AdvanceWorkFlow'))
    set @sql='select * From OPENROWSET('+ char(39)+ 'Microsoft.Jet.OLEDB.4.0' + char(39)+','  +char(39)+@FilePath + char(39)+ ' ;;,DCT_AdvanceWorkFlow ) '
    exec(@sql)


    i have changed the query as u can see...this is also is giving the same error...
    Friday, May 02, 2008 11:06 AM
  • Make sure your SQL works first. You don't need CR/LF.

     

    Code Snippet

    Declare @sql varchar(8000),

    @FilePath Varchar(200)

    declare @FromDate datetime

    declare @ToDate datetime

    declare @columnname varchar(2000)

    set @FilePath='D:\DCT\DCT Export and Import after 26042008\test.mdb'

    set @FromDate='19 feb 2008'

    set @columnname=''

    set @columnname=dbo.fn_GetcolumnNamesofTables(object_id('DCT_AdvanceWorkFlow'))

    set @sql='Insert into DCT_AdvanceWorkFlow ('+@columnname+')'+char(13)+char(10)+'Select '+@columnname+char(13)+'From OPENROWSET('+ char(39)+ 'Microsoft.Jet.OLEDB.4.0' + char(39)+',' +char(39)+@FilePath + char(39)+ ' ;;,DCT_AdvanceWorkFlow ) '+'where '+char(39)+convert(varchar(20),@FromDate,103) +char(39)+' between DCTFromDate and DCTToDate and DMLStatus = '+char(39)+'I'+char(39)

    print @sql

    --exec(@sql)

     

     

     

    Friday, May 02, 2008 11:33 AM
  • I get the error when i execute it...if i print or select it,it works fine...
    the problem occurs on exec(@sql).

    print @sql works fine

    Friday, May 02, 2008 11:37 AM