Problems "insert into openquery" with Relativity to COBOL data

Question

Post reply

Problems "insert into openquery" with Relativity to COBOL data

Peter1971

Old Hand

Points: 363
More actions
June 27, 2011 at 1:01 am

#243363

I'm having trouble with the following statement:
INSERT INTO OPENQUERY(linkedserver, 'select a,b,c from remote_table where a = -1')
VALUES ( 10, 20, '30')
It returns the error "Invalid Precision value."
The remote_table is an ODBC to COBOL data, through ODBC [Liant Relativity Client].
Linkedserver is accessed as admin, so no security issue.
The problem is probably field "c", a 'LONGVARCHAR' that is translated in Access as 'Memo' datatype.
I've tried declaring '30' as nvarchar, varchar, text, ntext..
The strange thing is that "UPDATE.." is working fine but INSERT fails..(?)
I've also tried OPENQUERY(..), 4 part naming, EXEC('INSERT...') but no results.
HELP needed asap..

A good description of the problem is half the solution.. ;-)

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Are you sure that the error comes from the text column? That message would make more sense on a numeric column.

-- Gianluca Sartori

Peter1971 Old Hand Points: 363 More actions · Answer 2

The error does not mention the column, but the other values are "normal" int's so I didn't believe it would come from that angle..

A good description of the problem is half the solution.. ;-)

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You could try to find the offending column excluding the other ones from the query.

For instance, if columns A and B have a default value, you could try to insert just column C and so on.

Also, do you have a sort of trace you can activate on the COBOL side to see the actual command? It would be extremely useful.

-- Gianluca Sartori

Peter1971 Old Hand Points: 363 More actions · Answer 4

This is a fragment of SQL Profiler:

<DBCOLUMNINFO>

<pwszName>IN_TEKST_TABEL</pwszName>

<pTypeInfo>0x00000000</pTypeInfo>

<iOrdinal>4</iOrdinal>

<dwFlags>4</dwFlags>

<ulColumnSize>1600</ulColumnSize>

<wType>129</wType>

<bPrecision>255</bPrecision>

<bScale>255</bScale>

..

A good description of the problem is half the solution.. ;-)

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

So, no doubt that the offending column is the one with LONGVARCHAR data type.

I have no idea how this can be set in T-SQL. Have you checked the provider's documentation?

-- Gianluca Sartori

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

This thread seems to have hit a stopping point and I have no specific technical information to lend but this looked weird so I thought I would call it out:

INSERT INTO OPENQUERY(linkedserver, 'select a,b,c from remote_table where a = -1')

VALUES ( 10, 20, '30')

Is it just for a boiled down example? If so ignore the rest...

Why provide a WHERE clause in the OPENQUERY query? The WHERE a = -1 predicate excludes your VALUES list. I am not sure if it makes a difference but it made me think of the WITH CHECK OPTION for VIEWS.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Peter1971 Old Hand Points: 363 More actions · Answer 7

The error message:

OLE DB provider "MSDASQL" for linked server "rl_smd01" returned message "[Liant][Relativity]Invalid precision value.".

Msg 7343, Level 16, State 2, Line 3

The OLE DB provider "MSDASQL" for linked server "rl_smd01" could not INSERT INTO table "[MSDASQL]".

A good description of the problem is half the solution.. ;-)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

I'll assume that means that's the actual query...what do we see from this?

INSERT INTO OPENQUERY(linkedserver, 'select a,b,c from remote_table')

VALUES ( 10, 20, '30')

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Peter1971 Old Hand Points: 363 More actions · Answer 9

This is the original code:

declare @tekst as nvarchar(1600)

set @tekst = 'test'

INSERT into

openquery(rl_smd01, 'select in_kr_nr, in_record_code, in_kr_key, in_tekst_tabel from info

where in_kr_nr = -1')

VALUES (1004589, 33,9887,@tekst)

I've used @tekst to try different datatypes, but none works...

edit: the "in_tekst_tabel" field is a COBOL LONGVARCHAR..

A good description of the problem is half the solution.. ;-)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Peter1971 (6/29/2011)
This is the original code:
declare @tekst as nvarchar(1600)
set @tekst = 'test'
INSERT into
openquery(rl_smd01, 'select in_kr_nr, in_record_code, in_kr_key, in_tekst_tabel from info
where in_kr_nr = -1')
VALUES (1004589, 33,9887,@tekst)
I've used @tekst to try different datatypes, but none works...
edit: the "in_tekst_tabel" field is a COBOL LONGVARCHAR..

My suspicion is around the predicate "where in_kr_nr = -1". Why is it in the query within OPENQUERY?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

opc.three (6/29/2011)
I'll assume that means that's the actual query...what do we see from this?
INSERT INTO OPENQUERY(linkedserver, 'select a,b,c from remote_table')
VALUES ( 10, 20, '30')

I don't expect it to be any different on the precision error.

On the performance side, depending on the OLEDB implementation, it could make a huge difference.

Usually, insert/updates on OPENQUERY results are performed with a rowset update, which means that the entire rowset could get transferred to SQL Server before the write operation. Limiting rows returned by the OPENQUERY function is almost always a good idea.

Just a quick check: does this work?

EXECUTE('INSERT INTO remote_table VALUES (10, 20, ''30'')') AT linkedserver

-- Gianluca Sartori

Peter1971 Old Hand Points: 363 More actions · Answer 12

I've also tried :

INSERT into rl_smd01.zegris__01__nloc03..INFO

VALUES (1004589, 33,9887,@tekst)

(4 part naming)

Same result...

A good description of the problem is half the solution.. ;-)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

Gianluca Sartori (6/29/2011)
opc.three (6/29/2011)
I'll assume that means that's the actual query...what do we see from this?
INSERT INTO OPENQUERY(linkedserver, 'select a,b,c from remote_table')
VALUES ( 10, 20, '30')
I don't expect it to be any different on the precision error.
On the performance side, depending on the OLEDB implementation, it could make a huge difference.
Usually, insert/updates on OPENQUERY results are performed with a rowset update, which means that the entire rowset could get transferred to SQL Server before the write operation. Limiting rows returned by the OPENQUERY function is almost always a good idea.
Just a quick check: does this work?
EXECUTE('INSERT INTO remote_table VALUES (10, 20, ''30'')') AT linkedserver

It was really just a guess...the rowset did not include the values being passed in the insert so I was not sure if the provider would care about that. This setup is not something I have seen before.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Peter1971 Old Hand Points: 363 More actions · Answer 14

This works!

execute ('INSERT into INFO VALUES (1004589, 33,9887,''tekst'') ') AT rl_smd01

I had to set the linked_server RPC to true to let it work.

Thanks guys, for all the effort.

Do I need to close the topic, or report as RESOLVED?

A good description of the problem is half the solution.. ;-)