update with openquery problem

[oakpark5]

OK, i've gotten a select and an insert to work but i cant seem to get the update function to work. I keep on getting an error. Error 7399: OLE DB provider 'MSDASQL' reported an error missing operand. Here is my coding:

SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO
create procedure PKUpdate
@PASSWORD nvarchar(10),
@CUSTNO nvarchar(6)
AS
UPDATE OPENQUERY(tester, 'select PASSWORD from test1.dbf where CUSTNO = @CUSTNO')
set PASSWORD = @PASSWORD
UPDATE testsql set PASSWORD = @PASSWORD where CUSTNO = @CUSTNO
GO

Software Engineer
Team Lead
damn bill gates, he has everything

 

[hmckillop]

Sounds that your linked server "tester" isnt set up correct.
Can you post the code you use for linking the server.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[oakpark5]

Here is my code, I dont think anything is wrong with the linked server aspect as I can select and insert..but ya never know.

USE master
GO

exec sp_addlinkedserver
@server='PK',
@srvproduct=' ',
@provider='MSDASQL',
@datasrc=NULL,
@location=NULL,
@provstr='Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=v:\cnaz;SourceType=DBF;Exclusive=NO;BackgroundFetch=YES;Collate=Machine;NULL=YES;Deleted=YES;'

Thanks for the help

Software Engineer
Team Lead
damn bill gates, he has everything

 

[philhege]

You'll have to construct the string for the update statement in the OPENQUERY. The variable is not passed to the link server; it doesn't know what @PASSWORD is.

Additionally, concatenation won't pass through the OPENQUERY statement, so you'll have to construct it dynamically.

So,
declare @sql varchar(1024)

set @sql = 'UPDATE OPENQUERY(tester, ''select PASSWORD from test1.dbf where CUSTNO = '''+@CUSTNO+'''''')
set @sql = @sql + 'set PASSWORD = '''+@PASSWORD+''''
exec (@SQL)

HTH,



Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[oakpark5]

Alright I'll give that a try, thanks for all your help.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[oakpark5]

I'm still getting a syntax error...after the ")" next to @CUSTNO. I'm guessing it has something to do with the number of quotes...

SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO
create procedure PKUpdate
@PASSWORD nvarchar(10),
@CUSTNO nvarchar(6),
@SQL nvarchar(1024)
AS
set @sql = 'UPDATE OPENQUERY(tester, ''select PASSWORD from test1.dbf where CUSTNO = '''+@CUSTNO+'''''')
set @sql = @sql + 'set PASSWORD = '''+@PASSWORD+''''
exec (@SQL)
UPDATE testsql set PASSWORD = @PASSWORD where CUSTNO = @CUSTNO
GO

Software Engineer
Team Lead
damn bill gates, he has everything