注册
登录
| 关键词： nbsp Upload Myuploadtable bat ECHO Server LogFile Dirlist 5249464618990 SQL |
来源：www.databasejournal.com 作者:MAK 翻译：xiaolu 来自：http://666w.com/ December 14, 2004Upload multiple files to SQL Server Image columnBy Muthusamy Anantha Kumar aka The MAK翻译:xiaolu 这篇文章是个上传多个文件到Ms sqlserver的image类型字段的例子，很多办法可以做到，我想介绍的这种方法利用了OSQL.exe和TEXTCOPY.exe工具。 先决条件： a.必须安装了ms sqlsever 2000的客户端工具 b.sql的用户必须至少是dbowner权限步骤： 1．建立upload.bat,内容如下： @ECHO offclsREM ECHO onREM Objective: To upload all the files in the folder to SQL server as a tableREM Created by: MAKREM Date: Nov 3, 2004REM Contact: [email protected] Check parametersif "%1"=="" Goto noparmErrif "%1"=="/?" Goto Help REM assign variablesset Sourcefolder=%1set servername=%2set Databasename=%3set loginname=%4set password=%5set LogFile=%6 Date/t > %6time /t >> %6 REM Export DIR listing to Dirlist.txtdir %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 :noparmErrEcho Usage help: example as followsecho ...Echo Objective: To upload all the files in the folder to SQL server Table Echo Created by: MAKEcho USAGE:ECHO ....ECHO ....Echo %0 SourceFolder Servername Databasename Login Password LogFileGoto END2 :HelpEcho Usage help: example as followsecho ...Echo Objective: To upload all the files in the folder to SQL server Table Echo Created by: MAKEcho USAGE:ECHO ....ECHO ....Echo %0 SourceFolder Servername Databasename Login Password LogfileGoto END2 :ENDEcho "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 Myuploadtable01-2004-08-20-.wav Pfizer 12625.doc STARTSQL.bat status.bat STOPSQL.bat sysperfinfo.zipTech Interview.xls Unix.bat Taiwan.zip select * from Myuploadtable01-2004-08-20-.wav 0x5249464618990A00574Pfizer 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 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
