VFP 6.0 using SQL stored procedures

I have a stored procedure in a SQL DB that accepts two parameters. Have
used remote views to access SQL tables but never called a stored
procedure. Tried using the VFP remote view wizard to access and the stored
procedure isn't listed.

What is the syntax to access a SQL stored procedure through remote view?
Or, if that's the wrong way how to I access the stored procedure another
way? Not talking about a VFP stored procedure, its on the SQL side.

My code currently looks something like (stripped it down for brievity):

** Create connection
Create CONNECTION MyConn;
DATASOURCE alltrim(config.sourceodbcn);
USERID alltrim(config.sourceodbcu);
PASSWORD alltrim(config.sourceodbcp)

** Create view
Create SQL VIEW 'vEmp' ;
REMOTE CONNECTION 'MyConn' ;
as SELECT *;
FROM EMPLOYEES EMPLOYEES;
WHERE Employees.HireDate = ?dDate

** Use the view
dDate=ctod('02/05/2005')
Use vEmp IN 0

Thierry
Tue Mar 22 11:20:46 CST 2005


CREATE SQL VIEW vEmp CONNECTION MyConn ;
as EXECUTE yourStoredProcedure ?dDate


--
Thierry


"KcK" <no@spam.com> a écrit dans le message de news:
qYX%d.12428$gx3.9924@tornado.rdc-kc.rr.com...
>I have a stored procedure in a SQL DB that accepts two parameters. Have
> used remote views to access SQL tables but never called a stored
> procedure. Tried using the VFP remote view wizard to access and the stored
> procedure isn't listed.
>
> What is the syntax to access a SQL stored procedure through remote view?
> Or, if that's the wrong way how to I access the stored procedure another
> way? Not talking about a VFP stored procedure, its on the SQL side.
>
> My code currently looks something like (stripped it down for brievity):
>
> ** Create connection
> Create CONNECTION MyConn;
> DATASOURCE alltrim(config.sourceodbcn);
> USERID alltrim(config.sourceodbcu);
> PASSWORD alltrim(config.sourceodbcp)
>
> ** Create view
> Create SQL VIEW 'vEmp' ;
> REMOTE CONNECTION 'MyConn' ;
> as SELECT *;
> FROM EMPLOYEES EMPLOYEES;
> WHERE Employees.HireDate = ?dDate
>
> ** Use the view
> dDate=ctod('02/05/2005')
> Use vEmp IN 0
>
>
>
>
>
>
>
>

Craig
Tue Mar 22 11:27:03 CST 2005

You can't call a stored procedure through a remote view. You'll have to use
SQL Pass Through or ADO.

--
Craig Berntson
MCSD, Visual FoxPro MVP
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net


"KcK" <no@spam.com> wrote in message
news:qYX%d.12428$gx3.9924@tornado.rdc-kc.rr.com...
>I have a stored procedure in a SQL DB that accepts two parameters. Have
> used remote views to access SQL tables but never called a stored
> procedure. Tried using the VFP remote view wizard to access and the stored
> procedure isn't listed.
>
> What is the syntax to access a SQL stored procedure through remote view?
> Or, if that's the wrong way how to I access the stored procedure another
> way? Not talking about a VFP stored procedure, its on the SQL side.
>
> My code currently looks something like (stripped it down for brievity):
>
> ** Create connection
> Create CONNECTION MyConn;
> DATASOURCE alltrim(config.sourceodbcn);
> USERID alltrim(config.sourceodbcu);
> PASSWORD alltrim(config.sourceodbcp)
>
> ** Create view
> Create SQL VIEW 'vEmp' ;
> REMOTE CONNECTION 'MyConn' ;
> as SELECT *;
> FROM EMPLOYEES EMPLOYEES;
> WHERE Employees.HireDate = ?dDate
>
> ** Use the view
> dDate=ctod('02/05/2005')
> Use vEmp IN 0
>
>
>
>
>
>
>
>

Thierry
Tue Mar 22 11:37:23 CST 2005

Why not ?


--
Thierry


