?
Solved

SQL Server Agent: Problem with large strings

Posted on 2013-01-28
9
Medium Priority
?
551 Views
Last Modified: 2013-02-03
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
0
Comment
Question by:ulf-jzl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 10

Expert Comment

by:deviprasadg
ID: 38829992
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
 
LVL 10

Accepted Solution

by:
Matt Bowler earned 1200 total points
ID: 38830017
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
 

Author Comment

by:ulf-jzl
ID: 38830026
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 38830985
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 38830995
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 38831021
0
 

Author Comment

by:ulf-jzl
ID: 38831264
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
 

Assisted Solution

by:ulf-jzl
ulf-jzl earned 0 total points
ID: 38832153
SET TEXTSIZE 2147483647
within the Step of the SQL Server Agent Job solves the problem. :-)

Thx guys.
0
 

Author Closing Comment

by:ulf-jzl
ID: 38848293
Setting TEXTSIZE  inside the SP did not work, you need to set inside the step itself
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question