Results 1 to 7 of 7

Thread: bcp import help

  1. #1
    Join Date
    Nov 2007
    Posts
    8

    Unanswered: bcp import help

    I am trying to import data thru a bcp call to pull data from an access database. I am having trouble accessing the access database. Below is the bcp I tried, along with an openrowset attempt. Neither of them are working. Any help would be greatly appreciated.

    bcp select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
    noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
    totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
    ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
    abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
    from Line Report in I:\2007\11\D1107.MDB -q -UXX -PXX

    select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
    noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
    totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
    ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
    abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'I:\2007\10\D1007.MDB';'XX';'XX', 'Line Report')

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you able to add the mdb as a linked server - you could then simply run SQL statements irectly on the data? I'm afraid I am unable to test this from my current location so I'm not able to check!
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Posts
    8
    I can add it as a linked server, but then I cant actually query against any of the access tables.

    sp_addlinkedserver 'D1107', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
    '\\ctisvr\stats\2007\11\D1107.MDB'

    SELECT *
    FROM D1107...Line Report

    when I run the select statment I then get:
    'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
    [OLE/DB provider returned message: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.]'

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you get the same error message for the OPENROWSET query? Also, what version is the Access database created in?

    As a side note, you have probably already learned that BCP is used solely for "flat files". Pure ASCII/UNICODE characters.

  5. #5
    Join Date
    Nov 2007
    Posts
    8
    Same error. Not sure what version it was created in as I am importing it from a 3rd party vendor.

  6. #6
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Hi.
    why not use a DTS package? and test this SELECT *
    FROM D1107...[Line Report]

  7. #7
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    Did you try the DTS Wizard?

    I don't have a full version of Sql Server, only MSDE. So I didn't have the DTS Wizard. Then I looked in an old Office 2000 disk I've got, and found it. I copied over dtswiz.exe and some other .dll and .rll files to my Sql Server\tools\binn folder and it worked.
    Don't Bogart That Squishee!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •