如何使用DTS将SqlServer中的二进制数据导出到文件
我在 sql server 2000 表中有一个图像列，用于存储 pdf 文件的二进制文件。
我需要使用 SqlServer 2000 DTS 将列中每一行的内容导出到实际的物理文件中。
我在 http://www.freevbcode 找到了以下 vb 方法。 com/ShowCode.asp?ID=1654&NoBox=True
Set rs = conn.execute("select BinaryData from dbo.theTable")
FileHandle = FreeFile
Open ("AFile") For Binary As #FileHandle
ByteLength = LenB(rs("BinaryData"))
ByteContent = rs("BinaryData").GetChunk(ByteLength)
Put #FileHandle, ,ByteContent
Close #FileHandle
不幸的是，DTS 脚本任务是 VBSCript，而不是 VB，它会在第三行的 AS 关键字上抛出错误。
还有其他想法吗？
I have an image column in a sql server 2000 table that is used to store the binary of a pdf file.
I need to export the contents of each row in the column to an actual physical file using SqlServer 2000 DTS.
I found the following method for vb at http://www.freevbcode.com/ShowCode.asp?ID=1654&NoBox=True
Set rs = conn.execute("select BinaryData from dbo.theTable")
FileHandle = FreeFile
Open ("AFile") For Binary As #FileHandle
ByteLength = LenB(rs("BinaryData"))
ByteContent = rs("BinaryData").GetChunk(ByteLength)
Put #FileHandle, ,ByteContent
Close #FileHandle
Unfortunately, the DTS script task is VBSCript, not VB, and it throws up on the AS keyword in the third line.
Any other ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 VBScript 中编写二进制文件是一项非常困难的任务。 VBScript 公开的唯一直接文件操作存在于 FileSystemObject 对象中，该对象仅支持写入文本文件。 唯一可行的选择是使用 ADO Stream 对象，这仍然很麻烦，因为 VBScript 不支持将脚本创建的字节数组传递给 COM 对象，而 COM 对象需要能够写入任意二进制数据。
这是一种使用 ADO 流的技术，它可能行不通，但可能会让您走上正确的解决方案。
我使用 Access 尝试了这段代码，不幸的是它似乎不起作用。 我没有获得与我创建的表中相同的二进制数据，但我没有费心进入十六进制编辑器来查看有什么不同。
如果 ADO 操作出现错误，您可以获取 实际消息，方法是在脚本顶部添加“On Error Resume Next”并使用此代码。
Writing binary files is a notoriously difficult task in VBScript. The only direct file operations exposed by VBScript live in the FileSystemObject object, which only supports writing text files. The only viable option is to use ADO Stream objects, which still is cumbersome because VBScript does not support passing script-created Byte arrays to COM objects, which is required to be able to write arbitrary binary data.
Here's a technique using ADO streams that probably won't work but might put you on the track to the right solution.
I experimented with this code using Access, and unfortunately it didn't seem to work. I didn't get the same binary data that was in the table I created, but I haven't bothered going into a hex editor to see what was different.
If you get errors from an ADO operation, you can get the actual messages by adding an "On Error Resume Next" to the top of the script and using this code.
如果可能的话，我会使用 SQL Server Integration Services (SSIS) 而不是 DTS，并使用允许您使用 VB.NET 的脚本任务。
您可以连接到 SQL Server 2000 数据源并将导出的输出指向文件。
I would go with SQL Server Integration Services (SSIS) instead of DTS, if at all possible, and use a Script Task which would allow you to use VB.NET.
You can connect to your SQL Server 2000 data source and point the exported output to a file.