Referencing images for an image itemwithin a table

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Referencing images for an image itemwithin a table

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-03-12 : 04:45:45

I have a table with an image item. I would like to display an image from the hard drive. I have tried setting the value of the image item to the following with no luck:

="file:" & First(Fields!EntryValue.Value, "Event_Images_Location") & "\VCPRS\" & "ReportBanner.png"

This provide the value of:

file:c:\vcp\vcpdataroot\VCPRS\ReportBanner.png

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 12:19:32

is image in local machine or in machine where report server is configured?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-03-12 : 17:48:26

SQL Server and the directory with the image files (jpg) are on the same PC. I cannot put the jpg files in the database due to the quantity and their size unless there is a way of having a reference in the db but the files on a hard drive. I do not want to use IIS. That is what I was doing with SQL 2005 but one of the main reasons for upgrading to 2008 is to get rid of IIS and reduce the setup complexity.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-03-14 : 20:53:30

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:29:02

quote:
Originally posted by harlingtonthewizard

SQL Server and the directory with the image files (jpg) are on the same PC. I cannot put the jpg files in the database due to the quantity and their size unless there is a way of having a reference in the db but the files on a hard drive. I do not want to use IIS. That is what I was doing with SQL 2005 but one of the main reasons for upgrading to 2008 is to get rid of IIS and reduce the setup complexity.

Since you're using SQL 2008, you can use FILESTREAM access to achieve that. See below

http://visakhm.blogspot.com/2010/02/filestream-storage-in-sql-2008.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jet1337
Starting Member

11 Posts

Posted - 2010-03-16 : 03:10:06

thanks for link

ASPnix.com

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-13 : 03:28:32

I ended up importing the required images into a temp table for use on a report by using the following example stored procedure.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Event_Image] Script Date: 04/13/2010 16:57:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER Procedure [dbo].[Event_Image]

@EventID nvarchar(255),
@DBName nvarchar (max)

AS

SET NOCOUNT ON

Declare @File As nvarchar(255)
Declare @Path As nvarchar(255)
Declare @EventID1 As int
Declare @EventExtraDataID As int
Declare @sql As nvarchar(max)

Select @sql=''
Select @sql=@sql+ 'Select EventDetails.EventID, EventExtraDataID, EntryValue + ''\EventExtraData\'' + CAST(SiteID As nvarchar(3)) + ''\'' + CAST(DATEPART(yyyy, EventStartTime) As nvarchar(4)) + ''\'' + CAST(DATEPART(mm, EventStartTime)As nvarchar(2)) + ''\'' + CAST(DATEPART(dd, EventStartTime)As nvarchar(2)) + ''\'' + FileName As [Path], FileName As [FileName]
FROM '+name+'.dbo.EventDetails
Inner Join '+name+'.dbo.EventExtraData ON '+name+'.dbo.EventDetails.EventID = '+name+'.dbo.EventExtraData.EventID
Left Join '+name+'.dbo.VCSystemData ON VCSystemData.EntryName = ''VCPDataRootPath''
WHERE EventDetails.EventID IN (Select Param From fn_MVParam ('''+@EventID+''','',''))
AND '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','',''))
' from sys.databases where name='VC' or name like 'VCA%'

CREATE TABLE #i ([EventID] int, [EventExtraDataID] int, [Path] nvarchar(255), [FileName] nvarchar(max))
INSERT INTO #i ([EventID], [EventExtraDataID], [Path], [FileName])

Exec(@sql)

CREATE TABLE #ib (ID int IDENTITY(1,1) NOT NULL, [FileName] nvarchar(255) NOT NULL, [Path] varbinary(max), [EventID] int, [EventExtraDataID] int)

DECLARE insert_cursor CURSOR FOR

Select [EventID], [EventExtraDataID], [Path], [FileName] from #i

OPEN insert_cursor

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

WHILE @@FETCH_STATUS = 0

BEGIN

--Print @Path
--Print @File

EXEC('INSERT INTO #ib ([EventID], [EventExtraDataID], [FileName], [Path])
SELECT ''' + @EventID1 + ''', ''' + @EventExtraDataID + ''', ''' + @File + ''', *
FROM (
SELECT *
FROM OPENROWSET(BULK ''' + @Path + ''', SINGLE_BLOB) As rs) As im')

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

END

CLOSE insert_cursor
DEALLOCATE insert_cursor

--Select * from #i

Select * from #ib

Drop table #i

Drop Table #ib

SET NOCOUNT OFF

RETURN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources