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 helper
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 helps.
Dan Guzman
SQL Server MVP
"pompair" <ti*******@gmail.comwrote in message
news:11*********************@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 helper
app) to do it?
-timonardo
