MS SQL 7.0 Inserts & Updates

[JeffGBrown857]

I am trying to perform INSERT and UPDATE statements in MS SQL 7.0 through a linked server set up through a ODBC connection using the MERANT 3.60 32-BIT Progress SQL92 v9.1B drivers.

From what I've read, this should be the standard format for an INSERT:

select * FROM
OPENQUERY ( UniDev, 'INSERT INTO pub."hm-test" (lastname, firstname) VALUES ("Rip", "Tendon")')

But when I execute this from SQL Query Analyzer, I get the following response:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [MERANT][ODBC PROGRESS driver][PROGRESS]Syntax error (7587)]

Is this not the proper syntax for INSERTing a row into a PROGRESS database? Any help that can be provided would be greatly appreciated.

Jeff Brown

 

[Unregistered]

Try this syntax:

INSERT INTO
OPENQUERY(LinkedServerName,'SELECT * FROM TableName')
([field-1],[field-2],[field-3])
values
('value1','value2','value3')

UPDATE
OPENQUERY(LinkedServerName,'SELECT * FROM TableName WHERE field-2 ="criteria" ')
SET [field-1]='value'

 

[JeffGBrown857]

Thanks for the response. I've tried that already; it does not work.

I'm sorry, I should have mentioned that I've already seen the Microsoft KB article

http://support.microsoft.com/support/kb/articles/Q270/1/19.ASP

Which talks about not being able to use INSERT/UDPATE/DELETE queries with the OPENQUERY command. I've tried the workarounds suggested (like the one you posted) but they don't work either.

I haven't used four-part names (linked_server_name.catalog.schema.object_name) yet, but I don't know if PUB is the catalog or the schema or neither and what the exact syntax should be.

Jeff G. Brown
Firstar Bank