Solved

SQL Server Agent: Problem with large strings

Posted on 2013-01-28
9
525 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
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 9

Accepted Solution

by:
MattSQL 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now