randhammack
asked on
SQL File List No Results via Web Page or Execute As
I have a stored procedure that gets data from SQL and also executes xp_dirtree to gather a file list, for linking. This works great when I execute the main stored procedure from SQL Mgmt Studio. However, when I try to execute it from the web page that dumps it into a .xlsx file, it does not give me the file list. (Whereas, the end users can choose the parameters they want for the results.)
1. Again, when I run via SQL it works fine.
2. The parameters being passed from the web page are correct.
3. I have given the domain account that runs SQL 'read' access to the directories and share. (Whereas, I have read this could be the issue)
4. I have tried to 'Execute as' when executing the xp_dirtree. (Doing this fails to give me a file list from within SQL Mgmt Studio)
Any advise would be helpful.
1. Again, when I run via SQL it works fine.
2. The parameters being passed from the web page are correct.
3. I have given the domain account that runs SQL 'read' access to the directories and share. (Whereas, I have read this could be the issue)
4. I have tried to 'Execute as' when executing the xp_dirtree. (Doing this fails to give me a file list from within SQL Mgmt Studio)
Any advise would be helpful.
Agree with David...
It will most likely be how the web app connects to your environment and then how that in turn connects to SQL Server.
So, if you can run it locally, it probably means you have the correct mapping and permissions. But your web user doesn't.
Unfortunately, xp_DirTree is one of those "unofficial" extended stored procedures.
So, for a start it will be hiding in master and you might need access or explicitly tell your routine to use master.
and from the above, you will see that it uses a DLL to execute, so, that will be something else to check up.
Ideally you use supported techniques such as xp_cmdshell to get your list...
It will most likely be how the web app connects to your environment and then how that in turn connects to SQL Server.
So, if you can run it locally, it probably means you have the correct mapping and permissions. But your web user doesn't.
Unfortunately, xp_DirTree is one of those "unofficial" extended stored procedures.
So, for a start it will be hiding in master and you might need access or explicitly tell your routine to use master.
use master
go
select sysusers.name, sysobjects.name, sysobjects.id
from sysobjects, sysusers, sysprotects
where sysobjects.id=sysprotects.id and sysprotects.uid = sysusers.uid
and sysobjects.name = 'xp_dirtree'
select * from syscomments where id = object_id('xp_dirtree')and from the above, you will see that it uses a DLL to execute, so, that will be something else to check up.
Ideally you use supported techniques such as xp_cmdshell to get your list...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ummm... can usually get xp_cmdshell to work especially when protected inside a stored procedure.
ASKER
Just going to give the access to that login.
Presumably when running from SSMS you have logged on with sa permissions.
What rights (in SQL) does the account get when running from the web page?
HTH
David