openrowset导入网络文件示例

基于xp_cmdshell、openrowset(bulk '路径',single_blob)、游标导入远程服务器碎文件入库

GO
/****** Object:  StoredProcedure [dbo].[exportAttachment]    Script Date: 04/25/2013 09:37:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[exportAttachment]
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

exec sp_configure 'show advanced options', 1;
reconfigure with override
exec sp_configure 'xp_cmdshell', 1;
reconfigure with override 
--访问一次远程地址(不要影射网络驱动器)
exec master..xp_cmdshell 'net use \\10.10.31.251\lv "123" /USER:usercount' 


select * from openrowset(bulk '\\10.10.31.251\lv\none.gif',single_blob) as a



exec sp_configure 'xp_cmdshell', 0;

RECONFIGURE with override
--关闭高级选项

exec sp_configure 'show advanced options', 0;
RECONFIGURE with override

END