Hi,
I have a script I'm running to pull images out of a table and store them in a folder. There are several thousand rows (images). When I use the following script I keep getting Server Out of Memory errors at around 2000 images saved. I tried running the free cache options, but that caused Management Studio to crash after around 2k images exported. Can anyone suggest a reason or fix? The SQL Server is configured with 64 gig of memory. Thanks
DECLARE @client varchar(11), @category varchar(15), @id int, @sql varchar(5000), @filename varchar(50)
DECLARE @outputpath varchar(500), @dbname varchar(500)
SET @outputpath = 'E:\Images'
SET @dbname = 'IMAGE_PRODUCTION'
DECLARE qimage CURSOR FAST_FORWARD FOR
SELECT client_code, category, imageid FROM dbo.images WITH (NOLOCK)
OPEN qimage
FETCH NEXT FROM qimage INTO @client, @category, @id
WHILE @@fetch_status = 0
BEGIN
SET @filename = RTRIM(@client) + '' + RTRIM(@category) + '' + CAST(@id AS varchar(50)) + '.pdf'
SET @sql = 'BCP "SELECT hidef_image FROM ' + @dbname + '.dbo.images WITH (NOLOCK) WHERE imageid = ' +CAST(@id AS varchar(50)) + ' " queryout "' + @outputpath + @filename + '" -T -N'
EXEC master..xp_cmdshell @sql
--DBCC FREESYSTEMCACHE
--DBCC FREESESSIONCACHE
--DBCC FREEPROCCACHE
FETCH NEXT FROM qimage INTO @client, @category, @id
END
CLOSE qimage
DEALLOCATE qimage