Subject:
I've got a report that I create every month. The creation of report consists of 2 steps:
For the last few month I've created report in manual mode. And now I want to automate this stuff. But here comes a
Problem:
The second step (parsing XML and file creation) runs like a charm, but with first step I'm observing weird behaviour.
I got Stored Procedure which gets XML:
ALTER PROCEDURE [Structure].[GetXML]
@LastActDate date,
@CurActDate date
AS
BEGIN
SET NOCOUNT ON;
begining:
DECLARE @URI varchar(2000),
@methodName varchar(50),
@objectID int,
@hResult int,
@setTimeouts nvarchar(255),
@serv nvarchar(255) = 'http://example.com/docs/',
@result nvarchar(max) = ''
DECLARE @t TABLE(Resp nvarchar(max))
declare @timeStamp nvarchar(50) = convert(nvarchar(50),CURRENT_TIMESTAMP,127)
declare @CurDate date = dateadd(day,0,getdate())
--EXEC @hResult = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objectID OUT
EXEC @hResult = sp_OACreate 'MSXML2.XMLHTTP', @ObjectID OUT
SELECT @URI = @serv + '.newchange?ds='+CONVERT(nvarchar(10),@LastActDate,104)+'&df='+CONVERT(nvarchar(10),@CurActDate,104)+'&pardaily=1',
@methodName='GET',
@setTimeouts = 'setTimeouts(9000,90000,900000,9000000)'
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'
EXEC @hResult = sp_OAMethod @objectID, @setTimeouts
EXEC @hResult = sp_OAMethod @objectID, 'send', null
INSERT INTO @t
EXEC sp_OAGetProperty @objectID, 'responseText'
SELECT top 1 @result = Resp
FROM @t
if @result is null
begin
delete from @t
exec sp_OAGetErrorInfo @objectID
exec sp_OADestroy @objectID
goto begining
end
else
begin
INSERT INTO Structure.MonthlyRow
SELECT @timeStamp, @result
end
END
When I run this SP like
EXEC [Structure].[GetXML] '2016-06-01', '2016-07-01'
I got a row in Structure.MonthlyRow
table with correct timestamp
and response
(the average length is ~70k symbols)
Here is creation script of a table:
CREATE TABLE Structure.MonthlyRow(
[timestamp] nvarchar(50) NOT NULL,
[RowResp] nvarchar(max) NULL,
CONSTRAINT [PK_dDayly] PRIMARY KEY CLUSTERED ([timestamp] DESC))
If I create a job that launch this SP I get a row in table with results, and the length of result is 512 symbols! It is a proper part of XML that looks like it was truncated from nvarchar(max)
to nvarchar(512)
, but I have no variables or table columns with length of 512 that are used.
What have I tried:
WITH EXECUTE AS OWNER
in SP;WinHttp.WinHttpRequest.5.1
and MSXML2.XMLHTTP
.Question:
What possibly could be a problem? Why I am getting correct results when I run my SP manually, and got only 512 symbols of response when run SP as job step?
Note:
Yes, I know that getting XML from web-service is better handled by PHP, C# or even PowerShell and if I can not find a solution I will use one of them.
How to fix “String or binary data would be truncated” The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size.
add this line at the top of your sp or in the job before EXEC
of your sp
SET TEXTSIZE 2147483647;
the problem is that jobs set a default
SET TEXTSIZE 1024
this limits data returned to 1024 chars (512 for nchars)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With