SQLTeam.com | Weblogs | Forums

Out of memory problem

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

BCP is really for bulk processing.

I would be inclined to use Powershell for this type of processing preferably not running on the SQL Server machine.

There are plenty of examples.

eg

https://social.technet.microsoft.com/wiki/contents/articles/890.export-sql-server-blob-data-with-powershell.aspx

1 Like

Thanks Ifor, I ran the script with SQLCMD and it worked. SSMS was the problem, apparently.