Invalid precision value inserting into sql varchar(max)

[ckeihn]

Getting an  "Output Data (71) Error SQLBindParameter: [Microsoft][SQL Server Native Client 11.0]Invalid precision value" when inserting data from Alteryx to a SQL table with a field with datatype varchar(max).  How can I get around this error.  I cannot change the datatype on the SQL table.  The data type passed is a v_string size of 4001.

[SeanAdams]

Hey @ckeihn - are you able to post 3 things in a reply,  to help diagnose and find a way round this for you?
- the table definition (create table XXX as ...)

- one or two sample rows of data that you're trying to insert

- the workflow that's failing with the error (or trim it down to just the failing parts if necessary)?

Happy to help - I have a SQL server at home so I can get this up and running very quickly to see if I can replicate and/or resolve

Cheers

Sean

[ckeihn]

Hi SeanAdams,

Thank you for responding so quickly.  The workflow is pulling data from an Oracle database, processing the data and inserting it into an existing SQL table using the Output Data tool.

-- This is the table structure:

CREATE TABLE [dbo].[MyTable](

[intReferralID_PK] [int] IDENTITY(1,1) NOT NULL,

[PATIENT_ID] [int] NULL,

[LAST] [varchar](100) NULL,

[FIRST] [varchar](100) NULL,

[TEAM] [varchar](100) NULL,

[REOPEN_COMMENT] [varchar](max) NULL,

[ETL_DATE] [datetime] NULL,

[Migration_Flag] [varchar](50) NULL,

CONSTRAINT [PK_Specialy_AHG_Direct_TAT_Prod_TAT_AlteryxTest] PRIMARY KEY CLUSTERED

(

[intReferralID_PK] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The field that where the error is thrown is REOPEN_COMMEN varchar(max).

-- Sample rows

clin_svcbr_id	patient_id	last	first	team	reopen_comment	etl_date	migration_flag
555	1	TEST_L	TEST_F	CUSTOMER SERVICE	Task with status COMPLETED was reopened by CS on 03/21/2017 10:35.	4/11/17 8:11 AM	ABD1
555	2	LAST	FIRST	SALES	Task with status COMPLETED was reopened by SLS on 03/29/2017 14:56.	4/11/17 8:12 AM	ABC3
555	3	DOE	JANE	CUSTOMER SERVICE	Task with status COMPLETED was reopened by CS on 11/17/2016 15:57.	4/11/17 8:12 AM	CAB4

--  Workflow throwing the error:

The Output Data is throwing the error:

Error: Output Data (71): Error SQLBindParameter: [Microsoft][SQL Server Native Client 11.0]Invalid precision value

[JessicaS]

Hi @ckeihn,

Are you connected to SQL server as ODBC? Can you try connecting to your SQL server as OleDB (set up a fresh connection using the OLE DB driver)?

Thanks,

[SeanAdams · Accepted solution]

Very interesting!

Firstly - thank you for making this really easy to work on - having the create table script and some useful sample data made this very quick (<5 mins to set up and test)!  Nicely done!

It looks like @JessicaS was spot on, ODBC connection is failing when it tries to interpret the long Varchar column definition - and here's the workings below to demonstrate.

ODBC:

I ran this- and progressively removed fields until there was only the varchar(max) one left, and it continued failing.    

Then did it the other way, and added back all fields ending with Varchar(max) field, and it worked for all columns except this one.

Then changed DB driver

OLEDB:

Worked flawlessly using the OLEDB driver for SQL server instead of the ODBC

SQL Server Bulk:

Connected with a SQL server bulk connection, using the SQL server native driver (ver 11.0 I think), and this also worked.

So - just like @JessicaS - this appears to be directly pointing to the ODBC driver.   That does make sense since the error is coming back about the precision of a column, and my guess is that the ODBC connection does not understand a varchar column of this size.

I've attached a workflow with all 3 connections, only the first one is failing.  you'll have to append these to work on your machine & DB, but even just looking at the setup should help.

If this solves your problem @ckeihn - would you mind marking this thread as solved so that it can add to the knowledge base - or if there are follow-on questions, please feel free to reply on this thread?

Cheers

Sean

[ckeihn]

SeanAdams and @JessicaS - This solution works perfectly!  Thank you for your help.

[d_auerbach]

Sean,

I'd like you to know that I'm still getting the issue even after setting the output tool to use SQL Server bulk loader. This is more of a FYI... it's not urgent as I am able to shorten the length of the field on SQL.