Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  BCP to output photos with different file names.

Author  Topic  AB1 Starting Member 1 Post Posted - 2014-03-18 : 06:31:12 Hello I have been tasked with extracting staff photos from an SQL Server and I have come across bcp so I am trying to use that. I have got my query working in cmd: bcp "select pictures.picture from pictures, staff where pictures.type= 1 and staff.staff_id = pictures.id and (staff.D in ('PROX') AND (STAFF.TEAM = 'ICT')) OR PICTURES.TYPE = 1 AND STAFF.STAFF_ID = PICTURES.ID AND (STAFF.D IN ('COMPANY1', 'COMPANY2', 'COMPANY3') and (staff.[group] like '%ict%'))" queryout "C:\abtest\test.jpg" -f "C:\pictures.fmt" -S SERVER -d DATABASE -T This copies 342 rows and outputs test.jpg (I am assuming all of those rows have gone into the one file or each row is overwriting the previous). If I add a where staff_id = 12345 then it comes up with the right photo so I think this part is ok. Now my problem is I want to output the staff.forenames and staff.surname with each individual photo so I should have 342 photos saved in my folder e.g. Joe Bloggs.jpg I have tried to follow help pages from the Internet and I have tried to adapt them in SQL Management Studio. I am under the impression I need to make some kind of loop. This is what I have so far: DECLARE @totrow int DECLARE @currow int DECLARE @result int DECLARE @nsql nvarchar(4000) DECLARE @sqlStatements table (ID int IDENTITY(1, 1), SqlStatement varchar(max)) declare @output varchar(500) = 'c:\abtest\ ' INSERT INTO @sqlStatements SELECT 'bcp "select pictures.picture from pictures, staff where pictures.type= 1 and staff.staff_id = pictures.id and (staff.area in ('PROX') AND (STAFF.TEAM = 'ICT')) OR PICTURES.TYPE = 1 AND STAFF.STAFF_ID = PICTURES.ID AND (STAFF.area IN ('COMPANY1', 'COMPANY2', 'COMPANY3') and (staff.[group] like ''%ict%''))" queryout @output + ' staff.forenames + ' staff.surname + ' .jpg -S SERVER -d DATABASE -T -f C:\pictures.fmt' FROM dbo.pictures, dbo.staff SET @totrow = 342 SET @currow = 1 WHILE @totrow > 0 and @currow <= @totrow BEGIN SELECT @nsql = SqlStatement FROM @sqlStatements WHERE ID = @currow EXEC @result = xp_cmdshell @nsql SET @currow = @currow + 1 END Any help would be greatly appreciated!