raise error

Question

Post reply

raise error

Mike Levan

SSChampion

Points: 14201
More actions
July 25, 2006 at 10:09 am

#115934

Can any

Viewing 15 posts - 1 through 15 (of 20 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Some questions...

1. Does this proc compile?

2. You have a ROLLBACK TRANSACTION but no begin or commit tran? Can you explain?

3. You are trying to create a table in your sproc? Why?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

If @File_Exists=1

BEGIN

TRUNCATE TABLE SIRSInput

EXEC('BULK INSERT Emp FROM ''E:\Development\Emp\'+@FileName+'.txt''

WITH (formatfile =''E:\Development\Emp\InputFormat.fmt'')')

END

ELSE

BEGIN

RAISERROR ('File does not exist!', 16, 1)

END

Kindest Regards,

Vasc

RyanRandall SSChampion Points: 13623 More actions · Answer 3

I've just read what Vasc has written, and I agree with that approach. I just wanted some answers first

A further question is why you're removing data from SIRSInput, but inserting into Emp?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Mike Levan SSChampion Points: 14201 More actions · Answer 4

sorry that was my mistake..I edited the table name.

but still am unable to get that error mesg when failed and also executing the bulkinsert command though it didnt meet the if statement.

Mike Levan SSChampion Points: 14201 More actions · Answer 5

Every time I execute EXEC Master..xp_fileexist , It is giving the error mesg file not found, either success or failure.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 6

-did you check if the file really exists?

-the executing user has required security rights to access the file?

Kindest Regards,

Vasc

Mike Levan SSChampion Points: 14201 More actions · Answer 7

yeah THe file exists there.

when I am able to bulk insert out of SP then it says that I have rights to that file.

Mike Levan SSChampion Points: 14201 More actions · Answer 8

The problem here is it is not executing IF statement , directly jumping to ELSE though the return value is 1

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 9

can t jump to else if return value is 1

the problem is that when you test you use a USER that has access rights to file and when you actually run it that user doesn t have access rights to file. Check the access rights for executing user.

(do a FULL SHARED FOLDER FOR ALL put your file there and pass the UNC path to SP if it works you have rights problem )

Kindest Regards,

Vasc

Mike Levan SSChampion Points: 14201 More actions · Answer 10

I shared the folder, but still the same.

is there anything else i can try

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 11

both files? the format and source file ?

I tried the statement and it works fine ... the problem is somewhere else...

Did you HARDCODED the file name for testing purposes?

Kindest Regards,

Vasc

Mike Levan SSChampion Points: 14201 More actions · Answer 12

yeah i have both files in the same shared folder.

No..its real file not hardcoded.

ANY HELP.............

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 13

EXEC Master..xp_fileexist 'E:\Development\Emp\@FileName.txt', @File_Exists OUTPUT

ya after a closer look .... this file DOESN T EXISTS

this one might

EXEC Master..xp_fileexist 'E:\Development\Emp\'+@FileName+'.txt', @File_Exists OUTPUT

Kindest Regards,

Vasc

Mike Levan SSChampion Points: 14201 More actions · Answer 14

yeah exactly...but i think single quotes doesnt work here again.