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')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!|||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.]'|||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.|||Same error. Not sure what version it was created in as I am importing it from a 3rd party vendor.|||Hi.
why not use a DTS package? and test this SELECT *
FROM D1107...[Line Report]|||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.
没有评论:
发表评论