"Craig Berntson" <iamcraig@iamcraigberntson.com> a écrit dans le message de
news: O1paeRwLFHA.1144@TK2MSFTNGP09.phx.gbl...
> You can't call a stored procedure through a remote view. You'll have to
> use SQL Pass Through or ADO.
>
> --
> Craig Berntson
> MCSD, Visual FoxPro MVP
> www.craigberntson.com
> Salt Lake City Fox User Group
> www.slcfox.org
> www.foxcentral.net
>
>
> "KcK" <no@spam.com> wrote in message
> news:qYX%d.12428$gx3.9924@tornado.rdc-kc.rr.com...
>>I have a stored procedure in a SQL DB that accepts two parameters. Have
>> used remote views to access SQL tables but never called a stored
>> procedure. Tried using the VFP remote view wizard to access and the
>> stored
>> procedure isn't listed.
>>
>> What is the syntax to access a SQL stored procedure through remote view?
>> Or, if that's the wrong way how to I access the stored procedure another
>> way? Not talking about a VFP stored procedure, its on the SQL side.
>>
>> My code currently looks something like (stripped it down for brievity):
>>
>> ** Create connection
>> Create CONNECTION MyConn;
>> DATASOURCE alltrim(config.sourceodbcn);
>> USERID alltrim(config.sourceodbcu);
>> PASSWORD alltrim(config.sourceodbcp)
>>
>> ** Create view
>> Create SQL VIEW 'vEmp' ;
>> REMOTE CONNECTION 'MyConn' ;
>> as SELECT *;
>> FROM EMPLOYEES EMPLOYEES;
>> WHERE Employees.HireDate = ?dDate
>>
>> ** Use the view
>> dDate=ctod('02/05/2005')
>> Use vEmp IN 0
>>
>>
>>
>>
>>
>>
>>
>>
>
>

KcK
Tue Mar 22 11:55:12 CST 2005

Great! I'll give it a go and thanks!

- Ben


"Thierry" <tper_NOSPAM@vfemail.net> wrote in message
news:OuGcGNwLFHA.1176@TK2MSFTNGP15.phx.gbl...
>
> CREATE SQL VIEW vEmp CONNECTION MyConn ;
> as EXECUTE yourStoredProcedure ?dDate
>
>
> --
> Thierry
>
>
> "KcK" <no@spam.com> a écrit dans le message de news:
> qYX%d.12428$gx3.9924@tornado.rdc-kc.rr.com...
> >I have a stored procedure in a SQL DB that accepts two parameters. Have
> > used remote views to access SQL tables but never called a stored
> > procedure. Tried using the VFP remote view wizard to access and the
stored
> > procedure isn't listed.
> >
> > What is the syntax to access a SQL stored procedure through remote view?
> > Or, if that's the wrong way how to I access the stored procedure another
> > way? Not talking about a VFP stored procedure, its on the SQL side.
> >
> > My code currently looks something like (stripped it down for brievity):
> >
> > ** Create connection
> > Create CONNECTION MyConn;
> > DATASOURCE alltrim(config.sourceodbcn);
> > USERID alltrim(config.sourceodbcu);
> > PASSWORD alltrim(config.sourceodbcp)
> >
> > ** Create view
> > Create SQL VIEW 'vEmp' ;
> > REMOTE CONNECTION 'MyConn' ;
> > as SELECT *;
> > FROM EMPLOYEES EMPLOYEES;
> > WHERE Employees.HireDate = ?dDate
> >
> > ** Use the view
> > dDate=ctod('02/05/2005')
> > Use vEmp IN 0
> >
> >
> >
> >
> >
> >
> >
> >
>
>

KcK
Tue Mar 22 12:09:39 CST 2005

Thierry:

I get an error when I try and run, it says it's invalid and I think its
failing on the EXECUTE piece. Is this compatible with VFP6?

- Ben

