SQL Xtended Stored Procedure Test

• BigCityCheese

  Old Hand

  Points: 313

  More actions

  May 4, 2007 at 6:32 pm

  #195564

  Hello,

  We are experiencing some issues with using extended stored procedures.

  The curious thing is that the direct execution of master..xpfileexist works just fine :

  exec master..xp_fileexist 'F:\tomcatdata\WCS\esp\draftfile\GLS_0001_ACH_DRAFT.DAT'

  However, when trying to code/execute within the following stored procedure :

  -------

  declare @PathFileName varchar(800)

  set @PathFileName = 'F:\tomcatdata\WCS\esp\draftfile\GLS_0001_ACH_DRAFT.DAT'

  declare @FileExists varchar(800)

  set @FileExists = 'xp_fileexist '''+@PathFileName+''''

  print @FileExists

  exec @FileExists

  -----------

  We are experincing the following error message :

  xp_fileexist 'F:\tomcatdata\WCS\esp\draftfile\GLS_0001_ACH_DRAFT.DAT'

  Msg 2812, Level 16, State 62, Line 6

  Could not find stored procedure 'xp_fileexist 'F:\tomcatdata\WCS\esp\draftfile\GLS_0001_ACH_DRAFT.DAT''.

  I'm not exactly sure why this behavior is occurring and I was hoping that you might share some of your insight and expertise regarding the use and coding of extended stored procedures.

  Thanks

  Dom

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  May 4, 2007 at 7:14 pm

  #704492

  I have NO experience with extended stored procedures, but my first thought is ...

  Change:

  set @FileExists = 'xp_fileexist '''+@PathFileName+''''

  To:

  set @FileExists = 'master..xp_fileexist '''+@PathFileName+''''

  AGAIN, JUST MY 1.5 cents!

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• hr_sn

  Ten Centuries

  Points: 1062

  More actions

  May 6, 2007 at 9:59 pm

  #704558

  I had the same issue with sp_sendmail SP and later I found the user who was trying to access wasn't having enough rights to execute/access it. That might be something you wish to look for.

• MohammedU

  SSCoach

  Points: 19644

  More actions

  May 6, 2007 at 10:32 pm

  #704562

  I believe Jason is right and you have to qualify with database and user name....at the same time make sure user has sufficient rights...

  declare @filepath sysname

  select @filepath = 'c:\boot.ini'

  exec master.dbo.xp_fileexist @filepath

  MohammedU
  Microsoft SQL Server MVP