RE: Using VFP to pull SQL2000 using vfp SQLEXEC statement

[msegal]

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]

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]

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_wi...@msn.com www.cindywinegarden.com

"msegal" <mse...@discussions.microsoft.com> wrote in message
news:B8E7623F-9077-4E4A...@microsoft.com...

[msegal]

Thanks very much Jack!

This did solve my problem.

Thank You!
Mike
segal

[msegal]

Thanks Cindy!

I am liking both worlds now.

Regards,
Mike

[QuanLV]

I have code :

Text to sSql noshow

/* --A

code here, 5000 character, in 300 lines
and every line not more than 255 characters

--B
*/

Endtext

=SQLEXEC( 1, sSql, 'MyCursor')

If run in VFP, I get -1 : error but unknow it.

If I copy from A to B and paste to QA, It run very good

Why ?

Help me.

Thank any help !

[Bernhard Sander]

Hi QuanLV

> =SQLEXEC( 1, sSql, 'MyCursor')
>
> If run in VFP, I get -1 : error but unknow it.

If SqlExec returns -1 then use aError to get more info about the error.
See topic "Handling SQL Pass-Through Errors" in foxpro help

Regards
Bernhard Sander