【标题】：上传多个文件到Ms sqlserver的image类型字段
【关键字】：文件,Ms,上传,server,sql,类型,sqlserver,ls,image,serv,Ms,sqlserver,image
【来源】：网络

上传多个文件到Ms sqlserver的image类型字段

    这篇文章是个上传多个文件到Ms sqlserver的image类型字段的例子，很多办法可以做到，我想介绍的这种方法利用了OSQL.exe和TEXTCOPY.exe工具。
先决条件：
    a.必须安装了ms sqlsever 2000的客户端工具
    b.sql的用户必须至少是dbowner权限
步骤：
    1．建立upload.bat,内容如下：

@ECHO off
cls
REM ECHO on
REM Objective: To upload all the files in the folder to SQL server as a table
REM Created by: MAK
REM Date: Nov 3, 2004
REM Contact: mak_999@yahoo.com
REM Check parameters
if "%1"=="" Goto noparmErr
if "%1"=="/?" Goto Help

REM assign variables
set Sourcefolder=%1
set servername=%2
set Databasename=%3
set loginname=%4
set password=%5
set LogFile=%6

Date/t > %6
time /t >> %6

REM Export DIR listing to Dirlist.txt
dir %1 /b > Dirlist.txt

OSQL /S%2 /U%4 /P%5 /d%3 /Q "if exists (select * from %3.dbo.sysobjects
where id = object_id(N'[Myuploadtable]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1) drop table %3.dbo.[Myuploadtable] " >> %6

OSQL /S%2 /U%4 /P%5 /d%3 /Q "create table %3.dbo.
Myuploadtable (id varchar(500),ImageFiles Image)" >> %6

FOR /F "usebackq delims==" %%i in (Dirlist.txt) do OSQL
/S%2 /U%4 /P%5 /Q "insert into %3.dbo.Myuploadtable
(id,ImageFiles) values('%%i',' ') " >> %6

FOR /F "usebackq delims==" %%i in (Dirlist.txt) do textcopy.exe /S%2
/U%4 /P%5 /D%3 /Tmyuploadtable /CImagefiles /F"%1%%i"
/I /W"where [id]='%%i'" >> %6

GOTo END2

:noparmErr
Echo Usage help: example as follows
echo ...
Echo Objective: To upload all the files in the folder to SQL server Table
Echo Created by: MAK
Echo USAGE:
ECHO ....
ECHO ....
Echo %0 SourceFolder Servername Databasename Login Password LogFile
Goto END2

:Help
Echo Usage help: example as follows
echo ...
Echo Objective: To upload all the files in the folder to SQL server Table
Echo Created by: MAK
Echo USAGE:
ECHO ....
ECHO ....
Echo %0 SourceFolder Servername Databasename Login Password Logfile
Goto END2

:END
Echo "Compare Query results Completed" >>%LogFile%
date/t >> %LogFile%
time/t >> %LogFile%
goto END2

:END2

upload.bat下载

2．把textcopy.Exe从Microsoft SQL Server\MSSQL\Binn目录拷贝到upload.bat同一目录。
3．运行upload.bat,参数如下：

      Upload  - bat文件名
      C:\myfiles\  - 所有要传的文件目录（放在一个目录下）
      SQL  - sqlserver的网络名称或者ip地址
      MyDB  - 数据库名（必须存在）
      sa  - 数据库用户名
      yeahright  - 数据库密码
      x.log  - 本地log文件

Select ID from Myuploadtable
01-2004-08-20-.wav
Pfizer 12625.doc
STARTSQL.bat
status.bat
STOPSQL.bat
sysperfinfo.zip
Tech Interview.xls
Unix.bat
Taiwan.zip

select * from Myuploadtable
01-2004-08-20-.wav 0x5249464618990A00574
Pfizer 12625.doc 0xD0CF11E0A1B11AE
STARTSQL.bat 0x6E65742073746F7D
status.bat 0x6E6574207374617SDF
STOPSQL.bat 0x6E65742073746FDF
sysperfinfo.zip 0x5249464618990A00574
Tech Interview.xls 0xD0CF11E0A1B11AEDF
Unix.bat 0x6E65742073746F7DER
Taiwan.zip 0x5249464618990A00DE