SQL Server Agent: Problem with large strings

Hi!

I have a strange problem, when running this code in SSMS it works just fine, but when it's executed from the "SQL Server Agent" it cuts of the string to 512 characters.

Any tips?

DECLARE @TableResponse2 TABLE
(
    Response VARCHAR(MAX)
)

UPDATE 
    data SET Response = '<?xml version="1.0" encoding="UTF-8" ?>
<view_channels><number_values>18</number_values><view_channel><channel_info><channel_type>Temperature</channel_type></channel_info><measurement_value><value>22.99</value><unit>°C</unit></measurement_value><meas_status><min>21.19</min><max>24.44</max><mean>21.89</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Relative humidity</channel_type></channel_info><measurement_value><value>33.8</value><unit>%RH</unit></measurement_value><meas_status><min>14.2</min><max>79.0</max><mean>33.1</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Relative humidity WMO</channel_type></channel_info><measurement_value><value>33.8</value><unit>%RH</unit></measurement_value><meas_status><min>14.2</min><max>79.0</max><mean>33.1</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Dewpoint</channel_type></channel_info><measurement_value><value>6.22</value><unit>°Ctd</unit></measurement_value><meas_status><min>-4.90</min><max>20.29</max><mean>4.86</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Absolute humidity</channel_type></channel_info><measurement_value><value>7.0</value><unit>g/m³</unit></measurement_value><meas_status><min>3.0</min><max>17.4</max><mean>6.4</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Degree of humidity</channel_type></channel_info><measurement_value><value>5.9</value><unit>g/kg</unit></measurement_value><meas_status><min>2.5</min><max>15.0</max><mean>5.4</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Enthalpy</channel_type></channel_info><measurement_value><value>38.1</value><unit>kJ/ kg</unit></measurement_value><meas_status><min>29.5</min><max>62.5</max><mean>35.7</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Psychrometer temperature</channel_type></channel_info><measurement_value><value>13.54</value><unit>°Ctw</unit></measurement_value><meas_status><min>10.07</min><max>21.43</max><mean>12.61</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Water vapour partial pressure</channel_type></channel_info><measurement_value><value>9.5</value><unit>hPa</unit></measurement_value><meas_status><min>4.1</min><max>23.8</max><mean>8.7</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Water content</channel_type></channel_info><measurement_value><value>9377</value><unit>ppm Vol</unit></measurement_value><meas_status><min>3998</min><max>23534</max><mean>8573</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Percent by volume</channel_type></channel_info><measurement_value><value>9377</value><unit>ppm</unit></measurement_value><meas_status><min>3998</min><max>23534</max><mean>8573</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Mixture dewpoint</channel_type></channel_info><measurement_value><value>6.80</value><unit>°Ctm</unit></measurement_value><meas_status><min>-4.84</min><max>20.82</max><mean>5.44</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Pressure dewpoint</channel_type></channel_info><measurement_value><value>6.22</value><unit>°Ctpd</unit></measurement_value><meas_status><min>-4.89</min><max>20.29</max><mean>4.86</mean></meas_status></view_channel><view_channel><channel_info><channel_type>16777728</channel_type></channel_info><measurement_value><value>28.0</value><unit>%RH</unit></measurement_value><meas_status><min>11.8</min><max>65.7</max><mean>27.2</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Temperature PCB</channel_type></channel_info><measurement_value><value>36.55</value><unit>°C</unit></measurement_value><meas_status><min>34.17</min><max>36.85</max><mean>35.68</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Voltage (3V)</channel_type></channel_info><measurement_value><value>3.293</value><unit>V</unit></measurement_value><meas_status><min>3.286</min><max>3.297</max><mean>3.298</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Voltage (6V)</channel_type></channel_info><measurement_value><value>13.042</value><unit>V</unit></measurement_value><meas_status><min>12.937</min><max>13.183</max><mean>13.015</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Heater current</channel_type></channel_info><measurement_value><value>0.000</value><unit>mA</unit></measurement_value><meas_status><min>0.000</min><max>0.000</max><mean>0.000</mean></meas_status></view_channel></view_channels>'
FROM 
   @TableResponse2 AS data

DECLARE @temp varchar(max);
SELECT @temp = tt.Response FROM @TableResponse2 AS tt
INSERT INTO dbo.[Test] ([Text]) VALUES(@temp)

Open in new window


this results into this text

<?xml version="1.0" encoding="UTF-8" ?>
<view_channels><number_values>18</number_values><view_channel><channel_info><channel_type>Temperature</channel_type></channel_info><measurement_value><value>22.83</value><unit>°C</unit></measurement_value><meas_status><min>21.19</min><max>24.44</max><mean>22.05</mean></meas_status></view_channel><view_channel><channel_info><channel_type>Relative humidity</channel_type></channel_info><measurement_value><value>30.3</value><unit>%RH</unit></measurement_value><meas_statu
ulf-jzlAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Matt BowlerConnect With a Mentor DB team leadCommented:
Can you put a select @@TEXTSIZE at the start of your query?

If the result is, as I expect, 512 then you could try putting a:

SET TEXTSIZE 1024 -- or 2048 or whatever is required...
0
 
deviprasadgCommented:
Looks like the script is updating data in @TableResponse2  without inserting any records.
Can you paste the original script here.

Also try this query after executing the script.

select LEN([Text]) as length,* From [Test]


if you find any length columns above with 512 we can debug further.
0
 
ulf-jzlAuthor Commented:
hehe.. good catch, :-) missed that one.

I have now seen that the problem is in (Get response data) a bit earlier in the code, but it still works fine in when SSMS is executing the script but not in SQL Server Agent.

Here is the code for retrieving the XML data via OLE.

DECLARE @TableResponse TABLE
(
  Response VARCHAR(MAX)
)

DECLARE
	@OLEObject int,
	@status int

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @OLEObject OUT;

EXEC  sp_OAMethod @OLEObject, 'Open', NULL, 'GET', 'http://xxx.xxx.com/data/getviewchannels', false;

EXEC sp_OAMethod @OLEObject, 'send'

EXEC sp_OAGetProperty @OLEObject, 'status', @status OUT;

--  Get response data. 
INSERT INTO @TableResponse (Response)
EXEC sp_OAGetProperty @OLEObject, 'responseText';

Open in new window


Any ideas why the script  don't work SQL Server Agent ?

The text size is set to maximum.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Anthony PerkinsConnect With a Mentor Commented:
Any ideas why the script  don't work SQL Server Agent ?
SQL Server Agent sets the TEXTSIZE and I don't believe there is a way around that.
0
 
Anthony PerkinsConnect With a Mentor Commented:
Actually I am wrong about it not being possible.  The default in SSMS is 2147483647, but for SQL Server Agent it is 1024. What you have to do is do a SET TEXTSIZE as suggested in the first comment.
0
 
Anthony PerkinsConnect With a Mentor Commented:
0
 
ulf-jzlAuthor Commented:
I run
SET TEXTSIZE 2147483647
in the beginning of the Procedure but that does not seem to help.

So that one doesn't change the SQL server Agent number, or does it?
0
 
ulf-jzlConnect With a Mentor Author Commented:
SET TEXTSIZE 2147483647
within the Step of the SQL Server Agent Job solves the problem. :-)

Thx guys.
0
 
ulf-jzlAuthor Commented:
Setting TEXTSIZE  inside the SP did not work, you need to set inside the step itself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.