asked on

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

deviprasadg

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.

ASKER CERTIFIED SOLUTION

Matt Bowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ulf-jzl

ASKER

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.

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ulf-jzl

ASKER

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?

SOLUTION

ulf-jzl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ulf-jzl

ASKER

Setting TEXTSIZE inside the SP did not work, you need to set inside the step itself