Hi,

Could someone give a pointer how to import couple of hundred images
into Sql Server 2005 Express Edition database?

Is there a tool for it? Can it be done with Sql Management Studio or
is it just a matter of writing own piece of software (a little er
app) to do it?

-timonardo

> Could someone give a pointer how to import couple of hundred images
> into Sql Server 2005 Express Edition database?
>
> Is there a tool for it? Can it be done with Sql Management Studio or
> is it just a matter of writing own piece of software (a little er
> app) to do it?

Below is a sample VBScript that will import all files in the specified
folder. This will work for SQL 2000 and SQL 2005. You can tweak this for
your needs. You can also use OPENROWSET in SQL 2005 (see Books Online), but
you'll need to provide your own method to provide the list of files to be
imported.

Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1

Function Main()

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")

'change connection string
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"

conn.Open ConnectionString
'change table and column names
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable",
conn,
adOpenKeyset,
adLockOptimistic

'change folder name
Set folder = fso.GetFolder("C:\MyFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next

conn.Close

Main = DTSTaskExecResult_Success

End Function

Sub InsertFile(FileName)

strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
'change column names
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close

End Sub

--
Hope this s.

Dan Guzman
SQL Server MVP

"pompair" <timonardo@gmail.com> wrote in message
news:1188981057.587680.44900@19g2000hsx.googlegrou ps.com...
> Hi,
>
> Could someone give a pointer how to import couple of hundred images
> into Sql Server 2005 Express Edition database?
>
> Is there a tool for it? Can it be done with Sql Management Studio or
> is it just a matter of writing own piece of software (a little er
> app) to do it?
>
> -timonardo
>