Solved

SQL Server Agent: Problem with large strings

Posted on 2013-01-28
9
543 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 300 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 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 200 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 200 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

728 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