HI
{edited Query to Use a Cursor but it runs without error but does not output}
I want to export Images associated to Products from a sales Order , I have borrowed and eddited a stored procedure to do this but i have come unstuck on 2 points
1- I cant get @Filename to link to the relevant Product
2 - It will only output one Image file and only if i apply TOP 1 to the Syntax
Any assistance as always appreciated
use Training
DECLARE @ImageData VARBINARY (max);
DECLARE @Path2OutFile NVARCHAR (2000);
DECLARE @Obj INT
DECLARE @ImageFolderPath NVARCHAR(1000)
DECLARE @ImageFilename NVARCHAR(1000)
SET @ImageFolderPath = '\ART-WM01\Import Services\TEST DUMP'
-- select @ImageFilename = (Select Productid from [#TempT3])
-- select @ImageFilename = (Select Prodcutid from [#TempT2])
CREATE TABLE [dbo].[#TempT3](
Productid NVARCHAR(15),
ImageData VARBINARY (max)
)
INSERT INTO #TempT3
select top 1 ProductId as ImageFilename ,
(SELECT top 1
(DocumentData) AS Expr1
FROM Documents
WHERE (TableName LIKE 'WebImages') and (DocumentTitle LIKE Products.ProductId) and(DocumentArchived = 0) AND (Identifier = Products.Product)
GROUP BY DocumentData) as ImageData
FROM SalesOrders INNER JOIN
SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder INNER JOIN
Products ON SalesOrderItems.Product = Products.Product AND SalesOrderItems.Product = Products.Product
WHERE Salesorders.SalesOrderId = 'UKS0074438'
--select *
--from [dbo].[#TempT1]
SET NOCOUNT ON
DECLARE db_cursor CURSOR FOR
SELECT @ImageData
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Path2OutFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Path2OutFile = CONCAT (
@ImageFolderPath
,'\'
, @ImageFilename
+'.Jpeg'
);
BEGIN TRY
EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
EXEC sp_OASetProperty @Obj ,'Type',1;
EXEC sp_OAMethod @Obj,'Open';
EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
EXEC sp_OAMethod @Obj,'Close';
EXEC sp_OADestroy @Obj;
END TRY
BEGIN CATCH
EXEC sp_OADestroy @Obj;
END CATCH
FETCH NEXT FROM db_cursor INTO @Path2OutFile
END
CLOSE db_cursor
DEALLOCATE db_cursor
Drop Table #TempT3