Reading from and writing to varchar(MAX)?

system · January 5, 2012, 9:00pm

I’ve been having trouble getting these to work in DI.

I’m on
XP SP3
SQL Server 2008
DI 12.2.3.0

Upon importing the table, DI classifies them as long which seems fair enough, but when I create a simple DF to try to extract data from the table
[Souce]->[Query]->[FlatFile]
It errors out near-immediately with an “Invalid descriptor index” error (twice, for some reason.) I never even get the “rows-per-commit reduced to 1 because of LONG” warning.

Viewing the data in the table still works (to the extent that anything classified as long only ever shows here anyways.)

I’ve been trying to assist a co-worker with this and nothing I try seems to work, though they have occasionally been getting a different error than I have: “The text, ntext, or image pointer value conflicts with the column name specified”

If anyone has any tips or guidance for working with varchar(MAX) (and nvarchar(max) varbinary(max) to boot, no doubt) we would definitely appreciate it.

Thanks!

Cirno (BOB member since 2008-07-25)

system · February 24, 2012, 8:28pm

Hi Cirno,

Sorry for reviving this old thread, but I am running into this same exact issue. Did you find any solution / workaround for this?

Thanks!

vshah (BOB member since 2007-12-10)

system · April 3, 2012, 5:11pm

Still nothing.

Cirno (BOB member since 2008-07-25)

system · April 3, 2012, 5:26pm

Well I tested this. I too got the same in 14.0 SP1

I will post some results scenario.

But I am really not able to catch up the business reason behind using the NVARCHAR(MAX) Datatype.

Can you give me an idea on what business scenario it is used in a Datawarehousing?

Expect my results soon…

ganeshxp (BOB member since 2008-07-17)

system · April 3, 2012, 8:11pm

Hi Ganesh,

We are migrating all the long-form text and binary columns in our database schema to nvarchar(max) and varbinary(max) because the text and image types are being deprecated.

To quote the SQL 2012 (was also present on SQL 2008) MSDN page on the ‘text’ type:

[quote:b42d269e74=“http://msdn.microsoft.com/en-us/library/ms187993.aspx”]ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
[/quote]

In this particular scenario, we aren’t using it in data warehousing; we are using it to export data from our system into a 3rd-party system. As soon as the table loader hits a table with a nvarchar(max) fields, it errors out with the invalid descriptor index error. Interestingly (or maybe not,) the error always shows up twice.

4024	8172	DBS-070401	4/3/2012 4:01:57 PM	|Data flow DF_XXXXXXX|Reader Q_XXXXXXX
4024	8172	DBS-070401	4/3/2012 4:01:57 PM	ODBC data source <DataStore> error message for operation <SQLGetData>: <[Microsoft][SQL Server Native Client 10.0]Invalid
4024	8172	DBS-070401	4/3/2012 4:01:57 PM	Descriptor Index>.
6972	2968	DBS-070401	4/3/2012 4:02:07 PM	|Data flow DF_XXXXXXX|Reader Q_XXXXXXX
6972	2968	DBS-070401	4/3/2012 4:02:07 PM	ODBC data source <DataStore> error message for operation <SQLGetData>: <[Microsoft][SQL Server Native Client 10.0]Invalid
6972	2968	DBS-070401	4/3/2012 4:02:07 PM	Descriptor Index>.

Cirno (BOB member since 2008-07-25)

system · April 3, 2012, 8:22pm

Yes, I will post my scenarios. I tested this elaborately. I am having PROD stuffs going on driving me crazy.

Just for your info, use the ODBC provided by Microsoft and not the one from SQL Server (SQL Sever Native Client) and the job shoudl work fine according to my testing…

ganeshxp (BOB member since 2008-07-17)

system · April 4, 2012, 12:16pm

Hmm, it does work with an ODBC connection via the older SQL server driver.

This also fixed an issue I was having with getting an xml type from the database too.

Thanks for the tip.

Cirno (BOB member since 2008-07-25)

system · April 4, 2012, 2:03pm

Case 1: SQL Server Table involving a NVARCHAR(MAX) column and DS Datastore uses a Direct Connect (I mean no ODBCs)
Case 2: SQL Server Table involving a NVARCHAR(MAX) column and DS Datastore uses a SQL Server Driver provided by Microsoft Windows that is pre-installed when Windows gets installed
Case 3: SQL Server Table involving a NVARCHAR(MAX) column and DS Datastore uses a SQL Server Native Client Driver that comes with SQL Server 2008 Client Package

How DS Imports the Column?
Case 1: LONG - Direct Connection using Server Information and DB Information
Case 2: LONG - SQL Server Driver from Microsoft Windows
Case 3: VARCHAR(0) - SQL Server Native Client

Results:
Case 1: Invalid Descriptor Index
Case 2: Load completes successfully
Case 3: Invalid Descriptor Index

ganeshxp (BOB member since 2008-07-17)

system · November 20, 2012, 6:17am

Hello All,

I am having the same issue. I followed the procedure as suggested by Ganesh and now the Invalid descriptor issue is resolved. I am able to fetch data from table but when I try to load the same table it throws error
“<The text, ntext, or image pointer value conflicts with the column name specified.>”
Am I doing something wrong. I have used an ODBC DS, the ODBC DSN is of SQL Server as provided by Microsoft.
:help…

vikash_chaturvedi (BOB member since 2011-05-09)

system · January 31, 2013, 12:18am

I ran across this issue today when doing a proof of concept for a client. Ganesh, your solution worked for me just fine. Kudos for posting the solution.

Now, why on earth does the ODBC connection work when the native client connection doesn’t? Is this a bug in how DS is working with the driver?

eganjp (BOB member since 2007-09-12)

system · February 5, 2013, 9:56pm

Changing the ODBC driver caused us to lose windows authentication. We found a different workaround: create a separate dataflow which reads the varchar(max) data and inserts into template table. The insert pushes down to the database, so data is never read into memory. The data is implicitly converted from varchar(max) to text; which DS can handle in subsequent dataflows without trouble. text datatype is not optimal, but this gets us through the day.

Dan

dhaag (BOB member since 2013-01-22)

system · February 11, 2013, 8:37pm

Yeah this should go in as a bug. But I guess they would have worked on this…We all know that DS with SQL Server 2008 had too many issues around!!!

ganeshxp (BOB member since 2008-07-17)

system · February 23, 2014, 3:58pm

Hi,

I am facing the same issue, from ganeshxp tips :

Can some advice how/where do I configure to “use the ODBC provided by Microsoft and not the one from SQL Server (SQL Sever Native Client)” ?

Appreciate reply soonest.

joycec (BOB member since 2007-05-07)

system · February 23, 2014, 4:19pm

Hi, I’ve found where to configure the usage of ODBC connection.
Please ignore my previous post.

joycec (BOB member since 2007-05-07)

system · March 4, 2015, 9:26pm

I wasn’t sure about the ODBC configuration either, but I think I figured it out too.

For future people who may be usure:

Set up a system ODBC data source on both the development server and the job server using driver named just “SQL Server” (our version was 6.01.7601.17514, file SQLSRV32.DLL, 11/20/2010). (We also had several variations of “SQL Native Client”, file names like SQLNCLI.DLL and variations, which I did not try.)
Configured the datastore to be database type “ODBC” instead of “Microsoft SQL Server”, and chose the DSN I configured in step 1.

Success!

JohnBrooking (BOB member since 2009-12-22)