Visual FoxPro SQL Queries - RE: Using VFP to pull SQL2000 using vfp SQLEXEC statement

Author

RE: Using VFP to pull SQL2000 using vfp SQLEXEC statement

msegal

2006-01-04, 8:25 pm

I am able to pull data from a SQL server using a simple select statement with
the SQLEXEC in VFP.

I am trying to select only a few records from the SQL server database into
new fox table.

This works for me on with this simple select statment.

htest = SQLEXEC(1, 'SELECT * FROM MPType', 'MyCursor')

But if I had a select statement like this one...that I fist test in SQL
server Query Analyzer.....can it be put into SQLEXEC statement too?

SELECT dbo.Event.EventDesc, dbo.Event.Counter, dbo.Event.DateCommenced,
dbo.Event.VenueCounter, dbo.Venue.Counter AS Expr1,
dbo.Venue.StreetAddress3, dbo.Venue.StreetAddress4, dbo.Venue.VenueName,
dbo.Venue.U_VenueCity, dbo.Event.U_EventCode

FROM dbo.Event LEFT OUTER JOIN
dbo.Venue ON dbo.Event.VenueCounter = dbo.Venue.Counter ;

WHERE (dbo.Event.U_EventCode = '2250')

Thank for Any help!

Regards,
Mike Segal

Jack Jackson

2006-01-04, 8:25 pm

On Wed, 4 Jan 2006 12:17:03 -0800, msegal
<msegal@discussions.microsoft.com> wrote:

>I am able to pull data from a SQL server using a simple select statement with
>the SQLEXEC in VFP.
>
>I am trying to select only a few records from the SQL server database into
>new fox table.
>
>This works for me on with this simple select statment.
>
>htest = SQLEXEC(1, 'SELECT * FROM MPType', 'MyCursor')
>
>
>
>But if I had a select statement like this one...that I fist test in SQL
>server Query Analyzer.....can it be put into SQLEXEC statement too?
>
> SELECT dbo.Event.EventDesc, dbo.Event.Counter, dbo.Event.DateCommenced,
>dbo.Event.VenueCounter, dbo.Venue.Counter AS Expr1,
> dbo.Venue.StreetAddress3, dbo.Venue.StreetAddress4, dbo.Venue.VenueName,
>dbo.Venue.U_VenueCity, dbo.Event.U_EventCode
>
>FROM dbo.Event LEFT OUTER JOIN
>dbo.Venue ON dbo.Event.VenueCounter = dbo.Venue.Counter ;
>
>WHERE (dbo.Event.U_EventCode = '2250')

I don't see any reason why it wouldn't work. Did you try it?

TEXT/ENDTEXT is a good way to get long strings like this into a VFP
variable.

TEXT TO cSQL TEXTMERGE NOSHOW
SELECT e.EventDesc, e.Counter,
e.DateCommenced,
e.VenueCounter, v.Counter AS Expr1,
v.StreetAddress3, v.StreetAddress4,
v.VenueName,
v.U_VenueCity, e.U_EventCode
FROM dbo.Event e
LEFT OUTER JOIN dbo.Venue v ON
e.VenueCounter = v.Counter
WHERE (e.U_EventCode = '2250')
ENDTEXT
htest = SQLEXEC(1, cSQL, "MyCursor")

It saves a lot of typing to alias the table names.

Cindy Winegarden

2006-01-05, 11:24 am

Hi Mike,

When you're doing SQL Pass-through you're basically sending T-SQL code to
SQL Server in the command string, not VFP SQL, so you can use anything that
SQL Server understands.

I'm with Jack on using Text...EndText. It makes it really easy to get the
code working in QA and then paste it directly into your Fox code window.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"msegal" <msegal@discussions.microsoft.com> wrote in message
news:B8E7623F-9077-4E4A-9D62- 782D93AE29FD@microso
ft.com...
>I am able to pull data from a SQL server using a simple select statement
>with
> the SQLEXEC in VFP.
>
> I am trying to select only a few records from the SQL server database into
> new fox table.
>
> This works for me on with this simple select statment.
>
> htest = SQLEXEC(1, 'SELECT * FROM MPType', 'MyCursor')
>
>
>
> But if I had a select statement like this one...that I fist test in SQL
> server Query Analyzer.....can it be put into SQLEXEC statement too?
>
> SELECT dbo.Event.EventDesc, dbo.Event.Counter,
> dbo.Event.DateCommenced,
> dbo.Event.VenueCounter, dbo.Venue.Counter AS Expr1,
> dbo.Venue.StreetAddress3, dbo.Venue.StreetAddress4, dbo.Venue.VenueName,
> dbo.Venue.U_VenueCity, dbo.Event.U_EventCode
>
> FROM dbo.Event LEFT OUTER JOIN
> dbo.Venue ON dbo.Event.VenueCounter = dbo.Venue.Counter ;
>
> WHERE (dbo.Event.U_EventCode = '2250')
>
>
> Thank for Any help!
>
> Regards,
> Mike Segal

msegal

2006-01-05, 11:24 am

Thanks very much Jack!

This did solve my problem.

Thank You!
Mike
segal

"Jack Jackson" wrote:

> On Wed, 4 Jan 2006 12:17:03 -0800, msegal
> <msegal@discussions.microsoft.com> wrote:
>
>
> I don't see any reason why it wouldn't work. Did you try it?
>
> TEXT/ENDTEXT is a good way to get long strings like this into a VFP
> variable.
>
> TEXT TO cSQL TEXTMERGE NOSHOW
> SELECT e.EventDesc, e.Counter,
> e.DateCommenced,
> e.VenueCounter, v.Counter AS Expr1,
> v.StreetAddress3, v.StreetAddress4,
> v.VenueName,
> v.U_VenueCity, e.U_EventCode
> FROM dbo.Event e
> LEFT OUTER JOIN dbo.Venue v ON
> e.VenueCounter = v.Counter
> WHERE (e.U_EventCode = '2250')
> ENDTEXT
> htest = SQLEXEC(1, cSQL, "MyCursor")
>
> It saves a lot of typing to alias the table names.
>
>

msegal

2006-01-05, 11:24 am

Thanks Cindy!

I am liking both worlds now.

Regards,
Mike

"Cindy Winegarden" wrote:

> Hi Mike,
>
> When you're doing SQL Pass-through you're basically sending T-SQL code to
> SQL Server in the command string, not VFP SQL, so you can use anything that
> SQL Server understands.
>
> I'm with Jack on using Text...EndText. It makes it really easy to get the
> code working in QA and then paste it directly into your Fox code window.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
>
>
> "msegal" <msegal@discussions.microsoft.com> wrote in message
> news:B8E7623F-9077-4E4A-9D62- 782D93AE29FD@microso
ft.com...
>
>
>

Sponsored Links