诡异:通过代理调用存储过程会截断IMAGE字段内容?

[烤火的鱼]

有这样一个业务需求，一台服务器上装有SQL SERVER 2008 R2，有两个数据库，不妨叫DB1和DB2，表结构均相同，其中一个表（假如表名为test），有两个字段：序号（int)、内容(image),序号是主键,内容用于存放二进制内容(文件流),业务需求是,定期通过代理作业,自动将DB1的内容更新到DB2里面,我写了一个存储过程,定期检查变化,如果变化了,调用改存储过程进行更新,这时,诡异的事情发生了,直接手工调用存储过程,正常,但通过作业自动执行时,更新后DB2中内容长度变成了1024,下面我将实现步骤简化(真正业务比较复杂,以下是简化后测试用的,因此,不要告诉我不该用这么笨的方法).

1.新建两个数据库,分别叫DB1和DB2
2.在两个数据库里面,都新建相同结构的一张表,用于测试,脚本如下:

CREATE TABLE [dbo].[test](
[序号] [INT] NOT NULL,
[内容] [IMAGE] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[序号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

很简单,就两列,INT型的序号主键,IMAGE型的内容字段

3.通过代码,或从其他表导入数据的方式,在DB1里面插入一条记录,序号设置为1
4.在DB2里面,添加一行,序号也为1,内容为NULL

5.在DB2里面,创建一个存储过程,用于将DB1的记录更新过来,存储过程内容如下:

CREATE PROCEDURE dbo.Download
AS
BEGIN
SET NOCOUNT ON;
--根据现有表,创建一个临时表结构
SELECT * INTO #temp FROM dbo.test WHERE 1=2

--创建一个sql语句,用于从db1里面取得数据(实际业务都是动态组合的)
DECLARE @strSql NVARCHAR(1000)
SELECT @strSql='select * from db1.dbo.test where 序号=1'

--通过执行SQL语句将查询结果放入临时表
INSERT INTO #temp
EXEC(@strSql)

--用临时表更新本地表
UPDATE A SET A.内容=B.内容
FROM test a INNER JOIN #temp b ON a.序号=b.序号

END

接下来开始测试了,测试都在DB2里面进行:
1.将test内容清空,然后检测长度:
UPDATE dbo.test SET 内容=NULL
SELECT DATALENGTH(内容) AS 长度 FROM dbo.test
这时,长度为NULL,正确

2.执行存储过程,然后测试长度:
EXEC dbo.Download
SELECT DATALENGTH(内容) AS 长度 FROM dbo.test

测试结果,正常,长度和DB1里面的一样

3.新建一个作业,让作业定期调用上面的存储过程(实测最小允许间隔是10秒)
4.查看效果:
(1)和上面1的方法一样,清空内容,这时长度测试为NULL
(2)不断查询长度,当作业执行后,检测到的长度为1024

非常头疼,不过,我将SQL改一下倒是正常的,即插入临时表也写入SQL语句里面:
SELECT @strSql='insert into #temp select * from db1.dbo.test where 序号=1'
EXEC(@strSql)
由于业务非常大,不太可能把所有的地方都改过来，因此，还是要在上面的存储过程代码里面查找原因，谁能帮忙分析解决一下呢？
不胜感激

[二月十六]

把数据先写到表中，然后看一下作业执行后表中的数据长度是多少，是不是1024

[zjcxc]

首先建议你使用 VARBINARY(MAX) 数据类型， IMAGE 是官方已经明确不推荐使用的类型
原因就很简单了，使用代理的时候，默认会使用 SET TEXTSIZE 1024， 这个影响了 EXEC 的输出，所以你直接查询入表是没问题的，但 INSERT EXEC 不正确， 你可以考虑在 INSERT EXEC 前加一句 SET TEXTSIZE 2147483647
如果你使用 VARBINARY(MAX) 类型，则不会有这个问题

[烤火的鱼]

楼上正解,SQL Profiler跟踪时,发现作业运行时,首先调用了SET TEXTSIZE 1024,然后我自己在存储过程里面添加了一句话SET TEXTSIZE ,值为1G大小,就正确了,image是历史遗留问题了,之前的项目不断完善的,为了兼容,不能改了,新的项目都是varbianary(max)了