Sql
Here you can find all sql related posts. Clicking read more will take you to the full story.
I used the following code to import a bulk of pictures and link them directly to a product within Exact Globe Next.
Y:\ is a mapped directory on de SQL server.
You uncomment print @sql to print out any sql errors.
[code]
USE [exact administration]
DECLARE @itemcode varchar(max)
DECLARE image_cursor CURSOR FOR
SELECT ItemCode FROM Items WHERE IsSalesItem = 1 AND syscreated > '2013-01-01'
OPEN image_cursor;
FETCH NEXT FROM image_cursor
INTO @itemcode;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(MAX)
DECLARE @clean VARCHAR(MAX)
DECLARE @imagePath VARCHAR(255)
DECLARE @image VARCHAR(255)
SET @image = RTRIM(LTRIM(@itemcode)) + '.jpg'
SET @imagePath = 'Y:\' + @image
SET @sql = 'UPDATE Items
SET Picture = (SELECT *
FROM OPENROWSET(BULK ''' + @imagePath + ''', SINGLE_BLOB) AS img),
PictureFileName=''' + @image + ''' WHERE ItemCode = ''' + @itemcode + ''''
--PRINT @sql
BEGIN
EXEC(@sql)
END
FETCH NEXT FROM image_cursor
INTO @itemcode;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;
SELECT ItemCode, Picture, PictureFilename FROM Items
WHERE Picture IS NOT NULL
[/code] Read more...
For my current job I get to develop a lot of SQL queries to pull all sorts of data from a ERP-program called Exact.
Would you like it if I posted some custom reports or queries that I build? [a=../contact]Contact me here[/a]. Read more...
