>I am running it from Visual Fox pro
>bharat

Public cnSQL As New ADODB.Connection
Public rsWare As New ADODB.Recordset
cnSQL.Open "Provider=MSDASQL;SERVER=HDS_SQLSERVER1;DATABASE=nms_jfm;DRIVER={SQL SERVER};uid=sa;pwd=sa"
rsWare.Open "select * from _4_CLAIM_DBF_TEMP", cnSQL, adOpenKeyset, adLockOptimistic, adCmdText

I asked that because syntax was VB. VFP translation would be :

cnSQL = createobject('ADODB.Connection') && VB new correspond to createobject
rsWare = createobject('ADODB.Recordset')
cnSQL.Open("Provider=MSDASQL;SERVER=HDS_SQLSERVER1;DATABASE=nms_jfm;DRIVER={SQL SERVER};uid=sa;pwd=sa")
#define adOpenKeySet 1
#define adLockOptimistic 3
#define adCmdText 1
rsWare.Open("select * from _4_CLAIM_DBF_TEMP", cnSQL, adOpenKeyset, adLockOptimistic, adCmdText)

If I'm not wrong MSDASQL is ODBC driver for OLEDB. You could directly use OLEDB driver for SQL server instead :

'Provider=SQLOLEDB.1;SERVER=HDS_SQLSERVER1;DATABASE=nms_jfm;user id=sa;password=sa'


In another message I see that what you want to do is to delete all records :

cnSQL = createobject('ADODB.Connection')
rsWare = createobject('ADODB.Recordset')
cnSQL.Open("Provider=MSDASQL;SERVER=HDS_SQLSERVER1;DATABASE=nms_jfm;DRIVER={SQL SERVER};uid=sa;pwd=sa")
rsWare.Open("delete from _4_CLAIM_DBF_TEMP", cnSQL)

If you really don't need using ADO you might be more comfortable with SQL passthrough :

lnHandle=SQLStringConnect('DRIVER=SQL Server;SERVER=HDS_SQLSERVER1;DATABASE=nms_jfm;uid=sa;pwd=sa')
if lnHandle > 0 && Connected
  lcSQL1 = 'create table _4_CLAIM_DBF_TEST (field1 varchar(30))'
  lcSQL2 = "insert into _4_CLAIM_DBF_TEST (field1) values ('Hello from SQL server')"
  lcSQL3 = 'select * from _4_CLAIM_DBF_TEST'
  lcSQL4 = 'delete from _4_CLAIM_DBF_TEST'
  SQLExec(lnHandle,lcSQL1)
  SQLExec(lnHandle,lcSQL2)
  SQLExec(lnHandle,lcSQL3,'AfterInsert')
  SQLExec(lnHandle,lcSQL4)
  SQLExec(lnHandle,lcSQL3,'AfterDelete')
  SQLDisconnect(lnHandle)
endif
select AfterInsert
brow
select AfterDelete
brow

Cetin