"KcK" <no@spam.com> wrote in message
news:KXY%d.12600$hu3.6585@twister.rdc-kc.rr.com...
> Great! I'll give it a go and thanks!
>
> - Ben
>
>
> "Thierry" <tper_NOSPAM@vfemail.net> wrote in message
> news:OuGcGNwLFHA.1176@TK2MSFTNGP15.phx.gbl...
> >
> > CREATE SQL VIEW vEmp CONNECTION MyConn ;
> > as EXECUTE yourStoredProcedure ?dDate
> >
> >
> > --
> > Thierry
> >
> >
> > "KcK" <no@spam.com> a écrit dans le message de news:
> > qYX%d.12428$gx3.9924@tornado.rdc-kc.rr.com...
> > >I have a stored procedure in a SQL DB that accepts two parameters.
Have
> > > used remote views to access SQL tables but never called a stored
> > > procedure. Tried using the VFP remote view wizard to access and the
> stored
> > > procedure isn't listed.
> > >
> > > What is the syntax to access a SQL stored procedure through remote
view?
> > > Or, if that's the wrong way how to I access the stored procedure
another
> > > way? Not talking about a VFP stored procedure, its on the SQL side.
> > >
> > > My code currently looks something like (stripped it down for
brievity):
> > >
> > > ** Create connection
> > > Create CONNECTION MyConn;
> > > DATASOURCE alltrim(config.sourceodbcn);
> > > USERID alltrim(config.sourceodbcu);
> > > PASSWORD alltrim(config.sourceodbcp)
> > >
> > > ** Create view
> > > Create SQL VIEW 'vEmp' ;
> > > REMOTE CONNECTION 'MyConn' ;
> > > as SELECT *;
> > > FROM EMPLOYEES EMPLOYEES;
> > > WHERE Employees.HireDate = ?dDate
> > >
> > > ** Use the view
> > > dDate=ctod('02/05/2005')
> > > Use vEmp IN 0
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

Thierry
Tue Mar 22 13:30:15 CST 2005

Sorry, when I try to use parameters, this seem to be a problem with VFP6.

OK with VFP8/9.

You should use SqlExec() instead of view.


--
Thierry


"KcK" <no@spam.com> a écrit dans le message de news:
h9Z%d.12601$hu3.3777@twister.rdc-kc.rr.com...
> Thierry:
>
> I get an error when I try and run, it says it's invalid and I think its
> failing on the EXECUTE piece. Is this compatible with VFP6?
>
> - Ben
>
> "KcK" <no@spam.com> wrote in message
> news:KXY%d.12600$hu3.6585@twister.rdc-kc.rr.com...
>> Great! I'll give it a go and thanks!
>>
>> - Ben
>>
>>
>> "Thierry" <tper_NOSPAM@vfemail.net> wrote in message
>> news:OuGcGNwLFHA.1176@TK2MSFTNGP15.phx.gbl...
>> >
>> > CREATE SQL VIEW vEmp CONNECTION MyConn ;
>> > as EXECUTE yourStoredProcedure ?dDate
>> >
>> >
>> > --
>> > Thierry
>> >
>> >
>> > "KcK" <no@spam.com> a écrit dans le message de news:
>> > qYX%d.12428$gx3.9924@tornado.rdc-kc.rr.com...
>> > >I have a stored procedure in a SQL DB that accepts two parameters.
> Have
>> > > used remote views to access SQL tables but never called a stored
>> > > procedure. Tried using the VFP remote view wizard to access and the
>> stored
>> > > procedure isn't listed.
>> > >
>> > > What is the syntax to access a SQL stored procedure through remote
> view?
>> > > Or, if that's the wrong way how to I access the stored procedure
> another
>> > > way? Not talking about a VFP stored procedure, its on the SQL side.
>> > >
>> > > My code currently looks something like (stripped it down for
> brievity):
>> > >
>> > > ** Create connection
>> > > Create CONNECTION MyConn;
>> > > DATASOURCE alltrim(config.sourceodbcn);
>> > > USERID alltrim(config.sourceodbcu);
>> > > PASSWORD alltrim(config.sourceodbcp)
>> > >
>> > > ** Create view
>> > > Create SQL VIEW 'vEmp' ;
>> > > REMOTE CONNECTION 'MyConn' ;
>> > > as SELECT *;
>> > > FROM EMPLOYEES EMPLOYEES;
>> > > WHERE Employees.HireDate = ?dDate
>> > >
>> > > ** Use the view
>> > > dDate=ctod('02/05/2005')
>> > > Use vEmp IN 0
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>

Craig
Wed Mar 23 09:56:45 CST 2005

By design, the view designer, and in turn the CREATE SQL VIEW command don't
allow SPs.

--
Craig Berntson
MCSD, Visual FoxPro MVP
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net


"Thierry" <tper_NOSPAM@vfemail.net> wrote in message
news:%23STHZWwLFHA.2736@TK2MSFTNGP09.phx.gbl...
> Why not ?
>
>
> --
> Thierry
>
>