Help with xp_fileexist

Hello,
I am wanting to test to see if we have photos that exist on our photo
server. We store the photo names in a database and want to run a cursor to
check whether the physical photo is there. When I run the following:
select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
exec master..xp_fileexist @.File, @.i out
if @.i = 1
print ' exists'
else
print @.File + ' does not exists'
I get the does not exist msg. If I change the @.File to a file locally on the
dbserver box I get the exists msg. I seem to have a permission issue. The
instance on the dbserver is running on a local admin account that is not
part of the domain. Is there a way to run the script and impersonate someone
within the domain? Any ideas would be greatly appreciated.
JakeDepedning on how this is run, either your NT login, or the NT login SQL
Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
have the appropriate permissions...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>|||Wayne,
I login query analyzer using windows authentication. When I run the code
I still get the "does not exist" msg. However I can goto windows explorer
and open the file. So I have appropriate permissions. Any ideas on why else
it would tell me the file doesn't exist?
Jake
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Depedning on how this is run, either your NT login, or the NT login SQL
> Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
> have the appropriate permissions...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jake" <rondican@.hotmail.com> wrote in message
> news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> I am wanting to test to see if we have photos that exist on our photo
>> server. We store the photo names in a database and want to run a cursor
>> to
>> check whether the physical photo is there. When I run the following:
>> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
>> exec master..xp_fileexist @.File, @.i out
>> if @.i = 1
>> print ' exists'
>> else
>> print @.File + ' does not exists'
>>
>> I get the does not exist msg. If I change the @.File to a file locally on
> the
>> dbserver box I get the exists msg. I seem to have a permission issue. The
>> instance on the dbserver is running on a local admin account that is not
>> part of the domain. Is there a way to run the script and impersonate
> someone
>> within the domain? Any ideas would be greatly appreciated.
>> Jake
>>
>|||The problem is that SQL is running under a local account. This doesn't have
access to the remote fileshare. You would need to run under a domain user
account.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
> the dbserver box I get the exists msg. I seem to have a permission issue.
> The instance on the dbserver is running on a local admin account that is
> not part of the domain. Is there a way to run the script and impersonate
> someone within the domain? Any ideas would be greatly appreciated.
> Jake
>|||"Jake" <rondican@.hotmail.com> wrote in message
news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Wayne,
> I login query analyzer using windows authentication. When I run the
code
> I still get the "does not exist" msg. However I can goto windows explorer
> and open the file. So I have appropriate permissions. Any ideas on why
else
> it would tell me the file doesn't exist?
I'm pretty sure the SERVER needs to have permissions. What account is SQL
Server running under?
> Jake
>
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
> > Depedning on how this is run, either your NT login, or the NT login SQL
> > Server uses, or the login SQL Agent uses ( if a scheduled TSQL task)
must
> > have the appropriate permissions...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Jake" <rondican@.hotmail.com> wrote in message
> > news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> >> Hello,
> >>
> >> I am wanting to test to see if we have photos that exist on our
photo
> >> server. We store the photo names in a database and want to run a cursor
> >> to
> >> check whether the physical photo is there. When I run the following:
> >>
> >> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> >> exec master..xp_fileexist @.File, @.i out
> >> if @.i = 1
> >> print ' exists'
> >> else
> >> print @.File + ' does not exists'
> >>
> >>
> >> I get the does not exist msg. If I change the @.File to a file locally
on
> > the
> >> dbserver box I get the exists msg. I seem to have a permission issue.
The
> >> instance on the dbserver is running on a local admin account that is
not
> >> part of the domain. Is there a way to run the script and impersonate
> > someone
> >> within the domain? Any ideas would be greatly appreciated.
> >>
> >> Jake
> >>
> >>
> >
> >
>|||Greg,
SqlServer is running on an admin account of the local box only. The box
has been joined to the domain but the user that sql runs under doesn't exist
in the domain. I would prefer not to have sql run under a domain account if
there is a way to impersonate a user of the domain during the script but if
SQL needs to run under a domain user then I guess that's what needs to be
done. Do you have any further suggestions?
Jake
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:QN%zd.77824$Uf.41443@.twister.nyroc.rr.com...
> "Jake" <rondican@.hotmail.com> wrote in message
> news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Wayne,
>> I login query analyzer using windows authentication. When I run the
> code
>> I still get the "does not exist" msg. However I can goto windows explorer
>> and open the file. So I have appropriate permissions. Any ideas on why
> else
>> it would tell me the file doesn't exist?
> I'm pretty sure the SERVER needs to have permissions. What account is SQL
> Server running under?
>
>> Jake
>>
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> > Depedning on how this is run, either your NT login, or the NT login SQL
>> > Server uses, or the login SQL Agent uses ( if a scheduled TSQL task)
> must
>> > have the appropriate permissions...
>> >
>> > --
>> > Wayne Snyder, MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> > www.mariner-usa.com
>> > (Please respond only to the newsgroups.)
>> >
>> > I support the Professional Association of SQL Server (PASS) and it's
>> > community of SQL Server professionals.
>> > www.sqlpass.org
>> >
>> > "Jake" <rondican@.hotmail.com> wrote in message
>> > news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
>> >> Hello,
>> >>
>> >> I am wanting to test to see if we have photos that exist on our
> photo
>> >> server. We store the photo names in a database and want to run a
>> >> cursor
>> >> to
>> >> check whether the physical photo is there. When I run the following:
>> >>
>> >> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
>> >> exec master..xp_fileexist @.File, @.i out
>> >> if @.i = 1
>> >> print ' exists'
>> >> else
>> >> print @.File + ' does not exists'
>> >>
>> >>
>> >> I get the does not exist msg. If I change the @.File to a file locally
> on
>> > the
>> >> dbserver box I get the exists msg. I seem to have a permission issue.
> The
>> >> instance on the dbserver is running on a local admin account that is
> not
>> >> part of the domain. Is there a way to run the script and impersonate
>> > someone
>> >> within the domain? Any ideas would be greatly appreciated.
>> >>
>> >> Jake
>> >>
>> >>
>> >
>> >
>>
>|||Yup, Jasper is right... If your SQL Server is running under localsystem, it
does, and can not have access to any network resources..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>