Visual FoxPro OPENQUERY Update Statement Blues

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

Import/Export (DTS) and Replication (2000)

Visual FoxPro OPENQUERY Update Statement Blues

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-20 : 08:32:56

phil writes "SQL Server 2000
Visual FoxPro 6.0 DBC linked server

Here's a very simple update statement on a Visual FoxPro linked server, constructed according to MS's instructions for OPENQUERY update statements:

update openquery(LINKEDDBC,'select * from mytable where myfield= "myvalue"')
set myotherfield= 'newvalue'

SQL frowns, scratches its head for a few seconds, and then returns

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too long.]

Doesn't look too long to me.

The problem is that Mytable has a gazillion columns (well, around 216) and a dearth of no-kidding key fields.

If you cut down the internal select statement to exactly and only the fields you need, the statement works:

update openquery(LINKEDDBC,'select myfield, myotherfield from mytable where myfield= "myvalue"')
set myotherfield= 'newvalue'

Well, sort of. In the FP SQL world, character fields have a nasty habit of saying "Hey, that's me too!" In my example above, the interior SELECT returned four records, not one. The values returned for myfield were something like

"myvalue"
"myvaluealso"
"myvainbrotherinlaw"
"myfunnyvalentine"

I experimented with a number of RTRIMs, ALLTRIMs, PADRs and the like with no success. Turns out it's no problem. Limit the final result in the SQL Server statement, not the OPENQUERY:

update openquery(LINKEDDBC,'select * from mytable where myfield= "myvalue"')
set myotherfield= 'newvalue' where myfield='myvalue'

SQL pays better attention than VFP when it comes to narrowing the results.

Now I have the UPDATE statement that I need for a Visual FoxPro linked server, and hopefully you have a how-to article. <aside>It's not easy to find information about other-than-select-style OPENQUERY statements. BOL is bereft, and so are a lot of other sources. I understand that the capabilities are driver-specific, but ya gotta start somewhere.</aside>

Rgds,

Phil Hegedusich
Senior Web Developer
IIMAK"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources