We help IT Professionals succeed at work.

Error while call sp_OAMethod in Stored Procedure

atkinsHOU
atkinsHOU asked
on
3,334 Views
Last Modified: 2012-08-14
Hi, all,
I need to read a text file in stroed procedure in SQL Server 2005, part of codes like the following:


DECLARE @Text1 VARCHAR(MAX)

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
EXEC @OLEResult = sp_OAMethod @FS, 'GetFolder', @folder OUT, @FolderPath

IF @OLEResult <> 0
  BEGIN
      PRINT 'GetFolder Error'
      RETURN
  END

SET @FileNameFullPath = @FolderPath + '\' + @FileName
PRINT @FileNameFullPath
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileNameFullPath, 1
IF @OLEResult <> 0
   BEGIN
      PRINT 'OpenTextFile Error'
      RETURN
   END

--Read the first line into Text1
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Text1 OUT
IF @OLEResult <> 0
   BEGIN
     PRINT 'ReadLine - Error ' + convert(varchar(MAX),@OLEResult)
     --RETURN
   END

I check the folder, no problem, I open the text file, also ok. but when I read the text file, get the error at the following line:
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Text1 OUT

When I print @OLEResult, it show value 2147211492, what's this error?

The text file has 4 long line as the following
 "STR_ID"      "TITLE"      "PFIELD"      "PDESC"      "DEF_UNIT"      "COMP_ID"      "ID_NO"      "Q_ID"      "CODE"      "COMPDESC"      "S_NODE"      "F_NODE"      "S_LEG"      "F_LEG"      "ELV_1"      "ELV_2"      "DIST"      "CLK_POS"      "COMPTYPE"      "INSP_ID"      "INSP_DATE"      "INSP_TIME"      "INSPECTOR"      "PROC"      "EQUIP"      "EQ_ID"      "CP_RDG"      "SPEC"      "SURF_COND"      "CLEAN_MET"      "SCAF"      "SUPV"      "DIVR"      "DIVE_NO"      "ELEVATION"      "TOP_UND"      "DEPLET_GRADE"      "ANODE_OUTPUT"      "MG_TYPE"      "MG_THICK"      "MEMB_CP"      "LENGTH"      "CIRC_C1"      "CIRC_C2"      "CIRC_C3"      "CORE_CIRC"      "TYPE"      "CONNECTED"      "DEPLETION"      "TOPSTUB_CP"      "BOTSTUB_CP"      "AVG_PIT"      "MAX_PIT"      "DEFECT"      "DEFECT_CODE"      "DEFECT_TYPE"      "DEFECT_DESC"      "RECTIFID"      "RECTIFID_DESC"      "RECT_DATE"      "INSPNO"      "JOBNAME"      "STATUS"      "INSP_COND"      "CMNTS"
203      BKJT-A      BAKAU.      DRILLING JACKET.      METRIC      32898      AN/000027-000048//      BAN 13      AN      BAN 13      27      48      A2      A1      -8      -20                  ANODE      38364      30-05-2003      195720      MAZLAN BOLHI      CIMG      REMOTE CP      S002                        HP WATER      NO      GARY CAMERON      CHRIS LEONARD      A159      -8      SUBSEA            970                  962      1640      620      650      660            A      NO      20      960      960      15      22      NO                        NO                  00000000470      UIM03/D      CLOSED
203      BKJT-A      BAKAU.      DRILLING JACKET.      METRIC      32923      AN/000037-000028//      BAN 5      AN      BAN 5      37      28      B2      A1      -8      -8                  ANODE      38365      30-05-2003      201920      MAZLAN BOLHI      CIMG      REMOTE CP      S002                        HP WATER      NO      GARY CAMERON      CHRIS LEONARD      A159      -8      SUBSEA            970                  956      1640      650      665      715            A      NO      10      966      965      18      23      NO                        NO                  00000000470      UIM03/D      CLOSED
203      BKJT-A      BAKAU.      DRILLING JACKET.      METRIC      32916      AN/000067-000113//      BAN 29      AN      BAN 29      67      113      A2      B2      -37      -46                  ANODE      38366      31-05-2003      070320      PHILIP CHIN      CIMG      REMOTE CP      S002                        HP WATER      NO      KU LOI HOCK      DAVIN HARDING      A164      -39      SUBSEA            -988                  -984      1630      580      580      540            A      YES      40      -970      -970      30      40      NO                        NO                  00000000470      UIM03/D      CLOSED      SELECTED ANODE BAN A29(-)39M


Please help, it's in a hurry, thank ahead.

LT      



Comment
Watch Question

Author

Commented:
Sorry, the error number is -2147211492, not 2147211492.

LT
Hi,

Can you try changing this:

--Read the first line into Text1
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Text1 OUT

to this:

--Read the first line into Text1
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine', NULL, @Text1 OUT

Author

Commented:
Thanks for your reply,

I change to as the following, still get the same error.
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine', NULL, @Text1 OUT

LT
Can you just confirm your script prints out: "'OpenTextFile Error" ?

I can't find anything on that specific error code so I can only suggest a few things to look into:
- permissions
- file is locked - does anything else have the file open when you run the SQL?

Author

Commented:
I think there is no permission issue, I have worked on this sp for some times, and struggle this part of codes step by step.

If the folder name is wrong, then "'GetFolder Error' will be printed.
if I change the filename to an nonexisted file,open file step will cause "OpenTextFile Error" be printed.

So I am sure the error happen at readLine statement. the length of each row in the text file is long, is this the reason?

Author

Commented:
I create a small text file and test it, still get same error, so the error is not caused because of the long line of text.

What's the problem?

Author

Commented:
Hi,

When I execute as the following, it works fine. but I need the return value, what should I do?

execute @OLEResult = sp_OAMethod @FileID, 'ReadLine'

Author

Commented:
according to the link: http://support.microsoft.com/kb/325492
it seem we cannot get the return value from this statement:
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine'

How can I solve my problem that I need to read line by line in the text file?
Can anybody help me?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.