September 11, 2006 at 4:58 pm
I have give the user xp_cmdshell execution permission on the master database. The use has full access to the file folder and to the database the query to generate the output result. It will run perfect if I grant sysadmin role to the user.
The following is the query we use.
SET @BCPCMD = 'BCP "' + @QUERYSTATEMENT + '" QUERYOUT "' + @FileName + '" -c -S ' + @@SERVERNAME + ' -T'
EXEC MASTER.dbo.XP_CMDSHELL @BCPCMD
September 11, 2006 at 8:10 pm
What is the error message which you are getting?
Are you doing bcp from remote machine?
September 12, 2006 at 7:35 am
As I recall the issues I ran into while starting out with BCP were permission related. On the server the database is on, check what the service MSSQLSERVICE is running as. And be sure that user has permission to the folder you're attempting to write to. I created a new user for this purpose.
An error message would be helpful in finding the problem.
September 12, 2006 at 1:53 pm
There is no error message. It just won't generate the file. I run the statement from the query analyzer against current database at current server. It will be fine when I give that user sysadmin right. The user can modify the file under the output file folder. The MSSQLSERVICE has mix mode.
September 12, 2006 at 1:58 pm
Are you writing the file to a network machine or to the machine the database is running on? I don't think you can write to a network machine.
September 12, 2006 at 2:05 pm
I wrote it to a network machine. It works if I give that user sysadmin right. We have another production server. It works on that production server just not on the test box. It is written to a network machine.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply