stored procedure execution

[oakpark5]

OK this is what I got, i have a stored procedure that updates a foxpro database through a linked server. Here is the stored procedure:

CREATE procedure PKUpdateF
@custno as varchar(6),
@password as varchar(6),
@sql as varchar
AS
set @sql = 'UPDATE OPENQUERY(tester, ''select password from test1.dbf where custno ='+convert(varchar(20),@custno)+''')'
set @sql = @sql + ' set password='''+@password + ''''
exec(@sql)
GO

Now I'm trying to execute it in query analyzer and I'm not sure what to set the @sql variable to. My code looks like this:

Use PKPRO
GO
exec PKUpdateF
@password = hello,
@custno = test,
@sql = ?(dont know what to set it to)

Thanks in advance!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[cLFlaVA]

Why are you even passing the @sql when you're just setting it within the SP itself?

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[oakpark5]

When I write:

use PKPRO
go
exec PKUpdateF
@custno = test,
@password = hello

This is the response I get:

Procedure 'PKUpdateF' expects parameter '@sql', which was not supplied.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLSister]

AS a guess I would think it would be the selct statment you have inthe openquery, but you would have to read the sp to find out for sure.

Questions about posting. See faq183-874

 

[cLFlaVA]

Yes, clearly it will state that. What I'm asking is, why do you even have a parameter set up in your Stored Procedure?

This would make much more sense:

CREATE procedure PKUpdateF

  -- parameters
  @custno as varchar(6),
  @password as varchar(6)

AS

  -- variables
  declare @sql as nvarchar(200)

  set @sql = 'UPDATE OPENQUERY(tester, ''select password from test1.dbf where custno ='+convert(varchar(20),@custno)+''')'
  set @sql = @sql + ' set password='''+@password + ''''
  exec(@sql)
GO

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[oakpark5]

Almost....it looks as if the query is looking for a colum named custno. So when I say the @custno is equal to "chris1" it returns this. Chris1 is a specified entry in the table under the column custno. I'm kinda at a loss now...thanks

Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]SQL: Column 'CHRIS1' is not found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned 0x80040e14].


Software Engineer
Team Lead
damn bill gates, he has everything

 

[oakpark5]

OK for some reason when i say @custno = chris1, it looks for the column 'chris1'. 'chris1' is an entry in the column custno. I dont know what else to do with this error, thanks for all the help, if anyone can think of anything else i would appreciate it greatly, thanks.

Software Engineer
Team Lead
damn bill gates, he has everything