OPENQUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OPENQUERY

Author

Topic

tjforce
Starting Member

8 Posts

Posted - 2005-10-31 : 15:46:52

I have a question about using an OPENQUERY with the UPDATE SYNTAX. In order to see data from a Progress 83B database I have to use the OPENQUERY syntax:

SELECT field1, field2
FROM OPENQUERY(PROGESSDATABASESERVER,
'SELECT field1, field2
FROM theTable
WHERE field3 = ''somevalue''')

My question is what is the syntax for an OPENQUERY to accomplish an UPDATE statement?

Kristen
Test

22859 Posts

Posted - 2005-11-01 : 01:28:33

Never tried it, but I think you are supposed to do it like this:


UPDATE U
SET field1 = 0, 
    field2 = getdate()
FROM OPENQUERY(PROGESSDATABASESERVER,
'SELECT field1, field2
FROM theTable
WHERE field3 = ''somevalue''') AS U

Kristen

tjforce
Starting Member

8 Posts

Posted - 2005-11-01 : 09:24:37

In Query Analyzer I tried your suggestion. The query will Parse ok, however, when I try to Execute it I get the following error message:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'U'.

Any ideas?

Kristen
Test

22859 Posts

Posted - 2005-11-01 : 10:29:24

Perhaps it can't hack the alias name then. Turn it round and try this perhaps?


UPDATE OPENQUERY(PROGESSDATABASESERVER,
'SELECT field1, field2
FROM theTable
WHERE field3 = ''somevalue''')
SET field1 = 0, 
    field2 = getdate()

Kristen

tjforce
Starting Member

8 Posts

Posted - 2005-11-01 : 10:42:06

The query you provided also errored out, however, if use the following it works:

UPDATE OPENQUERY(PROGESSDATABASESERVER,
'SELECT *
FROM theTable
WHERE field1 = ''TE1''')
SET field2 = '20'

I had to use the wildcard instead of listing individual field names. Thanks for all of the help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 00:28:42

>>The query you provided also errored out

Because you spelled column names incorrectly?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources