I have 1 table which contains a KEY and a file ref at the end of each record. I also have another table which has a lot of records with a KEY contained against each record. I then link both tables together by the KEY and wish to export the data based on the file ref. When I run it as a select query it works fine... but when I run it with the purpose of generating a file for each file ref it fails with the error in the title preceded by the line 'SQLState = S1000, NativeError = 0'. I have access to the directory and I'm running the code on the server. Any guidance would be appreciated.
DECLARE @File_number INT
DECLARE @SQL VARCHAR(8000)
DECLARE file_num CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT DISTINCT File_number FROM map_sequence_tranid ORDER BY 1
OPEN file_num
FETCH NEXT FROM file_num INTO @File_number
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQL = 'bcp "SELECT b.File_number, a.[Field1], b.[Field2] from Table1 a, Table2 b where a.[Key]=b.[key] and b.File_number=' + CAST(@File_number as varchar(10)) + ' order by a.[key]" queryout ''E:\Path\file' + CAST(@File_number AS VARCHAR(10)) + '.txt'' -c -T -t'','' -S ' + @@SERVERNAME
EXEC master..xp_cmdshell @SQL
FETCH NEXT FROM file_num INTO @File_number
END
CLOSE file_num
DEALLOCATE file_num
It is not your account that needs access to the share. Since you are running the BCP command through the "xp_cmdshell" command, the account that actually executes the bcp command is the same account that is running the SQL Server service on the SQL Server box. When you use "xp_cmdshell" you leave your session/authentication behind and pass control to a new session outside of SQL Server. This is done using the account that is running the SQL Server service and the command is executed on the OS that the SQL Server is running on. Most likely, you are not even able to logon to the OS underneath the SQL Server.
You must confirm that the SQL service account has access to the share.
Im not certain that this is your issue, but it may be. You can test other possibilties by:
Print the contents of the @SQL command instead of executing it. Copy that value into a command window and try to run the command yourself. This will test that the command is valid and that things like paths are valid.
If you can, log onto the physical server (windows?) that your SQL Server is running on. Log on as the SQL Server service account. Then try to execute the bcp command there in a command window. This is the most complete test, but usually we dont have permission to authenticate using an account that is being used to run the SQL Server service.