Query cannot be updated because it contains no searchable columns to use as key

Question

Post reply

Query cannot be updated because it contains no searchable columns to use as key

Nelson Viggiani-332216

Ten Centuries

Points: 1264
More actions
December 20, 2006 at 9:22 am

#115840

I'm sql server 2005 and run this query:
Insert
into openquery(LPUSA01,'select client_number,client_name,bill_to_name,account_exec_name,invoice_number,invoice_type,cast(invoice_date as char(8000)),cast(invoice_due_date as char(8000)),invoice_gross,invoice_header_text, billtoname_bz,invoice_number_bz,headerTXT_bz,onloadtotalchrg_bz from BZRCRP')VALUES (123456,'test1','test2','Nelson','12345678','A',2006-12-12,2006-12-12,10000,'test3','B','A','A',1000)
getting this error:
Query cannot be updated because it contains no searchable columns to use as key

with the same liked server I can select and delete but I cannot Insert...
any ideas??? thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

Is your error anything to do with your date column insert?

when you insert a date without single quote you will be treated differently...

Try adding single quotes around your date values..

create table #test (id int, dt datetime)

insert into #test

select 1, 2006-12-12

insert into #test

select 1, 2006-12-20

insert into #test

select 1, '2006-12-12'

select * from #test

MohammedU
Microsoft SQL Server MVP

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 2

I did, I add single quotes around the date field and the same error appears.

Insert

into openquery

(

LPUSA01,'select client_number,client_name,bill_to_name,account_exec_name,

invoice_number,invoice_type,cast(invoice_date as char(8000)),cast(invoice_due_date as char(8000)),invoice_gross,invoice_header_text,

billtoname_bz,invoice_number_bz,headerTXT_bz,onloadtotalchrg_bz from BZRCRP'

)

VALUES (123456,'test1','test2','Nelson','12345678','A','2006-12-12','2006-12-12',10000,'test3','B','A','A',1000)

MohammedU SSCoach Points: 19644 More actions · Answer 3

I don't know but try 4 part table name instead of openquery...

And Why are you using char(8000) when casting date column...Change it to char(10).

MohammedU
Microsoft SQL Server MVP

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 4

waht you mean 4 part table instead of openquery ???

the reason I'm using char(8000) is I spent the last 3 days trying to figure out and If I change back

to char(10) the select statement do not bring all the records, just bring part of then, so I fix that way

thanks

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 5

In my case How do I use the four part table ???

something like this..

select

* from LPUSA01.EBSTSTNEW..BZRCRP

it says missing the schema name, ????

thanks

SQLBill SSC Guru Points: 51440 More actions · Answer 6

As someone else said...this is just plain wrong.

cast(invoice_date as char(8000)),cast(invoice_due_date as char(8000)),

A datetime value is at the most 22 characters (1234-67-90 23:56:89.123). Change those values. If it doesn't return what you want, something else is wrong.

Can you provide us with your schema (table structure), sample data and what you want returned?

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 7

There seems to be another problem also.

You are doing an INSERT. What is the purpose of the SELECT?

An INSERT is done one of two basic ways:

1. INSERT INTO tablename ()

VALUES()

or

2. INSERT INTO tablename ()

SELECT FROM tablename

But the two are NOT combined.

So, again I ask - what ARE you trying to do?

-SQLBill

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 8

I'm trying to insert data from SQL 2005 to iSeries AS400

I create a linked server and :

if I run this query:

select

* from openquery(LPUSA01,'select * from BZRCRP')

I got only one record

if I run this query:

select

* from openquery(LPUSA01,'select client_number,client_name,bill_to_name,account_exec_name,invoice_number,invoice_type,cast(invoice_date as char(8000)) as invoice_date,cast(invoice_due_date as char(8000)) as invoice_due_date,invoice_gross,invoice_header_text, billtoname_bz,invoice_number_bz,headerTXT_bz,onloadtotalchrg_bz from BZRCRP')

I got all the records,

NOW, what I need to do is : INSERT DATA from SQL to AS400;

this is the query

Insert

into openquery

(

LPUSA01,'select client_number,client_name,bill_to_name,account_exec_name,invoice_number,invoice_type,cast(invoice_date as char(8000)),cast(invoice_due_date as char(8000)),invoice_gross,invoice_header_text, billtoname_bz,invoice_number_bz,headerTXT_bz,onloadtotalchrg_bz from BZRCRP')VALUES (123456,'test1','test2','Nelson','12345678','A','2006-12-12','2006-12-12',10000,'test3','B','A','A',1000)

and I'm getting this error:

OLE DB provider "MSDASQL" for linked server "LPUSA01" returned message "Query cannot be updated because it contains no searchable columns to use as a key.".

this is the schema:

client_number numeric 6,0 not null,client_name char(30) not null,Bill_to_name char(30) not null,

account_exec-name char(30) not null,Invoice_number numeric 8 not null PK,Invoice_date date not null,

invoice_due_date date not null,Invoice_gross numeric(10,2) not null,Invoice_header_text char(30) not null,

Billtoname_BZ varchar(100) null,Invoice_number_BZ varchar(100) null,HeaderTXT_BZ varchar(100)null,

onloadtotalchrg_bz numeric(10,2) not null.

This schema is on AS400 I need to copy the table into SQL manipulate the data and return back to as400.

thanks

SQLBill SSC Guru Points: 51440 More actions · Answer 9

It looks like you are actually trying to do TWO inserts at one time. Can't do it that way.

1st: do the INSERT that uses the SELECT:

Insert into openquery (LPUSA01,'select client_number,client_name,bill_to_name,account_exec_name,invoice_number,invoice_type,cast(invoice_date as char(8000)),cast(invoice_due_date as char(8000)),invoice_gross,invoice_header_text, billtoname_bz,invoice_number_bz,headerTXT_bz,onloadtotalchrg_bz from BZRCRP')

2nd: Do the Insert that adds the additional row:

Insert into openquery

(LPUSA01,(VALUES (123456,'test1','test2','Nelson','12345678','A','2006-12-12','2006-12-12',10000,'test3','B','A','A',1000))

Or something like that....I don't do 'openquery' stuff, so I don't know the exact format. But I do know that you can't do both an INSERT using a SELECT and VALUES at the same time.

-SQLBill

Nelson Viggiani-332216 Ten Centuries Points: 1264 More actions · Answer 10

That way doesn't work either because almost all the fields are not null, I cannot insert a row and then insert the values it won't work.

any other sugestions???

thanks

Jeff Gray SSChampion Points: 10667 More actions · Answer 11

Hi Nelson,

What provider are you using for the linked server? Are you using MSDASQL connected to a client ODBC DSN?

Or are you using an OLEDB provder for AS400 directly in the linked server....

This error if you are using MSDASQL usually indicates that the provider is trying to create a cursor, but can't figure out what to use as a primary key.

Another problem might be the cast(s) in the select statement. Have you tried using Select * in the OpenQuery?

Paul Bibby SSC Veteran Points: 239 More actions · Answer 12

Hi SQL Bill,

I think you are getting confused.

If you check out the syntax for an openquery, it is as used. You don't do "two queries" as you have suggested. The syntax does look a bit strange, I'll grant you that {:o)

Anywhoo, I'm having a similar problem and I believe it is to do with the fact the table that is being inserted in to has an auto increment field as the id, which is not in the select query (since you don't want to insert the id)