syntax help for an update

[oakpark5]

I cant seem to get the correct syntax for this procedure, I think it has to do with my quotes, i think i'm doing them right? Thanks in advance, appreciate it.


CREATE PROCEDURE PKUpdateF
@password as nvarchar(6),
@custno as nvarchar(6),
@sql as varchar(1024)
AS
set @sql = UPDATE OPENQUERY(tester, 'select password from test1.dbf where custno ='"+custno+"'')
set @sql = @sql + 'set password='"+@password"''
exec(@sql)

Software Engineer
Team Lead
damn bill gates, he has everything

 

[mrdenny]

It should look more like this. I haven't used openquery in a while, but something like this should work. Your quotes were all hosed up. I'm assuming that the table that you are trying to update is in some other database that isn't sql?

CREATE PROCEDURE PKUpdateF
@password as nvarchar(6),
@custno as nvarchar(6),
@sql as varchar(1024)
AS
UPDATE OPENQUERY(tester, 'select password from test1.dbf where custno ='+@custno+') a
set password='+@password

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[oakpark5]

Yes I'm calling a stored procedure from a asp page and its updating a sql table (which works) and a foxpro table (which doesnt work). Now i've been able to insert/select but not update the foxpro tables. At least i know its something with the syntax and not anything else. Your syntax i dont think was right..thanks for the help though, if you think there is anything else i should try let me know,

thanks

Software Engineer
Team Lead
damn bill gates, he has everything

 

[nigelrivett]

something like
set @sql = 'UPDATE OPENQUERY(tester, ''select password from test1.dbf where custno ='+convert(varchar(20),@custno)+''')'
set @sql = @sql + ' set password='''+@password + ''''
exec(@sql)
print the string to check the format

but easier something like
set @sql = 'select password from test1.dbf where custno ='+convert(varchar(20),@custno)
update OPENQUERY(tester, @sql) set password = @password

Depends on whether the provider supports ths sort of thing.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[oakpark5]

nigerlrivett, thanks for the help. As I am a bit of a novice at this, i'm trying to execute the stored procedure but i'm not sure how to go about declaring the @sql variable in the execution. Thanks for the help.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLBill]

What do you mean by declaring it in the execution?

Just add a DECLARE @sql VARCHAR(100). Then the SET command. Then you run the @sql by using exec(@sql).

Three easy steps..
1. DECLARE the size of @sql
2. SET the value of the variable
3. EXECute the variable.

Is that what you are looking for?

-SQLBill