Topic Options
#48775 - 07/21/14 04:48 PM Sql varchar(max) error with database task
-nth- Offline
OL Expert

Registered: 04/01/03
Posts: 234
Loc: Lincoln, NE
I had a couple workflows hiccup on me today that retrieve XML from a SQL Server database column. The system in question went through an upgrade over the weekend. Turns out the field that stores the XML used to be datatype TEXT, but now is datatype VARCHAR(max).

When PlanetPress attempts to connect it throws an error related to that field:

Code:
W3612 : Error running SQL: Invalid field size
PlanetPress Database: W1603 : Plugin failed - 15:34:44 (elapsed time: 00:00:00:343)


I tried updating the SQL native client PlanetPress connects with, but it still errored out. I also tried using a "CAST AS XML", but then it wouldn't retrieve the field at all. What finally worked was "CAST AS TEXT". I'd prefer not to do that since TEXT is deprecated by Microsoft (which I think is why the company converted the columns in the update).

So it's back working now, but is there another way to get VARCHAR(max) data?

Thanks,
-Nate

Top
#48809 - 07/23/14 01:00 PM Re: Sql varchar(max) error with database task [Re: -nth-]
Raphael Lalonde Lefebvre Offline
OL Expert

Registered: 10/14/05
Posts: 4828
Loc: Objectif Lune Montreal
Hi Nate,

I've tested the "varchar(max)" field, tried doing both a SELECT and an UPDATE, and it seems to be working just fine. I was able to select the field, and I was also able to update it using SQL. I did not have to cast it as any other type of fields.

What is your query, what are you trying to do? Can you provide more details? Maybe that could help us understand the problem better...

EDIT: The version of SQL Server I've tested this with was 2012.

Regards,
Raphaël Lalonde Lefebvre


Edited by Raphael Lalonde Lefebvre (07/23/14 01:03 PM)

Top
#48820 - 07/23/14 03:17 PM Re: Sql varchar(max) error with database task [Re: -nth-]
-nth- Offline
OL Expert

Registered: 04/01/03
Posts: 234
Loc: Lincoln, NE
Thanks Raphaël.

Here are a few more details.

Environment
SQL Server 2008 R2 (error happens on both dev and prod systems).
PlanetPress Workflow version 7.5.3.8067 running on Windows Server 2003 R2.
Database task is using System DSN based on Microsoft SQL Native Client Version 09.00.4035.
Database task is using dynamic SQL with parse normally and expect record set checked.
Database task emulation is set to XML, one record per datapage with data encoding of MS Windows Codepage 1252 (Latin 1).

Troubleshooting
Tested SELECT query using trusted connection and SQL credentials - same result (Invalid field size).
Tested SELECT query in several different SQL tools; all work just fine (to rule out ODBC quirks).

Query details
SELECT query is designed to retrieve "serialized" XML from a specific column.
Retrieved XML can run over a million characters in length on a single line (since it's serialized).
Here's the overall structure of the query:
Sql Query:
SELECT a.MessageUid,  b.StatusDescription, a.RawMessage 
FROM Message as a
LEFT JOIN MessageDetails as b ON a.MessageUid = b.MessageUid
WHERE a.Status = -1 AND a.PartnerMessageUid = '1235678910'


I have even tried simplifying the query to the point where it just retrieves a single row (with no joins) but it still errors out.

Anything jump out at you as the potential problem?

Thanks,
-Nate

Top
#54573 - 02/17/17 12:45 PM Re: Sql varchar(max) error with database task [Re: -nth-]
jim3108 Online   content
OL Expert

Registered: 04/19/10
Posts: 254
Loc: London, UK
Did you ever solve this because I am running into the same problem?
_________________________
James Stacey
Point Zero Solutions | Automate. Business. Workflow.

james@pointzerosolutions.co.uk

gmail: sirstace
skype: jim3108
-----------------------------------------------------------------------------

Top
#54575 - 02/17/17 02:23 PM Re: Sql varchar(max) error with database task [Re: -nth-]
-nth- Offline
OL Expert

Registered: 04/01/03
Posts: 234
Loc: Lincoln, NE
Yes, as I recall I did find a work around.

Code:
SELECT a.MessageUid,  b.StatusDescription, CAST(a.RawMessage AS TEXT) as RawMessage 
FROM Message as a
LEFT JOIN MessageDetails as b ON a.MessageUid = b.MessageUid
WHERE a.Status = -1 AND a.PartnerMessageUid = '1235678910'


I think the keys were to have the varchar(max) column be the last column in the select statement as well as casting it to text.

Hopefully that helps!

Top
#54577 - 02/19/17 10:36 AM Re: Sql varchar(max) error with database task [Re: -nth-]
jim3108 Online   content
OL Expert

Registered: 04/19/10
Posts: 254
Loc: London, UK
Excellent, thank you.
Worked a treat!
_________________________
James Stacey
Point Zero Solutions | Automate. Business. Workflow.

james@pointzerosolutions.co.uk

gmail: sirstace
skype: jim3108
-----------------------------------------------------------------------------

Top