Thank you all for the responses! There were great links to helpful articles especially those on the Wikis site.

I am creating my stored procedures in SQL Server 2000 - so I am just needing to connect, execute the stored procedures and return the resulting data into a cursor so I can work with it. I wanted to do this without having to create a DSN on every client.

After reading the information and your posts I managed to connect to SQL and execute a stored procedure on northwind using this:

PUBLIC gnConnHandle
LOCAL llConnected, lcCommand
STORE -1 TO gnConnHandle
STORE .F. TO llConnected
STORE "" TO lcCommand
STORE SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=PREXP01;UID=sa;PWD=mypass;APP=Microsoft® Visual FoxPro®;WSID=PREXP01;DATABASE=Northwind") TO gnConnHandle
IF gnConnHandle < 0
   =MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
   =MESSAGEBOX('Connection made', 48, 'SQL Connect Message')
   llConnected = .T.
ENDIF
lcCommand = "exec CustOrderHist ALFKI"
=SQLEXEC(gnConnHandle, lcCommand, "curTemp")
SELECT curTemp
BROWSE
USE IN curTemp
IF llConnected THEN
	=SQLDISCONNECT(gnConnHandle)
ENDIF

It works...

1. Is this all OK? Is there anything I am missing?
2. Is it customary to open just one connection to the database when the application is started and then close it when when the app is closed?
3. Lastly - I am used to using transactions in VFP databases with ROLLBACK etc. Can someone provide (or point to) an example of how this is done? Is it a case of multiple SQLEXEC commands wrapped in some procedure or is it all done in one stored procedure?

Thanks again!