0

I got a stored proc to retrieve XML data from web service It works as I expected. After test for a while, I want to use this stored proc as SCHEDULED JOB then I created new one with T-SQL 'EXEC sp_GetData;' and got failed results (error 9400).

I reproduced that my stored proc can work fine if I execute it via Query Editor Window (EXEC sp_GetData;) but can't work if retrieving data are more than 3 objects of following XML data via scheduled job.

Question : How to increase SQL Server Agent to retrieve all data (about 3MB file size) then insert into specific table.

<ArrayOfEmployeeMobilesInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/">  
   <EmployeeMobilesInfo>
      <EmployeeCode>00001</EmployeeCode>
      <TitleEN>Mr.</TitleEN>
      <EmpNameEN>John</EmpNameEN>
      <EmpSurnameEN>Doe</EmpSurnameEN>
      <PositionNameEN>Police</PositionNameEN>
      <Title>Mr.</Title>
      <EmpName>John</EmpName>
      <EmpSurname>Doe</EmpSurname>
      <PositionName>Police</PositionName>
      <CenterCode>PP777</CenterCode>
      <Location>PP</Location>
      <Email>john@person.com</Email>
      <DivisionCode>PPD77</DivisionCode>
      <DivisionName>PPD77</DivisionName>
      <DepartmentCode>PDD25</DepartmentCode>
      <DepartmentName>PDD25</DepartmentName>
      <PositionLevel>POC</PositionLevel>
      <PositionWeight>H</PositionWeight>
      <JoinDate>2013-10-01T00:00:00</JoinDate>
      <Mobiles>
         <Mobile>
           <MobileNo>1500111232</MobileNo>
         </Mobile>
      </Mobiles>
      <TerminateDate xsi:nil="true" />
   </EmployeeMobilesInfo>

... x 2

</ArrayOfEmployeeMobilesInfo>

my stored proc.

SET NOCOUNT ON;

DECLARE @URL NVARCHAR(500);
DECLARE @obj INT;

IF OBJECT_ID('xml_employees', 'U') IS NOT NULL DROP TABLE xml_employees;
IF OBJECT_ID('Employees', 'U') IS NOT NULL DROP TABLE Employees;
CREATE TABLE xml_employees ( resp XML );

SET @URL = 'http://localhost/DataService/Employee.asmx/GetEmployees';

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @URL , false
EXEC sp_OAMethod @obj, 'send'

INSERT xml_employees ( resp )
EXEC sp_OAGetProperty @obj, 'responseXML.xml'

Error message 

XML parsing: line 12, character 18, unexpected end of input [SQLSTATE 42000] (Error 9400).  The step failed.

Please give me some advice. Thank you

EDIT 1: Try to set TEXTSIZE as this thread mention

Community
  • 1
  • 1
SweNz
  • 53
  • 6

1 Answers1

0

SET TEXTSIZE 2147483647; save my ass !

Default TEXTSIZE of SQL Server Agent is 4KB, BUT my instance got 1KB.

SweNz
  • 53
  • 6