OPENROWSET import multiple jpg files for reports

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

Transact-SQL (2008)

OPENROWSET import multiple jpg files for reports

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-03-31 : 18:35:36

I am tring to import multiple jpg files into a temp table (#ib) and use #t to provide the file name and full path details. #t gives me the file name and full path for the file(s) so I need to replace @FullPath and @File in #ib with fname and file from #t. Both #t and #ib work independantly but how do I pull them together. Also need to add EventID and EventExtraDataID to #ib

#t looks like this:

EventID EventExtraDataID fname file
4 1 C:\VCP\VCPDataRoot\EventExtraData\5\2010\1\1\1_1.jpg 1_1.jpg
4 3 C:\VCP\VCPDataRoot\EventExtraData\5\2010\1\1\1_2.jpg 1_2.jpg
4 4 C:\VCP\VCPDataRoot\EventExtraData\5\2010\1\1\1_3.jpg 1_3.jpg

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Event_Image] Script Date: 03/29/2010 16:05:28 ******/
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),
@File nvarchar(255),
@FullPath nvarchar(255)

AS

SET NOCOUNT ON

Declare @sql 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 fname, FileName As [file]
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 #t ([EventID] int, [EventExtraDataID] int, [fname] nvarchar(255) NOT NULL, [file] nvarchar(max))
INSERT INTO #t ([EventID], [EventExtraDataID], [fname], [file])

Exec(@sql)

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

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

Select * from #t

Select * from #ib

Drop table #t

Drop Table #ib

SET NOCOUNT OFF

RETURN

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-04-06 : 03:33:05

I am using this to import jpeg files stored on the file system into temp tables so they can be used within reports within an image item. From here you can add an image item to a report and set the MIMEType to image/jpeg, set Sizing as required, set Source to Database and set Value to Expression and apply =Fields!Path.Value. Here is my solution:

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Event_Image] Script Date: 04/06/2010 11:00:54 ******/
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)

--This builds the filename and full path and stores the result in a temp table called #i.

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)

--Using the details from #i this stores the jpg files into a temp table called #ib.

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