熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

MS SQL SERVER 圖像或大文本的輸入輸出

2013-11-15 14:54:44  來源: SQL Server 

  在MS SQL SERVER 安裝目錄下有個可執行文件叫 TEXTCOPYEXE利用它可對 MS SQL SERVER 中的文本或圖像數據進行輸入輸出如果你對它不熟悉可以在MSDOS方式下執行textcopy /? 得到它的描述

  下面是這個工具的描述:

  Copies a single text or image value into or out of SQL Server The value is a specified text or image column of a single row (specified by the where clause) of the specified table

  If the direction is IN (/I) then the data from the specified file is copied into SQL Server replacing the existing text or image value If the direction is OUT (/O) then the text or image value is copied from SQL Server into the specified file replacing any existing file

  TEXTCOPY [/S ][sqlserver]] [/U [login]] [/P ][password]]

  [/D ][database]] [/T table] [/C column] [/Wwhere clause]

  [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

  /S sqlserver The SQL Server to connect to If sqlserver is not

  specified the local SQL Server is used

  /U login The login to connect with If login is not spec ified a trusted connection will be used

  /P password The password for login If password is not specified a NULL password will be used

  /D database The database that contains the table with the text or image data If database is not specified the default database of login is used

  /T table The table that contains the text or image value

  /C column The text or image column of table

  /W where clause A complete where clause (including the WHERE keyword) that specifies a single row of table

  /F file The file name

  /I Copy text or image value into SQL Server from file

  /O Copy text or image value out of SQL Server into file

  /K chunksize Size of the data transfer buffer in bytes Minimum value is bytes default value is bytes

  /Z Display debug information while running

  /? Display this usage information and exit

  You will be prompted for any required options you did not specify

  為此 可寫一個存儲過程調用這個命令

  CREATE PROCEDURE sp_textcopy (

  @srvname varchar ()

  @login varchar ()

  @password varchar ()

  @dbname varchar ()

  @tbname varchar ()

  @colname varchar ()

  @filename varchar ()

  @whereclause varchar ()

  @direction char())

  AS

  DECLARE @exec_str varchar ()

  SELECT @exec_str =

  textcopy /S + @srvname +

   /U + @login +

   /P + @password +

   /D + @dbname +

   /T + @tbname +

   /C + @colname +

   /W + @whereclause +

   /F + @filename +

   / + @direction

  EXEC masterxp_cmdshell @exec_str

  下面是一個拷貝圖像到SQL Server的pubs數據庫的例子 表名pub_info 字段名

  logo圖像文件名picturebmp保存到pub_id=記錄 sp_textcopy @srvn

  ame = ServerName

  @login = Login

  @password = Password

  @dbname = pubs

  @tbname = pub_info

  @colname = logo

  @filename = c:\picturebmp

  @whereclause = WHERE pub_id=

  @direction = I


From:http://tw.wingwit.com/Article/program/SQLServer/201311/22537.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.