sql server odbc: Hi,

I am trying to insert data into SQL Server view, which has "text" column
using MDAC driver (SQL Server driver).
When application calls SQLPutData, the following error returned:

DIAG [HY000] [Microsoft][ODBC SQL Server Driver]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The READTEXT,
WRITETEXT, and UPDATETEXT statements cannot be used with views or functions.
(285)

Since MDAC driver cannot recognize SQL_VARCHAR length=0(unlimited), I am
binding the parameter with SQL_LONGVARCHAR.
Prepared statement>> "INSERT INTO "J15USER1"."MSSQL_LOB_VIEW" ( "INT_COL1",
"CHAR_COL1", "VARCHAR_COL1", "CLOB_COL1") VALUES ( 2, 'bb', 'bbb',
)

If I use SQL Native Client, I am able to bind the parameter with SQL_VARCHAR
length=0 and the insert into view is successful.

I could not find any limitation about using SQLPutData against view column.
Does anyone know if this is an expected behavior or not?

Thank you for your help,
Kaoru



SQLPrepare with return code 0 (SQL_SUCCESS)
HSTMT 2A9E3C00
UCHAR * 0x2ABDFD00 [ -3] "INSERT INTO
"J15USER1"."MSSQL_LOB_VIEW" ( "INT_COL1", "CHAR_COL1", "VARCHAR_COL1",
"CLOB_COL1") VALUES ( 2, 'bb', 'bbb', ? )\ 0"
SDWORD -3

SQLBindParameter with return code 0 (SQL_SUCCESS)
HSTMT 2A9E3C00
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 1 <SQL_C_CHAR>
SWORD -1 <SQL_LONGVARCHAR>
SQLULEN 2147483647
SWORD 0
PTR 0x00000000
SQLLEN 0
SQLLEN * 0x2ABEFFBC (-2)

SQLPutData with return code -1 (SQL_ERROR)
HSTMT 2A9E3C00
PTR 0x2ABDFC20
SQLLEN 21

DIAG [HY000] [Microsoft][ODBC SQL Server Driver]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)

DIAG [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The READTEXT,
WRITETEXT,
and UPDATETEXT statements cannot be used with views or functions. (285)

Hi,
From your description, I understand that:
You application failed to insert data by using SQLPutData against view
column with MDAC; however you could do that with SQL Native Client.
If I have misunderstood, please let me know.

To find the root cause of this issue, I would like to confirm with you the
following questions:
a) What is the version of your SQL Server?
b) Could you manage to perform the insert operation by specifying the
parameter with SQL_LONGVARCHAR?

I believe that SQL_VARCHAR cannot be used on text/binary/image columns in
MDAC. SQL Native Client can support varchar(max), so it most likely support
using SQL_VARCHAR on text columns. However I have not found any public
documents clearly talking about this. I will consult product team to
confirm this and let you know the result as soon as possible.

If you have any other questions or concerns, please feel free to contact us.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Hi,

Some correction here.

[quoted text, click to view]

I would like to use SQL_LONGVARCHAR, not SQ_VARCHAR.
If I bind the parameter with SQL_VARCHAR, then the max length can only be up
to 8K. It is okay with SQL Native Client since it supports 2GB length with
VARCHAR, but not with MDAC driver. So I would like to bind the parameter with
SQL_LONGVACHAR and do piecemeal insert using SQLPutData.
But this operation is causing the problem when target is MSSQL view, not
table.

Please let me know if you need any other information.

Thank you,
KM

Hi, Thank you for your response.

[quoted text, click to view]

Yes, you are right. But binding text column with SQL_LONGVARCHAR caused same
problem with SQL Native Client also.

[quoted text, click to view]
Using SQL Server 2000 when connecting with MDAC, SQL Server 2005 for SQL
Native Client.

[quoted text, click to view]

Yes, I did the testing and binding the parameter with SQL_LONGVARCHAR is
causing the problem.

[quoted text, click to view]

I could not find the documentation either.
I would appreciate if you could contact product team if this is a limitation
for SQL Server ODBC.

Thank you very much for your help,
KM

Hi,
Sorry for this late response due to having not received the confrimation
from product team. I will strike them for a second time.

If the issue is urgent to your business, it is recommended that you contact
Microsoft Customer Support Services (CSS) via telephone so that a dedicated
Support Professional can assist you recover the server in a more efficient
manner. Please be advised that contacting phone support will be a charged
call.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Hi,
Unfortunately I have not got the confirmation from the SQL team. I have
submitted a feedback on this issue for reporting to product team.
However for now I recommend that you contact CSS immediately and
effectively to get the best support on this issue. Also, you may try using
SQL_LONGVARBINARY to see if this issue persists.

Sorry for bringing you any inconvenience. If you have any other questions
or concerns, please feel free to let me know.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

Hi Charles,

Thanks for your support.
I will try to contact CSS.

KM

[quoted text, click to view]