May 18, 2017 at 9:15 am
I am moving some data containing image from server1 to Server2 using a job. The image column is defined as [image] datatype.
A job in server2 executes the stored procedure usp_Document2. The owner of the job is the SQL agent service account (windows account).
After the job run, I found out that all images have been truncated, and they all have the same datalength: 1024.
However, When I manually run the stored procedure usp_Document2, the images are copied properly.
My windows account and the SQL agent service account are both sysadmin in both servers.
Scenario:
In server1, there is a stored procedure usp_Document1
Use Database1
go
CREATE procedure usp_DocumentTest1
as
Begin
SET nocount ON
Select GUID_document, name, type, image from dbo.document
END
In Server2, there is a stored procedure usp_Document2
Use database2
Go
CREATE Procedure usp_Document2
as
Begin
SET nocount ON
Truncate Table dbo.document2
Insert into dbo.document2
EXEC server1.database1.dbo.usp_Document1
END
Has anyone ever seen this issue..
Thank you
May 18, 2017 at 12:05 pm
SET TEXTSIZE 2147483647 Sue
May 18, 2017 at 12:35 pm
It works,
Thank you Sue.
May 18, 2017 at 12:39 pm
Also, If I use OPENQUERY to execute the stored procedure, it works too.
Use database2
Go
CREATE Procedure usp_Document2
as
Begin
SET nocount ON
Truncate Table dbo.document2
Insert into dbo.document2
Select * from OpenQuery(server1, 'SET FMTONLY OFF EXECUTE database1.dbo.usp_Document1)
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply