I have a procedure @lpInputPath NVARCHAR(2000), @lpMeasNum NVARCHAR(20), @lpOutputPath NVARCHAR(2000) = NULL OUTPUT AS BEGIN DECLARE @lsInputPath NVARCHAR(1000), @lsMeasNum NVARCHAR(20), @flag INT = 0; --check to see if the temp table dose exist, if it exist drop it and --re-create it. IF OBJECT_ID('tempdb..#xp_fileexist_output') IS NOT NULL DROP TABLE #xp_fileexist_output; --Create the temp table CREATE TABLE #xp_fileexist_output ( [FILE_EXISTS] int not null, [FILE_IS_DIRECTORY] int not null, [PARENT_DIRECTORY_EXISTS] int not null) SELECT @lpInputPath = REPLACE(@lpInputPath,'ä',@lpMeaNum); SET @lsInputPath = @lpInputPath ----Insert into the temp table INSERT INTO #xp_fileexist_output EXEC MASTER.dbo.xp_fileexist @lpInputPath --Check to see if the directory for measDir exist or not SELECT @flag = FILE_IS_DIRECTORY FROM #xp_fileexist_output IF (@flag = 1) BEGIN SET @lpOutputPath = '<a href="http:' + @lpInputPath + '">Open link...</a>' END ELSE BEGIN SET @lpOutputPath = NULL END I have a local SQL Server 2008/Report services and this works very well. When I push this into my Test Server (exact version), it always returns 0. Both machines are have a full rights on that directory too, but one works on not. Do I have to run a special setup on my "test" server? Any help would be great. Thanks again IA

Having in mind that xp_fileexist is an extended stored procedure, have you enabled xp_cmdshell on the Test Box? Another thing is this SP is not working with network drives. About permissions - the directory should have permissions for the SQL Server service account (unless it is Local System, of course). Ivan Donev MCTS SQL 2005,MCTS SQL Server 2008, MCITP 2008 BI Development

Both machines are have a full rights on that directory too, but one works on not. Hello, With drive/folder are you try to access? Is it a local on or a network share? Under with account is the SQL Server service running? It the service runs under "LocalSystem" as by default the db engine can't access network resources and here it doesn't mind if "the machine" has full rights, the account of the engine don't have the right to access. .... REPLACE(@lpInputPath,'ä',@lpMeaNum); Only ä? What about öü and ß? Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Thank you to both Ivan and Olaf for your respond. SQL Server service = NT AUTHORITY\NETWORK "It the service runs under "LocalSystem" as by default the db engine can't access network resources and here it doesn't mind if "the machine" has full rights, the account of the engine don't have the right to access." ===> Do you mean I need to create an account in our domain with access to all of the directories, then add that user into SQL Server and have it to be the DataSource user for my report? Is it easy to change the "the account of the engine "? Lets say I have 5 users and on the network all these 5 users have a directory assigned to them and each one has access to their own dir. So each user has a folder \\server\dir\usern\ In SQL Server I have added domain\Domain Users and all of them have execute rights on the stored procedure that I am using. I assume by now each one of those 5 users have access to the stored procedure. In the Report Manager inside the data source area if I modify my dataSource to use Credentials supplied by the user running the report option and use the check box in front of Use as Windows credentials when connecting to the data source then when reports lunches, it will ask for username and password. If each user type their username and password then it works perfectly. I want to use the Windows integrated security instead. I have enabled xp_cmdshell on my Text box too. On our Intranet a user clicks on a report link, I am getting the username (SYSTEM_USER) and find which group they are in LDAP, build the directory and pass all the info to my udsp to just check if they have specifice directory in their \\server\dir\usern\'ä'-2010 I crate a link on a report for that directory. Thanks for any help I can get.

Hello again, The stored procedure xp_fileexist does not work with network shares so currently you could not use it in your logic.Ivan Donev MCTS SQL 2005,MCTS SQL Server 2008, MCITP 2008 BI Development

Thank you Ivan, so what could be my alternative because this the very important part of my project. If this part does not work then the project is failed. Question: 1- Why it works in this situation “In the Report Manager inside the data source area if I modify my dataSource to use Credentials supplied by the user running the report option and use the check box in front of Use as Windows credentials when connecting to the data source then when reports lunches, it will ask for username and password. If each user types their username and password then it works perfectly. “ 2- If I add a user into our LDAP, give it rights to the network folders and add this user into SQL Server then in SQL Server Security tab Enable server proxy account add this user would it make any difference? 3- If I change the SQL Server services owner from NT AUTHORITY\NETWORK to this user I just created then may be? 4- If I create C# and add it into the report .dll then?

Hello, It is kind of strange that with manually entered username/password the logic works and with integrated security it doesn't. Besides running SQL Server under NT AUTHORITY\NETWORK... is not recommended by Microsoft, it will be actually good idea to change the service to run under a domain account. Please refer to this article: http://msdn.microsoft.com/en-us/library/ms143504(v=SQL.90).aspx. If you do that, then on the network shares you will have to give permissions on the SQL Server account. Can you try that and give feedback if it works. Ivan Donev MCTS SQL 2005,MCTS SQL Server 2008, MCITP 2008 BI Development

Sure, I will do this to make sure BUT, do I need to change all of the service, ie. sql server, report services... and if yes I was reading from a book that for changing the report services service account, I better use the reporting services configuration manager instead of windows services applet. I cannot believe my entire work is halted just because of this :( Will let you know....

Okay, when I changed the report and sql server services to run under a domain\user account then I could not access the report manager or reports from anywhere else, it just keep asking for username and password. I could access the report and report manager from the server itslef but not anywhere else. I found this link, do you think this would help https://connect.microsoft.com/SQLServer/feedback/details/533197/sql-server-2008-r2-november-ctp-forms-authentication-not-working-in-ssrs?wa=wsignin1.0

Hi, I am not sure if I start over with new post or just continure. DECLARE @FileName varchar(255) SELECT @FileName='\\server\folderA\folderB' EXEC Master.dbo.xp_fileexist @filename GO Question: 1- If I I run this, and i have a rights to the folderB then it works but if I don't have a rights to the folderB it does not work, what is executing the xp_fileexisit? Is it the user who loged in or the sql server services? 2- If I go ahead and re-install sql server 2008 r2, and I want the sql server to be able to check if a directory or even a file exisit do I need to setup the services to run as a local or domain user? 3- How would you do it if you wanting to see (from report) if the dir exisit or not? Thank you.